Auditing the Use of Privileges

In the earlier lesson on auditing SQL statements, you learned about some of the ways to audit the use of system privileges. When you choose to audit TABLE statements, Oracle will create an audit trail for the CREATE TABLE, DROP TABLE, and TRUNCATE TABLE statements. You can track when these statements are executed, which in turn will indicate if the user had the appropriate system privileges to execute the statements. You can also audit specific system privileges by indicating a specific system privilege as the option with the AUDIT statement, as with the statement

AUDIT DELETE ANY TABLE

If a user issues a SQL statement that invokes the DELETE ANY TABLE system privilege with this type of auditing turned on, Oracle will write a record to the audit log.
Keep in mind that this record will be written only if the specific privilege of DELETE ANY TABLE is invoked by the user. If a user has DELETE privileges on a specific table in a schema, those privileges will be used, and the DELETE ANY TABLE system privilege will not be invoked.

You can use a few keyword shortcuts to audit system privileges. These keywords can be used with the AUDIT command to turn on auditing for a number of privileges. The shortcuts are as follows:

1. CONNECT audits the CREATE SESSION privilege.
2. RESOURCE audits the ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE PROCEDURE, CREATE ROLLBACK SEGMENT, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TABLESPACE, and CREATE VIEW privileges.
3. DBA audits the AUDIT SYSTEM, CREATE PUBLIC DATABASE LINK, CREATE PUBLIC SYNONYM, CREATE ROLE, and CREATE USER privileges.
4. ALL PRIVILEGES audits the use of all system privileges.

In the next lesson, you will learn how to audit access to specific database objects.