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

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

 

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

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

 

1oinstall用户组

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

 

2OSDBA用户组(dba)

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

 

3OSOPER用户组(oper)

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

 

创建OSOPER用户组的方法:

# /usr/sbin/groupadd oper

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

 

而在11.2GI/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)

 

注意OSDBAOSOPER用户组都受到$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 InfrastructureASM实例,因此有必要创建更多的os用户组以满足不同的权限分配。

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

 

1OSASM(asmadmin)用户组

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

 

2OSDBA for ASM group(asmdba)用户组

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

 

3OSOPER for ASM(asmoper)用户组

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

11.2GI/CRS环境中一般会创建gridgriduser用户来管理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中有两类特殊的权限SYSDBASYSOPER,当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)

此设置值在WindowsLinux是作用一样的,指定Oracle只使用口令文件认证。

  • 不设置此参数或SQLNET.AUTHENTICATION_SERVICES =

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

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

 

OS认证实现

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

Operating
System Group

UNIX
User Group

UNIX
User Group

OSDBA

dba

ORA_DBA

OSOPER

oper

ORA_OPER

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

CONNECT / AS SYSDBA 
CONNECT / AS SYSOPER

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

CONNECT ANY_USER_NAME/ANY_PASSWORD AS SYSDBA 
CONNECT ANY_USER_NAME/ANY_PASSWORD AS SYSOPER

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

  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 11g1版开始,数据库密码区分大小写。(可以通过将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:

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

  • 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 clientFigure 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数据库,必须通过密码文件进行身份验证。 使用密码文件认证时,数据库使用密码文件来跟踪已被授予SYSDBASYSOPER系统权限的数据库用户名。

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.

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

 

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

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

Using Operating System Authentication

This section describes
how to authenticate an administrator using the operating system.

OSDBA and OSOPER

Membership in one of two special
operating system groups enables a DBA to authenticate to the database through
the operating system rather than with a database user name and password. This
is known as operating system authentication. These operating system groups are
generically referred to as OSDBA and OSOPER. The groups are created and
assigned specific names as part of the database installation process. The default
names vary depending upon your operating system, and are listed in the
following table:

Operating System Group

UNIX User Group

Windows User Group

OSDBA

dba

ORA_DBA

OSOPER

oper

ORA_OPER

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

Oracle Universal
Installer uses these default names, but you can override them. One reason to
override them is if you have multiple instances running on the same host
computer. If each instance is to have a different person as the principal DBA,
you can improve the security of each instance by creating a different OSDBA
group for each instance. For example, for two instances on the same host, the
OSDBA group for the first instance could be named dba1, and OSDBA for the second instance
could be named dba2. The first DBA would be a member
of dba1 only, and the second DBA would be
a member of dba2 only. Thus, when using operating
system authentication, each DBA would be able to connect only to his assigned
instance.

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

Membership in
the OSDBA or OSOPER group affects your connection to the database in the
following ways:

  • If you are a member of
    the OSDBA group and you specify AS SYSDBA when you connect to the database, then you
    connect to the database with theSYSDBA system
    privilege.
  • If you are a member of
    the OSOPER group and you specify AS SYSOPER when you connect to the database, then you
    connect to the database with theSYSOPER system
    privilege.
  • If you are not a member
    of either of these operating system groups and you attempt to connect
    as SYSDBA or SYSOPER, the CONNECT command
    fails.

 

Preparing to Use Operating System
Authentication

To enable operating system
authentication of an administrative user:

  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.

 

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

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

  1. 使用orapwd工具生成口令文件
  2. 设置REMOTE_LOGIN_PASSWORDFILEEXCLUSIVE或是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 11g1版开始,密码文件中的密码区分大小写,除非您包含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.

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

 

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.

如果您不在OSDBAOSOPER组中,并且您不在密码文件中,则尝试以SYSDBASYSOPER身份连接。

 

Creating
and Maintaining a Password File

You can create a password
file using the password file creation utility, ORAPWD. For some
operating systems, you can create this file as part of your standard
installation.

Creating
a Password File with ORAPWD

The syntax of 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.

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) If y, permits overwriting an existing password file.

IGNORECASE

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

 

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

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

此参数指定需要密码文件接受的条目数。 此数字对应于允许以SYSDBASYSOPER连接到数据库的不同用户的数量。 允许的条目的实际数量可以高于用户数,因为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
,并允许向用户授予SYSDBASYSOPER权限,则此参数是必需的。

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设置为EXCLUSIVESHARED,并且密码文件丢失,则相当于将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.

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

 

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:

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 is TRUE, then the user can log on with the SYSDBA system privileges.

SYSOPER

If the value of this column is TRUE, then the user can log on with the SYSOPER system privileges.

SYSASM

If the value of this column is TRUE, then the user can log on with the SYSASM system privileges.

 

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,结果集中的SYSDBSYSOP分别代表是否有SYSDBASYSOPER权限。

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(苦练七十二变,笑对八十一难)

 

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » Oracle中两种认证方式:OS认证与口令文件认证

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址