How to enable database Auditing?
Database is generally meant for multiple users to access the information. However there is always a threat of the misuse of data from the users.To prevent this and monitor appropriate use of data it is possible to audit database usage.
In order to enable auditing the parameter audit_trail must be set in init.ora file or spfile.The possible values for audit_trail parameter is db (true) ,operating system (false) or none.With oracle 10g the parameter can value 1 more value called db_extended.
When the value is set to ‘db (true)’ the auditing information is stored in aud$ table stored in system tablespace of SYS schema.
When the value is set to ‘db_extended’ the field sql_text,sql_bind of clob datatype is populated in aud$ table.
When the value is set to ‘operating system (false)’ the auditing information is stored external to database.
When the value is set ‘none’ the auditing is disabled.
It is possible to audit system privilege or DML on tables either by username, by session or by access.
for e.g
AUDIT create table,alter table,drop table
By scott
whenever sucessful;
here the above example shows the auditing of system privilege of creating table,altering existing table, or droping the table by user scott.
Also the statement ‘whenever sucessful’ makes sure that aud$ is populated only when the statement is sucessfully executed.
Audit update,delete,insert on scott.emp by access whenever not sucessful.
To turn off the auditing the keyword noaudit is substituted in place of audit.
To turn off all the auditing the statement is.
NOAUDIT ALL;
NOAUDIT ALL ON SCOTT.EMP;
Also it is possible the activities on aud$ table itself
AUDIT DELETE on aud$;
The Data dictionary views related auditing are
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL