New Security Features in Oracle Database 12cR1

The latest release of the Oracle Database – 12c Release 1 (12cR1) – is rapidly being acknowledged as a major transformation for Oracle DBAs because it offers a plethora of new features, with database security receiving major attention. One especially glaring issue that needed attention was the broad set of system privileges relegated to the

SYSDBA privilege. Even in Oracle 11gR2, the SYSDBA privilege was required for a significant number of administrative tasks, including establishment and management of Oracle Data Guard physical and logical standby database environments; performing backup and restore actions via Recovery Manager (RMAN);  and even for managing Transparent Data Encryption (TDE) wallets and decryption features.

The good news is that 12cR1 now breaks off these three sets of administrative privileges from

SYSDBA, delegating them instead to three brand-new privilege sets described below:

• SYSBACKUP: RMAN Only.  Oracle 11gR2 required that a user account had been granted the SYSDBA privilege to perform any RMAN backup, restoration, or recovery operation. Oracle 12cR1 now provides the SYSBACKUP privilege that limits a user account to handle just these tasks.
• SYSDG: Data Guard Only.  Likewise, in Oracle 11gR2 the SYSDBA privilege is still required for setting up and managing a Data Guard environment, but Oracle 12cR1’s new SYSDG privilege grants just the necessary privileges for management of Data Guard environments.
• SYSKM: Hands Off My Wallet! The SYSDBA privilege is still needed in Oracle 11gR2 to permit a user to open the Oracle “wallet” for Transparent Data Encryption (TDE), thus also permitting access to encrypted database objects, but Oracle 12cR1’s new SYSKM privilege now limits TDE management tasks to only a user granted this privilege.

These new sets of system privileges are summarized in Table 1 below:

Table 1. Privileges GRANTed to SYSBACKUP, SYSDG, and SYSKM.

The code examples in Listing 1-1 offer a simple illustration of how these new system privilege sets can be used to limit new user accounts for both container and pluggable databases to their respective jurisdictions – for example, permitting a user account granted the SYSBACKUP privilege set to only perform RMAN BACKUP and RESTORE commands while simultaneously restricting that same user account from performing other typical DBA tasks that now require full SYSDBA privileges in Oracle 12cR1.

OS Authentication and Password Files

The new SYSBACKUP, SYSDG, and SYSKM user accounts are also by default the only members in the corresponding OSBACKUP, OSDG, and OSKM groups in Linux / UNIX. (The same groups are also supported in Microsoft Windows environments; however, the groups are named ORA_%HOMENAME%_SYSBACKUP, ORA_%HOMENAME%_SYSDG, and ORA_%HOMENAME%_SYSKM, respectively.)

These new administrative privileges are also supported during the creation or upgrade of a database’s password file via the Oracle 12cR1 version of the orapwd utility. To reflect this, orapwd now accepts several new parameters shown in Table 2 below.

Table 2. New Options for orapwd Utility

In the example below, orapwd will convert the TESTDB database’s existing Oracle 11gR2 password file to its Oracle 12cR1 equivalent while also creating entries for SYSBACKUP and SYSDG entry in the new password file:

$> orapwd -format 12 -sysbackup y -sysdg y -inputfile $ORACLE_HOME/dbs/pwdtestdb

Analyzing and Capturing Errant Privileges

The new SYSBACKUP, SYSDG, and SYSKM privileges provide considerable surety when it’s necessary to segregate selected privileges to only the most appropriate user, but DBAs are often vexed by another, more pernicious issue: system and object privileges that were granted to user accounts that should have never received those privileges. The good news here is that Oracle 12cR1 finally provides the ability to find these “over-privileged” users with a new package, DBMS_PRIVILEGE_CAPTURE:

• Procedure CREATE_CAPTURE builds a privilege analysis policy that will monitor either privileges granted based on specific application contexts, or only those granted by a specific security role, or even the ones granted within an entire database.
• Once a privilege capture policy is created, it’s enabled via a call to procedure ENABLE_CAPTURE.
• After the DBA decides that sufficient time has passed, or application activity has completed, calling DISABLE_CAPTURE will disable the analysis. The DBA can then invoke procedure GENERATE_RESULT to flush all privilege analysis findings to the database’s data dictionary. The DBA can then run reports against data dictionary views specific to this new functionality. I’ve listed the privilege reporting views in Table 1-3 below.
• Finally, after privilege capture is completed and the policy has been disabled, The DBA can invoke the DROP_CAPTURE procedure to drop a privilege analysis policy that’s no longer required. Note that invoking DROP_CAPTURE against a privilege analysis policy also destroys any used or unused privilege metadata that’s already been captured.

Listing 1-2 illustrates exactly how the DBMS_PRIVILEGE_CAPTURE procedure and the metadata it collects can aid in analysis of questionably-granted system and object privileges.

Listing 1-3 also provides several preformatted SQL*Plus queries against the new data dictionary views in Table 3 below, as well as samples of the privilege analysis findings metadata contained in each view:

Table 3. Privilege Analysis Reporting Views

These queries demonstrate how to discover not just the overly-powerful system and object privileges that have been previously granted to users, but privileges that were granted but have never been used

Next Steps

A future article in this series will take a look at the significant enhancements to auditing capabilities in Oracle 12cR1, including audit policies, the Unified Audit Trail, and automatic audit trail maintenance features.