Oracle OS认证与口令文件认证详解

实验环境：Oracle 10.2 + Windows 2003
sqlnet.ora中
把SQLNET.AUTHENTICATION_SERVICES = (none) 禁用操作系统认证
C:\>sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 10月 29 16:26:10 2015


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


ERROR:
ORA-01031: insufficient privileges




请输入用户名:


操作系统认证不能登录
使用密码文件认证，创建一个密码文件


C:\>orapwd file=E:\oracle\product\10.2.0\db_1\database\PWDorcl.ora password=oracle entries=5


C:\>sqlplus sys/oracle as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 10月 29 16:29:55 2015


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




连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


远程使用口令文件方式登陆,
C:\>sqlplus sys/oracle@aux as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 31 19:10:40 2015


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




连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>


远程也能登录,因为remote_login_passwordfile值为exclusive
SQL> show parameter remote_login_passwordfile


NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
remote_login_passwordfile            string      EXCLUSIVE


我们把remote_login_passwordfile值修改为NONE,看是否能登录


2、两种认证都失效


设置SQLNET.ORA中参数SQLNET.AUTHENTICATION_SERVICES = (NONE)，REMOTE_LOGIN_PASSWORDFILE = NONE，
1)本地使用下面两种方式登陆,都失败
C:\>sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 31 19:38:29 2015


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


ERROR:
ORA-01031: insufficient privileges




请输入用户名:


C:\>sqlplus sys/oracle as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 31 19:44:06 2015


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


ERROR:
ORA-01017: invalid username/password; logon denied




请输入用户名:


2)远程使用口令文件方式登陆，失败
C:\>sqlplus sys/oracle@aux as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 31 19:45:04 2015


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


ERROR:
ORA-01017: invalid username/password; logon denied




请输入用户名:


3.验证口令认证
设置SQLNET.ORA中参数SQLNET.AUTHENTICATION_SERVICES = (NTS),REMOTE_LOGIN_PASSWORDFILE = NONE
本地使用下面两种方式登录，都能成功
C:\>sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 31 19:48:27 2015


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




连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


C:\>sqlplus sys/oracle as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 31 19:48:54 2015


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




连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL>


远程使用口令文件方式登录，成功
C:\>sqlplus sys/oracle@aux as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 10月 31 19:52:46 2015


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




连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL>


SQL> select *from v$pwfile_users;


USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE


4、忘记了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的密码