Auditing Database Activities

Contents:- Different Ways to Audit Database Activities

Auditing

Notes:- if you created database using dbca then by default the audit_Trail parameter is set to DB. when AUDIT_TRAIL is set to DB, the default behaviour is to record the audit in the database AUD$ table.

Check Audit Policy of your organization

Sql> select * from dba_audit_policies;

Sql> select audit_option,failure,success,user_name from dba_stmt_audit_opts;

No Auditing

Sql > Alter system set audit_Trail=none scope=spfile;

Different Types of Auditing the Database Activities

Manadatory Auditing –

Such as auditing of system startup and shutdown.

Mandatory audit record stored in the directory of AUDIT_FILE_DEST parameter points.

Standard database auditing

(i) Audit Location = OS

          sql> Alter system set audit_trail=os scope=spfile;

          sql> Startup force

          Show Audit into event log

          cmd>eventvwr

(ii) Audit location = DB or Audit location=DB,EXTENDED (includes sql statement also)

          sql> Alter system set audit_Trail=db scope=spfile;

          or

          sql> Alter system set audit_trail=db,extended scope=spfile;

          sql> Audit select on hr.employees;

          sql> Select * from aud$;

          or

          sql> Select * from dba_audit_trail;

          or

          SQL> Select * from dba_common_audit_trail;

(iii) Audit location = xml or Audit location=xml,EXTENDED (includes sql statement also)

It stores xml audit into the location of where AUDIT_FILE_DEST parameter suggests.

          sql> Alter system set audit_Trail=xml scope=spfile;

          or

          sql> Alter system set audit_trail=xml,extended scope=spfile;

          sql> Audit select on hr.employees;

          sql> Select * from V$XML_audit_trail;

          or

          sql> Select * from dba_common_audit_trail;

3) Value-Based auditing

Auditing not only the event but also capture the actual values that are inserted,updated or deleted.

sql> CREATE OR REPLACE TRIGGER system.hrsalary_audit

AFTER UPDATE OF salary

ON hr.employees

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

IF :old.salary != :new.salary THEN

INSERT INTO system.audit_employees

VALUES (sys_context('userenv','os_user'), sysdate,sys_context('userenv','ip_address'),:new.employee_id ||

' salary changed from '||:old.salary||' to '||:new.salary);

END IF;

END;

/

4) Fine-grained auditing

Fine-grained auditing is mainly used to audit at column-level.

DBMS_FGA.ALL_COLUMNS and DBMS_FGA.ANY_COLUMNS are provided to audit on the basis of wheather any or all of the relevant columns are used in the statement.

FGA Automatically focuses auditing at the statement level, and so a SELECT statement that returns thousands of rows generates only one audit record.

Prac:

A) Add Policy

          Sql>begin

                   DBMS_FGA.ADD_POLICY(object_schema=>’HR’

                   ,object_name=>’EMPLOYEES’

                   ,policy_name=>’COMPENSATION_AUD’

                   ,audit_condition   => 'department_id=10',

                   ,audit_column=>’SALARY, COMMISSION_PCT’

                   ,handler_schema    => 'secure'

                   ,handler_module    => 'log_emps_salary'

                   ,enable=>FALSE

                   ,statement_types=>’SELECT,UPDATE’);

          end;

B) To Enable

          Sql>begin

                   DBMS_FGA.ENABLE_POLICY(object_schema=>'HR'

                   ,object_name=>'EMPLOYEES'

                   ,policy_name=>'COMPENSATION_AUD');

          End;

C) To Disable

          SQL>begin

                   DBMS_FGA.DISABLE_POLICY(object_schema=>'HR'

                   ,object_name=>'EMPLOYEES'

                   ,policy_name=>'COMPENSATION_AUD');

          end;

D) To Drop Policy

          Sql>begin

                   DBMS_FGA.DROP_POLICY(object_schema=>’HR’

                   ,object_name=>’EMPLOYEES’

                   ,policy_name=>’COMPENSATION_AUD’);

          end;

Show Auditing Information

          SQL> SELECT * from dba_fga_audit_Trail;

                   or

          SQL> Select * from dba_common_audit_trail;

                   or

          sql> Select * from fga_log$;

5) DBA Auditing

SQL> Alter system set audit_trail=os scope=spfile;

SQL > Alter system set audit_sys_operations=true scope=spfile;

DBA audit store:

Control panel->administrative tool ->event viewer -> windows logs->application

Linux: oracle_base/orcl/adump.....file auto created...