REMOTE_LOGIN_PASSWORDFILE参数和ORAPW密码文件

俗话说 万丈高楼从地起，从业ORACLE 7年多后，我重新开始看基础类容，将列出一些DBA容易忽视的类容，并且做一些笔记和实验和大家共享。
关于REMOTE_LOGIN_PASSWORDFILE参数文件：
REMOTE_LOGIN_PASSWORDFILEare:
■ 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 SYSOPER system privileges must be added to the
password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all
users are added, you can change REMOTE_LOGIN_PASSWORDFILEto SHARED, and then
share the file.
  If REMOTE_LOGIN_PASSWORDFILEis set to EXCLUSIVE or SHAREDand the password file is
missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

  If you change the REMOTE_LOGIN_PASSWORDFILEinitialization parameter from NONEto
EXCLUSIVEor SHARED, or if you re-create the password file with a different SYS
password, then you must ensure that the passwords in the data dictionary and
password file for the SYSuser are the same.
  To synchronize the SYS passwords, use the ALTER USER statement to change the SYS
password. The ALTER USERstatement 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
SYSOPERprivilege, you must revoke and then regrant the privilege to the user
   After you remove this file, only those users who can be authenticated by
the operating system can perform SYSDBA or SYSOPER database administration operations.
首先文档给出了REMOTE_LOGIN_PASSWORDFILE的定义，其实这里注意REMOTE视乎有些迷惑性
本参数值控制了是否可以使用ORAPWD文件，即使本机非DBA组的用户同样适用。同时注意一下
ORAPWD文件除了存储SYS密码外其实也会存储其他非SYS用户拥有SYSDBA权限的用户密码。如果
缺少文件或者设置为NONE则只有操作系统认证用户才能使用SYSDBA权限

NONE        代表不使用ORAPWD文件进行SYSDBA权限的认证。
EXCLUSIVE 代表ORAPWD文件只共一个实例使用，可以进行修改，当然也支持使用ALTER USER来修改
                 ORAPWD文件，言外之意SHARED是不支持ALTER USER修改的
SHARED    代表ORAPWD文件能够供多个数据库或者多个实例共享，所以是不能新的赋予SYSDBA和回收
               SYSDBA的操作都会失败，同时修改密码也会失败。
文档指出：
如果如果使用ALTER USER SYS修改了SYS密码，那么这个操作会自动同步数据字典和ORAPWD文件，而
如果是非SYS用户，必须要使用REVOKE和GRANT重新赋权才能同步，但是测试中发现并不需要重新
revoke和GRANT。

          
实验如下：
NONE方式
SQL>  alter system set remote_login_passwordfile='none' scope=spfile;

System altered.

重启
SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      NONE
实验是否可以使用ORAPWD文件登陆（LLL用户非DBA组，使用ORWPWD认证）
[lll@tasm ~]$ sqlplus sys/gelc123 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 15 09:44:08 2014

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

ERROR:
ORA-01031: insufficient privileges


Enter user-name:
EXCLUSIVE 方式
同样修改参数使用LLL用户登录（LLL用户非DBA组，使用ORWPWD认证）
[lll@tasm ~]$ sqlplus sys/gelc123 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 15 09:49:31 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>

尝试增加特权用户
 grant sysdba to ppzhu;
 然后查看一下
 SQL> select * from V$PWFILE_USERS;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
PPZHU                          TRUE  FALSE FALSE
增加成功，如果我们修改了PPZHU用户的密码如下：
SQL> alter user ppzhu identified by test123;
然后试图LLL用户登录（LLL用户非DBA组，使用ORWPWD认证）：
[lll@tasm ~]$ sqlplus ppzhu/test123 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 15 10:10:15 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
可以登录，所以我说非SYS用户实际ALTER USER修改密码也会进行同步。

[oracle@tasm dbs]$ date
Tue Apr 15 09:59:55 CST 2014
[oracle@tasm dbs]$ ls -lrt|grep orapw
-rw-r----- 1 oracle oinstall       1536 Apr 15 09:59 orapwora11g
这里也可能看一下时间，alter 非sys用户orapwd文件也进行了更新这也从侧面表示ORAPWD文件时
存储非SYS用户密码的。同时也说明ALTER USER修改密码也会进行同步。

最后修改SHARED方式
alter system set remote_login_passwordfile='shared' scope=spfile
重启
测试如下：
SQL> alter user sys identified by kkk;
alter user sys identified by kkk
*
ERROR at line 1:
ORA-28046: Password change for SYS disallowed


SQL> grant sysdba to ppzhu;
grant sysdba to ppzhu
*
ERROR at line 1:
ORA-01999: password file cannot be updated in SHARED mode


SQL> alter user ppzhu identified by kkk;
alter user ppzhu identified by kkk
*
ERROR at line 1:
ORA-01999: password file cannot be updated in SHARED mode
可以看到一切关于会设涉及到RAPWD修改操作都禁止了。

总结一下关于密码文件：
1、ORAPWD文件用于存储拥有SYSDBA(SYSOPER)权限用户的密码，SYS是一定存储的，还有一些GRANT SYSDBA权限的用户
2、当REMOTE_LOGIN_PASSWORDFILE设置为EXCLUSIVE的情况下，可以通过ALTER USER的方式增加和删除 SYSDBA（SYSOPER)权限用户，并且同步到ORAPWD文件，SHARED不能增加和删除用户
3、当REMOTE_LOGIN_PASSWORDFILE设置为EXCLUSIVE的情况下，可以修改SYSDBA（SYSOPER)权限用户密码，并且同步到ORAPWD文件，SHARED不能修改
4、设置REMOTE_LOGIN_PASSWORDFILE 为NONE和缺失ORAPWD文件一样，不能使用ORAPWD文件验证用户的SYSDBA（SYSOPER)权限
5、设置REMOTE_LOGIN_PASSWORDFILE 为NONE或者缺失ORAPWD文件一样，只能通过操作系统认证（操作系统DBA组的方式）来验证SYSDBA权限