Database Administrator Authentication

SYSDBA and SYSOPER

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

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
CREATE TABLE admin_test(name VARCHAR2(20));

Later, user oe issues these statements:

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

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 user names that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in "Using Password File Authentication".

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.

OSDBA and OSOPER

Membership in one of two special operating system groups enables a DBA to authenticate to the database through the operating system rather than with a database user name and password. This is known as operating system authentication. These operating system 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 vary depending upon your operating system, and are listed in the following table:

Oracle Universal Installer uses these default names, but you can override them. One reason to override them is if you have multiple instances running on the same host computer. If each instance is to have a different person as the principal DBA, you can improve the security of each instance by creating a different OSDBA group for each instance. For example, for two instances on the same host, the OSDBA group for the first instance could be named dba1, and OSDBA for the second instance could be named dba2. The first DBA would be a member of dba1 only, and the second DBA would be a member of dba2 only. Thus, when using operating system authentication, each DBA would be able to connect only to his assigned instance.

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.

  See Also:

  Your operating system specific Oracle documentation for information about creating the OSDBA and OSOPER groups

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

Preparing to Use Password File Authentication

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

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

   ORAPWD FILE=filename ENTRIES=max_users
   

   See "Creating and Maintaining a Password File" for details.

   Notes:

   • When you invoke Database Configuration Assistant (DBCA) as part of the Oracle Database installation process, DBCA creates a password file.

   • Beginning with Oracle Database 11g Release 1, passwords in the password file are case-sensitive unless you include the IGNORECASE = Y command-line argument.

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).

   Note:

   REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

3. Connect to the database as user SYS (or as another user with the administrative privileges).

4. 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.)

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

   See Also:

   "Creating and Maintaining a Password File" for instructions for creating and maintaining a password file.

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