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 adminstrativeaccess. */
/* Refer to the Installation and User'sGuide 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 thess_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}; Oracle11gRelease2中oracle建议独立地管理Grid Infrastructure和ASM实例，因此有必要创建更多的os用户组以满足不同的权限分配。
我们在11.2的GI中常用的ASM用户组有以下三个：
1、OSASM(asmadmin)用户组
如果使用ASM，那么我们必须创建osasm(asmadmin)用户组，该OSASM用户组的成员将被赋予SYSASM权限，以满足组成员管理Oracle Clusterware和Oracle ASM的权限需求。
2、OSDBA for ASM group(asmdba)用户组
OSDBA(asmdba)用户组的成员将被赋予读写访问ASM文件的权限。GI/CRS拥有者用户和所有oracle数据库软件的拥有者必须是该组的成员。同时所有OSDBA(dba)用户组的成员也必须是asmdba组的成员。
3、OSOPER for ASM(asmoper)用户组
asmoper和osoper类似都是额外的可选择创建的用户组，创建该独立的用户组以满足赋予用户一套受限的ASM实例管理权限(ASM的SYSOPER角色)，该权限包括了启动和停止ASM实例，默认情况下OSASM(asmadmin)组成员将拥有所有SYSOPER的ASM管理权限。
在11.2的GI/CRS环境中一般会创建grid或griduser用户来管理GI软件和ASM实例，以如下方式创建grid用户:
useradd -g oinstall -G asmadmin,asmdba,asmoper grid
id grid
uid=54322(grid) gid=54321(oinstall)
groups=54321(oinstall),700(asmadmin),701(asmdba),55000(asmoper)
在Oracle中有两类特殊的权限SYSDBA和SYSOPER，当DBA需要对数据库进行维护管理操作的时候必须具有这两类特殊权限之中的一种。在数据库没有打开的时候，使用数据库内建的账号是无法登陆数据库的，但是拥有SYSDBA或是SYSOPER权限的用户是可以登陆的。认证用户是否拥有两类特殊权限的方法有两种：OS认证和口令文件认证。
Oracle数据库究竟使用OS认证还是口令文件认证来进行管理取决于下面三个因素：
1、SQLNET.ORA参数文件中的参数SQLNET.AUTHENTICATION_SERVICES设置
2、PFILE(SPFILE)参数文件中的参数REMOTE_LOGIN_PASSWORDFILE设置
3、口令文件orapw$SID(Linux) | PWD$SID.ora(Windows)
Oracle权限认证的基本顺序是这样的，先由SQLNET.AUTHENTICATION_SERVICES的设置值来决定是使用OS认证还是口令文件认证，如果使用口令文件认证的话就要看后面两个条件了：如果REMOTE_LOGIN_PASSWORDFILE参数设置为非NONE而且口令文件存在的话就能正常使用口令文件认证，否则将会失败。
SQLNET.AUTHENTICATION_SERVICES参数
在SQLNET.ORA（位于$ORACLE_HOME/NETWORK/ADMIN目录中）文件中，需要修改时直接用文本编辑器打开修改就行了，对于不同的操作系统SQLNET.AUTHENTICATION_SERVICES的取值会有些不一样，通常我们会用到下面的一些设置值：
SQLNET.AUTHENTICATION_SERVICES = (ALL)对Linux系统，支持OS认证和口令文件认证。
对Windows系统，实际实验是不支持此参数，验证失败。
SQLNET.AUTHENTICATION_SERVICES = (NTS)此设置值仅用于Windows系统，此设置同时支持OS认证和口令文件认证，只有在设置了(NTS)值之后运行在Windows系统上的Oracle才支持OS认证。
SQLNET.AUTHENTICATION_SERVICES = (NONE)此设置值在Windows和Linux是作用一样的，指定Oracle只使用口令文件认证。
不设置此参数或SQLNET.AUTHENTICATION_SERVICES =对Linux系统，默认支持OS认证和口令文件认证。
对Windows系统，默认只支持口令文件认证，不支持OS认证。
OS认证实现
Oracle使用操作系统中的两个用户组来控制OS认证，在不同的操作系统中这两个用户组的名称是不一样的，一般来说他们是OSDBA 和 OSOPER，这两个用户组都是在Oracle安装的时候创建的。下面列出不同系统中这两个用户组的名字：
Operating System Group
UNIX User Group
UNIX User Group
OSDBA
dba
ORA_DBA
OSOPER
oper
ORA_OPER
OSDBA用户组的用户可以使用SYSDBA权限登陆数据库，OSOPER用户组的的用户可以使用SYSOPER权限来登陆数据库。使用sqlplus可以用下面方法登陆
CONNECT/ASSYSDBA
CONNECT/ASSYSOPER
拥有OS权限的用户登陆数据库时不再需要输入用户名和密码，因此使用下面的命令也是可以正常登陆的：
CONNECTANY_USER_NAME/ANY_PASSWORDASSYSDBA
CONNECTANY_USER_NAME/ANY_PASSWORDASSYSOPER
因此要创建一个新的OS认证帐号步骤是：
建立一个OS用户将用户加入到OSDBA或是OSOPER用户组用新增加的用户登陆系统，然后输入sqlplus / AS SYSDBA进行登陆REMOTE_LOGIN_PASSWORDFILE参数
REMOTE_LOGIN_PASSWORDFILE系统参数的设置制定了数据库使用口令文件的方法，此参数可以设置的值有三个：
REMOTE_LOGIN_PASSWORDFILE = NONE不使用口令文件
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE使用口令文件，但只有一个数据库实例可用使用
REMOTE_LOGIN_PASSWORDFILE = SHARED多个数据库实例共用一个口令文件，这种设置下是不能增加其他数据库用户作为特殊权限用户到口令文件中的。
REMOTE_LOGIN_PASSWORDFILE参数属于初始化参数，只能在init.ora/pfile中指定或是在数据库打开状态下使用下面语句修改，然后重新启动数据库。
ALTERSYSTEMSETREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVESCOPE=SPFILE;
要检查当前REMOTE_LOGIN_PASSWORDFILE的设定值在登陆Oracle后输入下面的命令
SYS@seiang11g>show parameter remote
NAME TYPE VALUE
------------------------------------ -----------------------------------------
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfilestring EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
result_cache_remote_expiration integer 0
下面是11g官方文档的解释：
*******************************************************************************
Selecting anAuthentication Method for Database Administrators
DatabaseAdministrators can authenticate database administrators through the datadictionary, (using an account password) like other users.Keep in mind that beginning with OracleDatabase 11gRelease 1, database passwords are case-sensitive. (Youcan disable case sensitivity and return to pre–Release 11gbehavior bysetting theSEC_CASE_SENSITIVE_LOGONinitialization parameter toFALSE.)
数据库管理员可以通过数据字典（使用帐户密码）与其他用户进行身份验证数据库管理员。 请记住，从Oracle Database 11g第1版开始，数据库密码区分大小写。（可以通过将SEC_CASE_SENSITIVE_LOGON初始化参数设置为FALSE来禁用区分大小）
In addition tonormal data dictionary authentication, the following methods are availableforauthenticating database administrators withtheSYSDBAorSYSOPERprivilege:
除了正常的数据字典认证之外，以下方法可用于使用SYSDBA或SYSOPER权限对数据库管理员进行身份验证
Operating system (OS) authenticationPassword filesStrong authentication with a network-based authentication service, such as Oracle Internet DirectoryThese methodsare required to authenticate a database administrator when the database is notstarted or otherwise unavailable. (They can also be used when the database isavailable.)
当数据库未启动或不可用时，需要这些方法来对数据库管理员进行身份验证。（当数据库可用时也可以使用它们。）
Notes:
·These methods replace theCONNECT INTERNALsyntax provided with earlierversions of Oracle Database.CONNECT INTERNALis no longer supported.
·Operating system authentication takes precedence over passwordfile authentication. If you meet the requirements for operating systemauthentication, then even if you use a password file, you will be authenticatedby operating system authentication.
Your choice willbe influenced by whether you intend to administer your database locally on thesame system where the database resides, or whether you intend to administermany different databases from a single remote client.Figure 1-2illustrates the choices you havefor database administrator authentication schemes.
Figure 1-2 Database AdministratorAuthentication Methods
If you areperforming remote database administration, consult your Oracle Netdocumentation to determine whether you are using a secure connection. Mostpopular connection protocols, such as TCP/IP and DECnet, are not secure.
Nonsecure Remote Connections
To connect toOracle Database as a privileged user over a nonsecure connection, you must beauthenticated by a password file.When using passwordfile authentication, the database uses a password file to keep track ofdatabase user names that have been granted theSYSDBAorSYSOPERsystem privilege. This form ofauthentication is discussed in"Using Password FileAuthentication".
要通过非安全连接作为特权用户连接到Oracle数据库，必须通过密码文件进行身份验证。 使用密码文件认证时，数据库使用密码文件来跟踪已被授予SYSDBA或SYSOPER系统权限的数据库用户名。
Local Connections and Secure RemoteConnections
You can connectto Oracle Database as a privileged user over a local connection or a secureremote connection in two ways:
If the database has a password file and you have been granted theSYSDBAorSYSOPERsystem 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 grantedSYSDBAorSYSOPERprivileges 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 grantedSYSDBAprivileges. A similar group, OSOPER, is used to grantSYSOPERprivileges to users.如果服务器未使用密码文件，或者尚未授予SYSDBA或SYSOPER权限，因此不在密码文件中，则可以使用操作系统身份验证。 在大多数操作系统上，数据库管理员的身份验证包括将数据库管理员的操作系统用户名放在一个特殊的组中，一般称为OSDBA。 该组中的用户被授予SYSDBA权限。 类似的组OSOPER用于向用户授予SYSOPER权限。
Using Operating System Authentication
This section describeshow to authenticate an administrator using the operating system.
OSDBA and OSOPER
Membership in one of two specialoperating system groups enables a DBA to authenticate to the database throughthe operating system rather than with a database user name and password. Thisis known as operating system authentication. These operating system groups aregenerically referred to as OSDBA and OSOPER. The groups are created andassigned specific names as part of the database installation process. The defaultnames vary depending upon your operating system, and are listed in thefollowing table:
Operating System Group
UNIX User Group
Windows User Group
OSDBA
dba
ORA_DBA
OSOPER
oper
ORA_OPER
在两个特殊操作系统组之一中的成员使DBA能够通过操作系统而不是使用数据库用户名和密码对数据库进行身份验证， 这被称为操作系统认证。
Oracle UniversalInstaller uses these default names, but you can override them. One reason tooverride them is if you have multiple instances running on the same hostcomputer. 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 OSDBAgroup for each instance. For example, for two instances on the same host, theOSDBA group for the first instance could be nameddba1, and OSDBA for the second instancecould be nameddba2. The first DBA would be a memberofdba1only, and the second DBA would bea member ofdba2only. Thus, when using operatingsystem authentication, each DBA would be able to connect only to his assignedinstance.
Oracle Universal Installer使用默认名称，但你可以覆盖它们。 覆盖它们的一个原因是如果您有多个实例运行在同一主机上。 如果每个实例都要有一个不同的人作为DBA，则可以通过为每个实例创建一个不同的OSDBA组来提高每个实例的安全性。 例如，对于同一主机上的两个实例，第一个实例的OSDBA组可以命名为dba1，第二个实例的OSDBA组可以命名为dba2。 第一个DBA只是dba1的成员，第二个DBA只是dba2的成员。 因此，当使用操作系统认证时，每个DBA将只能连接到他分配的实例。
Membership inthe OSDBA or OSOPER group affects your connection to the database in thefollowing ways:
If you are a member of the OSDBA group and you specifyAS SYSDBAwhen you connect to the database, then you connect to the database with theSYSDBAsystem privilege.If you are a member of the OSOPER group and you specifyAS SYSOPERwhen you connect to the database, then you connect to the database with theSYSOPERsystem privilege.If you are not a member of either of these operating system groups and you attempt to connect asSYSDBAorSYSOPER, theCONNECTcommand fails.Preparing to Use Operating SystemAuthentication
To enable operating systemauthentication of an administrative user:
Create an operating system account for the user.Add the account to theOSDBAorOSOPERoperating system defined groups.Connecting Using Operating SystemAuthentication
A user can be authenticated, enabled as an administrativeuser, and connected to a local database by typing one of the following SQL*Pluscommands:
CONNECT / ASSYSDBA
CONNECT / AS SYSOPER
For the Windowsplatform only, remote operating system authentication over a secure connectionis 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 computerand database host computer must be on a Windows domain.
口令文件存放着被授予SYSDBA或SYSOPER权限的用户的用户名和密码。它是一个加密的文件，用户不能修改这个文件，在Linux系统中口令文件一般保存在$ORACLE_HOME/dbs目录下，文件名为orapw$SID；在Windows系统中口令文件一般保存在$ORACLE_HOME/database目录下，文件名为PWD$SID.ora。
使用口令文件认证的基本步骤是：
使用orapwd工具生成口令文件设置REMOTE_LOGIN_PASSWORDFILE为EXCLUSIVE或是SHARED 使用SYS登陆数据库，创建新的数据库用户使用GRANT命令授予新创建的用户SYSDBA/SYSOPER权限Using Password File Authentication
This sectiondescribes how to authenticate an administrative user using password fileauthentication.
Preparing to Use Password FileAuthentication
To enable authentication of anadministrative user using password file authentication you must do thefollowing:
If not already created, create the password file using theORAPWDutility:2. ORAPWD FILE=filename ENTRIES=max_users
See"Creating and Maintaining aPassword File"for details
Notes:
o When you invoke Database ConfigurationAssistant (DBCA) as part of the Oracle Database installation process, DBCAcreates a password file.
o Beginning with Oracle Database 11gRelease1, passwords in the password file are case-sensitive unless you includetheIGNORECASE=Ycommand-lineargument.
从Oracle Database 11g第1版开始，密码文件中的密码区分大小写，除非您包含IGNORECASE = Y命令行参数。
Set theREMOTE_LOGIN_PASSWORDFILEinitialization parameter toEXCLUSIVE. (This is the default).Note:
REMOTE_LOGIN_PASSWORDFILEis a static initialization parameter and thereforecannot be changed without restarting the database.
Connect to the database as userSYS(or as another user with the administrative privileges).If the user does not already exist in the database, create the user and assign a password.Keep in mind that beginning with OracleDatabase 11gRelease 1, database passwords are case-sensitive. (Youcan disable case sensitivity and return to pre–Release 11gbehaviorby setting theSEC_CASE_SENSITIVE_LOGONinitialization parameter toFALSE.)
Grant theSYSDBAorSYSOPERsystem privilege to the user:7. GRANT SYSDBA to oe;
This statement adds the user to the password file,thereby enabling connectionASSYSDBA.
Connecting UsingPassword File Authentication
Administrativeusers can be connected and authenticated to a local or remote database by usingthe SQL*PlusCONNECTcommand. They must connect usingtheir username and password and theAS SYSDBAorAS SYSOPERclause.Notethat beginning with Oracle Database 11gRelease 1, passwords arecase-sensitive unless the password file was created with theIGNORECASE=Yoption.
For example,useroehas been granted theSYSDBAprivilege, sooecan connect as follows:
CONNECT oe ASSYSDBA
However,useroehas not been granted theSYSOPERprivilege, so the followingcommand will fail:
CONNECT oe ASSYSOPER
Note:
Operating system authentication takesprecedence over password file authentication. Specifically, if you are a memberof the OSDBA or OSOPER group for the operating system, and you connect asSYSDBA or SYSOPER, you will be connected with associated administrativeprivileges regardless of theusername/passwordthat youspecify.
操作系统认证优先于密码文件认证。 具体来说，如果您是操作系统的OSDBA或OSOPER组的成员，并以SYSDBA或SYSOPER身份连接，则无论您指定的用户名/密码如何，都将连接相关联的管理权限。
If you are not inthe OSDBA or OSOPER groups, and you are not in the password file, thenattempting to connect asSYSDBAorasSYSOPERfails.
如果您不在OSDBA或OSOPER组中，并且您不在密码文件中，则尝试以SYSDBA或SYSOPER身份连接。
Creatingand Maintaining a Password FileYou can create a passwordfile using the password file creation utility,ORAPWD. For someoperating systems, you can create this file as part of your standardinstallation.
Creatinga Password File with ORAPWDThe syntax of theORAPWDcommand is as follows:
ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}]
使用Oracle提供的工具orapwd来创建或者重新初始化一个口令文件：
[oracle@seiang11g ~]$ orapwd
Usage: orapwd file=<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 commandline,
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 Vaultonly).
Theremust be no spaces around the equal-to (=) character.
[oracle@seiang11g ~]$ orapwdfile='$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 aresummarized in the following table.
Argument
Description
FILE
Name to assign to the password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
ENTRIES
(Optional) Maximum number of entries (user accounts) to permit in the file.
FORCE
(Optional) Ify, permits overwriting an existing password file.
IGNORECASE
(Optional) Ify, passwords are treated as case-insensitive.
There are no spacespermitted around the equal-to (=) character.
The command prompts fortheSYSpassword and stores the password in the created password file.
ORAPWD Command Line Argument DescriptionsThe following sectionsdescribe theORAPWDcommand line arguments.
FILE
This argumentsets the name of the password file being created. You must specify the fullpath name for the file. The contents of this file are encrypted, and the filecannot be read directly. This argument is mandatory.
此参数设置正在创建的密码文件的名称。 您必须指定文件的完整路径名。 该文件的内容被加密，文件无法直接读取。 这个说法是强制性的。
The file namerequired for the password file is operating system specific. Some operatingsystems require the password file to adhere to a specific format and be locatedin a specific directory. Other operating systems allow the use of environmentvariables to specify the name and location of the password file.
密码文件所需的文件名是基于特定的操作系统的。 某些操作系统要求密码文件遵循特定格式，并位于特定的目录中。 其他操作系统允许使用环境变量来指定密码文件的名称和位置。
Table 1-1lists the required name and location for the password file onthe UNIX, Linux, and Windows platforms. For other platforms, consult yourplatform-specific documentation.
Table 1-1Required Password File Name and Location on UNIX, Linux, and Windows
Platform
Required Name
Required Location)
UNIX and Linux
orapwORACLE_SID
ORACLE_HOME/dbs
Windows
PWDORACLE_SID.ora
ORACLE_HOME\database
For example,for a database instance with the SIDorcldw, the passwordfile must be namedorapworcldwon Linux andPWDorcldw.oraon Windows.
In an OracleReal Application Clusters environment on a platform that requires anenvironment variable to be set to the path of the password file, theenvironment variable for each instance must point to the same password file.
在需要将环境变量设置为密码文件路径的平台上的RAC环境中，每个实例的环境变量必须指向相同的密码文件。
Caution:
Itis critically important to the security of your system that you protect yourpassword file and the environment variables that identify the location of thepassword file. Any user with access to these could potentially compromise thesecurity of the connection.
警告：
从系统的安全性来说，保护密码文件和标识密码文件位置的环境变量至关重要。 任何具有访问权限的用户都可能会危及连接的安全性。
ENTRIES
This argumentspecifies the number of entries that you require the password file to accept.This number corresponds to the number of distinct users allowed to connect tothe database asSYSDBAorSYSOPER. The actualnumber of allowable entries can be higher than the number of users, because theORAPWDutility continues to assign password entries until an operatingsystem block is filled. For example, if your operating system block size is 512bytes, it holds four password entries. The number of password entries allocatedis always a multiple of four.
此参数指定需要密码文件接受的条目数。 此数字对应于允许以SYSDBA或SYSOPER连接到数据库的不同用户的数量。 允许的条目的实际数量可以高于用户数，因为ORAPWD实用程序继续分配密码条目，直到操作系统块被填充为止。 例如，如果您的操作系统块大小为512字节，则它将保存四个密码条目。 分配的密码条目数量总是四的倍数。
Entries can bereused as users are added to and removed from the password file. If you intendto specifyREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allowthe granting ofSYSDBAandSYSOPERprivileges to users, this argument is required.
当用户被添加到密码文件和从密码文件中删除时，可以重复使用条目。 如果您打算指定REMOTE_LOGIN_PASSWORDFILE= EXCLUSIVE，并允许向用户授予SYSDBA和SYSOPER权限，则此参数是必需的。
Caution:
Whenyou exceed the allocated number of password entries, you must create a newpassword file. To avoid this necessity, allocate more entries than you thinkyou will ever need.
当超过分配的密码条目数时，必须创建一个新的密码文件。 为了避免这种需要，请分配比您以前需要的更多的条目。
FORCE
This argument,if set toY, enables youto overwrite an existing password file. An error is returned if a password fileof the same name already exists and this argument is omitted or set toN.
此参数（如果设置为Y）使您能够覆盖现有的密码文件。 如果同名的密码文件已经存在，并且此参数被省略或设置为N，则返回错误。
IGNORECASE
If this argumentis set toy, passwordsare case-insensitive. That is, case is ignored when comparing the password thatthe user supplies during login with the password in the password file.
如果此参数设置为y，则密码不区分大小写。 也就是说，将用户在登录时提供的密码与密码文件中的密码进行比较时，将忽略大小写。
Sharingand Disabling the Password File
You use theinitialization parameterREMOTE_LOGIN_PASSWORDFILEto control whether a password file is shared among multipleOracle Database instances. You can also use this parameter to disable passwordfile authentication. The values recognized forREMOTE_LOGIN_PASSWORDFILEare:
可以使用初始化参数REMOTE_LOGIN_PASSWORDFILE来控制是否在多个Oracle数据库实例之间共享密码文件。 您还可以使用此参数来禁用密码文件身份验证。
·NONE: Setting this parameter toNONEcauses Oracle Database to behave as if the password file doesnot exist. That is, no privileged connections are allowed over nonsecureconnections.
·EXCLUSIVE: (The default) AnEXCLUSIVEpassword file can be used with only one instance of onedatabase. Only anEXCLUSIVEfile can be modified. Using anEXCLUSIVEpassword file enables you to add, modify, and delete users. Italso enables you to change theSYSpassword with theALTERUSERcommand.
·SHARED: ASHAREDpassword file can be used by multiple databases running on thesame server, or multiple instances of an Oracle Real Application Clusters(Oracle RAC) database. ASHAREDpassword file cannot be modified. Therefore, you cannot addusers to aSHAREDpassword file. Any attempt to do so or to change the password ofSYSor other users with theSYSDBAorSYSOPERprivileges generates an error. All users needingSYSDBAorSYSOPERsystemprivileges must be added to the password file whenREMOTE_LOGIN_PASSWORDFILEis set toEXCLUSIVE. After allusers are added, you can changeREMOTE_LOGIN_PASSWORDFILEtoSHARED, and thenshare the file.
This option isuseful if you are administering multiple databases or an Oracle RAC database.
IfREMOTE_LOGIN_PASSWORDFILEis set toEXCLUSIVEorSHAREDand the password file is missing, this is equivalent to settingREMOTE_LOGIN_PASSWORDFILEtoNONE.
如果REMOTE_LOGIN_PASSWORDFILE设置为EXCLUSIVE或SHARED，并且密码文件丢失，则相当于将REMOTE_LOGIN_PASSWORDFILE设置为NONE。
Note:
You cannotchange the password forSYSifREMOTE_LOGIN_PASSWORDFILEis set toSHARED. An errormessage is issued if you attempt to do so.
如果REMOTE_LOGIN_PASSWORDFILE设置为“共享”，则无法更改SYS的密码。 如果您尝试这样做，将发出错误消息。
KeepingAdministrator Passwords Synchronized with the Data Dictionary
If you change theREMOTE_LOGIN_PASSWORDFILEinitialization parameter fromNONEtoEXCLUSIVEorSHARED, or if youre-create the password file with a differentSYSpassword, thenyou must ensure that the passwords in the data dictionary and password file fortheSYSuser are the same.
To synchronize theSYSpasswords, use theALTERUSERstatement tochange theSYSpassword. TheALTERUSERstatement updatesand synchronizes both the dictionary and password file passwords.
To synchronize thepasswords for non-SYSusers who log in using theSYSDBAorSYSOPERprivilege, you must revoke and then regrant the privilege to theuser, as follows:
1.Find all users who have been granted theSYSDBAprivilege.
2. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
3.Revoke and then re-grant theSYSDBAprivilege 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 theSYSOPERprivilege.
7. SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';
8.Revoke and regrant theSYSOPERprivilege to these users.
9. REVOKE SYSOPER FROM non-SYS-user;
10.GRANT SYSOPER TO non-SYS-user;Adding Users to aPassword File
When you grantSYSDBAorSYSOPERprivileges to a user, that user's name and privilege informationare added to the password file. If the server does not have anEXCLUSIVEpassword file (that is, if the initialization parameterREMOTE_LOGIN_PASSWORDFILEisNONEorSHARED, or thepassword file is missing), Oracle Database issues an error if you attempt togrant these privileges.
A user's name remains inthe 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 fromthe password file.
Creating a Password File and Adding New Users to It
Use the followingprocedure to create a password and add new users to it:
1.Follow the instructions for creating a password file asexplained in"Creating a PasswordFile with ORAPWD".
2.Set theREMOTE_LOGIN_PASSWORDFILEinitialization parameter toEXCLUSIVE. (This is thedefault.)
Note:
REMOTE_LOGIN_PASSWORDFILEis a static initialization parameter and therefore cannot bechanged without restarting the database.
3.Connect withSYSDBAprivileges as shown in the following example, and enter theSYSpassword when prompted:
4. CONNECT SYS AS SYSDBA
5.Start up the instance and create the database if necessary, ormount and open an existing database.
6.Create users as necessary. GrantSYSDBAorSYSOPERprivileges to yourself and other users as appropriate. Granting and Revoking SYSDBA andSYSOPER Privileges
If your server is usinganEXCLUSIVEpassword file, use theGRANTstatement to grant theSYSDBAorSYSOPERsystem privilege to a user, as shown in the following example:
GRANT SYSDBA TO oe;
Use theREVOKEstatement to revoke theSYSDBAorSYSOPERsystem privilege from a user, as shown in the following example:
REVOKE SYSDBA FROM oe;
BecauseSYSDBAandSYSOPERare the most powerful database privileges, theWITH ADMINOPTIONis not used intheGRANTstatement. That is, the grantee cannot in turn grant theSYSDBAorSYSOPERprivilege to another user. Only a user currently connected asSYSDBAcan grant or revoke another user'sSYSDBAorSYSOPERsystemprivileges. These privileges cannot be granted to roles, because roles areavailable only after database startup. Do not confuse theSYSDBAandSYSOPERdatabaseprivileges with operating system roles.
因为SYSDBA和SYSOPER是最强大的数据库权限，所以在GRANT语句中不使用WITH ADMIN OPTION。 也就是说，受让人不能依次向其他用户授予SYSDBA或SYSOPER权限。 只有当前以SYSDBA身份连接的用户才能授予或撤销其他用户的SYSDBA或SYSOPERsystem权限。 这些权限不能授予角色，因为角色仅在数据库启动后可用。 不要将SYSDBA和SYSOPER数据库特权与操作系统角色混淆。
ViewingPassword File Members
Use theV$PWFILE_USERSview to seethe users who have been granted theSYSDBA,SYSOPER, orSYSASMsystem privileges. The columns displayed by this view are asfollows:
Column
Description
USERNAME
This column contains the name of the user that is recognized by the password file.
SYSDBA
If the value of this column isTRUE, then the user can log on with theSYSDBAsystem privileges.
SYSOPER
If the value of this column isTRUE, then the user can log on with theSYSOPERsystem privileges.
SYSASM
If the value of this column isTRUE, then the user can log on with theSYSASMsystem privileges.
Note:
SYSASMis valid only for Oracle Automatic Storage Management instances.
每次在Oracle系统里面使用GRANT SYSDBA/SYSOPER授予新用户特殊权限或是ALTER USER命令修改拥有SYSDBA/SYSOPER权限的用户密码的时候，Oracle都会自动的修改口令文件，增加或是修改相应的项目，这样保证在数据没有打开的情况拥有特殊权限的用户能正常的登陆数据库以进行管理操作。
实验环境：
操作系统：CentOS 7.1
数据库：Oracle 11.2.0.4
上面长篇大论的说了那么多，下面我们来做实验验证一下。本实验是基于Linux系统来做的，做实验之前先使用下面的命令创建一个口令文件：
[oracle@seiang11g ~]$ orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=XXX entries=5force=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 onWed Aug 9 11:58:26 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options
SYS@seiang11g>
[oracle@seiang11g ~]$ sqlplus111/222 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production onWed Aug 9 11:58:41 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options
SYS@seiang11g>
远程使用口令文件方式登陆，失败
[oracle@seiang11g ~]$ sqlplussys/oracle@ORADB11G as sysdba
SQL*Plus: Release 11.2.0.4.0 Production onWed 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 3attempts, exiting SQL*Plus
[oracle@seiang11g ~]$ sqlplussys/oracle@10.1.1.46/ORADB11G as sysdba
SQL*Plus: Release 11.2.0.4.0 Production onWed 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 3attempts, 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 912: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 912: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 912: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 assysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 912: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 912: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 ~]$ sqlplussys/oracle as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 912: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 RealApplication 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 rightsreserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With thePartitioning, 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 rightsreserved.
Connectedto:
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With thePartitioning, 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 onWed Aug 9 11:58:26 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining andReal Application Testing options
SYS@seiang11g>
远程使用口令文件认证，成功
[oracle@seiang11g admin]$ sqlplussys/oracle@ORADB11G as sysdba
SQL*Plus: Release 11.2.0.4.0 Production onWed 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 913: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 RealApplication Testing options
SYS@ORADB11G>
说明：如果要取消SYSDBA权限只需要运行下面的语句就可以了
SYS@ORADB11G>revokesysdba 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 TRUEFALSE
TEST FALSE TRUE FALSE
2、授予权限时出现”ORA-01994: GRANT failed: password file missing or disabled”
出现这种情况是因为没有创建口令文件，或者是口令文件放置的目录不正确，Oracle找不到。只要重建或将口令文件置于$ORACLE_HOME/dbs/目录中就可以了。
3、忘记了SYS帐号的密码怎么办？
如果数据库启用的OS认证登陆，则可以用OS认证登陆数据库，然后使用下面的命令进行修改
alteruserSYSidentifiedbypwd;
如果没有启用OS认证登陆，则需要用orapwd重建口令文件
orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID'password=pwd entries=10 force=y；其中的password项所指定的就是SYS的密码
作者：SEian.G（苦练七十二变，笑对八十一难）
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
![实战演练丨SCN太大引发ORA-600[2252]](/upload/rand_pic/2-07.jpg)
