Wednesday, December 7, 2011

Oracle Database Auditing using DBMS_FGA



1. connect to the schema that needs an auditing on a particular table


SQL> conn reporting/password


2. identify the table or create a new one as below and add some data into it


SQL> create table FGA_TEST
     (empno number,ename varchar2(22),sal number);


SQL> insert into FGA_TEST values(1, 'Bilal', 1000);
SQL> insert into FGA_TEST values(2, 'Ashraf', 1000);
SQL> insert into FGA_TEST values(1, 'Kanetix', 2000);
SQL> commit;


3. connect to the sys user and add a policy by identifying when user performs any DML on table created above in step 2 . In our case the schema is 'REPORTING',  table is 'FGA_TEST'  and the column is 'SAL


SQL> conn / as sysdba


SQL> BEGIN 


 DBMS_FGA.ADD_POLICY ( object_schema => 'REPORTING', object_name => 'FGA_TEST', policy_name => 'FGA_POLICY', audit_condition => NULL, audit_column => 'SAL', handler_schema => 'AUDIT_ACTIVITY', handler_module => 'sp_audit', enable => true,statement_types => 'INSERT,UPDATE,DELETE' );
          END;


Note: the default 'statement_types' is 'SELECT'


4. Create a separate user that will increment the number of modifications every time there is any activity on the target table


SQL> create user audit_activity identified by audit_activity;
SQL> grant resource,connect to audit_activity;
SQL> conn audit_activity/audit_activity;
SQL> SQL> create table audit_event
     (audit_event number);


5. Connect to sys schema and create a sp_audit procedure which work act as an alerting mechanism for the DBA


SQL> conn / as sysdba


SQL> create or replace procedure sp_audit(object_schema in varchar2,object_name in varchar2,policy_name in varchar2)
     as
     count number;
     begin
           select nvl(max(audit_event),0) into count from audit_activity.audit_event;
           insert into audit_activity.audit_event values (count+1); commit;
     end;


6 connect to the target schema and do any modifications on the SAL column as below


SQL> conn reporting/password


SQL>  update fga_test
     set sal = 500
    where empno = 1;


SQL> commit;


7 connect to audit_activity schema to see the result of update statement in step 6
SQL> conn audit_activity/audit_activity


AUDIT_EVENT
-----------
          1
         
8 connect as SYS and see the audit result of the update (DML) activity
SQL> conn sys / as sysdba


SQL> select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT, TIMESTAMP from dba_fga_audit_trail where POLICY_NAME='FGA_POLICY';


DB_USER       OS_USER    POLICY_NAME     SQL_TEXT                                                          TIMESTAMP
------------------------------------------------------------------------------------------------------------------------------------------------------------------
REPORTING    oracle        FGA_POLICY         "update fga_test  set sal = 500 where empno = 1" 11-12-07

No comments:

Post a Comment