5. Oracle Database Audit

1. Audit (Audit):

The tracking records of the database activities performed by the user are easy to supervise and inspect afterwards.

Audit Information storage location:

Audit record information that is stored in the SYS in the system table space . aud$ data dictionary table;

or the operating system default location $ORACLE _base/admin/$ORACLE _sid/adump/;

Audit parameter audit_trail

Value List of parameter values:

(1). db/true ---- Audit is enabled and the audit results are stored in the database's SYS. in the aud$ table

(2). OS ----Audit is enabled and the audit results are stored in the audit information of the operating system

(3). db_extended ---- Audit is enabled and the audit results are stored in the database SYS. aud$ table, and Record additional information in the SQLBIND and SQLText fields of the CLOB column;

(4). XML ---- enable auditing, Write all audit records in XML format;

(5). EXTENDED ---- Enable auditing, record all columns in the audit trail, including sqltext and SQLBIND values

(6). None/false ---- Disabling auditing

The following steps set up Oracle 's auditing capabilities:

(1). Modify Audit Parameters audit_trail:

Alter Set Audit_trail=DB Scope=spfile;

(2). Initialize the database audit function:

Execute script cataudit.sql with connect /as sysdba , script location D:\app\ administrator\product\11.2.0\dbhome_1\rdbms\admin;

(3). To Restart the database:

Because the parameter Audit_trail is not dynamic, you need to turn off the database restart to take effect .

2. Audit classification and usage: Statement Audit:

-- Grammar

AUDIT sql_statement_clause [by user_name] | [By [SESSION | ACCESS]

Whenever [not] successful]

Parameter description:

Sql_statement_clause ----SQL statements or options;

by ACCESS ---- access method, each statement is executed once to audit, regardless of the statement is the same;

by SESSION ---- session mode, the same statement is only audited once, which is the default mode of the system;

whenever successful ---- means that only successful statements are audited;

whenever not successful ---- indicates that only unsuccessful statements are audited.

-- Example:

--(1). Audit user actions on objects:

Audit table by Scott;

--(2). Audit User-executed SQL statements:

Audit CREATE table by Scott;

Audit Insert Table by u0007; ---- Audit INSERT into table or view;

Audit Delete Table by u0007; ---- Audit deletes rows from a table or view;

--(3). Specify the Audit method:

Audit Delete Table by u0007 by access; ---- access mode, each statement is executed once to audit;

--(4). audit successes with unsuccessful sessions:

Audit Session by u007 whenever not successful; ---- Only the unsuccessful statements are audited;

--(5). Find out which users are audited and query the data dictionary view dba_stmt_audit_opts

Select *  from where user_name = ' U007 ';

--(6). Stop statement auditing:

Noaudit ALTER TABLE by U007;

Rights audits:

-- Syntax:

AUDIT Privilege [by user_name] | [By [SESSION | ACCESS]

Whenever [not] successful]

Object audits:

-- Syntax:

AUDIT schema_object_clause on schema

[by user_name] | [By [SESSION | ACCESS]

Whenever [not] successful]

Select,insert,delete on by Access;

3. Fine-grained audit (FGA)

is implemented by the dbms_fga PL/SQL program .

5. Oracle Database Audit