Manually created database doesn't have the auditing feature activated by default.
The value of the initial parameter AUDIT_TRAIL is defined to NONE.
For database created using DBCA
For database created automatically through the DBCA utility, the audit feature is activated using the parameter AUDIT_TRAIL set to DB.
All audit information and activity is traced into a vue DBA_AUDIT_TRAIL.
Mandatory auditing information
Even if the auditing feature is disabled, Oracle traces some specific actions that are mandatory to ensure the database security.
Oracle traces all audit information in a directory that is defined through the AUDIT_FILE_DEST initialization parameter.
The default auditing options for Oracle 11g are seen in the following table:
Default audited priviledge
Alter any procedure
Alter any table
Alter database
Alter profile
Alter system
Alter user
Audit system
Create any library
Create any procedure
Create any table
Create external job
Create public database link
Create session
Create user
Create any job
Drop any table
Drop profile
Drop user
Drop any procedure
Exempt access policy
Grant any object privilege
Grant any privilege
Audit system
Grant any role
Default audited statement
System audit by access
Role by access
Audit levels
There are 5 levels of audit :
- Mandatory auditing
- Standard database audit
- Data audit
- Fine grained audit
- SYSDBA & SYSOPER audit
Available values for AUDIT_TRAIL parameter
See here under all values available for the AUDIT_TRAIL parameter that defines how the audit feature will make traces :
- NONE : Audit is not activated
- OS : all audit information are redirected to a file where the location is defined through the AUDIT_FILE_DEST initialization parameter.
- DB : all audit information are write in the DBA_AUDIT_TRAIL table defined under the SYS schema.
- DB, EXTENDED : When extended is specified the SQL text, bind variables and handled columns are traced in the DBA_AUDIT_TRAIL table.
- XML : all audit information are writen in xml files where the location is defined through the AUDIT_FILE_DEST initialization parameter. You can check instead the V$XML_AUDIT_TRAIL view to see all the content of all xml files managed by the audit trail feature.
- XML, EXTENDED : When extended is specified the SQL text, bind variables and handled columns are added to the audit information.
Remark : The AUDIT_TRAIL parameter is a static database initialization parameter. The database need to be shutdowned and restarted after any modification of this parameter.
Audit trail views
See here under some important views :
DBA_AUDIT_TRAIL : view used when AUDIT_TRAIL set to DB or DB,EXTENDED
DBA_FGA_AUDIT_TRAIL : view used when fine grained audit activated
DBA_COMMON_AUDIT_TRAIL : Is the merge of the 2 previous views.
Audit options
There is 3 kinds of audit options:
- Audit of SQL instructions: Trace all DDL instructions that affect a table (CREATE TABLE, ALTER TABLE, DROP TABLE)
- Audit of system priviledges: Audit all system priviledge used by all users or by a specific user
- Audit of object priviledges: Audit all object priviledges used by all users or a specific user
Audit trace could be generated when used (BY ACCESS) or could be grouped and generated at the session level (BY SESSION).
How to see audit information
Check the following views :
DBA_AUDIT_TRAIL : view used when AUDIT_TRAIL set to DB or DB,EXTENDED
DBA_FGA_AUDIT_TRAIL : view used when fine grained audit activated
DBA_COMMON_AUDIT_TRAIL : Is the merge of the 2 previous views.
DBA_AUDIT_OBJECT : View used when instruction options used
DBA_AUDIT_POLICIES :
DBA_AUDIT_SESSION :
How to retrieve activated audit options
Check the following views :
DBA_OBJ_AUDIT_OPTS : display object priviledge audited
DBA_PRIV_AUDIT_OPTS : display system priviledge audited
DBA_STMT_AUDIT_OPTS : display statement audited
Data based audit
The standard audit level register the fact that something has been added in a table or something has changed but it does not take care of the value of the columns that has changed. The Data base audit enhance the standard audit by taking into account and register the values of a table that has been modified.
To handle table modification, oracle register triggers to audited tables and use the value of the AUDIT_TRAIL & AUDIT_FILE_DEST initialization parameter to how and where to trace audit information.
when a table <table> is audited, Oracle create a table SYSTEM.AUDIT_<table> to store all audit information in relation the audited table.
Fine grained audit
Fine grained audit brings more flexibility than the detailed audit.
It is possible to specify columns to audit and define also a condition for auditing.
If the result of a specific request matches the audit condition then a trace is generated in the audit trail whatever the number of lines that match the condition.
SELECT, UPDATE,DELETE & MERGE are the statement handled by the FGA audit.
A procedure can be executed if a statement matches the audit condition, audit columns and the type of statement to audit.
New FGA audit feature, can be enabled using the DBMS_FGA package through the use of the DBMS_FGA.ADD_POLICY procedure.
DBMS_FGA.ADD_POLICY( object_schema => '...', object_name => '...', policy_name => '...', audit_condition => 'employee_id > 10', audit_column => 'EMP_ID, SALARY', handler_schema => 'audit_schema', handler_module => 'log_audit_proc', enable => TRUE, statement_types => 'SELECT, INSERT, UPDATE, DELETE' );
FGA policy could be deleted using DBMS_FGA_DROP_POLICY
FGA policy could be enabled/disabled using DBMS_FGA.ENABLE_POLICY, DBMS_FGA.DISABLE_POLICY
SYSDBA & SYSOPER audit
The audit level is activated using the AUDIT_SYS_OPERATIONS parameter setted to TRUE.
The default value of the parameter is FALSE.
All audit traces a written in files located under the directory defined by the value of the AUDIT_FILE_DEST parameter.
Deleting audit information
For standard auditing, truncate the SYS.AUD$ table.
For fine grained auditing, truncate the SYS.FGA_LOG$ table
Some external References :
- good samples at http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
- Managment of audit trace @see : New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information [ID 731908.1] for oracle support
