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

Advertisements

Oracle ACE Director and President of LAOUC, NZOUG and CLOUG. Organizer of LA and APAC OTN Tours,

Posted in Oracle FAQ, Questions, Security

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: