Oracle中两种认证方式：OS认证与口令文件认证

相关链接：SYS,SYSTEM,DBA,SYSDBA,SYSOPER的区别与联系 

首先谈谈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安装的时候创建的。下面列出不同系统中这两个用户组的名字：

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认证帐号步骤是：

1. 建立一个OS用户
2. 将用户加入到OSDBA或是OSOPER用户组
3. 用新增加的用户登陆系统，然后输入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中指定或是在数据库打开状态下使用下面语句修改，然后重新启动数据库。

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = 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. Figure 1-2 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 "Using Password File Authentication".

要通过非安全连接作为特权用户连接到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:

在两个特殊操作系统组之一中的成员使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:

1. Create an operating system account for the user.
2. 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。

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

1. 使用orapwd工具生成口令文件
2. 设置REMOTE_LOGIN_PASSWORDFILE为EXCLUSIVE或是SHARED
3. 使用SYS登陆数据库，创建新的数据库用户
4. 使用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:

1. If not already created, create the password file using the ORAPWD utility:

2.    ORAPWD FILE=filename ENTRIES=max_users

See "Creating and Maintaining a Password File" 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命令行参数。

1. 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.

1. Connect to the database as user SYS (or as another user with the administrative privileges).
2. 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.)

1. 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身份连接。

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 the ORAPWD command is as follows:

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

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

[oracle@seiang11g ~]$ orapwd

Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

  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.

There are no spaces permitted around the equal-to (=) character.

The command prompts for the SYS password and stores the password in the created password file.

ORAPWD Command Line Argument Descriptions

The following sections describe the ORAPWD command 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.

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

Table 1-1 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

For example, for a database instance with the SID orcldw, the password file must be named orapworcldw on Linux and PWDorcldw.ora on 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:

It 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 as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because theORAPWD utility 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 specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges 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 to Y, 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 to N.

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

IGNORECASE

If this argument is set to y, 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 parameter REMOTE_LOGIN_PASSWORDFILE to 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 for REMOTE_LOGIN_PASSWORDFILE are:

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

·        NONE: Setting this parameter to NONE causes 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) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

·        SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (Oracle RAC) database. A SHARED password file cannot be modified. Therefore, you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPERsystem privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can changeREMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

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

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

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

Note:

You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. 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 the REMOTE_LOGIN_PASSWORDFILE initialization parameter from NONE to EXCLUSIVE or SHARED, or if you re-create the password file with a different SYSpassword, then you must ensure that the passwords in the data dictionary and password file for the SYS user are the same.

To synchronize the SYS passwords, use the ALTER USER statement to change the SYS password. The ALTER USER statement updates and synchronizes both the dictionary and password file passwords.

To synchronize the passwords for non-SYS users who log in using the SYSDBA or SYSOPER privilege, you must revoke and then regrant the privilege to the user, as follows:

1.    Find all users who have been granted the SYSDBA privilege.

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

3.    Revoke and then re-grant the SYSDBA privilege to these users.

4.  REVOKE SYSDBA FROM non-SYS-user;

5.  GRANT SYSDBA TO non-SYS-user;

6.    Find all users who have been granted the SYSOPER privilege.

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

8.    Revoke and regrant the SYSOPER privilege 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 grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, 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:

1.    Follow the instructions for creating a password file as explained in "Creating a Password File with ORAPWD".

2.    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.

3.    Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:

4.  CONNECT SYS AS SYSDBA

5.    Start up the instance and create the database if necessary, or mount and open an existing database.

6.    Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. Granting and Revoking SYSDBA and SYSOPER Privileges

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:

GRANT SYSDBA TO oe;

Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM oe;

Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPERsystem privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPERdatabase privileges with operating system roles.

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

Viewing Password File Members

Use the V$PWFILE_USERS view to see the users who have been granted the SYSDBA, SYSOPER, or SYSASM system privileges. The columns displayed by this view are as follows:

Note:

SYSASM is 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

Copyright (c) 1982, 2013, Oracle.  All rights 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@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

Copyright (c) 1982, 2013, Oracle.  All rights 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@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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights 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@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

Copyright (c) 1982, 2013, Oracle.  All rights 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

Copyright (c) 1982, 2013, Oracle.  All rights 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

Copyright (c) 1982, 2013, Oracle.  All rights 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@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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2013, Oracle.  All rights 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>

说明：如果要取消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认证登陆数据库，然后使用下面的命令进行修改

alter user SYS identified by pwd ;

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

orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=pwd entries=10 force=y；其中的password项所指定的就是SYS的密码

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