Oracle Database 12c: New Privilege Management Features

With the much-anticipated release of the 12c Database, Oracle has added many new features. Easily the most significant is the concept of multi-tenant (pluggable) databases.  However, along with this major new feature, there were plenty of other “smaller” new features – and one set that is of particular interest in the user security realm are the new features around database privilege management. 

Task Based System Privileges

One basic security requirement is the ability to allow for separation of duties.  In the previous releases of Oracle, most system administration activities were done under the SYSDBA privilege.  However, having almost all activities handled under one set of privileges does not truly lend itself to the concept of separation of duties, or to the accepted principle of least privilege.

To that end, the Oracle Database now has new task-based privileges for standard activities such as backup & recovery using RMAN, Oracle Data Guard and Transparent Data Encryption (TDE).

There are now six different system level administrative roles – all with different privileges and associated user names – below is an overview of all of them.

• SYSDBA – username SYS/PUBLIC – same system operations and privileges as 11g and earlier
• SYSOPER – username SYS/PUBLIC – same system operations and privileges as 11g and earlier
• SYSASM – username SYS – privileges for administering an ASM instance only (first introduced in 11g)
• SYSBACKUP – username SYSBACKUP – the ability to perform RMAN backup and recovery commands both from SQL and RMAN command line – new in 12c
• SYSDG – username SYSDG – the ability to perform Data Guard operations with Data Guard Broker or the DGMGRL command line – new in 12c
• SYSKM – username SYSKM – the ability to manage the encryption keys for Transparent Data Encryption – new in 12c

None of the above users (SYS, SYSBACKUP, SYSDG and SYSKM) can be dropped from the database.  Also, with all of these privileges, the user will have the ability to connect to the database even if it is currently closed.  Once connected with any of the new roles, you will also see that you are connected as the associated user.

SQL> connect / as SYSBACKUP
SQL> show user
SQL> USER is “SYSBACKUP”

The following table identifies the associated privileges with each of the new system administration roles.

DBCA has also been modified to allow for each of these system roles to be associated with different OS level groups (such as OSBACKUP, OSDG, OSKM) to also allow for complete separation of OS user accounts and privileges.

There are also changes that can be made to the password file that will allow for remote connections with these new roles as follows.

 $ orapwd file=orapwSID
 > password=abc entries=5
 > format=12
 > sysbackup=y sysdg=y

The new options:

• format=12 creates the password file in the new 12c format, which supports the new arguments – the other option would be format=legacy
• sysbackup=y and sysdg=y are new to allow for remote access using both SYSBACKUP and SYSDG

Lastly, these new system roles can be incorporated into Oracle Database Vault and actions performed while connected with the new system privilege roles would also be included in the audit trail if AUDIT_SYS_OPERATIONS is set to TRUE.

Privilege Analysis

A second significant change that has been made with regard to privilege management is the new option for conducting Privilege Analysis. 

Besides being able to better manage the system privileges for routine operations, another key aspect of privilege management is ensuring that users do not have unnecessary and unused privileges assigned to their accounts.  The challenge for DBAs has always been trying to determine which privileges are actually being used by a user and which are not.

In Oracle Database 12c, they have introduced a new package that can be used to help with the following:

The basic steps to run an analysis are:

Step A: Define the target objects to analyze

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
 name=>’All_Priv_Analysis’, -
 description=>’Captures everything’, - 
 type => dbms_privilege_capture.g_database);

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
 name=>’Role_Priv_Analysis’, -
 description=>’Captures for a role’, -
 type=> dbms_privilege_capture.g_role, -
 roles=> role_name_list(‘ROLE1’,’ROLE2’);

 exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
 name=>’Priv_GL_Analysis’, -
 description=>’Captures for GL App’, -
 type=>dbms_privilege_capture.g.context, -
 condition=> ‘SYS_CONTEXT -
 (‘ ‘USERENV’ ‘,’ ‘MODULE’ ‘)=’ ‘General Ledger’ ‘ ‘);

Step B: Start (and end) the capture

 exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ( - name =>’All_Priv_Analysis’;

  exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ( - name =>’All_Priv_Analysis’;

Step C: Generate the Analysis Report

exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ( - name=>’All_Priv_Analysis’;

Step D: Review the Results

exec DBMS_PRIVILEGE_CAPTURE.DROP_CAPUTURE(‘All_Priv_Analysis’);

With these new features to help with Privilege Management in the new Oracle Database 12c, Oracle has taken some great steps to help DBAs better separate duties, and enforce the standard principle of least when it comes to managing user security in an Oracle Database.

See all articles by Karen Reliford