As a database administrator, you usually have special tasks to perform, such as closing or starting the database, and because these tasks are performed only by the database administrator, the database administrator needs a secure authentication system.

Administrative Privileges:

Administrative Privileges required for the administrator to perform tasks on the database are granted through these special privileges, and these privileges are:

SYSDBA

SYSOPER

SYSBACKUP

SYSDG

SYSKM

SYSRAC

With the exception of the privilege (SYSRAC), these privileges are granted to users depending on the level of authorization they need, as for the privilege SYSRAC, it cannot be granted to users because it is only used by (Oracle agent of Oracle Clusterware) to connect to the database using operating system authentication.

Starting from Oracle Database 12c Release 1 (12.1), the privileges (SYSBACKUP & SYSDG & SYSKM) became available, and starting from Oracle Database 12c Release 2 (12.2), the privilege SYSRAC was made available. Each new administrative privilege grants the minimum privileges required to complete tasks in each area of ​​​​administration, as the new privileges enable you to avoid granting the administrative privilege SYSDBA to perform many tasks.

These administrative privileges enable you to connect to the Database Instance even if the database is closed, because control of these privileges is outside the database itself, as the methods of authentication of database administrators with these privileges include authentication via the operating system or the Password File.

Operations Authorized by Administrative Privileges:

Each administrative privilege allows a specific set of tasks as shown in the table below:

When a user is granted one of these administrative privileges and connects to the database through the privilege, he connects to the Shema associated with the privilege and not through his own Schema. For example, when connecting to the SYSDBA privilege, the current Shema is SYS, and when connecting to the SYSOPER privilege, the current Schema is PUBLIC, and when connecting to the privileges (SYSBACKUP & SYSDG & SYSRAC) the current Schema is SYS, and when connecting to the SYSKM privilege, the current Schema is SYSKM.

As for connecting to the database with one of the administrative privileges (Administrative Privileges), you connect to the specified user. When connecting as SYSDBA, the Session User is SYS, and when connecting as STSOPER, the Session User is PUBLIC, and when connecting as

SYSBACKUP & SYSDG & SYSDG & SYSKM & SYSRAC)) the Session User is as follows

SYSBACKUP & SYSDG & SYSDG & SYSKM & SYSRAC)) respectively.

Here is the following example:

Current Schema and Session User When Connecting AS SYSBACKUP

CONNECT mydba AS SYSBACKUP

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

-----------------------------------------------------------------

SYS

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

------------------------

SYSBACKUP

Authentication Methods for Database Administrators:

Database administrators are verified and authenticated by the account password or by verification via the operating system or via password files.

About Authentication Methods for Database Administrators:

As we mentioned earlier, there are several ways to verify database administrators. Oracle database can verify database administrators via the data dictionary and via the password, just like other users.

In addition to verifying via the password stored in the data dictionary, there are several ways to verify database administrators who have privileges

(SYSDBA, SYSOPER, SYSBACKUP, SYSDG or SYSKM):

Verification via the operating system.

Verification via the password file

Verification via the directory-based authentication services

For SYSRAC Privilege, verification is only done via the operating system.

These verification methods are necessary if the database is closed or unavailable. Verification via the data dictionary cannot be done if the database is closed. You can also use these methods if the database is open and available.

In this section, we will discuss verification via the operating system or via the password file. As for verification via the directory-based authentication services, you can review the Oracle Database Security Guide for more information.

It should be noted that verification and authentication via the operating system have priority over verification via the password file. If you meet the requirements for verification via the operating system, even if you use verification via the password file, verification will be done via the operating system.

Your choice is influenced by whether you intend to manage the database locally from within the server on which the database is running, or whether you intend to manage the database remotely from another device.

Nonsecure Remote Connections:

To connect to the database as a user with administrative privileges (SYSDBA, SYSOPER, SYSBACKUP, SYSDG or SYSKM) remotely via an insecure connection, you must verify the password file. When verifying the password file, the database uses the password file to track the names of database users who have been granted administrative privileges.

It should be noted that most common communication protocols are insecure, such as (TCP/IP and DECnet).

Local Connections and Secure Remote Connections:

If you want to connect to the database locally or from within the server on which the database is running, or to connect remotely via a secure connection, you have two options:

If the database has a password file and you have administrative privileges, you can connect to the database using the password file.

