Authentication Method for Database Administrators

Database Administrators can authenticate through the database data dictionary, (using an account password ) like other users. In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA or SYSOPER privilege:

Operating system (OS) authentication
A password file
Strong authentication with a network-based authentication service

These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. The remainder of this section focuses on operating system authentication and password file authentication.

Using Operating System Authentication

This section describes how to authenticate an administrator using the operating system.

OSDBA and OSOPER

Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process.

The default names assumed by the Oracle Universal Installer can be overridden. How you create the OSDBA and OSOPER groups is operating system specific.

Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:

If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.

Preparing to Use Operating System Authentication

To enable operating system authentication of an administrative user:

Create an operating system account for the user
Add the account to the OSDBA or OSOPER operating system defined groups.

Connecting Using Operating System 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 the Windows platform only, remote operating system authentication over a secure connection is supported. You must specify the net service name for the remote database:

CONNECT /@net_service_name AS SYSDBA

CONNECT /@net_service_name AS SYSOPER

Both the client computer and database host computer must be on a Windows domain.

Using Password File Authentication

This section describes how to authenticate an administrative user using password file authentication.

Preparing to Use Password File Authentication

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

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

ORAPWD FILE=filename ENTRIES=max_users

Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE
Connect to the database as user SYS (or as another user with the administrative privileges).
If the user does not already exist in the database, create the user and assign a password. Keep in mind that beginning with Oracle Database 11g Release 1, database passwords are case sensitive. (You can disable case sensitivity and return to pre–Release 11g behavior by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.)
Grant the SYSDBA or SYSOPER system privilege to the user:

GRANT SYSDBA to oe;

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

Connecting Using Password File Authentication

Administrative users can be connected and authenticated to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and the AS SYSDBA or AS SYSOPER clause. Note that beginning with Oracle Database 11g Release 1, passwords are case-sensitive unless the password file was created with the IGNORECASE = Y option.

For example, user oe has been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe AS SYSDBA

However, user oe has not been granted the SYSOPER privilege, so the following command will fail:

CONNECT oe AS SYSOPER

Posted in: DBA Responsibility