oracle的mysql管理证书_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具体设置是不同的。

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下的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