1.6 Database Administrator Authentication

«« Previous
Next »»

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 user names require a secure authentication scheme.

Administrative Privileges


Administrative privileges that are required for an administrator to perform basic database operations are granted through special system privileges.

These privileges are:
  • SYSDBA
  • SYSOPER
  • SYSDG
  • SYSKM
  • SYSBACKUP
You must have one of these privileges granted to you, depending upon the level of authorization you require.

Starting with Oracle Database 12c, the SYSBACKUP, SYSDG, and SYSKM administrative privileges are available. Each new administrative privilege grants the minimum required privileges to complete tasks in each area of administration. The new administrative privileges enable you to avoid granting SYSDBA administrative privilege for many common tasks.

Note: These administrative privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself. Methods for authenticating database administrators with these privileges include operating system (OS) authentication, password files, and strong authentication with a directory-based authentication service.

These privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, if you have the SYSDBA privilege, then you can connect to the database by specifying CONNECT AS SYSDBA and perform STARTUP and SHUTDOWN operations. See "Authentication Methods for Database Administrators".

Operations Authorized by Administrative Privileges


Each administrative privilege authorizes a specific set of operations.

The following table lists the operations that are authorized by each administrative privilege:

Administrative Privilege  Operations Authorized
SYSDBA
  • Perform STARTUP and SHUTDOWN operations
  • ALTER DATABASE: open, mount, back up, or change character set
  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SPFILE
  • ALTER DATABASE ARCHIVELOG
  • ALTER DATABASE RECOVER
  • Includes the RESTRICTED SESSION privilege

This administrative privilege allows most operations, including the ability to view user data. It is the most powerful administrative privilege.
SYSOPER
  • Perform STARTUP and SHUTDOWN operations
  • CREATE SPFILE
  • ALTER DATABASE: open, mount, or back up
  • ALTER DATABASE ARCHIVELOG
  • ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)
  • Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to view user data.
SYSBACKUP This privilege allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.
SYSDG This privilege allows a user to perform Data Guard operations. You can use this privilege with either Data Guard Broker or the DGMGRL command-line interface.
SYSKM This privilege allows a user to perform Transparent Data Encryption keystore operations.

The manner in which you are authorized to use these privileges depends upon the method of authentication that you use.

When you connect with an administrative privilege, you connect with a current schema that is not generally associated with your username. For SYSDBA, the current schema is SYS. For SYSOPER, the current schema is PUBLIC. For SYSBACKUP, SYSDG, and SYSKM, the current schema is SYS for name resolution purposes.

Also, when you connect with an administrative privilege, you connect with a specific session user. When you connect as SYSDBA, the session user is SYS. For SYSOPER, the session user is PUBLIC. For SYSBACKUP, SYSDG, and SYSKM, the session user is SYSBACKUP, SYSDG, and SYSKM, respectively.

Example 1-17 Current Schema When Connecting AS SYSDBA

This example illustrates that a user is assigned another schema (SYS) when connecting with the SYSDBA administrative privilege. Assume that the sample user mydba has been granted the SYSDBA administrative privilege and has issued the following command and statement:

CONNECT mydba
CREATE TABLE admin_test(name VARCHAR2(20));

Later, user mydba issues this command and statement:

CONNECT mydba AS SYSDBA
SELECT * FROM admin_test;

User mydba now receives the following error:

ORA-00942: table or view does not exist

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

Example 1-18 Current Schema and Session User When Connecting AS SYSBACKUP

This example illustrates that a user is assigned another schema (SYS) and another session user (SYSBACKUP) when connecting with the SYSBACKUP administrative privilege. Assume that the sample user mydba has been granted the SYSBACKUP administrative privilege and has issued the following command and statements:

CONNECT mydba AS SYSBACKUP

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SYS

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SYSBACKUP


Authentication Methods for Database Administrators


Database administrators can be authenticated with account passwords, operating system (OS) authentication, password files, or strong authentication with a directory-based authentication service, such as Oracle Internet Directory.


About Authentication Methods for Database Administrators


There are several ways to authenticate database administrators.

Database Administrators can authenticate database administrators through the data dictionary, (using an account password) like other users. Keep in mind that database passwords are case-sensitive. See Oracle Database Security Guide for more information about case-sensitive database passwords.

In addition to normal data dictionary authentication, the following methods are available for authenticating database administrators with the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM privilege:
  • Operating system (OS) authentication
  • Password files
  • Strong authentication with a directory-based authentication service, such as Oracle Internet Directory
These methods are required to authenticate a database administrator when the database is not started or otherwise unavailable. (They can also be used when the database is available.)

The remainder of this section focuses on operating system authentication and password file authentication. 

Note:

Operating system authentication takes precedence over password file authentication. If you meet the requirements for operating system authentication, then even if you use a password file, you will be authenticated by operating system authentication.

Your choice is influenced by whether you intend to administer your database locally on the same system where the database resides, or whether you intend to administer many different databases from a single remote client. Figure 1-2 illustrates the choices you have for database administrator authentication schemes.

Figure 1-2 Database Administrator Authentication Methods

Database Administrator Authentication

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 user names that have been granted the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative 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.

You can connect in two ways:
  • If the database has a password file and you have been granted a 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 a system privilege and are therefore not in the password file, then 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.
For example, users in the OSDBA group are granted the SYSDBA administrative privilege. Similarly, the OSOPER group is used to grant SYSOPER administrative privilege to users, the OSBACKUPDBA group is used to grant SYSBACKUP administrative privilege to users, the OSDGDBA group is used to grant SYSDG administrative privilege to users, and the OSKMDBA group is used to grant SYSKM administrative privilege to users.

Using Operating System Authentication


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

Operating System Groups


Operating system groups are created and assigned specific names as part of the database installation process.

The default names of the operating system groups vary depending upon your operating system, and are listed in the following table:

Operating System Group UNIX or Linux User Group Windows User Group
OSDBA dba ORA_DBA (for all Oracle homes)
ORA_HOMENAME_DBA (for each specific Oracle home)
OSOPER backupdba ORA_OPER (for all Oracle homes)
ORA_HOMENAME_OPER (for each specific Oracle home)
OSBACKUPDBA backupdba ORA_HOMENAME_SYSBACKUP
OSDGDBA dgdba ORA_HOMENAME_SYSDG
OSKMDBA kmdba ORA_HOMENAME_SYSKM

For the Windows user group names, replace HOMENAME with the Oracle home name.

Oracle Universal Installer uses these default names, but, on UNIX or Linux, you can override them. On UNIX or Linux, one reason to override them is if you have multiple instances running on the same host computer in different Oracle homes. If each instance has a different person as the principal DBA, then you can improve the security of each instance by creating different groups for each instance.

For example, for two instances on the same UNIX or Linux host in different Oracle homes, the OSDBA group for the first instance might be named dba1, and OSDBA for the second instance might 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.

On Windows, default user group names cannot be changed. The HOMENAME placeholder enables you to have different user group names when you have multiple instances running on the same host Windows computer.

Membership in a 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 administrative 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 administrative privilege.
  • If you are a member of the OSBACKUPDBA group, and you specify AS SYSBACKUP when you connect to the database, then you connect to the database with the SYSBACKUP administrative privilege.
  • If you are a member of the OSDGDBA group, and you specify AS SYSDG when you connect to the database, then you connect to the database with the SYSDG administrative privilege.
  • If you are a member of the OSKMDBA group, and you specify AS SYSKM when you connect to the database, then you connect to the database with the SYSKM administrative privilege.
  • If you are not a member of one of these operating system groups, and you attempt to connect as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM, then the CONNECT command fails.

Preparing to Use Operating System Authentication


DBAs can authenticate to the database through the operating system rather than with a database user name and password.

To enable operating system authentication of an administrative user:

1. Create an operating system account for the user.
2. Add the account to the appropriate operating-system defined groups.

Connecting Using Operating System Authentication


A user can connect to the database using operating system authentication.

You can use operating system authentication by performing one of the following actions.
  • 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
CONNECT / AS SYSBACKUP
CONNECT / AS SYSDG
CONNECT / AS SYSKM
  • 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
CONNECT /@net_service_name AS SYSBACKUP
CONNECT /@net_service_name AS SYSDG
CONNECT /@net_service_name AS SYSKM

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

Using Password File Authentication


You can use password file authentication for an Oracle database instance and for an Oracle Automatic Storage Management (Oracle ASM) instance. The password file for an Oracle database is called a database password file, and the password file for Oracle ASM is called an Oracle ASM password file.


Preparing to Use Password File Authentication


To prepare for password file authentication, you must create the password file, set the REMOTE_LOGIN_PASSWORDFILE initialization parameter, and grant privileges.

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

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

orapwd FILE=filename ENTRIES=max_users FORMAT=12

Note:

When you invoke Database Configuration Assistant (DBCA) as part of the Oracle Database installation process, DBCA creates a password file.
  • The administrative privileges SYSBACKUP, SYSDG, and SYSKM are supported in the password file only when the file is created created with the FORMAT=12 argument. 12 is the default for the FORMAT command-line argument.
  • By default, passwords in the password file are case-sensitive.
  • When you create a database password file that is stored in an Oracle ASM disk group, it can be shared among the multiple Oracle RAC database instances. The password file is not duplicated on each Oracle RAC database instance.
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, then create the user and assign a password.
Keep in mind that database passwords are case-sensitive. See Oracle Database Security Guide for more information about case-sensitive database passwords.

5. Grant the SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privilege to the user. For example:

GRANT SYSDBA to mydba;

This statement adds the user to the password file, thereby enabling connection AS SYSDBA, AS SYSOPER, AS SYSBACKUP, AS SYSDG, or AS SYSKM.


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 By default, passwords are case-sensitive.

To connect using password file authentication:
  • In SQL*Plus, execute the CONNECT command with a valid username and password and the AS SYSDBA, AS SYSOPER, AS SYSBACKUP, AS SYSDG, or AS SYSKM clause.
For example, if user mydba has been granted the SYSDBA privilege, then mydba can connect as follows:

CONNECT mydba AS SYSDBA

However, if user mydba has not been granted the SYSOPER privilege, then the following command fails:

CONNECT mydba AS SYSOPER

Note:

Operating system authentication takes precedence over password file authentication. Specifically, if you are a member of the appropriate operating system group, such as OSDBA or OSOPER, and you connect with the appropriate clause (for example, AS SYSDBA), then you will be connected with associated administrative privileges regardless of the username/password that you specify.

If you are not in the one of the operating system groups, and you are not in the password file, then attempting to connect with the clause fails.

«« Previous
Next »»