Oracle管理与维护.数据库登录的身证验证

Oracle管理与维护.数据库登录的身证验证

草木瓜

20080505

一、写在前面

 曾经并不知道conn / as sysdba为何物，在unix下搞了半天方知，还需要加上"号，Oracle 10g里
终于也考虑到了这个问题，不再受到" as sysdba "的限制。
 如下所示：
 
 [oracle@liwei admin]$ sqlplus / as sysdba
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 15:55:18 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 Connected to an idle instance.
 
 SQL> quit
 Disconnected
 [oracle@liwei admin]$ sqlplus "/ as sysdba"
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 15:55:24 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 Connected to an idle instance.
 
 SQL> quit
 Disconnected
 
二、Oracle登录认证方式

 《Oracle管理与维护.手工创建数据库以及脚本》一文中提到如下一个错误：
 
 [oracle@liwei oracle]$ sqlplus "/ as sysdba"
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 14:51:59 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 ERROR:
 ORA-01031: insufficient privileges
 
 这个便是登录认证方式的问题了。
 
 Oracle登录认证有两种方式：基于操作系统的登录认证和基于Oracle密码文件的登录认证。
具体方式记录在服务端$ORACLE_HOME/network/admin/sqlnet.ora文件中，不过Windows与
Unix/Linux具体设置是不同的。

 <Windows>
 
 sqlnet.ora文件为空时采用Oracle密码文件验证
 
 SQLNET.AUTHENTICATION_SERVICES= (NTS)     基于操作系统验证；
 SQLNET.AUTHENTICATION_SERVICES= (NONE)  基于Oracle密码文件验证
 SQLNET.AUTHENTICATION_SERVICES= (NONE,NTS)  二者并存，注意是半角，否则不识别

    sqlnet.ora示例一：
   
  # This file is actually generated by netca. But if customers choose to
  # install "Software Only", this file wont exist and without the native
  # authentication, they will not be able to connect to the database on NT.
 
  #SQLNET.AUTHENTICATION_SERVICES = (NTS)
 
  C:/Documents and Settings/Administrator>sqlplus " / as sysdba"
 
  SQL*Plus: Release 9.2.0.1.0 - Production on Mon May 5 17:54:32 2008
 
  Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
  ERROR:
  ORA-01031: insufficient privileges

    sqlnet.ora示例二：
   
  # This file is actually generated by netca. But if customers choose to
  # install "Software Only", this file wont exist and without the native
  # authentication, they will not be able to connect to the database on NT.
 
  SQLNET.AUTHENTICATION_SERVICES = (NONE,NTS)
 
  C:/Documents and Settings/Administrator>sqlplus " / as sysdba"
 
  SQL*Plus: Release 9.2.0.1.0 - Production on Mon May 5 18:01:14 2008
 
  Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
  Connected to:
  Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
  With the Partitioning, OLAP and Oracle Data Mining options
  JServer Release 9.2.0.1.0 - Production
 
  SQL>

 <Unix/Linux>
 
 默认情况下Unix/Linux下的sqlnet.ora文件是没有SQLNET.AUTHENTICATION_SERVICES参数的，
此时是操作系统验证和Oracle密码验证并存，加上SQLNET.AUTHENTICATION_SERVICES这个参
数后，不管SQLNET.AUTHENTICATION_SERVICES设置为NONE还是NTS还是(NONE,NTS)，都是
基于Oracle密码验证。

    sqlnet.ora示例一：
 [oracle@liwei admin]$ cat sqlnet.ora
 # This file is actually generated by netca. But if customers choose to
 # install "Software Only", this file wont exist and without the native
 # authentication, they will not be able to connect to the database on NT.
 
 #SQLNET.AUTHENTICATION_SERVICES = (NONE)
 #SQLNET.AUTHENTICATION_SERVICES=(NTS)
 SQLNET.AUTHENTICATION_SERVICES = (NONE,NTS)
 
 [oracle@liwei oracle]$ sqlplus "/ as sysdba"
 
 SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 1 18:57:31 2008
 
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 ERROR:
 ORA-01031: insufficient privileges

三、解释REMOTE_LOGIN_PASSWORDFILE参数

Parameter type
 String
Syntax
 REMOTE_LOGIN_PASSWORDFILE= {NONE | SHARED | EXCLUSIVE}
Default value
 NONE
Parameter class
 Static
Real Application Clusters
 Multiple instances must have the same value.
 
REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file and
how many databases can use the password file.

Values:

NONE
Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

SHARED
More than one database can use a password file. However, the only user recognized by the password file is SYS.

EXCLUSIVE
The password file can be used by only one database and the password file can contain names other than SYS.

 顾名思义，这个参数是设置远程登录的。
 
 alter system set remote_login_passwordfile  = none scope=spfile;
 NONE 很好理解，Oracle忽略所有的密码文件，只能使用操作系统身份验证。此时不能远程登录
为sysdba和sysoper角色。也不能将sysdba,sysoper授权给其他用户。
 SQL> grant sysdba to system;
 grant sysdba to system
 *
 ERROR at line 1:
 ORA-01994: GRANT failed: password file missing or disabled

 alter system set remote_login_passwordfile  = exclusive scope=spfile;
 EXCLUSIVE 最为常用，远程认证的用户可以是具有sysdba和sysoper角色的所有用户。可以通
过sysdba角色修改sys密码。一个password file只能被数据库一个instance使用。可以对其他用户
进行sysdba,sysoper授权。

 SQL> grant sysoper to system;
 
 Grant succeeded.
 
 如果EXCLUSIVE下，密码文件丢失则数据库模式等同于NONE。

 alter system set remote_login_passwordfile  = shared scope=spfile;
 SHARED，远程认证的用户可以是具有sysdba和sysoper角色的所有用户。不可以通过sysdba角
色修改sys密码。一个password file可以被数据库的多个instance使用，如果这个数据库有多个instance
的话，但远程认证的用户只能是具有sysdba和sysoper角色的sys用户

 SQL> alter user sys identified by sys;
 alter user sys identified by sys
 *
 ERROR at line 1:
 ORA-28046: Password change for SYS disallowed
 
 SQL> grant sysoper to system;
 grant sysoper to system
 *
 ERROR at line 1:
 ORA-01999: password file cannot be updated in SHARED mode

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/liwei_cmg/archive/2008/05/06/2403337.aspx