DBMS_FGA - Oracle Fine Grained Auditing
Suppose your boss calls you one day and tells you that there has been some unexpected changes in the employee database.Employee's designation, their salary are being manipulated illegally.Such things have been continuing from a past few days and he asks if you could help getting hold of the culprit.
Don't worry Oracle's DBMS_FGA package will save your day and earn you a raise in your job.
The Oracle DBMS_FGA package provides fine grained auditing on objects.
To have an overview of the summary of dbms_fga subprograms visit :
In this article I am going to add a policy on a table FGA_TEST in the SCOTT schema.
The policy will report on any dml actions on this table affecting its 2 columns 'esal' and 'designation'.
Another user HACKER will execute dml queries on this table and we will try and investigate whether the actions of the HACKER are reported.
The corresponding event handler of this policy will be in a 3rd schema FGA_HANDLER .we will also find out if the audit event was handled properly.
Note: Here it is worthwhile mentioning that the DBMS_FGA package can be used not only to audit records in case of data manipulation(DML) but also in cases where data might have been simply viewed depending upon the policy we define. eg:- in case of selecting particular records from a database table.
First of all let us create a new schema FGA_HANDLER which will contain the event handler .
Now, let us 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.
Let us insert some dummy rows in it now.
Given below are the parameters of the ADD_POLICY Procedure:
Now, let us add a policy on our FGA_TEST table such that whenever any user tries to insert, update or delete the ‘esal’ or the ‘designation’ columns of any row of FGA_TEST table ,the action will be recorded.
Now, sp_audit is the audit procedure, which will act as the alerting mechanism for the administrator.
The required interface for such a procedure is as follows:
the sp_audit procedure.
First let us create the table where the sp_audit procedure will dump the data into.
We create the sp_audit procedure as follows:
The procedure simply adds a record to the audit_event table each time it is executed and the column audit_event_no acts as counter which displays the number of times the proc has been executed.
Now, finally we create another schema ‘HACKER’ which tries to manipulate the values of the ‘esal’ or ‘designation’ columns of the FGA_TEST table.
Now, we connect with SCOTT to see the dba_fga_audit_trail view to find if the event was recorded.
|HACKER||HOME-6C286D743C\Dwaipayan||FGA_TEST_POLICY||update scott.fga_test set designation = 'manager' where empname='dwaipayan'||5-Jun-11|
Now we connect to the FGA_HANDLER schema to see if the event handler(sp_audit) was called:
We execute the following from HACKER schema:
Note: I have just created the sp_audit procedure to add rows to the audit_event table in this testing environment but in a ideal production scenario, we are likely to send emails or Page instead.
ABOUT AUTHOR :
LINKEDIN PROFILE : http://www.linkedin.com/pub/dwaipayan-de/21/20/29b
CONTACT : 9903063253