DBMS_PRIVILEGE_CAPTURE:
————————-

Oracle 12c introduced the DBMS_PRIVILEGE_CAPTURE package, which helps us in doing privilege analyze and find report on used and unused privileges.

In order to use the DBMS_PRIVILEGE_CAPTURE package you must be granted the CAPTURE_ADMIN role.

steps involve:
—————-

CREATE_CAPTURE
ENABLE_CAPTURE
DISABLE_CAPTURE( after waiting for necessary time)
GENERATE_RESULT
DROP_CAPTURE

Though there are 4 options to create_capture . we will discuss on one or two option(mostly used)

G_DATABASE : Analyzes all privilege usage on the database, except the SYS user.
G_ROLE : Analyzes all privilege usage by the roles specified in the ROLES parameter

Example:
——-

Create an user and give some privileges:

Create capture process

Enable the created capture(test_capture)

Lets do some operations in the database.

Now you can disable the capture

Generate the capture result:

Now check whats privileges were used in the database and by which user:

The result shows that c##test users used select any table and create session privileges. This way we can estimate which privileges the user is using and after analysis and checking with the app team, we can revoke the unused privileges.

Find existing capture policies:

To drop the capture procedure:

In similar way we can use g_role,G_CONTEXT,G_ROLE_AND_CONTEXT .