- Audit is done to monitor Database activity (i.e. gathering a database activities for planning and tuning purposes.).
- Tracing or tracking activity of a user SQL.
- To collect statistics.
- Parameter related to auditing
AUDIT_TRIAL = TRUE - DBA can set this parameter for Auditing
SET AUDIT_TRAIL = TRUE. - Practically we can set auditing for each and every SQL statement
- After setting ADUIT_TRIAL = TRUE oracle data base will be slowed down
- Whatever audit is started its entry comes into DBA_AUDIT_TRIAL.
Audit record always contain the following information
- Username .
- Session identifier.
- Terminal identifier.
- Name of the schema object accessed.
- Operation performed or attempted.
- Completion code of the operation.
- Date and timestamp.
- System privileges used.
TYPES OF AUDITING
[1] Statement: Audits the SQL statements used.
- AUDIT SELECT BY SCOTT;
[2] Privilege: Audits the Privilege used.
- AUDIT CREATE TRIGGER.
[3] Object: Audits the use of a specific object.
- AUDIT SELECT ON scott.emp;
CLASS
[1] BY user:
- Audit particular users work.
[2] BY SESSION:
- Every thing on that session is audited.
- One Audit record per session.
AUDIT SESSION BY SCOTT WHENEVER SUCCESSFUL;
[3] BY ACCESS:
- Audit record is generated for each statement.
[4] WHENEVER SUCCESSFUL
- AUDIT CREATE TABLE WHENEVER SUCCESSFUL;
- AUDIT ALL BY SCOTT WHENEVER SUCCESSFUL;
Closing the AUDIT
[5] WHENEVER NOT SUCCESSFUL
- AUDIT CREATE TABLE WHENEVER NOT SUCCESSFUL;
What are the performance and complexity issues?
Audit is generally perceived to be complex and slow. The reason for this is usually ignorance. If many or all options are turned on, then the resultant audit trail produced can be large and difficult to interpret and manage. Furthermore, if audit is used on all tables and views in the database, then this can have an effect on performance. Every time an action performed is auditable a record is written to the database; clearly the more audit is used, the more records will be written to the system tablespace purely for audit. In some cases double the amount of access to the database can be performed: the original write and the audit record being written.
The watchword here is simplicity and caution. Use only the audit that is needed to give an overall view of what is happening and for detailed monitoring of critical data and objects. The simpler the audit trail set-up, the more likely it is that the data will be analyzed and be of some use. It is important to define what actions or abuses are being checked for so that simple reports can be written to filter the audit trail for these actions. A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyse any audit trail produced. These reasons, and the fact that there are many options available are, in my opinion, why audit is perceived to be complex.
The standard audit commands do not allow audit to be performed at row level. It is also not possible to audit the actions of privileged users such as SYS and "as sysdba" until Oracle 9iR2.
