Steps to implement Oracle Fine Grained Auditing (FGA)

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.

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:

Then, let’s proceed to enable the policy:

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

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

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

Enjoy!!