Oracle Database Audit Overview

Oracle provides three technologies for SYSDBA Auditing:

1. Database Audit user privileges, commands executed and tables accessed, and logon status

2. Use a database trigger to initiate a value-based audit;

3. fine-grained auditing can track which rows in the table are accessed;

When the initialization parameter file AUDIT_SYS_OPERATIONS is set to true, the statements executed by SYSDBA and SYSOPER are recorded in the audit file of the operating system;

Standard audit

Before database audit, the AUDIT_TRAIL initialization parameter file must be set:

• NONE: audit is not performed.
• OS: the audit file is written to the Operating System -- the Application Log on windws, or the AUDIT_FILE_DEST directory on Uinx
• DB: The audit file is written to the database's data dictionary table: SYS. AUD $
• DB_EXTENDED
• XML
• XML_EXXTENDED

Example:

Audit create any trigger; -- audit all trigger creation operations

Auditselect any table by session;

Audit insert on scott. emp whenever successful; -- another option is whenever not successful;

Audit allon scott. emp;

Audit session whenever not successful; -- audit User Logon;

-- View audit information generated by the system

Select * fromdba_audit_trail;

Other audit information views also include:

DBA_AUDIT_OBJECT, DBA_AUDIT_STATEMENT, DBA_AUDIT_SESSION

 

Use triggers to audit values

A database trigger is a block of PL/SQL code that wil runautomaitcally whenever in INSERT, UPDATE, OR DELETE is executed against a table.

Example:

Create orreplace trigger system. creditrating_audit

Afterupdat of creditrating

ON scott. customers

REFERENCINGNEW AS NEW OLD AS OLD

FOR EACHROW

BEGIN

IF: old. creditrationg! =: New. creditrating THEN

Insert into system. creditrating_audit

VALUES (sys_context ('userenv', 'OS _ user '),

Sys_context ('userenv', 'IP _ address '),

: New. customer_id | 'credit rating changed from '|: old. creditrating | 'to' |: new. creditrating );

End if;

END;

/

 

Fine-Grained audit Fine-Grained Auditing (FGA)

FGA isconfigured with the package DBMS_FGA

SQL> execute dbms_fga.add_policy (-

Object_schema => 'hr ',-

Object_name => 'ployees ',-

Policy_name => 'pol1 ',-

Audit_condition => 'department _ id = 80 ',-

Audit_column => 'salary ');

 

DBA_AUDIT_TRIALis used for standard database auditing;

DBA_FGA_AUDIT_TRAIL: is used for fine-grained auditing;

DBA_COMMON_AUDIT_TRAIL: is used for both;

To seethe results of auditing with triggers, you must create your own views thataddress your own tables;