Starting with Oracle Database 12c Release 1, Oracle has introduced a set of more task-oriented new administrative privileges. Prior to Oracle 12c you had to have the SYSDBA privilege to make a backup, monitor the Data Guard status, or administrate certain features of Oracle Transparent Data Encryption. With Oracle 12c Release 1, this is not the case anymore. You can now grant limited administrative privileges for the operating of a Oracle 12c database.

This new feature can already be seen during the Oracle 12c installation process. Indeed, during the binary installation, Oracle asks for the granting of privileges to the following groups:

Oracle 12.1 Administrative Roles during Installation

Despite the fact that we still have the possibility to provide only one group for each of these entries, Oracle recommends to provide a unique group to designate each privilege.

Among these new administrative privileges, one can find the following:

OS Group Database Privilege Short description
backupdba SYSBACKUP This group allows operating system users to have a limited set of database backup and recovery related administrative privileges
dgdba SYSDG This group allows a group of operating sytem users to have a limited set of privileges to administer and monitor Oracle Data Guard
kmdba SYSKM This allows operating sytem users to have a limited set of privileges for encryption key management such as Oracle Wallet Manager management, since, by default, Oracle 12c encapsulates features such as database vault

Of course, in order to grant these privileges to users you will have to know exactly what is behind these new administrative privileges. You can find the operations allowed for each of these administrative privileges below:

SYSBACKUP

  • STARTUP
  • SHUTDOWN
  • ALTER DATABASE
  • ALTER SYSTEM
  • ALTER SESSION
  • ALTER TABLESPACE
  • CREATE CONTROLFILE
  • CREATE ANY DIRECTORY
  • CREATE ANY TABLE
  • CREATE ANY CLUSTER
  • CREATE PFILE
  • CREATE RESTORE POINT (including GUARANTEED restore points)
  • CREATE SESSION
  • CREATE SPFILE
  • DROP DATABASE
  • DROP TABLESPACE
  • DROP RESTORE POINT (including GUARANTEED restore points)
  • FLASHBACK DATABASE
  • RESUMABLE
  • UNLIMITED TABLESPACE
  • SELECT ANY DICTIONARY
  • SELECT ANY TRANSACTION
  • SELECT
    • X$ tables (that is, the fixed tables)
    • V$ and GV$ views (that is, the dynamic performance views)
    • APPQOSSYS.WLM_CLASSIFIER_PLAN
    • SYSTEM.LOGSTDBY$PARAMETERS
  • DELETE/INSERT
    • SYS.APPLY$_SOURCE_SCHEMA
    • SYSTEM.LOGSTDBY$PARAMETERS
  • EXECUTE
    • SYS.DBMS_BACKUP_RESTORE
    • SYS.DBMS_RCVMAN
    • SYS.DBMS_DATAPUMP
    • SYS.DBMS_IR
    • SYS.DBMS_PIPE
    • SYS.SYS_ERROR
    • SYS.DBMS_TTS
    • SYS.DBMS_TDB
    • SYS.DBMS_PLUGTS
    • SYS.DBMS_PLUGTSP
  • SELECT_CATALOG_ROLE

SYSDG

  • STARTUP
  • SHUTDOWN
  • ALTER DATABASE
  • ALTER SESSION
  • ALTER SYSTEM
  • CREATE RESTORE POINT (including GUARANTEED restore points)
  • CREATE SESSION
  • DROP RESTORE POINT (including GUARANTEED restore points)
  • FLASHBACK DATABASE
  • SELECT ANY DICTIONARY
  • SELECT
    • X$ tables (that is, the fixed tables)
    • V$ and GV$ views (that is, the dynamic performance views)
    • APPQOSSYS.WLM_CLASSIFIER_PLAN
  • DELETE
    • APPQOSSYS.WLM_CLASSIFIER_PLAN
  • EXECUTE
    • SYS.DBMS_DRS

SYSKM

  • ADMINISTER KEY MANAGEMENT
  • CREATE SESSION
  • SELECT (only when database is open)
    • SYS.V$ENCRYPTED_TABLESPACES
    • SYS.V$ENCRYPTION_WALLET
    • SYS.V$WALLET
    • SYS.V$ENCRYPTION_KEYS
    • SYS.V$CLIENT_SECRETS
    • SYS.DBA_ENCRYPTION_KEY_USAGE

Tests

In order to illustrate the usage of this new feature, let’s connect with an operating system user who is member of the backupdba operating system group and change the sga_target parameter:

oracle@vmtestoel01:/home/oracle/ [SOUK] id grs
uid=54323(grs) gid=54324(backupdba) groups=54324(backupdba)

oracle@vmtestoel01:/home/oracle/ [SOUK] su - grs

grs@vmtestoel01:/home/grs/ [SOUK]  sqlplus / as sysbackup
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
SQL> alter system set sga_target=800M;
System altered.

Now, let’s try to drop a tablespace with a user (in our case “her”) who is a member of the dgdba operating system group (SYSDG). This user should not be allowed to perform such an operation since the privilege “drop tablespace” is not in the SYSDG group.

[root@vmtestoel01 ~]# id her
uid=54324(her) gid=54325(dgdba) groups=54325(dgdba)
 
oracle@vmtestoel01:/home/oracle/ [SOUK] su - her
 
her@vmtestoel01:/home/her/ [SOUK]  sqlplus /as sysdg
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
SQL> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-01031: insufficient privileges

As expected, the user “her” was not allowed to drop a tablespace, since SYSDG is not allowed to perform such an operation.

Conclusion

I think this new feature will be very valuable and appreciated especially in an environment where a strong segregation of duties is made. However, the usage of this new feature is not mandatory since Oracle allows to use the same OS group (for instance dba) for each administrative group.

Have fun with Oracle 12c Release 1!