If the database does not have a password file or if you are not granted any administrative privileges, you can connect using verification via the operating system.

In most operating systems, database administrator authentication includes user names in groups specific to the operating system.

Using Operating System Authentication:

Database administrators in groups specific to the operating system allow them to connect to the database by verifying with the operating system instead of using a user name and password.

Operating System Groups:

Oracle database operating system groups are created and names are assigned to the groups during database installation.

The default operating system group names for the database vary depending on your operating system, as in the table below:

The Oracle Universal Installer uses these default names to create groups on the operating system, but in the case of the operating system (Unix & Linux), this naming can be ignored if you have more than one Instances on the same server and each Instance is located in a different Oracle Home. If each Instance has Different DBA, in this case we need a number of groups for each Instance.

For the Windows operating system, the default naming does not change, but if there is more than one Instance on the same server and each Instance on Oracle Home is different, then the groups are named including the name of the Oracle Home as shown in the table above.

Users in these groups can connect to the database as database administrators as follows:

If you are a member of the OSDBA Group and use the AS SYSDBA administrative privilege when connecting to the database, you can connect to the database with the SYSDBA administrative privilege.

If you are a member of the OSOPER Group and use the AS SYSOPER administrative privilege when connecting to the database, you can connect to the database with the SYSOPER administrative privilege.

If you are a member of the OSBACKUPDBA Group and use the AS SYSBACKUP administrative privilege when connecting to the database, you can connect to the database with the SYSBACKUP administrative privilege.

If you are a member of the OSDGDBA Group and use the AS SYSDG administrative privilege when connecting to the database, you can connect to the database with the SYSDG administrative privilege.

If you are a member of the OSKMDBA Group and use the AS SYSKM administrative privilege when connecting to the database, you can connect to the database with the SYSKM administrative privilege.

If you are a member of the OSRACDBA Group and use the AS SYSRAC administrative privilege when connecting to the database, you can connect to the database with the SYSRAC administrative privilege.

However, if you are not a member of any of the operating system groups above and you try to connect to the database with one of the administrative privileges, the connection to the database will fail if verification is done through the operating system.

Preparing to Use Operating System Authentication:

As we mentioned previously, database administrators can connect to the database and verify it through the operating system without entering the user and password, but in order to verify it through the operating system you need:

You must have an account on the operating system, and if you do not have an account, you must create an account on the operating system.

Add the operating system account to the operating system group for database management.

Connecting Using Operating System Authentication:

You can connect as a database administrator from within the server on which the database runs through authentication and verification via the operating system as follows:

CONNECT / AS SYSDBA

CONNECT / AS SYSOPER

CONNECT / AS SYSBACKUP

CONNECT / AS SYSDG

CONNECT / AS SYSKM

Only the Windows operating system supports operating system authentication during a secure remote connection by specifying the Net Service Name during the connection as follows:

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 

But both the client computer and the server on which the database runs must be on the Windows operating system.

Using Password File Authentication:

Database administrators and those with administrative privileges can also connect to the database and verify through the Password file.

Preparing to Use Password File Authentication:

In preparation for using authentication and verification via the Password file, a Password file must exist, and if it does not exist, it must be created, and the REMOTE_LOGIN_PASSWORDFILE variable must also be set on the database.

If you created the database through the Database Configuration Assistant (DBCA) tool, the DBCA creates a Password file while creating the database.

To activate authentication and verification via the Password file for users with administrative privileges, you must do the following:

If the Password file does not exist, you must create it using the ORAPWD tool

orapwd FILE=filename FORMAT=12.2

The REMOTE_LOGIN_PASSWORDFILE variable must take the value exclusive, which is the default value for the variable.

Connect to the database with administrative privileges.

The database user must have administrative privileges (SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM).

n:

Users with administrative privileges can connect to the database locally or remotely by checking the Password file. By default, the Password file is case-sensitive.

To connect to the database and verify via the Password file, you must first have one of the administrative privileges and then connect to SQL*Plus with the user name and password in addition to the phrase (AS SYSDBA, AS SYSOPER, AS SYSBACKUP, AS SYSDG, or AS SYSKM), for example:

CONNECT mydba AS SYSDBA

CONNECT mydba AS SYSOPER

If the mydba user is not granted SYSDBA or SYSOPER permission, the above connection will fail.

It should also be noted that verification via the operating system takes priority over verification via the Password file if verification via the operating system is available.