How to setup a Basic Audit?

For any Oracle versions prior to 9i you will need to set audit_trail parameter in the pfile ie.g.:

audit_trail = DB

For later Oracle Versions:

SQL>alter system set audit_trail = DB;

To enable auditing for a specific object run the following command:

SQL>AUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;

To stop auditing use:

SQL>NOAUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;

Or to audit any statement that queries, delete, insert or updates any table, issue the following statement:

AUDIT SELECT TABLE, DELETE TABLE, INSERT TABLE, UPDATE TABLE;

If you want to audit a specific user will need to run the following commands:
AUDIT ALTER TABLE, SELECT TABLE, INSERT TABLE, ... ,GRANT PROCEDURE by <USERNAME>;
To stop the audit for the user you will need to run:
NOAUDIT ALTER TABLE, SELECT TABLE, INSERT TABLE, ... ,GRANT PROCEDURE by <USERNAME>;

To see the results of the auditing use please run:

SQL> select * from dba_audit_trail;

This is an excellent Oracle documentation to learn how to implement it:


Also I’ll recommend you to take a look on the following links:


Cheers,
Francisco Munoz Alvarez

Leave a Reply