AUDITING

Below are the  auditing views mostly used:

1)DBA_STMT_AUDIT_OPTS :: Describes current system auditing options across the system and by user
2)DBA_PRIV_AUDIT_OPTS :: Describes current system privileges being audited across the system and by user
3)DBA_OBJ_AUDIT_OPTS  :: Describes auditing options on all objects

Below are queries for checking the auditing information ::

STATEMENT AUDITING:  The DBA_STMT_AUDIT_OPTS shows grant for specific statements( e.g. alter table, grant table).

Query to check if any statement level auditing is enabled in the database:

col user_name format a10
col audit_option format a16
col timest format a13
col userid format a8 trunc
col obn format a10 trunc
col name format a13 trunc
col sessionid format 99999
col entryid format 999
col owner format a10
col object_name format a10
col object_type format a6
col priv_used format a15 trunc
set pause 'Return...'

select * from sys.dba_stmt_audit_opts;

OBJECT AUDITING : The DBA_OBJ_AUDIT_OPTS shows specific privileges for specific objects (e.g. grant table)

query to check if any auditing enabled at table level :

select owner, object_name, object_type, 

       alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,ref,exe 

from sys.dba_obj_audit_opts 

where  

   alt !='-/-' or aud !='-/-' or com !='-/-' 

or del !='-/-' or gra !='-/-' or ind !='-/-' 

or ins !='-/-' or loc !='-/-' or ren !='-/-' 

or sel !='-/-' or upd !='-/-' or ref !='-/-' or exe !='-/-' 

/  

OWNER              OBJECT_NAME          OBJECT ALT       AUD       COM       DEL       GRA       IND       INS       LOC       REN       SEL       UPD       REF EXE

------------------ -------------------- ------ --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- 

SCHEMA1            TABLENAME1           TABLE  S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-

SCHEMA2            TABLENAME2           TABLE  S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-

SCHEMA3            TABLENAME3           TABLE  S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-

SCHEMA4            TABLENAME4           TABLE  S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       S/S       -/- -/-

Note :: The AUDIT statement only specifies auditing options; it does not enable auditing as a whole. To turn auditing on and control whether Oracle generates audit records based on the audit options currently set, set the parameter AUDIT_TRAIL in the database's parameter file

How to enable auditing for a table :

ENABLING STATEMENT PRIVILEGE AUDITING 

To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION (the default and only value for this option), enter the following statement:

AUDIT SESSION;

You can set this option selectively for individual users also, as in the next example:

AUDIT SESSION BY scott, lori;

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:

AUDIT DELETE ANY TABLE;

To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, issue the following statement:

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,

   EXECUTE PROCEDURE

      BY ACCESS

      WHENEVER NOT SUCCESSFUL;

The AUDIT SYSTEM system privilege is required to set any statement or privilege audit option. Normally, the security administrator is the only user granted this system privilege.

ENABLING OBJECT AUDITING :::

To audit all successful and unsuccessful DELETE statements on the SCOTT.EMP table, BY SESSION (the default value), enter the following statement:

AUDIT DELETE ON scott.emp;

To audit all successful SELECT, INSERT, and DELETE statements on the DEPT table owned by user TIGER, BY ACCESS, enter the following statement:

AUDIT SELECT, INSERT, DELETE

     ON tiger.dept

     BY ACCESS

     WHENEVER SUCCESSFUL;

To set the default object auditing options to audit all unsuccessful SELECT statements, BY SESSION (the default), enter the following statement:

AUDIT SELECT

     ON DEFAULT

     WHENEVER NOT SUCCESSFUL;

A user can set any object audit option for the objects contained in the user's schema. The AUDIT ANY system privilege is required to set an object audit option for an object contained in another user's schema or to set the default object auditing options; normally, the security administrator is the only user granted this system privilege

DISABLING STATEMENT AND PRIVILEGE AUDITING :::

The following statements turn off the corresponding audit options:

NOAUDIT session;

NOAUDIT session BY scott, lori;

NOAUDIT DELETE ANY TABLE;

NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,

    EXECUTE PROCEDURE;

The following statements turn off all statement (system) and privilege audit options:

NOAUDIT ALL;

NOAUDIT ALL PRIVILEGES;

To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege.

DISABLING OBJECT AUDITING ::: 

The following statements turn off the corresponding auditing options:

NOAUDIT DELETE

   ON emp;

NOAUDIT SELECT, INSERT, DELETE

   ON jward.dept;

Furthermore, to turn off all object audit options on the EMP table, enter the following statement:

NOAUDIT ALL

   ON emp;

DISABLING DEFAULT OBJECT AUDIT OPTIONS :::

To turn off all default object audit options, enter the following statement:

NOAUDIT ALL

   ON DEFAULT;

Note that all schema objects created before this NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit NOAUDIT statement after their creation.