ORACLE 12C DATABASE AUTHENTICATION METHODS FOR THE ADMINISTRATORS

The purpose of differentiating these authentication methods was to differentiate the roles and also secure the database and the docs read

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

Database Administrator Authentication

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 the following special system privileges:

• SYSDBA
• SYSOPER
• SYSBACKUP
• SYSDG
• SYSKM

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

Operations Authorized by Administrative Privileges

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

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. ForSYSOPER, the session user is PUBLIC. For SYSBACKUP, SYSDG, and SYSKM, the session user is SYSBACKUP, SYSDG, and SYSKM, respectively.

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

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