User Task-Specific Administrative Privileges

• To continue with the objective of separating duties and the least privileges, Oracle 12c introduce new administratives privileges all destinated to accomplish specific duties:
  • SYSBACKUP : Used for RMAN operations like BACKUP, RESTORE, RECOVER
  • SYSDG : Used to administer DATAGUARD, In 12c when you use DGMGRL commandline interface your are automatically loggued on with the SYSDG privilege.
  • SYSKM : This privilege is meant for TDE operations like OPEN/CLOSE key stores, change master key or manage column encryption keys and tablespace encryption.

New OS groups

• In addition with these new privileges comes new OS user groups :
  • OSBACKUP 
  • OSDG
  • OSKM
• The members of these groups are automatically granted the corresponding predifined oracle privileges mentionned before.
• Like for the SYSDBA privilege and the dba group, you can’t use the OS authentication  for these privileges if your are not member of the OS group.
• By default in Oracle 12c, the passwordfile supports the new SYSDB, SYSBACKUP and SYSKM privileges, however you can choose to disable the authentication for one or more of these groups:

orapwd FILE=filename [ENTRIES=numusers] [FORCE={y|n}] [ASM={y|n}] 
[DBUNIQUENAME=dbname] [FORMAT={12|legacy}] [SYSBACKUP={y|n}] [SYSDG={y|n}] 
[SYSKM={y|n}] [DELETE={y|n}] [INPUT_FILE=input-fname]

•  The FORMAT and INPUT_FILE keyword in the orapwd command is used to migrate an old passwordfile to the new format by using :

orapwd file='/u01/app/oracle/product/12.1.0.1/dbs/orapwdDB' FORMAT=12 INPUT_FILE='/u01/app/oracle/product/12.1.0.1/dbs/orapwdDB.old' FORCE=y

•  You can view the users configured in the passwordfile with their associated privileges by querying the V$PWFILE_USERS view.

 Create, Enable and Use Privilege Analysis

• The main objective is to analyze privileges given to users and observe those which are actually used and those which are not.
• It is very difficult to keep track of what privileges users really use, this is why Oracle introduced a capture mechanism to keep track of privileges utilization, the PL/SQL package DBMS_PRIVILEGE_CAPTURE.
• Creating a privileges capture with the CREATE_CAPTURE procedure:

DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE (
   name            IN  VARCHAR2,
   description     IN  VARCHAR2 DEFAULT NULL, 
   type            IN  NUMBER DEFAULT G_DATABASE,
   roles           IN  ROLE_NAME_LIST DEFAULT ROLE_NAME_LIST(),
   condition       IN  VARCHAR2 DEFAULT NULL);

•  Exemples :

--Create a database privilege analysis policy
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
        name         => 'all_priv_analysis_pol',
        description  => 'database-wide policy to analyze all privileges',
        type         => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;

--Create a privilege analysis policy to analyze privileges from the role PUBLIC
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
       name         => 'pub_analysis_pol',
       description  => 'Policy to record privilege use by PUBLIC',
       type         => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
       roles        => role_name_list('PUBLIC'));
END;

-- Create a policy to analyze privileges from the application module, "Account
-- Payable"
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name         => 'acc_pay_analysis_pol',
  type         => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  condition    => 'SYS_CONTEXT(''USERENV'', ''MODULE'') = ''Account Payable''');
END;

-- Create a policy that records privileges for session user APPS when running the
-- application module "Account Payable"
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  name         => 'acc_pay_analysis_pol',
  type         => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  condition    => 'SYS_CONTEXT(''USERENV'', ''MODULE'') = ''Account Payable'' AND
                   SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''APPS''');
END;

• To list the created privilege captures, you can use the view DBA_PRIV_CAPTURES.

 Stop/Start/Drop the privilege analysis

• Once you created the capture, you have to enable it:

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE('MY_CREATED_CAPTURE');

•  If you want to disable it:

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE('MY_CREATED_CAPTURE');

•  To drop it:

EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE('MY_CREATED_CAPTURE');

 Analyze the capture results

• To analyze the capture you have to generate a report with the GENERATE_RESULT procedure:

SQL> EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT('MY_CREATED_CAPTURE');

•  Once you generated the report, you can consult the two views DBA_USED_SYSPRIVS and DBA_USED_OBJPRIVS
• These two views list the USED privileges, to list the UNUSED privileges which is the first objective of the capture, you can use the view DBA_UNUSED_PRIVS.