To support the principles of least privilege and separation of duty, the SYSOPER administrative privilege allows an administrator to perform limited tasks such as starting and stopping the database without having the full range of powers conferred by the SYSDBA privilege.
The main types of user privileges are as follows:
System privileges—A system privilege gives a user the ability to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the system privilege CREATE TABLE permits a user to create tables in the schema associated with that user, and the system privilege CREATE USER permits a user to create database users.
Object privileges—An object privilege gives a user the ability to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privilege to select rows from the EMPLOYEES table or to delete rows from the DEPARTMENTS table are examples of object privileges.
Managing privileges is made easier by using roles, which are named groups of related privileges. You create roles, grant system and object privileges to the roles, and then grant roles to users. You can also grant roles to other roles. Unlike schema objects, roles are not contained in any schema.
Oracle Database 12c adds additional administrative privileges called SYSBACKUP, SYSDG, SYSRAC, and SYSKM to enable database backups, Data Guard administration, key management, and RAC management, respectively. With these targeted privileges, one or more administrators can perform all of the normal operations to manage a database without needing the all-powerful SYSDBA privilege.
User privileges provide a basic level of database security.
They are designed to control user access to data and to limit the kinds of SQL statements that users can execute. When creating a user, you grant privileges to enable the user to connect to the database, to run queries and make updates, to create schema objects, and more.
Previously all Oracle DBA related activities were either performed using the powerful SYSDBA or the SYSOPER role. In support of the SOD requirements starting with Oracle 12c, new administrative roles have been introduced to conform to the principle of access to the least privilege.
Three new user SYSBACKUP, SYSDG and SYSKM are created in support of this, when the database is created, with their account in the “EXPIRED & LOCKED” status. An equivalent administrative privilege with the same name as the user is created as well.
SQL>SELECT username , account_status
FROM dba_users
ORDER BY created;
USERNAME ACCOUNT_STATUS
———————- ——————
SYS OPEN
SYSTEM OPEN
SYSKM EXPIRED & LOCKED
SYSDG EXPIRED & LOCKED
SYSBACKUP EXPIRED & LOCKED
New Administrative Privileges
These new accounts have been provisioned for use with the appropriate privileges.
SQL>SELECT *
FROM V$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
—————————— —– —– —– —– —– —– ———-
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
SYSBACKUP will be used to perform all backup and recovery-related operations either via RMAN or SQL*PLUS. Here you can find a complete list of SYSBACKUP privileges you are assigned when logged in with the SYSBACKUP administrative privilege. SYSDG is in place to separate the Data Guard related operations from other activities. Here you can find a complete list of SYSDG privileges you are assigned when logged in with the SYSDG administrative privilege.
SYSKM will be responsible for all TDE (Transparent Data Encryption) and Data Vault related administrative operations. Here you can find a complete list of SYSKM privileges you are assigned when logged in with the SYSKM administrative privilege.
None of these new database roles can be dropped. They have enough privileges that using them user can connect to the database even if it is closed. Also, all these roles are incorporated into the Oracle database Vault. Actions performed using these privileges can be audited if AUDIT_SYS_OPERATIONS is set to true.
Add New Privileges to Password File
When a user needs to connect to the database using the SYSBACKUP, SYSDG, or SYSKM administrative privilege, the user must be added to the password file with the appropriate user privilege flag. The option to include these new privileges has been added to the orapwd utility.
orapwd file=[fname] entries=[users] force=[y/n] asm=[y/n] dbuniquename=[dbname] format=[legacy/12] sysbackup=[y/n] sysdg=[y/n] syskm=[y/n] delete=[y/n] input_file=[input-fname] orapwd FILE=’$ORACLE_HOME/dbs/orapwvstdb01′ ENTRIES=10 SYSBACKUP=y
Current Schema and Session for SYSBACKUP, SYSDG and SYSKM
When a user is connected using any of these admin privileges, the schema that they are assigned to is the SYS schema, and the session name corresponds to the privilege name that they are using.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select sys_context(‘userenv’, ‘current_schema’) current_schema, sys_context(‘userenv’, ‘session_user’) session_user from dual;
CURRENT_SCHEMA SESSION_USER
—————————— ——————————
SYS SYS
SQL> conn sysdg as sysdg
Enter password:
Connected.
SQL> select sys_context(‘userenv’, ‘current_schema’) current_schema, sys_context(‘userenv’, ‘session_user’) session_user from dual;
CURRENT_SCHEMA SESSION_USER
—————————— ——————————
SYS SYSDG
SQL> conn sysbackup as sysbackup
Enter password:
Connected.
SQL> select sys_context(‘userenv’, ‘current_schema’) current_schema, sys_context(‘userenv’, ‘session_user’) session_user from dual;
CURRENT_SCHEMA SESSION_USER
—————————— ——————————
SYS SYSBACKUP
SQL> conn syskm as syskm
Enter password:
Connected.
SQL> select sys_context(‘userenv’, ‘current_schema’) current_schema, sys_context(‘userenv’, ‘session_user’)
session_user from dual;
CURRENT_SCHEMA SESSION_USER
—————————— ——————————
SYSKM SYSKM
New Database Role OS Group
To further ensure the separation of access to the new SYSBACKUP, SYSDG and SYSKM privileges, Oracle recommends mapping them to the new OSBACKUPDBA, OSDGDBA and OSKMDBA operating system groups respectively.
With the introduction of the new Database Administration users and the scaled-down privileges, implementing segregation of duties is indeed possible. Further, by providing the flexibility to only assign the required DBA privilege and mapping it to the specific OS role groups, accountability on the use of the specific role is made easier.
