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.

SYSBACKUP

ALTER DATABASE
ALTER SYSTEM
CREATE SESSION
ALTER SESSION
ALTER TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
RESUMABLE
CREATE ANY DICTIONARY
CREATE ANY TABLE
AUDIT ANY
SELECT ANY DICTIONARY
SELECT ANY TRANSACTION
SELECT X$TABLES, V$VIEWS
EXECUTE SYS.DBMS_BACKUP_RESTORE
EXECUTE SYS.DBMS_RCVMAN
EXECUTE SYS.DBMS_TR
EXECUTE SYS.DBMS_TTS
EXECUTE SYS.DBMS_TDB
EXECUTE SYS.DBMS_PLUGTS
EXECUTE SYS.DBMS_PLUGTSP
CREATE PFILE
CREATE SPFILE
CREATE CONTROLFILE
DROP DATABASE
STARTUP
SHUTDOWN
CREATE RESTORE POINT
DROP RESTORE POINT
FLASHBACK_DATABASE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
ABLE TO CONFIRM TABLE EXISTENCE BUT NOT QUERY DATA

SYSDG

CREATE SESSION
ALTER SYSTEM
ALTER SESSION
ALTER DATABASE
SELECT ANY DICTIONARY
SELECT X$TABLES, V$VIEWS
DELETE/SELECT ON APPQOSSYS.WLM_CLASSIFIER_PLAN
EXECUTE SYS.DBMS_DRS
STARTUP
SHUTDOWN
CREATE RESTORE POINT
DROP RESTORE POINT
FLASHBACK DATABASE
ABLE TO CONFIRM TABLE EXISTENCE BUT NOT QUERY DATA

SYSKM

CREATE SESSION
ADMINISTER KEY MANAGEMENT
SELECT SYS.V$WALLET
SELECT SYS.V$ENCRYPTION_WALLET
SELECT SYS.V$ENCRYPTED_TABLESPACES
NO ACCESS TO APPLICATION DATA

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:

1) Use a policy to identify the necessary system and object privileges used to run an application or execute SQL statements or those in use by different roles

2) Create reports of used and unused privileges during the analysis period

3) Use the report to help determine which privileges can be safely revoked from users

The basic steps to run an analysis are:

a) Define the target objects you wish to analyze

b) Start the analysis capture, and end it after a period of time

c) Generate the report of used and unused privileges

d) Use the results to determine if any changes need to be made to the existing privileges

Step A: Define the target objects to analyze

There are three kinds of analysis that can be performed:

Database – analyze used privileges within the entire database (except for those being used by administrative users).

To run a database level analysis:

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

Role – analyze the privileges used by any specified role.

 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’);

Context Specific – analyze the privileges used through a specified module.

 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’;

After a reasonable period of time:

  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

There are new dictionary views that can be used to examine the results.

  • DBA_USED_SYSPRIVS shows which system privileges were used during the analysis period
  • DBA_USED_OBJPRIVS shows which object privileges were used during the analysis period
  • DBA_USED_OBJPRIVS_PATH shows how the privileges were granted – direct to the user or via a role
  • DBA_UNUSED_PRIVS shows privileges that were granted to the users but not used during the analysis period
  • DBA_PRIV_CAPTURES shows information about the various captures that have been created

To drop a capture that is no longer required use the following:

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

Karen Reliford
Karen Reliford
Karen Reliford is an IT professional who has been in the industry for over 25 years. Karen's experience ranges from programming, to database administration, to Information Systems Auditing, to consulting and now primarily to sharing her knowledge as an Oracle Certified Instructor in the Oracle University Partner Network. Karen currently works for TransAmerica Training Management, one of the foremost Oracle Authorized Education Centers (OAEC) in the Oracle University North America region. TransAmerica Training Management offers official Oracle and Peoplesoft Training in Coral Gables FL, Fayetteville AR, Albuquerque NM, Providence RI and San Juan PR. Karen has now been teaching Oracle for Oracle University for more than 15 years. Karen has attained her Certified Technical Trainer designation along with several Oracle certifications including OCP-DBA, OCP-Internet Developer, Oracle Expert - Oracle 10g RAC and Oracle Expert - Oracle Application Express (3.2). Additionally, Karen achieved her Oracle 10g Oracle Certified Master (OCM) in 2008. Karen was raised in Canada, and in November 2009 became a US Citizen. Karen resides in Columbus OH with her husband, Ron along with their 20 pets, affectionately referred to as the "Reliford Zoo".

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles