Steps to implement Oracle Fine Grained Auditing (FGA)

Arcel Perdomo

6 years ago

Guarantee security on Data is a key element and auditing is one of them. FGA provides a flexible way to achive this goal. With this we can audit DML operation plus SELECT statements on a particular table.

And it allows to combine some factors to make efective an audit record. For instance, you might audit only the operations SELECT on table EMP executed by user BOB using the software TOAD as we can see next.

First, in order to get all these factores or conditions are met, we must create a boolean function as follow.

Note:

This procedure must be executed by an user with enough privileges to make the following operations, in my case I used SYS user.

create or replace function auditif
return number
as
begin
if
sys_context ('userenv','session_user')='BOB'
and
sys_context('userenv','module') like 'TOAD%'
then return 1;
else return 0;
end if;
end;

create or replace function auditif

return number

begin

sys_context ('userenv','session_user')='BOB'

and

sys_context('userenv','module') like 'TOAD%'

then return 1;

else return 0;

end if;

end;

Then, we can create a policy using the function created previously. In this case, an audit record will be created only if user BOB executes the statements SELECT, UPDATE, DELETE and INSERT on table EMP owned bu user RRHH from Toad:

BEGIN
DBMS_FGA.ADD_POLICY(
object_schema=>'RRHH',
object_name=>'EMP',
policy_name=>'POLICY_FGA',
statement_types=>'select, update, delete, insert',
audit_condition=>'AUDITIF=1');
END;

BEGIN

DBMS_FGA.ADD_POLICY(

object_schema=>'RRHH',

object_name=>'EMP',

policy_name=>'POLICY_FGA',

statement_types=>'select, update, delete, insert',

audit_condition=>'AUDITIF=1');

END;

Then, let’s proceed to enable the policy:

BEGIN
DBMS_FGA.enable_policy(
object_schema => 'RRHH',
object_name => 'EMP',
policy_name => 'POLICY_FGA');
END;

BEGIN

DBMS_FGA.enable_policy(

object_schema => 'RRHH',

object_name => 'EMP',

policy_name => 'POLICY_FGA');

END;

Next, we can check the policy just created as follow:

select * from dba_audit_policies;

1	select * from dba_audit_policies;

And to check the audit records, we can query the following views:

dba_fga_audit_trail
dba_common_audit_trail

1 2	dba_fga_audit_trail dba_common_audit_trail

If we want to unable and remove the policy, we can do it executing the following:

BEGIN
DBMS_FGA.disable_policy(
object_schema => 'RRHH',
object_name => 'EMP',
policy_name => 'POLICY_FGA');
END;

BEGIN
DBMS_FGA.drop_policy(
object_schema => 'RRHH',
object_name => 'EMP',
policy_name => 'POLICY_FGA');
END;

BEGIN

DBMS_FGA.disable_policy(

object_schema => 'RRHH',

object_name => 'EMP',

policy_name => 'POLICY_FGA');

END;

BEGIN

DBMS_FGA.drop_policy(

object_schema => 'RRHH',

object_name => 'EMP',

policy_name => 'POLICY_FGA');

END;

Enjoy!!