LATEST TOPICS

Oracle 12c: Track and analyze privilege utilization with DBMS_PRIVILEGE_CAPTURE

Introduction

It is always desired to grant least possible privileges to database users to prevent any potential security threat. Granting additional/excessive privilege to a user is always associated with a potential database security threat.

If we are building new database system, it would be relatively easy to implement the “least privilege” user standards. However, implementing the “least privilege” user standards in an existing system would be much complicated where excessive privileges has already been granted. It would be a tough job to monitor/audit the utilization of granted privileges and then trim those down to the lowest possible level to have a “least privilege” user standard.

Good news is that, Oracle 12c allows us to monitor/track the utilization of granted privileges (without the need of complex auditing) to be able to perform privilege analysis and in turn revoke any excessive privileges granted to a database user.

With the introduction of Oracle 12c, we have a new database package called DBMS_PRIVILEGE_CAPTURE; which is supplemented with a wide range of procedures to facilitate tracking and analysis of database privileges. Privilege tracking is done by implementing following framework (in the given order) with the help of procedures provided with the DBMS_PRIVILEGE_CAPTURE package

  1. Create a privilege analysis policy with CREATE_CAPTURE procedure from DBMS_PRIVILEGE_CAPTURE package
  2. Enable the privilege analysis policy with ENABLE_CAPTURE procedure from DBMS_PRIVILEGE_CAPTURE package
  3. Let the policy run for a required analysis period. Oracle will gather/collect all the required tracking details for this duration
  4. Disable the privilege analysis policy with DISABLE_CAPTURE procedure from DBMS_PRIVILEGE_CAPTURE package
  5. Analyze the captured information with GENERATE_RESULT procedure from DBMS_PRIVILEGE_CAPTURE package
  6. Drop the privilege analysis policy (if the policy and its associated captured information is no longer required) using DROP_CAPTURE procedure from DBMS_PRIVILEGE_CAPTURE package

Let us go through each of these procedures along with examples to have a firm understanding about the framework.

CREATE_CAPTURE

To be able to start analysing privilege utilization, we need to first create a privilege analysis policy with the help of CREATE_CAPTURE procedure provided by the DBMS_PRIVILEGE_CAPTURE package. The syntax for CREATE_CAPTURE procedure is as follows

----//
----// Syntax for 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
);

--- where
--- name		: Name of the privilege analysis policy (mandatory).
--- description : A brief description about the policy (optional).
--- types		: Type of the privilege analysis policy. Possible values are G_DATABASE, G_ROLE, G_CONTEXT or G_ROLE_AND_CONTEXT.
---				where
---				--- G_DATABASE			: Tracks all privileges in the database except the ones used by SYS user
---				--- G_ROLE				: Tracks the usage of a privilege, if it is part of a specified role or list of roles
---				--- G_CONTEXT			: Tracks the privilege, if the context specified by the condition evaluates to true
---				--- G_ROLE_AND_CONTEXT	: Tracks the privilege, if it is part of specified role or list of roles as well as if the condition evaluates to true
--- roles		: Role(s), whose privileges needs to be monitored (mandatory, if types is selected as G_ROLE/G_ROLE_AND_CONTEXT)
--- condition	: PL/SQL boolean expression up to 4000 chars (mandatory, if types is G_CONTEXT/G_ROLE_AND_CONTEXT)

As we can see from the syntax, the most important parameter is the types parameter, which lets us defined at which level we want track the usage of database privileges. For instance, it lets us define whether we want track the utilization of all the database privileges or only the privileges which belongs to a particular set of roles or track the privilege utilization when a specific condition evaluates to TRUE.

Example (Create privilege analysis policy)

In the following set of examples, I am creating different privilege analysis policies to illustrate each of the policy type.

In the 1st example, I am creating a privilege analysis policy with name MON_PRIVS_DB, which is defined to capture/monitor all the database privileges.

---//
---// privilege analysis policy of type G_DATABASE (DB level) //----
---//
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
  3  (
  4  name => 'MON_PRIVS_DB',
  5  type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE
  6  );
  7  END;
  8  /

PL/SQL procedure successfully completed.

In the 2nd example, I am creating a privilege analysis policy with name MON_PRIVS_ROLES, which is defined to capture/monitor all the database privileges belonging to DBA and SELECT_CATALOG_ROLE roles.

---//
---// privilege analysis policy of type G_ROLE (role specific) //----
---//
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
  3  (
  4  name => 'MON_PRIVS_ROLES',
  5  type => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  6  roles => role_name_list('DBA','SELECT_CATALOG_ROLE')
  7  );
  8  END;
  9  /

PL/SQL procedure successfully completed.

In the 3rd example, I am creating a privilege analysis policy with name MON_PRIVS_CONTEXT, which is defined to capture/monitor any privileges used by the MYAPP and TEST_RO database user.

---//
---// privilege analysis policy of type G_CONTEXT (conditional) //----
---//
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
  3  (
  4  name => 'MON_PRIVS_CONTEXT',
  5  type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
  6  condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') IN (''MYAPP'',''TEST_RO'')'
  7  );
  8  END;
  9  /

PL/SQL procedure successfully completed.

In the 4th example, I am creating a privilege analysis policy with name MON_PRIVS_ROLES_CONTEXT, which is defined to capture/monitor privileges belonging to DBA role when they are used by the MYAPP user.

---//
---// privilege analysis policy of type G_ROLE (conditional and role specific) //----
---//
SQL> BEGIN
  2  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
  3  (
  4  name => 'MON_PRIVS_ROLES_CONTEXT',
  5  type => DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT,
  6  roles => role_name_list('DBA'),
  7  condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''MYAPP'''
  8  );
  9  END;
 10  /

PL/SQL procedure successfully completed.

We have created four different privilege analysis policies to demonstrate the different types analysis policies that can be defined by CREATE_CAPTURE procedure. We will see how to enable these policies in the upcoming section.

ENABLE_CAPTURE

Once we decide on the privileges to tracked and define a privilege analysis policy with CREATE_CAPTURE procedure, we need to enable the policy with the help of ENABLE_CAPTURE procedure provided by the DBMS_PRIVILEGE_CAPTURE package. The syntax for ENABLE_CAPTURE procedure is as follows.

----//
----// Syntax for ENABLE_CAPTURE procedure //---
----//
DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE 
(
   name      IN VARCHAR2
);
--- where
--- name	: Name of the privilege policy defined with CREATE_CAPTURE, which we want to enable to start tracking privilege utilization.

As we can see from the syntax, the ENABLE_CAPTURE procedure takes just one argument, which is the name of the privilege analysis policy that we want to enable to start monitoring privilege utilization. Once we enable the policy, Oracle will start gathering/collecting the privilege utilization data, until we disable the policy with DISABLE_CAPTURE procedure, which is described in next section.

Example (Enable privilege analysis policy)

A policy is not enabled by default. This can be confirmed by querying the DBA_PRIV_CAPTURES view as shown below.

---//
---// checking privilege analysis policy status //---
---//
SQL> select name,type,roles,context,enabled from dba_priv_captures order by type;

NAME                      TYPE             ROLES                CONTEXT                                                           E
------------------------- ---------------- -------------------- ----------------------------------------------------------------- -
MON_PRIVS_CONTEXT         CONTEXT                               SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('MYAPP','TEST_RO')     N
ORA$DEPENDENCY            DATABASE                                                                                                N
MON_PRIVS_DB              DATABASE                                                                                                N
MON_PRIVS_ROLES           ROLE             ROLE_ID_LIST(4, 9)                                                                     N
MON_PRIVS_ROLES_CONTEXT   ROLE_AND_CONTEXT ROLE_ID_LIST(4)      SYS_CONTEXT('USERENV', 'SESSION_USER') = 'MYAPP'                  N

Lets enable the privilege analysis policies that we have defined earlier.

---//
---// enable G_DATABASE (DB level) policy //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'MON_PRIVS_DB');

PL/SQL procedure successfully completed.

---//
---// enable G_CONTEXT (condition based) policy //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'MON_PRIVS_CONTEXT');

PL/SQL procedure successfully completed.

---//
---// enable G_ROLE (role based) policy //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'MON_PRIVS_ROLES');
BEGIN DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'MON_PRIVS_ROLES'); END;

*
ERROR at line 1:
ORA-47934: Two privilege captures are already enabled.
ORA-06512: at "SYS.DBMS_PRIVILEGE_CAPTURE", line 28
ORA-06512: at line 1

---//
---// enable G_ROLE_AND_CONTEXT (role and condition based) policy //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'MON_PRIVS_ROLES_CONTEXT');
BEGIN DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'MON_PRIVS_ROLES_CONTEXT'); END;

*
ERROR at line 1:
ORA-47934: Two privilege captures are already enabled.
ORA-06512: at "SYS.DBMS_PRIVILEGE_CAPTURE", line 28
ORA-06512: at line 1

As we can observe, we are not allowed to enable more than two privilege analysis policies simultaneously. Here is the description about the error that we have received

---//
---// error description for ORA-47934 //---
---//
SQL> !oerr ora 47934
47934, 00000, "Two privilege captures are already enabled."
// *Cause: The maximum number of two privilege captures were enabled."
// *Action: Disable one of the enabled privilege captures.

In general, only one policy can be enabled at a time with the exception where we are allowed to have at most two policies enabled if one of the policy is of G_DATABASE type and the other is a NON G_DATABASE type.

As we are not allowed to have more than 2 policies enabled simultaneously, lets continue our demonstration with the two enabled policies. We have enabled the policies MON_PRIVS_DB which if of G_DATABASE type (for all privileges) and MON_PRIVS_CONTEXT which is of G_CONTEXT type (defined for users MYAPP and TEST_RO).

---//
---// enabled privilege analysis policies //---
---//
SQL> select name,type,roles,context,enabled
  2  from dba_priv_captures where ENABLED='Y';

NAME                      TYPE             ROLES                CONTEXT                                                           E
------------------------- ---------------- -------------------- ----------------------------------------------------------------- -
MON_PRIVS_DB              DATABASE                                                                                                Y
MON_PRIVS_CONTEXT         CONTEXT                               SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('MYAPP','TEST_RO')     Y

It is recommended to let the policy run for a reasonable analysis period to be able to capture all the necessary privilege utilization data. As part of this demonstration, I have accessed few database privileges as different database users including MYAPP and TEST_RO users (for which I have enabled a CONTEXT type policy)

DISABLE_CAPTURE

Once, we are sure that we have let the privilege analysis policy run for a reasonable analysis period, we need to stop monitoring the privilege utilization. This is done with the help of DISABLE_CAPTURE procedure provided by the DBMS_PRIVILEGE_CAPTURE package. The syntax for DISABLE_CAPTURE procedure is as follows.

----//
----// Syntax for DISABLE_CAPTURE procedure //---
----//
DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE 
(
   name        IN VARCHAR2
);
--- where
--- name	: Name of the policy that we want to disable (enabled by ENABLE_CAPTURE procedure) to stop monitoring privilege utilization.

DISABLE_CAPTURE procedure also take just a single argument, which is the name of the privilege analysis policy that we want to disable (which was enabled earlier, with ENABLE_CAPTURE procedure)

Once we disable the privilege analysis policy, Oracle will stop collecting privilege utilization data. At this point, we can begin our analysis about the privilege utilization based on the collected data. To facilitate the analysis, DBMS_PRIVILEGE_CAPTURE package provides a procedure called GENERATE_RESULT; which can be used to view the reports on privilege utilization and is described in the next section.

Example (Disable privilege analysis policy)

I have utilized few of the database privileges for our demonstration. Lets stop the privilege monitoring using DISABLE_CAPTURE procedure as shown below

---//
---// disable G_DATABASE (DB level) policy  //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(name => 'MON_PRIVS_DB');

PL/SQL procedure successfully completed.

---//
---// disable G_CONTEXT (conditional) policy  //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(name => 'MON_PRIVS_CONTEXT');

PL/SQL procedure successfully completed.

GENERATE_RESULT

The GENERATE_RESULT procedure from DBMS_PRIVILEGE_CAPTURE package is used to generate the result based on the privilege utilization data collected by Oracle through privilege analysis policy enforcement. The syntax for GENERATE_RESULT procedure is as follows.

----//
----// Syntax for GENERATE_RESULT procedure //---
----//
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT 
(
   name      IN VARCHAR2
);
--- where
--- name	: Name of the privilege analysis policy against which we want to generate the utilization report.

The GENERATE_RESULT procedure also takes a single argument, which is the name of the privilege analysis policy (policy that was used to track the privilege utilization) for which we want the utilization result. We are not allowed to generate report against a enabled policy and are only allowed to generate report once we disable the policy marking the end of utilization monitoring.

When we use GENERATE_RESULT procedure it doesn’t display any utilization report on the screen, rather it populates few Oracle dictionary views with the result of privilege monitoring data, which can be queried for privilege analysis. Oracle 12c has added following dictionary views which can be queried to view the result of privilege analysis runs.

DBA_USED_PRIVS Lists all the used privileges reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_USED_OBJPRIVS Lists used object privileges (without grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_USED_OBJPRIVS_PATH Lists used object privileges (with grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_USED_PUBPRIVS Lists all the used privileges from PUBLIC role reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_USED_SYSPRIVS Lists used system privileges (without grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_USED_SYSPRIVS_PATH Lists used system privileges (with grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_USED_USERPRIVS Lists used user privileges (without grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_USED_USERPRIVS_PATH Lists used user privileges (with grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_UNUSED_PRIVS Lists all the unused privileges (with grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_UNUSED_OBJPRIVS Lists unused object privileges (without grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_UNUSED_OBJPRIVS_PATH Lists unused object privileges (with grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_UNUSED_SYSPRIVS Lists unused system privileges (without grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_UNUSED_SYSPRIVS_PATH Lists unused system privileges (with grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_UNUSED_USERPRIVS Lists unused user privileges (without grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.
DBA_UNUSED_USERPRIVS_PATH Lists unused user privileges (with grant path) reported by the DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT procedure.

Once we feel that, we no longer need the utilization data from a policy, we can decide to drop the policy with the help of DROP_CAPTURE procedure provided by the DBMS_PRIVILEGE_CAPTURE package.

Example (Generate privilege analysis result)

Let’s generate analysis result for the policy MON_PRIVS_DB, which have disabled in the earlier section.

 
---//
---// populate dictionary views with analysis result //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(name => 'MON_PRIVS_DB');

PL/SQL procedure successfully completed.

SQL> exec DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(name => 'MON_PRIVS_CONTEXT');

PL/SQL procedure successfully completed.

Now that, we have populated the dictionary views with the privilege analysis result; we can query the above mentioned views to determine which privileges were used and which were not during the analysis period. The simplest way would be to query DBA_USED_PRIVS view, which will list out all kind of privileges used along with the grant path (how the privilege was granted). Lets find out, which all privileges were used during our analysis period.

capture_privs_result

As we can see, we are able to track which all privileges were used and how they were granted to particular user. From this data, it is clear that the roles DBA and SELECT_CATALOG_ROLE are granted to generic database users rather than granting user specific privileges to them, which can be avoided to prevent potential security threats. Similarly, we can query DBA_UNUSED_PRIVS to find out which granted privileges were not used during the analysis period.

If we want to individually query usage information for a specific type of privilege, we can make use of DBA_USED_OBJPRIVS (object privileges) or DBA_USED_SYSPRIVS (system privileges) or DBA_USED_USERPRIVS (user privileges) to find out the related usage information.

Once, we thoroughly analyze the usage (used/unused privileges) information from a valid analysis period, we can easily determine which all excessive privileges/roles we can revoke from a particular user in order to avoid any potential security threat to the database.

DROP_CAPTURE

Once we are certain that, we do not need the captured utilization data any more, we may decide to drop a privilege analysis policy with the help of DROP_CAPTURE procedure provided by DBMS_PRIVILEGE_CAPTURE package. The syntax for DROP_CAPTURE procedure is as follows

DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE 
(
   name      IN VARCHAR2
);
--- where
--- name	: Name of the privilege analysis policy to be removed.

Example (Drop privilege analysis policy)

Once we are done with our analysis and we are certain that we no longer need a particular privilege analysis policy, we may drop the policy as show below

---//
---// drop G_DATABASE (DB level) analysis policy //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE(name => 'MON_PRIVS_DB');

PL/SQL procedure successfully completed.

---//
---// drop G_CONTEXT (conditional) analysis policy //---
---//
SQL> exec DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE(name => 'MON_PRIVS_CONTEXT');

PL/SQL procedure successfully completed.

Dropping the analysis policy also cleans up the data (analysis result) associated with that policy as shown below.

---//
---// drop policy cleaned up all associated results //---
---//
SQL> select * from DBA_USED_PRIVS where capture='MON_PRIVS_DB';

no rows selected

SQL> select * from DBA_USED_OBJPRIVS where capture='MON_PRIVS_DB';

no rows selected

SQL> select * from DBA_USED_SYSPRIVS where capture='MON_PRIVS_DB';

no rows selected

SQL> select * from DBA_USED_USERPRIVS where capture='MON_PRIVS_DB';

no rows selected

SQL> select * from DBA_USED_PUBPRIVS where capture='MON_PRIVS_DB';

no rows selected

Conclusion

Oracle has provided a very useful feature to track the utilization of granted privileges which enables us to determine and decide what all privileges would be sufficient enough for a database user to carry out their daily jobs. We need to ensure that we are capturing the usage details for a reasonable analysis period in order to avoid missing out on any required privilege which is used occasionally by a database user.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed