It is the process of recording user actions in the database.
Auditing is enabled by setting AUDIT_TRAIL to
None – no auditing enabled (default)
DB – audited information will be recorded in AUD$ table and can be viewed using DBA_AUDIT_TRAIL view
Note: AUDIT_TRAIL=DB is default in 11g i.e auditing is enabled by default in 11g
OS – audited information will be stored in the form of trace files at OS level. For this we need to set AUDIT_FILE_DEST parameter.
By default audit file destination will be $ORACLE_HOME/admin/SID/a dump
DB, Extended – it is same as DB option but will still record info like SQL_TEXT, BIND_VALUE etc.
XML – it will generated XML files to store auditing information.
XML, Extended – same as XML but will record much more information.
Even though we set AUDIT_TRAIL parameter to some value, oracle will not start auditing until one of the following types of auditing commands are issued.
Statement level auditing
Schema level auditing
Object level auditing
Database auditing (it is only till 9i. due to performance issues it was removed from 10g)
By default some activites like startup & shutdown of database, any structural changes to database are audited and recorded in alert log file.
If auditing is enabled with DB, then we need to monitor space in SYSTEM tablespace as there is a chance of getting full when more and more information is keep on recorded.
SYS user activities can also be captured by setting AUDIT_SYS_OPERATIONS to TRUE.
Auditing should use following scopes.
Whenever successful / not successful
By session / By access
Disadvantage of auditing is, it will not capture changed values. For that DBA will use triggers. This is replaced with Fine Grained Auditing (FGA) which will capture old and new values when a record is modified.
Note: when we use triggers, they will create separate tables to store the audited information which are called trigger tables. To access them we need to create indexes and maintain them which is a quite difficult task
FGA can be initiated using DBMS_FGA and by creating and setting audit policies. Information that is captured can be viewed using DBA_FGA_AUDIT_TRAIL view.
Enabling auditing at database level will have adverse impact on the database performance.
# To enable auditing
SQL> alter system set audit_trail=DB/OS/XML scope=spfile;
Note: AUDIT_TRAIL parameter is static and require a restart of database before going to be effective