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.
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:
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.