Thursday, February 28, 2013

0 Database audit

  • Mandatory auditing
  • DBA Auditing
  • Standard database audit
  • Fine-Grained Auditing ( FGA)
  • Value base auditing

Mandatory auditing

Mandatory audit or called auditing by default .

  • Database  startup/shutdown
  • Connect as SYSDBA

Audit trace is store at AUDIT_FILE_DEST. Below is the sample output of the audit file.

image

DBA auditing :AUDIT_SYS_OPERATIONS

When We change the AUDIT_SYS_OPERATIONS to true, the database would audit all the trace for the SYSDBA. The parameter require the database bounce to make it effective.

alter system set AUDIT_SYS_OPERATIONS=true scope=spfile;

image

After the database bounce, in the audit file, we can see all the SQL that has execute run by SYSDBA.

image

 

Standard audit

Audit_trail can be set as below levels.

none/false Disable the auditing  
db Write the audit log to database. aud$ table  
db,extended Write the audit log to database with SQL. aud$ table  
os write to OS file audit_file_dest
xml write to os file with XML format audit_file_dest
xml,extended write to os file with XML format + SQL audit_file_dest

 

image

 

  • Audit target can be privileges ( SELECT ANY TABLE ..etc) , Objects ( table, index ..etc) or SQL ( select , insert .. etc)
  • SELECT TABLE would record the select statement for the tables belong to the schema owner. SELECT ANY TABLE would not record the select statement for the table belong to the schema owner.
  • Change the audit setting only effective after user re connect. It does not apply to the current connect session.
  • The different between BY ACCESS and BY SESSION would show differently in the ACTION_NAME column within the DBA_AUDIT_TRAIL table. BY SESSION only shows SESSION REC for privileges.  It wont distinguish between INSERT, DELETE, SELECT …etc.
  • DBA_AUDIT_TRIAL is the view base on AUD$
AUDIT SELECT TABLE BY HR BY ACCESS;
AUDIT SELECT TABLE BY HR BY SESSION;
AUDIT SELECT ANY TABLE BY HR BY ACCESS;
AUDIT INSERT TABLE BY HR BY SESSION;
NOAUDIT SELECT TABLE BY HR;
NOAUDIT SELECT TABLE BY HR;
NOAUDIT  SELECT ANY TABLE BY HR;
NOAUDIT INSERT TABLE BY HR;

Query the trail table

select username,userhost,owner,obj_name,action_name,extended_timestamp from dba_audit_trail where owner= 'HR';

image

The audit trail 18 and 19, I insert 2 records to the TEST_TAB, but because the audit is set at session, therefore the ACTION_NAME column record “SESSION REC” hence we don’t know whether it is insert or select.

I change the audit level to BY ACCESS and reconnect as HR user to insert the record. The trail 20 shows the INSERT in the ACTION_NAME.

 

Fine-Grained Auditing (FGA)

FGA audit can provide more control on the audit condition.

  • DBA_AUDIT_POLICIES –> List the current FGA policy. A view base on FGA$
  • DBA_FGA_AUDIT_TRAIL –> store the audit information , A view base on FGA_LOG$

 

  • DBMS_FGA.ADD_POLICY  -> Create the new FGA policy
  • DBMS_FGA.DROP_POLIC  -> Drop the FGA policy
  • DBMS_FGA.DISABLE_POLICY –> Disable the given FGA policy
  • DBMS_FGA.ENABLE_POLICY –> Enable the given FGA policy

 

DBMS_FGA.ADD_POLICY

exec DBMS_FGA.ADD_POLICY (    object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST', audit_column=>'SALARY', audit_condition =>'SALARY> 10000',STATEMENT_TYPES=>'SELECT,UPDATE')

image

DBA_AUDIT_POLICIES

select object_schema,object_name,policy_name,policy_column,policy_text,enabled from dba_audit_policies;

image

DBA_FGA_AUDIT_TRAIL

select DB_USER,sql_text,extended_timestamp from  DBA_FGA_AUDIT_TRAIL

image

DBMS_FGA.DROP_POLIC

exec DBMS_FGA.DROP_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')

image

DBMS_FGA.DISABLE_POLICY

exec DBMS_FGA.DISABLE_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')

image

 

DBMS_FGA.ENABLE_POLICY

exec DBMS_FGA.ENABLE_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')

image

KNOWN ISSUE:

If I try to create the audit_condition for the string comparison, like audit_condition =>'JOB_ID = "SH_CLERK"'. The policy would create successfully , but I would get the ORA-28112: failed to execute policy function when execute the query.

image

image

Value bases auditing with System trigger

We can create the system trigger that audit the system event such as startup, shutdown, log on ..etc

Before demo the logon trigger and logoff trigger audit.

Create table to store the audit data

CREATE TABLE logon_audit
  (
    EVENT VARCHAR2(10),
    username   VARCHAR2(30),
    sid        NUMBER,
    SESSIONID     NUMBER,
    logon_time DATE,
    logoff_time DATE,
    host       VARCHAR2(20),
    program    VARCHAR2(100)
  );

image

Create logon trigger

CREATE OR REPLACE TRIGGER logon_audit_trig AFTER LOGON ON DATABASE
  DECLARE
    var_program VARCHAR2(100);
  BEGIN
    SELECT program
    INTO var_program
    FROM v$session
    WHERE sid = sys_context('userenv', 'SID');
    INSERT
    INTO logon_audit VALUES
      (
        'LOGIN',
        USER,
        sys_context('userenv', 'SID'),
        sys_context('userenv', 'sessionid'),
        sysdate,
        NULL,
        sys_context('userenv', 'host'),
        var_program
      );
  END;

 

image

Create logoff trigger

CREATE OR REPLACE TRIGGER logoff_audit_trig BEFORE LOGOFF ON DATABASE
  DECLARE
    var_program VARCHAR2(100);
  BEGIN
    SELECT program
    INTO var_program
    FROM v$session
    WHERE sid = sys_context('userenv', 'SID');
    INSERT
    INTO logon_audit VALUES
      (
        'LOGOFF',
        USER,
        sys_context('userenv', 'SID'),
        sys_context('userenv', 'sessionid'),
        NULL,
        sysdate,
        sys_context('userenv', 'host'),
        var_program
      );
  END;

image

Query the logon_audit table

select * from logon_audit

image

Clean the env

drop trigger logon_audit_trig;
drop trigger logoff_audit_trig;
drop table logon_audit;

image

Resource

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates