相關連結：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.  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:

在兩個特殊作業系統組之一中的成員使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  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.

密碼檔案所需的檔名是基於特定的作業系統的。 某些作業系統要求密碼檔案遵循特定格式，並位於特定的目錄中。 其他作業系統允許使用環境變數來指定密碼檔案的名稱和位置。

 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 .

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