Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 12 | 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; |
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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 6 | 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:
1 | select * from dba_audit_policies; |
And to check the audit records, we can query the following views:
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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!!
Creating a boolean function is not a MUST.