Operating system (OS) authentication and Password

Operating system (OS) authentication and Password files authentication

To connect to Oracle as a privileged user over a local connection or a secure remote connection, you have the following options:

l         You can connect and be authenticated by a password file; provided the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege.

l         If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use OS authentication. On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group, generically referred to as OSDBA.

(你只能够在两种情况下以 privileged user  connect to database.

l         一个database user, have been granted the SYSDBA or SYSOPER system privilege. 通过Password file authenticated method.

Conn username/passwd as sysdba

 

l         在OS xxx 用户环境下，如果这个用户 xxx 是 OSDBA (dba) 组中的一员.

conn / as sysdba

 

Example: gdut is a normal user

Password file authentication method.

[gdut@redhat gdut]$ id

uid=500(gdut) gid=500(gdut) groups=500(gdut)

[gdut@redhat gdut]$ sqlplus /nolog

idle> conn / as sysdba

ERROR:

ORA-01031: insufficient privileges

 

idle> conn mouse/mouse as sysdba “mouse is a normal database user”

ERROR:

ORA-01031: insufficient privileges

 

After

idle> grant sysdba to mouse;

 

Grant succeeded.

 

Again:

idle> conn mouse/mouse as sysdba

Connected.

 

Of course, the remote_login_passwordfile must be exclusive to use password file authentication.

 

idle> show parameter remote_login_passwordfile

 

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

remote_login_passwordfile            string                 NONE

idle> alter system set remote_login_passwordfile=none scope=spfile;

 

System altered.

 

[gdut@redhat gdut]$ sqlplus /nolog

 

SQL*Plus: Release 9.2.0.4.0 - Production on Sun May 2 01:09:12 2004

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

idle> conn mouse/mouse as sysdba

ERROR:

ORA-01031: insufficient privileges

 

 

OS authentication

 

[oracle@redhat dbs]$ id oracle is a member of dba group

uid=501(oracle) gid=501(dba) groups=501(dba),502(oinstall)

 

[oracle@redhat dbs]$ sqlplus /nolog

 

SQL*Plus: Release 9.2.0.4.0 - Production on Sun May 2 01:01:49 2004

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

idle> conn / as sysdba

Connected.

)

 

Your choice will be influenced by whether you intend to administer your database locally on the same machine where the database resides, or whether you intend to administer many different databases from a single remote client.

 

Preparing to Use OS Authentication

To enable authentication of an administrative user using the operating system you must do the following:

1. Create an operating system account for the user.

2. Add the user to the OSDBA or OSOPER operating system defined groups.

3. Ensure that the initialization parameter, REMOTE_LOGIN_PASSWORDFILE, is set to NONE. This is the default value for this parameter.

 

show parameter remote_login_passwordfile

 

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ------------------------------

remote_login_passwordfile            string                 EXCLUSIVE

这是怎么回事，remote_login_passwordfile 并不是NONE，但仍可用OS authenticated method login ?

两种方式可以并存

Operating system authentication takes precedence over password file authentication. (因为OS 认证方式优于Password File 认证方式)

 

Connecting Using OS Authentication

A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:

CONNECT / AS SYSDBA

CONNECT / AS SYSOPER

For a remote database connection over a secure connection, the user must also specify the net service name of the remote database:

CONNECT /@net_service_name AS SYSDBA

CONNECT /@net_service_name AS SYSOPER

OS authentication has nothing to do with whether you connect database locally or remotely.

 

OSDBA and OSOPER

Two special operating system groups control database administrator connections when using OS authentication. These groups are generically referred to as OSDBA and OSOPER.

The following describes how membership in the OSDBA or OSOPER group affects your connection to Oracle:

 If you are a member of the OSDBA group, and specify AS SYSDBA when you connect to the database, you are granted the SYSDBA system privilege.

If you are a member of the OSOPER group, and specify AS SYSOPER when you connect to the database, you are granted the SYSOPER system privilege.

If you are not a member of the associated operating system group for SYSDBA or SYSOPER system privileges, the CONNECT command fails.

如果用户所在的组是OSDBA group (dba), 那么就可以在这个用户下 conn / as sysdba ,以操作系统认证方式登录，否则报insufficient privileges.

 

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

1. Create an operating system account for the user.

2. If not already created, create the password file using the ORAPWD utility:

ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users

3. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

4. Connect to the database as user SYS (or as another user with the administrative privilege).

5. If the user does not already exist in the database, create the user. Grant the SYSDBA or SYSOPER system privilege to the user:

GRANT SYSDBA to scott;

This statement adds the user to the password file, thereby enabling connection AS SYSDBA.