How to enable auditing in oracle database?

Following are steps to enable auditing in oracle database
1) In the initialization parameter file add an entry
audit_trail=db
2) Shutdown the database.
3) startup the database using the init file.
4) After logging into the database at the SQL prompt run the following script
SQL>@/rdbms/admin/cataudit.sql
With the help of this script various views are created which will help you to monitor the auditing information:-
a) STMT_AUDIT_OPTION_MAP:-Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
b) AUDIT_ACTIONS:- Contains descriptions for audit trail action type codes
c) ALL_DEF_AUDIT_OPTS:- Contains default object-auditing options that will be applied when objects are created
d) DBA_STMT_AUDIT_OPTS:-Describes current system auditing options across the system and by user
e) DBA_PRIV_AUDIT_OPTS:-Describes current system privileges being audited across the system and by user
f) USER_OBJ_AUDIT_OPTS & DBA_AUDIT_TRAIL describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
g) USER_AUDIT_TRAIL & USER_AUDIT_TRAIL:- Lists all audit trail entries. USER view shows audit trail entries relating to current user.
h) DBA_AUDIT_OBJECT & USER_AUDIT_OBJECT:-Contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects that are accessible to the current user.
i) DBA_AUDIT_SESSION & USER_AUDIT_SESSION:-Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
j) DBA_AUDIT_STATEMENT & USER_AUDIT_STATEMENT :- Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user
k) DBA_AUDIT_EXISTS:-Lists audit trail entries produced BY AUDIT NOT EXISTS
5) After completion of the script SQL prompt will return.
6)Run the following commands to enable the various auditing
a)SQL>audit create session;
b)SQL>audit create user;
c)SQL>audit drop user;
d)SQL>audit create user;
e)SQL>audit drop tablespace;
f)SQL>audit grant any role;
g)SQL>audit grant any privelege;
h)SQL>audit alter system;
i)SQL>audit alter session;
j)SQL>audit delete on AUD$ by access;
k)SQL>audit insert on AUD$ by access;
l)SQL>audit update on AUD$ by access;
CAUTION:-
As the table AUD$ which contains all the auditing data is created in the system tablespace,
so as the auditing information grows the size of the system tablespace also increases,
so it is advisable to move this particular table AUD$ to some another tablespace.
THE COMMAND TO MOVE THE TABLE IS:-
SQL>create table AUDX tablespace as select * from AUD$;
SQL>rename AUD$ to AUD$$;
SQL>rename AUDX to AUD$;
TO check whether AUD$ table has shifted to the new tablespace write the following query in the SQL prompt
SQL>select table_name,tablespace_name from dba_tables where table_name=’AUD$’;

 


_________________________________________________________________________________

If you want to audit a specific user run the following:

audit alter table, select table, insert table, update table, delete table, grant table, grant
procedure by USERNAME;

To stop audit for that user run:

noaudit alter table, select table, insert table, update table, delete table, grant table,
grant procedure by USERNAME;

To enable auditing for a specific object do:

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

To stop auditing:

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


To see the results:

SQL> select * from dba_audit_trail;