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


 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' );

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)
     count number;
           select nvl(max(audit_event),0) into count from audit_activity.audit_event;
           insert into audit_activity.audit_event values (count+1); commit;

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

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


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