OCP 12C – Privileges

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

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

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

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

  •  Exemples :

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

  •  If you want to disable it:

  •  To drop it:

 Analyze the capture results

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

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.