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;
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.
