Oracle安装os认证,Oracle中两种认证方式：OS认证与口令文件认证

最新推荐文章于 2024-04-20 18:58:30 发布

AI量化数据之佳

最新推荐文章于 2024-04-20 18:58:30 发布

阅读量966

点赞数

文章标签： Oracle安装os认证

首先谈谈Oracle安装与OS用户组.Oracle在安装和维护过程中经常要和操作用户组(OS User Group)打交道，从早前的只有oracle用户和dba组发展到今天11gR2中的grid用户和asm组。

我们在单实例环境中常用的三个操作用户组，分别是:

1、oinstall用户组

oinstall组是Oracle推荐创建的OS用户组之一，建议在系统第一次安装Oracle产品之前创建oinstall组，理论上该oinstall组应当拥有Oracle软件产品目录(例如$CRS_HOME和$ORACLE_HOME)和oracle Inventory信息目录仓库，oracle Inventory信息目录记录了系统上安装过的Oracle产品的记录。

若系统中已有安装过Oracle产品软件，则现有的oracle Inventory目录的所有组必须是今后用来安装新oracle软件产品的用户的主组。

现有的oracle Inventory拥有者组可以通过/etc/oraInst.loc位置文件了解:

inventory_loc=/u01/app/oracle/oraInventory

inst_group=oinstall

若/etc/oraInst.loc位置文件不存在，那么建议创建oinstall用户组，注意在RAC环境中要保持各节点上用户组的GID一致:

# /usr/sbin/groupadd -g GID oinstall

2、OSDBA用户组(dba)

OSDBA是我们必须要创建的一种系统DBA用户组(dba)，若没有该用户组我们将无法安装数据库软件及执行管理数据库的任务。

3、OSOPER用户组(oper)

OSOPER是一种额外的用户组(oper)，我们可以选择要不要创建该用户组，创建该用户组可以满足让os用户行使某些数据库管理权限(包括SYSOPER角色权限)的目的。

创建OSOPER用户组的方法:

# /usr/sbin/groupadd oper

综上所述在单实例环境中Oracle拥有者用户(常用的是oracle)，因该同时是oinstall、dba、oper用户组的成员。同时该用户的主用户组必须是oinstall。

而在11.2的GI/CRS环境中数据库软件拥有者用户(oracle)还必须是asmdba用户组的成员。

usermod -g oinstall -G dba,oper,asmdba oracle

id oracle

uid=54321(oracle) gid=54321(oinstall)

groups=54321(oinstall),54322(dba),701(asmdba),54324(oper)

注意OSDBA和OSOPER用户组都受到$ORACLE_HOME/rdbms/lib/config.c源文件的影响，该文件定义了默认的SS_DBA_GRP “dba”和SS_OPER_GRP “oper”,该源文件内容如下:

SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative

access. */

/* Refer to the Installation and User's

Guide for further information. */

