Oracle Fine Grained Auditing At Schema Level
1). Add a policy on a table FGA_TEST in the SCOTT schema
2). The policy will report on any dml actions on this table affecting its 2 columns 'esal' and 'designation'
3). Another user HACKER will execute dml queries on this table and we will try and investigate whether the actions are reported
4). The corresponding event handler of this policy will be in the FGA_HANDLER schema.we will also find out if the audit event was handled properly
--conn sys / as sysdba
grant select any table to scott;
grant create user to scott;
grant resource,connect to scott;
--create a new schema FGA_HANDLER which will contain the event handler
SQL> create user fga_handler1 identified by fga_handler1;
conn sys
grant resource,connect to fga_handler1;
grant execute on DBMS_FGA to fga_handler1;
--create a new table ,FGA_TEST in SCOTT schema, on which we will enforce the audit conditions(policy) with the help of the DBMS_FGA package
SQL> create table fga_test (empno number,empname varchar2(30),age number,designation varchar2(20));
--Let us insert some
prototype table rows
insert into FGA_TEST values(10000,'Carol',100,'Developer');
insert into FGA_TEST values(10001,'Esther',200,'Analyst' );
insert into FGA_TEST values(10002,'Bob',300,'Manager') ;
--ADD_POLICY Procedure
BEGIN DBMS_FGA.ADD_POLICY ( object_schema => 'SCOTT', object_name => 'FGA_TEST', policy_name => 'FGA_TEST_POLICY1', audit_condition => NULL, audit_column => 'AGE,DESIGNATION', handler_schema => 'FGA_HANDLER1', handler_module => 'sp_audit', enable => true,statement_types => 'INSERT,UPDATE,DELETE' );end;
/
--connect to the fga_handler1 schema
SQL> conn fga_handler1/fga_handler1
--create the table to store audit records
SQL> create table audit_event (audit_event_no number);
--The procedure adds a record to the table above any time it executeds and the column audit_event_no acts as counter displaying the number of times the procedure has been executed
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_no),0) into count from audit_event;insert into audit_event values (count+1); commit; end;
/
--Finally create another schema ‘HACKER’ which tries to manipulate the values of the ‘age’ or ‘designation’ columns of the FGA_TEST table
SQL> conn sys / as sysdba
SQL> create user hacker1 identified by hacker1;
grant resource,connect to hacker1;
grant all on scott.fga_test to hacker1;
--Connect as hacker and update the policed columns(s)
SQL> conn hacker/hacker;
SQL> update scott.fga_test set designation='CIO' where empname='carol';
--connect with SCOTT to see the dba_fga_audit_trail view to find if the event was recorded
SQL> conn scott /scott
SQL> col DB_USER for a12
SQL> col OS_USER for a14
SQL> col POLICY_NAME for a16
SQL> col SQL_TEXT for a70
SQL> select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT, TIMESTAMP from dba_fga_audit_trail where POLICY_NAME='FGA_TEST_POLICY1';
--Connect to the FGA_HANDLER schema to see if the event handler(sp_audit) was called
SQL> conn hacker/hacker;
--Now, execute the following from HACKER schema
SQL> select * from scott.fga_test;
--Attack to change designation
update SCOTT.FGA_TEST set designation='HR' where name='Bob';
--conn as sysdba to see who did what and when
conn sys / as sysdba
SQL> col DB_USER for a12
SQL> col OS_USER for a14
SQL> col POLICY_NAME for a16
SQL> col SQL_TEXT for a70
SQL> select DB_USER,OS_USER,POLICY_NAME,SQL_TEXT, TIMESTAMP from dba_fga_audit_trail where POLICY_NAME='FGA_TEST_POLICY';
--Have Fun,
