相關鏈接：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（苦練七十二變，笑對八十一難）