Proof in the Privileges

Asking for full permissions on the database, tables or schemas may have been an easy way to do upgrades or run other database applications, but it provide a secure environment and it definitely doesn't get to the level of least privilege. Least privilege is a way to minimize access to sensitive data and granting only the permissions needed is a key step for database security.
Knowing what permissions have been granted is easy to find out by querying the catalog tables and auditing changes in grants. With Privilege Analysis we can also verify what permissions are actually being used. This analysis can provide roles that only have the permissions used or scripts to revoke the used permissions. Nice to have proof that only an SELECT or EXECUTE on a few objects is required instead of DBA role.
So how does it work? The DBMS_PRIVILEGE_CAPTURE is used to gather the information around what permissions are used and generate the results. The tables DBA_USED_PRIVS and DBA_UNUSED_PRIVS are some of the tables that contain the information from the capture.

BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE (NAME => 'dba_capture_all_privs', DESCRIPTION => 'privilege_analysis_example_for_all_users', TYPE => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;

BEGIN
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (NAME => 'dba_capture_all_privs');
END;

There are options that will capture different privileges for the database, roles and context: G_DATABASE, G_ROLE, G_CONTEXT and G_ROLE_AND_CONTEXT.
Creating scripts to create a role or revoke privileges can be done with a query against the DBA_USED_OBJPRIVS or DBA_UNUSED_OBJPRIVS tables.

It is great to have proof of what privileges are being used and being able to do the analysis to help secure the environment.

There are several layers of security that are important for the environment and to get more information about Oracle Database security a few of us have written an ebook, Securing Oracle Database 12c: A Technical Primer, with references and with 12c information. It is available free for a limited time by registering at  www.mhprofessional.com/dbsec and use code: db12c. There are also a few more examples on Privilege Analysis. Be sure to check it out as there some excellent examples on auditing, encryption, handling privileged users and many others.