用户验证-OS认证和口令认证测试

验证用户 

验证指的是对要使用数据、资源或应用程序的用户、设备或其它实体的身份进行验证。通过对该身份进行验证可建立一种信任关系，从而可进一步执行交互。通过验证可将访问和操作与特定的身份联系起来，从而实现可靠性。完成验证后，验证流程可允许或限制该实体许可的访问和操作的级别。 

Oracle数据库究竟使用OS认证还是口令文件认证来进行管理取决于下面三个因素 
SQLNET.ORA参数文件中的参数SQLNET.AUTHENTICATION_SERVICES设置
PFILE(SPFILE)参数文件中的参数REMOTE_LOGIN_PASSWORDFILE设置拥有数据字典 
口令文件orapw$SID(Linux) | PWD$SID.ora(Windows) 是否存SYSTEM 帐户授予了 DBA 角色。 
Oracle权限认证的基本顺序
先由SQLNET.AUTHENTICATION_SERVICES的设置值来决定是使用OS认证还是口令文件认证
如果使用口令文件认证的话就要同时满足下面两个条件才能认证成功：
         1、REMOTE_LOGIN_PASSWORDFILE参数设置为非NONE
         2、口令文件存在就能正常使用口令文件认证，否则将会认证失败

创建用户时，必须确定要使用的验证方法，以后可修改此方法。 
口令：又称为 Oracle 数据库验证。创建的每一个用户都有一个关联口令，用户尝试建立连接时，必须提供这个口令。设置口令时，可以使该口令立即失效，这会强制用户在首次登录后更改口令。如果决定要使用户口令失效，请确保用户能够更改口令。有些应用程序不具备此功能。 
在（客户机/服务器之间和服务器/服务器之间）建立网络连接期间，系统总是先通过使用修改过的数据加密标准 (DES) 算法，以自动透明方式对口令加密，然后通过网络发送这些口令。

操作系统验证：

conn / as sysdba既是操作系统认证，即使用操作系统用户登录数据库（root用户不可以)，该用户必须同时添加到dba组和oinstall中，如果只添加dba组也不可以

只能用在本地 因为数据库软件安装在操作系统之上

密码文件验证：

口令认证：必须启动监听，用于网络连接，否则认证失败。

密码文件存储的位置  $ORACLE_HOME/dbs/orapw$ORACLE_SID

实验环境：

[oracle@tyger ~]$ id

uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)
[oracle@tyger ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 09:15:35 2014


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




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


SYS@ORCL>select * from v$version;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQLNET.AUTHENTICATION_SERVICES参数在SQLNET.ORA（位于$ORACLE_HOME/network/admin目录中） 文件中对于不同的操作系统SQLNET.AUTHENTICATION_SERVICES的取值会有些不一样，通常我们会用到下面的一些设置值：
        linux下： none  all nts 不设置或BEQ 四种情况

第一种情况：

参数SQLNET.AUTHENTICATION_SERVICES 不设置或设置为BEQ---------口令认证和操作系统认证都启动

[oracle@tygeradmin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@tyger admin]$ ls
listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora

[oracle@tyger admin]$ vim sqlnet.ora 

NAME.DIRECTORY_PATH=(tnsnames,ezconnect)


SQLNET.AUTHENTICATION_SERVICES=(BEQ)

[oracle@tyger admin]$ lsnrctl status


LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 28-FEB-2014 09:52:19


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


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= tyger )(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                28-FEB-2014 09:48:44
Uptime                    0 days 0 hr. 3 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= tyger )(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle@whgg admin]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 09:52:50 2014


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




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


SYS@ORCL>exit    
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@tyger admin]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@tyger dbs]$ ls
hc_ORCL.dat  initdw.ora  init.ora  lkORCL  orapwORCL  spfileORCL.ora
[oracle@tyger dbs]$ sqlplus sys/oracle@ORCL as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 09:53:51 2014


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




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


SYS@ORCL>

第二种情况：

参数SQLNET.AUTHENTICATION_SERVICES 设置为 all  -----------------------屏蔽口令认证都启用操作系统认证

[oracle@tyger admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@tyger admin]$ vim sqlnet.ora 
NAME.DIRECTORY_PATH=(tnsnames,ezconnect)

SQLNET.AUTHENTICATION_SERVICES=(ALL)

[oracle@tyger admin]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 09:59:20 2014


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




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


SYS@ORCL>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@tyger admin]$ sqlplus sys/oracle@ORCL as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 09:59:39 2014


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


ERROR:
ORA-12641: Authentication service failed to initialize




Enter user-name: 

第三种情况：

参数SQLNET.AUTHENTICATION_SERVICES 设置为  NONE 或NTS ----------------屏蔽操作系统认证都启用口令认证

[oracle@tyger admin]$ vim sqlnet.ora 

NAME.DIRECTORY_PATH=(tnsnames,ezconnect)


SQLNET.AUTHENTICATION_SERVICES=(NTS)

[oracle@tyger admin]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 10:36:35 2014


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


ERROR:
ORA-01031: insufficient privileges

[oracle@tyger admin]$ sqlplus sys/oracle@ORCL as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 10:36:52 2014


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




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


SYS@ORCL>

第四种情况：

当数据库屏蔽了操作系统认证，启用口令认证方式的话，需要检查remote_login_passwordfile参数，如果该参数设置为NONE,该值表示不使用口令文件，因此，此时数据库将不能登录

解决办法：启动操作系统认证，登录数据库后将remote_login_passwordfile参数修改EXCLUSIVE，然后再屏蔽操作系统认证，启用口令认证。

> remote_login_passwordfile参数可以设置为3个值 none、exclusive、shared 

[oracle@tyger admin]$ cat sqlnet.ora 
NAME.DIRECTORY_PATH=(tnsnames,ezconnect)


SQLNET.AUTHENTICATION_SERVICES=(NTS)
[oracle@tyger admin]$ sqlplus sys/oracle@ORCL as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 10:48:15 2014


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




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


SYS@ORCL>show parameter remote


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable                string      true
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
SYS@ORCL>alter system set remote_login_passwordfile=none scope=spfile;


System altered.

---静态参数，重启数据库生效
SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>startup
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

[oracle@tyger admin]$ vim sqlnet.ora 


NAME.DIRECTORY_PATH=(tnsnames,ezconnect)


#SQLNET.AUTHENTICATION_SERVICES=(NTS)

[oracle@tyger admin]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 10:57:25 2014


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


Connected to an idle instance.


SYS@ORCL>startup
ORACLE instance started.


Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SYS@ORCL>show parameter remote


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable                string      true
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      NONE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
SYS@ORCL>alter system set remote_login_passwordfile=exclusive scope=spfile;


System altered.

SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>startup
ORACLE instance started.


Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              96470608 bytes
Database Buffers          184549376 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SYS@ORCL>show parameter remote


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable                string      true
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
SYS@ORCL>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@tyger admin]$ sqlplus sys/oracle@ORCL as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 28 11:04:16 2014


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




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


SYS@ORCL>