/* IMPORTANT: this file needs to be in sync with

rdbms/src/server/osds/config.c,

specifically regarding the

number of elements in the

ss_dba_grp array.

#define SS_DBA_GRP "dba"

#define SS_OPER_GRP "oper"

#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP}; Oracle11g

Release2中oracle建议独立地管理Grid Infrastructure和ASM实例，因此有必要创建更多的os用户组以满足不同的权限分配。

我们在11.2的GI中常用的ASM用户组有以下三个：

1、OSASM(asmadmin)用户组

如果使用ASM，那么我们必须创建osasm(asmadmin)用户组，该OSASM用户组的成员将被赋予SYSASM权限，以满足组成员管理Oracle Clusterware和Oracle ASM的权限需求。

2、OSDBA for ASM group(asmdba)用户组

OSDBA(asmdba)用户组的成员将被赋予读写访问ASM文件的权限。GI/CRS拥有者用户和所有oracle数据库软件的拥有者必须是该组的成员。同时所有OSDBA(dba)用户组的成员也必须是asmdba组的成员。

3、OSOPER for ASM(asmoper)用户组

asmoper和osoper类似都是额外的可选择创建的用户组，创建该独立的用户组以满足赋予用户一套受限的ASM实例管理权限(ASM的SYSOPER角色)，该权限包括了启动和停止ASM实例，默认情况下OSASM(asmadmin)组成员将拥有所有SYSOPER的ASM管理权限。

在11.2的GI/CRS环境中一般会创建grid或griduser用户来管理GI软件和ASM实例，以如下方式创建grid用户:

useradd -g oinstall -G asmadmin,asmdba,asmoper grid

id grid

uid=54322(grid) gid=54321(oinstall)

groups=54321(oinstall),700(asmadmin),701(asmdba),55000(asmoper)

在Oracle中有两类特殊的权限SYSDBA和SYSOPER，当DBA需要对数据库进行维护管理操作的时候必须具有这两类特殊权限之中的一种。在数据库没有打开的时候，使用数据库内建的账号是无法登陆数据库的，但是拥有SYSDBA或是SYSOPER权限的用户是可以登陆的。认证用户是否拥有两类特殊权限的方法有两种：OS认证和口令文件认证。

Oracle数据库究竟使用OS认证还是口令文件认证来进行管理取决于下面三个因素：

1、SQLNET.ORA参数文件中的参数SQLNET.AUTHENTICATION_SERVICES设置

2、PFILE(SPFILE)参数文件中的参数REMOTE_LOGIN_PASSWORDFILE设置

3、口令文件orapw$SID(Linux) | PWD$SID.ora(Windows)

Oracle权限认证的基本顺序是这样的，先由SQLNET.AUTHENTICATION_SERVICES的设置值来决定是使用OS认证还是口令文件认证，如果使用口令文件认证的话就要看后面两个条件了：如果REMOTE_LOGIN_PASSWORDFILE参数设置为非NONE而且口令文件存在的话就能正常使用口令文件认证，否则将会失败。

SQLNET.AUTHENTICATION_SERVICES参数

在SQLNET.ORA(位于$ORACLE_HOME/NETWORK/ADMIN目录中)文件中，需要修改时直接用文本编辑器打开修改就行了，对于不同的操作系统SQLNET.AUTHENTICATION_SERVICES的取值会有些不一样，通常我们会用到下面的一些设置值：

SQLNET.AUTHENTICATION_SERVICES

= (ALL)

对Linux系统，支持OS认证和口令文件认证。

对Windows系统，实际实验是不支持此参数，验证失败。

SQLNET.AUTHENTICATION_SERVICES

= (NTS)

此设置值仅用于Windows系统，此设置同时支持OS认证和口令文件认证，只有在设置了(NTS)值之后运行在Windows系统上的Oracle才支持OS认证。

SQLNET.AUTHENTICATION_SERVICES

= (NONE)

此设置值在Windows和Linux是作用一样的，指定Oracle只使用口令文件认证。

不设置此参数或SQLNET.AUTHENTICATION_SERVICES =

对Linux系统，默认支持OS认证和口令文件认证。

对Windows系统，默认只支持口令文件认证，不支持OS认证。

OS认证实现

Oracle使用操作系统中的两个用户组来控制OS认证，在不同的操作系统中这两个用户组的名称是不一样的，一般来说他们是OSDBA和OSOPER，这两个用户组都是在Oracle安装的时候创建的。下面列出不同系统中这两个用户组的名字：

Operating

System Group

UNIX

User Group

UNIX

User Group

OSDBA

dba

ORA_DBA

OSOPER

oper

ORA_OPER

OSDBA用户组的用户可以使用SYSDBA权限登陆数据库，OSOPER用户组的的用户可以使用SYSOPER权限来登陆数据库。使用sqlplus可以用下面方法登陆

CONNECT / AS SYSDBA

CONNECT / AS SYSOPER

拥有OS权限的用户登陆数据库时不再需要输入用户名和密码，因此使用下面的命令也是可以正常登陆的：

CONNECT ANY_USER_NAME/ANY_PASSWORD AS SYSDBA

CONNECT ANY_USER_NAME/ANY_PASSWORD AS SYSOPER

因此要创建一个新的OS认证帐号步骤是：

建立一个OS用户

将用户加入到OSDBA或是OSOPER用户组

用新增加的用户登陆系统，然后输入sqlplus / AS SYSDBA进行登陆

REMOTE_LOGIN_PASSWORDFILE参数

REMOTE_LOGIN_PASSWORDFILE系统参数的设置制定了数据库使用口令文件的方法，此参数可以设置的值有三个：

REMOTE_LOGIN_PASSWORDFILE

= NONE

不使用口令文件

REMOTE_LOGIN_PASSWORDFILE

= EXCLUSIVE

使用口令文件，但只有一个数据库实例可用使用

REMOTE_LOGIN_PASSWORDFILE

= SHARED

多个数据库实例共用一个口令文件，这种设置下是不能增加其他数据库用户作为特殊权限用户到口令文件中的。

REMOTE_LOGIN_PASSWORDFILE参数属于初始化参数，只能在init.ora/pfile中指定或是在数据库打开状态下使用下面语句修改，然后重新启动数据库。

ALTERSYSTEMSETREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVESCOPE=SPFILE;

要检查当前REMOTE_LOGIN_PASSWORDFILE的设定值在登陆Oracle后输入下面的命令

SYS@seiang11g>show parameter remote

NAME TYPE VALUE

------------------------------------ -----------

------------------------------

remote_dependencies_mode string TIMESTAMP

remote_listener string

remote_login_passwordfile

string EXCLUSIVE

remote_os_authent boolean FALSE

remote_os_roles boolean FALSE

result_cache_remote_expiration integer 0

下面是11g官方文档的解释：

*******************************************************************************

Selecting an

Authentication Method for Database Administrators

Database

Administrators can authenticate database administrators through the data

dictionary, (using an account password) like other users. Keep in mind that beginning with Oracle

Database 11g Release 1, database passwords are case-sensitive. (You

can disable case sensitivity and return to pre–Release 11gbehavior by

setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

数据库管理员可以通过数据字典(使用帐户密码)与其他用户进行身份验证数据库管理员。请记住，从Oracle Database 11g第1版开始，数据库密码区分大小写。(可以通过将SEC_CASE_SENSITIVE_LOGON初始化参数设置为FALSE来禁用区分大小)

In addition to

normal data dictionary authentication, the following methods are available

for authenticating database administrators with

the SYSDBA or SYSOPERprivilege:

除了正常的数据字典认证之外，以下方法可用于使用SYSDBA或SYSOPER权限对数据库管理员进行身份验证

Operating

system (OS) authentication

Password

files

Strong

authentication with a network-based authentication service, such as Oracle

Internet Directory

These methods

are required to authenticate a database administrator when the database is not

started or otherwise unavailable. (They can also be used when the database is

available.)

当数据库未启动或不可用时，需要这些方法来对数据库管理员进行身份验证。(当数据库可用时也可以使用它们。)

Notes:

These methods replace the CONNECT INTERNAL syntax provided with earlier

versions of Oracle Database. CONNECT INTERNAL is no longer supported.

Operating system authentication takes precedence over password

file authentication. If you meet the requirements for operating system

authentication, then even if you use a password file, you will be authenticated

by operating system authentication.

Your choice will

be influenced by whether you intend to administer your database locally on the

same system where the database resides, or whether you intend to administer

many different databases from a single remote client. illustrates the choices you have

for database administrator authentication schemes.

Figure 1-2 Database Administrator

Authentication Methods

If you are

performing remote database administration, consult your Oracle Net

documentation to determine whether you are using a secure connection. Most

popular connection protocols, such as TCP/IP and DECnet, are not secure.

Nonsecure Remote Connections

To connect to

Oracle Database as a privileged user over a nonsecure connection, you must be

authenticated by a password file. When using password

file authentication, the database uses a password file to keep track of

database user names that have been granted the SYSDBA or SYSOPER system privilege. This form of

authentication is discussed in .

要通过非安全连接作为特权用户连接到Oracle数据库，必须通过密码文件进行身份验证。使用密码文件认证时，数据库使用密码文件来跟踪已被授予SYSDBA或SYSOPER系统权限的数据库用户名。

Local Connections and Secure Remote

Connections

You can connect

to Oracle Database as a privileged user over a local connection or a secure

remote connection in two ways:

If the database has a

password file and you have been granted the SYSDBA or SYSOPER system privilege,

then you can connect and be authenticated by a password file.

如果数据库具有密码文件，并且您已被授予SYSDBA或SYSOPER系统权限，则可以通过密码文件进行连接和身份验证。

If the server is not

using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are

therefore not in the password file, you can use operating system

authentication. On most operating systems, authentication for database

administrators involves placing the operating system username of the

database administrator in a special group, generically referred to as

OSDBA. Users in that group are granted SYSDBA privileges. A

similar group, OSOPER, is used to grant SYSOPER privileges to

users.

如果服务器未使用密码文件，或者尚未授予SYSDBA或SYSOPER权限，因此不在密码文件中，则可以使用操作系统身份验证。在大多数操作系统上，数据库管理员的身份验证包括将数据库管理员的操作系统用户名放在一个特殊的组中，一般称为OSDBA。该组中的用户被授予SYSDBA权限。类似的组OSOPER用于向用户授予SYSOPER权限。

Using Operating System Authentication

This section describes

how to authenticate an administrator using the operating system.

OSDBA and OSOPER

Membership in one of two special

operating system groups enables a DBA to authenticate to the database through

the operating system rather than with a database user name and password. This

is known as operating system authentication. These operating system groups are

generically referred to as OSDBA and OSOPER. The groups are created and

assigned specific names as part of the database installation process. The default

names vary depending upon your operating system, and are listed in the

following table:

Operating System Group

UNIX User Group

Windows User Group

OSDBA

dba

ORA_DBA

OSOPER

oper

ORA_OPER

在两个特殊操作系统组之一中的成员使DBA能够通过操作系统而不是使用数据库用户名和密码对数据库进行身份验证，这被称为操作系统认证。

Oracle Universal

Installer uses these default names, but you can override them. One reason to

override them is if you have multiple instances running on the same host

computer. If each instance is to have a different person as the principal DBA,

you can improve the security of each instance by creating a different OSDBA

group for each instance. For example, for two instances on the same host, the

OSDBA group for the first instance could be named dba1, and OSDBA for the second instance

could be named dba2. The first DBA would be a member

of dba1 only, and the second DBA would be

a member of dba2 only. Thus, when using operating

system authentication, each DBA would be able to connect only to his assigned

instance.

Oracle Universal Installer使用默认名称，但你可以覆盖它们。覆盖它们的一个原因是如果您有多个实例运行在同一主机上。如果每个实例都要有一个不同的人作为DBA，则可以通过为每个实例创建一个不同的OSDBA组来提高每个实例的安全性。例如，对于同一主机上的两个实例，第一个实例的OSDBA组可以命名为dba1，第二个实例的OSDBA组可以命名为dba2。第一个DBA只是dba1的成员，第二个DBA只是dba2的成员。因此，当使用操作系统认证时，每个DBA将只能连接到他分配的实例。

Membership in

the OSDBA or OSOPER group affects your connection to the database in the

following ways:

If you are a member of

the OSDBA group and you specify AS SYSDBA when you connect to the database, then you

connect to the database with theSYSDBA system

privilege.

If you are a member of

the OSOPER group and you specify AS SYSOPER when you connect to the database, then you

connect to the database with theSYSOPER system

privilege.

If you are not a member

of either of these operating system groups and you attempt to connect

as SYSDBA or SYSOPER, the CONNECT command

fails.

Preparing to Use Operating System

Authentication

To enable operating system

authentication of an administrative user:

Create

an operating system account for the user.

Add

the account to the OSDBA or OSOPER operating system defined groups.

Connecting Using Operating System

Authentication

A user can be authenticated, enabled as an administrative

user, and connected to a local database by typing one of the following SQL*Plus

commands:

CONNECT / AS

SYSDBA

CONNECT / AS SYSOPER

For the Windows

platform only, remote operating system authentication over a secure connection

is supported. You must specify the net service name for the remote database:

CONNECT /@net_service_name AS SYSDBA

CONNECT /@net_service_name AS SYSOPER

Both the client computer

and database host computer must be on a Windows domain.

口令文件存放着被授予SYSDBA或SYSOPER权限的用户的用户名和密码。它是一个加密的文件，用户不能修改这个文件，在Linux系统中口令文件一般保存在$ORACLE_HOME/dbs目录下，文件名为orapw$SID；在Windows系统中口令文件一般保存在$ORACLE_HOME/database目录下，文件名为PWD$SID.ora。

使用口令文件认证的基本步骤是：

使用orapwd工具生成口令文件

设置REMOTE_LOGIN_PASSWORDFILE为EXCLUSIVE或是SHARED

使用SYS登陆数据库，创建新的数据库用户

使用GRANT命令授予新创建的用户SYSDBA/SYSOPER权限

Using Password File Authentication

This section

describes how to authenticate an administrative user using password file

authentication.

Preparing to Use Password File

Authentication

To enable authentication of an

administrative user using password file authentication you must do the

following:

If not already created,

create the password file using the ORAPWD utility:

2. ORAPWD FILE=filename ENTRIES=max_users

See for details

Notes:

o When you invoke Database Configuration

Assistant (DBCA) as part of the Oracle Database installation process, DBCA

creates a password file.

o Beginning with Oracle Database 11g Release

1, passwords in the password file are case-sensitive unless you include

the IGNORECASE = Y command-line

argument.

从Oracle Database 11g第1版开始，密码文件中的密码区分大小写，除非您包含IGNORECASE = Y命令行参数。

Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).

Note:

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore

cannot be changed without restarting the database.

Connect to the database

as user SYS (or as another user

with the administrative privileges).

If the user does not

already exist in the database, create the user and assign a password.

Keep in mind that beginning with Oracle

Database 11g Release 1, database passwords are case-sensitive. (You

can disable case sensitivity and return to pre–Release 11g behavior

by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)

Grant the SYSDBA or SYSOPER system privilege to the user:

7. GRANT SYSDBA to oe;

This statement adds the user to the password file,

thereby enabling connection AS SYSDBA.

Connecting Using

Password File Authentication

Administrative

users can be connected and authenticated to a local or remote database by using

the SQL*Plus CONNECT command. They must connect using

their username and password and the AS SYSDBA or AS SYSOPER clause. Note

that beginning with Oracle Database 11g Release 1, passwords are

case-sensitive unless the password file was created with the IGNORECASE = Y option.

For example,

user oe has been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe AS

SYSDBA

However,

user oe has not been granted the SYSOPER privilege, so the following

command will fail:

CONNECT oe AS

SYSOPER

Note:

Operating system authentication takes

precedence over password file authentication. Specifically, if you are a member

of the OSDBA or OSOPER group for the operating system, and you connect as

SYSDBA or SYSOPER, you will be connected with associated administrative

privileges regardless of the username/password that you

specify.

操作系统认证优先于密码文件认证。具体来说，如果您是操作系统的OSDBA或OSOPER组的成员，并以SYSDBA或SYSOPER身份连接，则无论您指定的用户名/密码如何，都将连接相关联的管理权限。

If you are not in

the OSDBA or OSOPER groups, and you are not in the password file, then

attempting to connect as SYSDBA or

as SYSOPER fails.

如果您不在OSDBA或OSOPER组中，并且您不在密码文件中，则尝试以SYSDBA或SYSOPER身份连接。

Creating

and Maintaining a Password File

You can create a password

file using the password file creation utility,ORAPWD. For some

operating systems, you can create this file as part of your standard

installation.

Creating

a Password File with ORAPWD

The syntax of theORAPWDcommand is as follows:

ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}]

使用Oracle提供的工具orapwd来创建或者重新初始化一个口令文件：

[oracle@seiang11g ~]$ orapwd

Usage: orapwd file=

entries= force= ignorecase=

nosysdba=

where

file - name of password file (required),

password - password for SYS will be prompted if not specified at command

line,

entries - maximum number of distinct DBA (optional),

force - whether to overwrite existing file (optional),

ignorecase - passwords are case-insensitive (optional),

nosysdba - whether to shut out the SYSDBA logon (optional Database Vault

only).

There

must be no spaces around the equal-to (=) character.

[oracle@seiang11g ~]$ orapwd

file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=XXX entries=5 force=y

注意：使用orapwd重新生成口令文件之后以保存的授予的其他用户的SYSDBA或是SYSOPER权限将会丢失，需要重新的GRANT。

设定的entries值是不能修改的，如果要修改entries的话需要重新生成口令文件，在生成口令文件之前可以先通过V$PWFILE_USERS视图查询出当前被授予SYSDBA/SYSOPER权限的用户，然后在重新生成口令文件以后重新对这些用户授予SYSDBA/SYSOPER权限

Command arguments are

summarized in the following table.

Argument

Description

FILE

Name to assign to the password file. You

must supply a complete path. If you supply only a file name, the file is

written to the current directory.

ENTRIES

(Optional) Maximum number of entries

(user accounts) to permit in the file.

FORCE

(Optional) Ify, permits overwriting an existing password file.

IGNORECASE

(Optional) Ify, passwords are treated as case-insensitive.

There are no spaces

permitted around the equal-to (=) character.

The command prompts for

theSYSpassword and stores the password in the created password file.

ORAPWD Command Line Argument Descriptions

The following sections

describe theORAPWDcommand line arguments.

FILE

This argument

sets the name of the password file being created. You must specify the full

path name for the file. The contents of this file are encrypted, and the file

cannot be read directly. This argument is mandatory.

此参数设置正在创建的密码文件的名称。您必须指定文件的完整路径名。该文件的内容被加密，文件无法直接读取。这个说法是强制性的。

The file name

required for the password file is operating system specific. Some operating

systems require the password file to adhere to a specific format and be located

in a specific directory. Other operating systems allow the use of environment

variables to specify the name and location of the password file.

密码文件所需的文件名是基于特定的操作系统的。某些操作系统要求密码文件遵循特定格式，并位于特定的目录中。其他操作系统允许使用环境变量来指定密码文件的名称和位置。

lists the required name and location for the password file on

the UNIX, Linux, and Windows platforms. For other platforms, consult your

platform-specific documentation.

Table 1-1

Required Password File Name and Location on UNIX, Linux, and Windows

Platform

Required Name

Required Location)

UNIX and Linux

orapwORACLE_SID

ORACLE_HOME/dbs

Windows

PWDORACLE_SID.ora

ORACLE_HOME\database

For example,

for a database instance with the SIDorcldw, the password

file must be namedorapworcldwon Linux andPWDorcldw.oraon Windows.

In an Oracle

Real Application Clusters environment on a platform that requires an

environment variable to be set to the path of the password file, the

environment variable for each instance must point to the same password file.

在需要将环境变量设置为密码文件路径的平台上的RAC环境中，每个实例的环境变量必须指向相同的密码文件。

Caution:

is critically important to the security of your system that you protect your

password file and the environment variables that identify the location of the

password file. Any user with access to these could potentially compromise the

security of the connection.

警告：

从系统的安全性来说，保护密码文件和标识密码文件位置的环境变量至关重要。任何具有访问权限的用户都可能会危及连接的安全性。

ENTRIES

This argument

specifies the number of entries that you require the password file to accept.

This number corresponds to the number of distinct users allowed to connect to

the database asSYSDBAorSYSOPER. The actual

number of allowable entries can be higher than the number of users, because theORAPWDutility continues to assign password entries until an operating

system block is filled. For example, if your operating system block size is 512

bytes, it holds four password entries. The number of password entries allocated

is always a multiple of four.

此参数指定需要密码文件接受的条目数。此数字对应于允许以SYSDBA或SYSOPER连接到数据库的不同用户的数量。允许的条目的实际数量可以高于用户数，因为ORAPWD实用程序继续分配密码条目，直到操作系统块被填充为止。例如，如果您的操作系统块大小为512字节，则它将保存四个密码条目。分配的密码条目数量总是四的倍数。

Entries can be

reused as users are added to and removed from the password file. If you intend

to specifyREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow

the granting ofSYSDBAandSYSOPERprivileges to users, this argument is required.

当用户被添加到密码文件和从密码文件中删除时，可以重复使用条目。如果您打算指定REMOTE_LOGIN_PASSWORDFILE

= EXCLUSIVE，并允许向用户授予SYSDBA和SYSOPER权限，则此参数是必需的。

Caution:

When

you exceed the allocated number of password entries, you must create a new

password file. To avoid this necessity, allocate more entries than you think

you will ever need.

当超过分配的密码条目数时，必须创建一个新的密码文件。为了避免这种需要，请分配比您以前需要的更多的条目。

FORCE

This argument,

if set toY, enables you

to overwrite an existing password file. An error is returned if a password file

of the same name already exists and this argument is omitted or set toN.

此参数(如果设置为Y)使您能够覆盖现有的密码文件。如果同名的密码文件已经存在，并且此参数被省略或设置为N，则返回错误。

IGNORECASE

If this argument

is set toy, passwords

are case-insensitive. That is, case is ignored when comparing the password that

the user supplies during login with the password in the password file.

如果此参数设置为y，则密码不区分大小写。也就是说，将用户在登录时提供的密码与密码文件中的密码进行比较时，将忽略大小写。

Sharing

and Disabling the Password File

You use the

initialization parameterREMOTE_LOGIN_PASSWORDFILEto control whether a password file is shared among multiple

Oracle Database instances. You can also use this parameter to disable password

file authentication. The values recognized forREMOTE_LOGIN_PASSWORDFILEare:

可以使用初始化参数REMOTE_LOGIN_PASSWORDFILE来控制是否在多个Oracle数据库实例之间共享密码文件。您还可以使用此参数来禁用密码文件身份验证。

· NONE:Setting this parameter toNONEcauses Oracle Database to behave as if the password file does

not exist. That is, no privileged connections are allowed over nonsecure

connections.

· EXCLUSIVE:(The default) AnEXCLUSIVEpassword file can be used with only one instance of one

database. Only anEXCLUSIVEfile can be modified. Using anEXCLUSIVEpassword file enables you to add, modify, and delete users. It

also enables you to change theSYSpassword with theALTER

USERcommand.

· SHARED: ASHAREDpassword file can be used by multiple databases running on the

same server, or multiple instances of an Oracle Real Application Clusters

(Oracle RAC) database. ASHAREDpassword file cannot be modified. Therefore, you cannot add

users to aSHAREDpassword file. Any attempt to do so or to change the password ofSYSor other users with theSYSDBAorSYSOPERprivileges generates an error. All users needingSYSDBAorSYSOPERsystem

privileges must be added to the password file whenREMOTE_LOGIN_PASSWORDFILEis set toEXCLUSIVE. After all

users are added, you can changeREMOTE_LOGIN_PASSWORDFILEtoSHARED, and then

share the file.

This option is

useful if you are administering multiple databases or an Oracle RAC database.

IfREMOTE_LOGIN_PASSWORDFILEis set toEXCLUSIVEorSHAREDand the password file is missing, this is equivalent to settingREMOTE_LOGIN_PASSWORDFILEtoNONE.

如果REMOTE_LOGIN_PASSWORDFILE设置为EXCLUSIVE或SHARED，并且密码文件丢失，则相当于将REMOTE_LOGIN_PASSWORDFILE设置为NONE。

Note:

You cannot

change the password forSYSifREMOTE_LOGIN_PASSWORDFILEis set toSHARED. An error

message is issued if you attempt to do so.

如果REMOTE_LOGIN_PASSWORDFILE设置为“共享”，则无法更改SYS的密码。如果您尝试这样做，将发出错误消息。

Keeping

Administrator Passwords Synchronized with the Data Dictionary

If you change theREMOTE_LOGIN_PASSWORDFILEinitialization parameter fromNONEtoEXCLUSIVEorSHARED, or if you

re-create the password file with a differentSYSpassword, then

you must ensure that the passwords in the data dictionary and password file for

theSYSuser are the same.

To synchronize theSYSpasswords, use theALTER

USERstatement to

change theSYSpassword. TheALTER

USERstatement updates

and synchronizes both the dictionary and password file passwords.

To synchronize the

passwords for non-SYSusers who log in using theSYSDBAorSYSOPERprivilege, you must revoke and then regrant the privilege to the

user, as follows:

Find all users who have been granted theSYSDBAprivilege.

2.SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';

Revoke and then re-grant theSYSDBAprivilege to these users.

4.REVOKE SYSDBA FROM non-SYS-user;

5.GRANT SYSDBA TO non-SYS-user;

Find all users who have been granted theSYSOPERprivilege.

7.SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';

Revoke and regrant theSYSOPERprivilege to these users.

9.REVOKE SYSOPER FROM non-SYS-user;

10.GRANT SYSOPER TO non-SYS-user;

Adding Users to a

Password File

When you grantSYSDBAorSYSOPERprivileges to a user, that user's name and privilege information

are added to the password file. If the server does not have anEXCLUSIVEpassword file (that is, if the initialization parameterREMOTE_LOGIN_PASSWORDFILEisNONEorSHARED, or the

password file is missing), Oracle Database issues an error if you attempt to

grant these privileges.

A user's name remains in

the password file only as long as that user has at least one of these two privileges.

If you revoke both of these privileges, Oracle Database removes the user from

the password file.

Creating a Password File and Adding New Users to It

Use the following

procedure to create a password and add new users to it:

Follow the instructions for creating a password file as

explained in.

Set theREMOTE_LOGIN_PASSWORDFILEinitialization parameter toEXCLUSIVE. (This is the

default.)

Note:

REMOTE_LOGIN_PASSWORDFILEis a static initialization parameter and therefore cannot be

changed without restarting the database.

Connect withSYSDBAprivileges as shown in the following example, and enter theSYSpassword when prompted:

4.CONNECT SYS AS SYSDBA

Start up the instance and create the database if necessary, or

mount and open an existing database.

Create users as necessary. GrantSYSDBAorSYSOPERprivileges to yourself and other users as appropriate. Granting and Revoking SYSDBA and

SYSOPER Privileges

If your server is using

anEXCLUSIVEpassword file, use theGRANTstatement to grant theSYSDBAorSYSOPERsystem privilege to a user, as shown in the following example:

GRANT SYSDBA TO oe;

Use theREVOKEstatement to revoke theSYSDBAorSYSOPERsystem privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM oe;

BecauseSYSDBAandSYSOPERare the most powerful database privileges, theWITH ADMIN

OPTIONis not used in

theGRANTstatement. That is, the grantee cannot in turn grant theSYSDBAorSYSOPERprivilege to another user. Only a user currently connected asSYSDBAcan grant or revoke another user'sSYSDBAorSYSOPERsystem

privileges. These privileges cannot be granted to roles, because roles are

available only after database startup. Do not confuse theSYSDBAandSYSOPERdatabase

privileges with operating system roles.

因为SYSDBA和SYSOPER是最强大的数据库权限，所以在GRANT语句中不使用WITH ADMIN OPTION。也就是说，受让人不能依次向其他用户授予SYSDBA或SYSOPER权限。只有当前以SYSDBA身份连接的用户才能授予或撤销其他用户的SYSDBA或SYSOPERsystem权限。这些权限不能授予角色，因为角色仅在数据库启动后可用。不要将SYSDBA和SYSOPER数据库特权与操作系统角色混淆。

Viewing

Password File Members

Use theV$PWFILE_USERSview to see

the users who have been granted theSYSDBA,SYSOPER, orSYSASMsystem privileges. The columns displayed by this view are as

follows:

Column

Description

USERNAME

This column contains the name of the user

that is recognized by the password file.

SYSDBA

If the value of this column isTRUE, then the user can log on with theSYSDBAsystem privileges.

SYSOPER

If the value of this column isTRUE, then the user can log on with theSYSOPERsystem privileges.

SYSASM

If the value of this column isTRUE, then the user can log on with theSYSASMsystem privileges.

Note:

SYSASMis valid only for Oracle Automatic Storage Management instances.

每次在Oracle系统里面使用GRANT SYSDBA/SYSOPER授予新用户特殊权限或是ALTER USER命令修改拥有SYSDBA/SYSOPER权限的用户密码的时候，Oracle都会自动的修改口令文件，增加或是修改相应的项目，这样保证在数据没有打开的情况拥有特殊权限的用户能正常的登陆数据库以进行管理操作。

实验环境：

操作系统：CentOS 7.1数据库：Oracle 11.2.0.4

上面长篇大论的说了那么多，下面我们来做实验验证一下。本实验是基于Linux系统来做的，做实验之前先使用下面的命令创建一个口令文件：

[oracle@seiang11g ~]$ orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=XXX entries=5

force=y

1、验证OS认证

设置SQLNET.ORA中参数SQLNET.AUTHENTICATION_SERVICES = (ALL)或是不设置，REMOTE_LOGIN_PASSWORDFILE

= NONE，然后进行下面的操作。

本地使用下面两种方式登陆，都能成功

[oracle@seiang11g ~]$ sqlplus /

as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on

Wed Aug 9 11:58:26 2017

Connected to:

Oracle Database 11g Enterprise Edition

Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and

Real Application Testing options

SYS@seiang11g>

[oracle@seiang11g ~]$ sqlplus

111/222 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on

Wed Aug 9 11:58:41 2017

Connected to:

Oracle Database 11g Enterprise Edition

Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and

Real Application Testing options

SYS@seiang11g>

远程使用口令文件方式登陆，失败

[oracle@seiang11g ~]$ sqlplus

sys/oracle@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on

Wed Aug 9 11:59:31 2017

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3

attempts, exiting SQL*Plus

[oracle@seiang11g ~]$sqlplus

sys/oracle@10.1.1.46/ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on

Wed Aug 9 12:04:36 2017

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3

attempts, exiting SQL*Plus

2、两种认证都失效

设置SQLNET.ORA中参数SQLNET.AUTHENTICATION_SERVICES = (NONE)，REMOTE_LOGIN_PASSWORDFILE

= NONE，然后进行下面的操作。

本地使用下面两种方式登陆，都失败

[oracle@seiang11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9

12:06:26 2017

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts,

exiting SQL*Plus

[oracle@seiang11g ~]$sqlplus 111/222 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9

12:06:55 2017

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts,

exiting SQL*Plus

远程使用口令文件方式登陆，失败

[oracle@seiang11g ~]$sqlplus sys/oracle@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9

12:07:19 2017

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts,

exiting SQL*Plus

[oracle@seiang11g ~]$ sqlplus system/oracle@10.1.1.46/ORADB11G as

sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9

12:07:46 2017

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts,

exiting SQL*Plus

3、验证口令文件认证

设置SQLNET.ORA中参数SQLNET.AUTHENTICATION_SERVICES = (NONE)，REMOTE_LOGIN_PASSWORDFILE

= EXCLUSIVE或SHARED，然后进行下面的操作。

本地使用验证OS认证，失败

[oracle@seiang11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9

12:14:43 2017

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts,

exiting SQL*Plus

本地验证口令文件认证，成功

[oracle@seiang11g ~]$sqlplus

sys/oracle as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9

12:15:10 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

- 64bit Production

With the Partitioning, OLAP, Data Mining and Real

Application Testing options

SYS@seiang11g>

远程使用口令文件认证，成功

[oracle@seiang11g

~]$ sqlplus sys/oracle@ORADB11G as sysdba

SQL*Plus:

Release 11.2.0.4.0 Production on Wed Aug 9 12:18:21 2017

reserved.

Connected

to:

Oracle

Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the

Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORADB11G>

[oracle@seiang11g

~]$sqlplus scott/tiger@10.1.1.46/ORADB11G

SQL*Plus:

Release 11.2.0.4.0 Production on Wed Aug 9 12:18:55 2017

reserved.

Connected

to:

Oracle

Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the

Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@10.1.1.46/ORADB11G>

4、两种认证都成功

设置SQLNET.ORA中参数SQLNET.AUTHENTICATION_SERVICES = (ALL)，REMOTE_LOGIN_PASSWORDFILE

= EXCLUSIVE，然后进行下面的操作。

本地使用验证OS认证，成功

[oracle@seiang11g ~]$ sqlplus /

as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on

Wed Aug 9 11:58:26 2017

Connected to:

Oracle Database 11g Enterprise Edition

Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and

Real Application Testing options

SYS@seiang11g>

远程使用口令文件认证，成功

[oracle@seiang11g admin]$sqlplus

sys/oracle@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on

Wed Aug 9 14:39:32 2017

ERROR:

ORA-12641: Authentication service failed to initialize

5、将SYSDBA/SYSOPER权限授权给其它数据库帐户

先查看口令文件的修改时间

[oracle@seiang11g dbs]$ll orapwseiang11g

-rw-r----- 1 oracle oinstall 1536 Aug 7 18:51 orapwseiang11g

用SYS登陆数据库，创建新用户test，并赋予SYSDBA权限

SYS@seiang11g>create user test identified by test;

User created.

SYS@seiang11g>grant sysdba to test;

Grant succeeded.

再看口令文件，已经修改了

[oracle@seiang11g dbs]$ ll orapwseiang11g

-rw-r----- 1 oracle oinstall 1536 Aug 9 13:53 orapwseiang11g

再用新的test帐号登陆，能成功的登陆

[oracle@seiang11g ~]$sqlplus test/test@ORADB11G as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 9

13:55:48 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

- 64bit Production

With the Partitioning, OLAP, Data Mining and Real

Application Testing options

SYS@ORADB11G>

说明：如果要取消SYSDBA权限只需要运行下面的语句就可以了

SYS@ORADB11G>revoke

sysdba from test;

Revoke succeeded.

常见问题说明

1、如何查找拥有SYSDBA或是SYSOPER权限的用户

使用视图V$PWFILE_USERS，结果集中的SYSDB和SYSOP分别代表是否有SYSDBA和SYSOPER权限。

SYS@ORADB11G>select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS TRUE TRUE

FALSE

TEST FALSE TRUE FALSE

2、授予权限时出现”ORA-01994: GRANT failed: password file missing or disabled”

出现这种情况是因为没有创建口令文件，或者是口令文件放置的目录不正确，Oracle找不到。只要重建或将口令文件置于$ORACLE_HOME/dbs/目录中就可以了。

3、忘记了SYS帐号的密码怎么办？

如果数据库启用的OS认证登陆，则可以用OS认证登陆数据库，然后使用下面的命令进行修改

alteruserSYSidentifiedbypwd;

如果没有启用OS认证登陆，则需要用orapwd重建口令文件

orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID'

password=pwd entries=10 force=y；其中的password项所指定的就是SYS的密码

作者：SEian.G(苦练七十二变，笑对八十一难)

AI量化数据之佳

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle安装os认证,Oracle中两种认证方式：OS认证与口令文件认证

首先谈谈Oracle安装与OS用户组.Oracle在安装和维护过程中经常要和操作用户组(OS User Group)打交道，从早前的只有oracle用户和dba组发展到今天11gR2中的grid用户和asm组。我们在单实例环境中常用的三个操作用户组，分别是:1、oinstall用户组oinstall组是Oracle推荐创建的OS用户组之一，建议在系统第一次安装Oracle产品之前创建oinstal...
复制链接

扫一扫