[Oracle] remote_login_passwordfile&SQLNET.AUTHENTICATION_SERVICES参数

1、remote_login_passwordfile参数

shared：一个或者多个库都能使用该password file。sys用户不能修改，如果尝试修改sys密码，会提示 "ORA-28046: Password change for SYS disallowed." ;其他用户有（SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM）权限的，密码不能更新，如果更新会提示"ORA-01999: password file cannot be updated in SHARED mode." ;给个别用户授权（SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM）是不被允许的，否则提示"ORA-01999: password file cannot be updated in SHARED mode.";如果passwordfile文件不存在，等同于REMOTE_LOGIN_PASSWORDFILE=none.

exclusive：password file只能被一个库使用，文件可以包含sys和非sys用户。如果password file文件不存在，等同于REMOTE_LOGIN_PASSWORDFILE=none. 
remote_login_passwordfile =exclusive时，启用口令文件，允许远程登录；

none：忽略password file，只能通过本地操作系统验证。

修改值使用下面方法，因为该值为静态参数，修改完成之后，需要重新启动数据库才能生效：

alter system set remote_login_passwordfile='exclusive' scope=spfile sid='*';

2、SQLNET.AUTHENTICATION_SERVICES参数详解

2.1、值为none时

#本地验证方式失效
[oracle@dax-mysql-slave admin]$ sqlplus / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
#使用用户名密码方式登录正常：
[oracle@dax-mysql-slave admin]$ sqlplus sys/oracle as sysdba
22:51:21 SYS@ boston> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle@boston
22:52:00 TEST@ boston> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle@192.168.168.177:1521/boston.us.oracle.com
22:53:02 TEST@ 192.168.168.177:1521/boston.us.oracle.com> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.2、值为all时

网络服务名方式和service_name方式失效
[oracle@dax-mysql-slave admin]$ sqlplus / as sysdba
22:54:21 SYS@ boston> 

[oracle@dax-mysql-slave admin]$ sqlplus sys/oracle as sysdba
22:54:45 SYS@ boston> 

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle@boston
ERROR:
ORA-12641: Authentication service failed to initialize

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle@192.168.168.177:1521/boston.us.oracle.com
ERROR:
ORA-12641: Authentication service failed to initialize

2.3、如果SQLNET.AUTHENTICATION_SERVICES=(none)的情况下,修改remote_login_passwordfile的值从exclusive改为none,重启数据库使其生效,会报错提示权限不足：

22:58:28 SYS@ boston> alter system set remote_login_passwordfile='NONE' scope=spfile;
System altered.
22:58:47 SYS@ boston> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
22:59:15 SYS@ boston> startup 
ORA-01031: insufficient privileges
22:59:34 SYS@ boston> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
尝试重新登录启动数据库
[oracle@dax-mysql-slave admin]$ sqlplus sys/oracle  as sysdba
Connected to an idle instance.
22:59:46 SYS@ boston> startup
ORA-01031: insufficient privileges
22:59:48 SYS@ boston> exit
Disconnected

只能禁用sqlnet.ora文件中的SQLNET.AUTHENTICATION_SERVICES=(none)参数，否则无法启动数据库。

2.4、启动数据库之后，再次启用sqlnet.ora文件中的SQLNET.AUTHENTICATION_SERVICES=(none)参数，如果在数据库开启的状态下，as sysdba将无法登录，只能使用网络服务名和service_name登录

oracle@dax-mysql-slave admin]$ sqlplus / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@dax-mysql-slave admin]$ sqlplus sys/oracle as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle@boston as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle@boston
10:13:30 TEST@ boston> exit

[oracle@dax-mysql-slave admin]$ sqlplus test/oracle@192.168.168.177:1521/boston.us.oracle.com
10:16:49 TEST@ 192.168.168.177:1521/boston.us.oracle.com> select * from user_role_privs;

USERNAME		       GRANTED_ROLE		      ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEST			       CONNECT			      NO  YES NO
TEST			       DBA			          NO  YES NO
TEST			       RESOURCE 		      NO  YES NO
Elapsed: 00:00:00.02