Database Administrator Authentication - Oracle 10g

As a DBA, you often perform special operations such as shutting down or starting up a database. Because only a DBA should perform these operations, the database administrator usernames require a secure authentication scheme.

This section contains the following topics:

  • Administrative Privileges
  • Selecting an Authentication Method
  • Using Operating System Authentication
  • Using Password File Authentication
  • Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.

SYSDBA and SYSOPER
The following operations are authorized by the SYSDBA and SYSOPER system privileges:

SYSDBA and SYSOPER system privilegesSYSDBA and SYSOPER system privileges

System Privilege Operations Authorized

The manner in which you are authorized to use these privileges depends upon the method of authentication that you use. When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

Connecting with Administrative Privileges: Example
This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA system privilege. Assume that the sample user oe has been granted the SYSDBA system privilege and has issued the following statements:

CONNECT oe/oe CREATE TABLE admin_test(name VARCHAR2(20));

Later, user oe issues these statements:

CONNECT oe/oe AS SYSDBA SELECT * FROM admin_test;

User oe now receives the following error:

ORA-00942: table or view does not exist

Having connected as SYSDBA, user oe now references the SYS schema, but the table was created in the oe schema.

Selecting an Authentication Method
The following methods are available for authenticating database administrators:

  • Operating system (OS) authentication
  • A password file

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. illustrates the choices you have for database administrator authentication schemes.

Database Administrator Authentication Methods

Database Administrator Authentication Methods

If you are performing remote database administration, consult your Oracle Net documentation to determine whether you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure.
Nonsecure Remote Connections

To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database
usernames that have been granted the SYSDBA or SYSOPER system privilege.

Local Connections and Secure Remote Connections

You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:

  • If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.
  • 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 operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.

Using Operating System Authentication
This section describes how to authenticate an administrator using the operating system.

Preparing to Use Operating System Authentication
To enable operating system authentication of an administrative user:

  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, the default.

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

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 specific names vary depending upon your operating system and are listed in the following table:

OSDBA and OSOPER

Operating System Group UNIX Windows

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 onnection 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, the CONNECT command fails.

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:

  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 privileges).
  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 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. For example, user oe has been granted the SYSDBA privilege, so oe can connect as follows:

CONNECT oe/oe AS SYSDBA

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

CONNECT oe/oe AS SYSOPER

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics