Oracle Database 12c new features: segregation of duties with new administrative roles

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:

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:

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.

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.

Grégory Steulet is Chief Financial Officer (CFO) and Region Manager at dbi services. He has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix/Linux, etc.). Grégory Steulet is "Oracle Certified Professional 10g", "MySQL Cluster 5.1 Certified", and "Avaloq Certified Professional 2.6". Prior to joining dbi services, Grégory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Grégory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services / Banking, Logistics, Pharma etc.