Specifying a Handler Module
So far, you have seen how FGA can log the fact that someone SELECTed (in Oracle9i Database) from a table or performed some type of DML (in Oracle Database 10g) into an FGA audit trail table, FGA_LOG$. FGA also performs another important function: it can optionally execute a PL/SQL stored program unit such as a stored PL/SQL procedure or a Java method. If the stored procedure, in turn, encapsulates a shell or OS program, it can execute that as well. This stored program unit is known as the handler module. In the previous example, where I built the mechanism to audit accesses to the EMP table, you can optionally specify a stored procedure—standalone or packaged—to be executed as well. For instance, suppose that I have a stored procedure named myproc that is owned by user FGA_ADMIN; simply call the ADD_POLICY procedure with two new parameters as follows:
BEGIN DBMS_FGA.add_policy (object_schema => 'HR', object_name => 'EMP', policy_name => 'EMP_SEL', audit_column => 'SALARY, COMM', audit_condition => 'SALARY >= 150000 OR EMPID = 100', handler_schema => 'FGA_ADMIN', handler_module => 'MYPROC' ); END;
Whenever the policy’s audit conditions are satisfied and the relevant columns are referenced, two things happen: the action is recorded in the audit trails, and the myproc procedure in the FGA_ADMIN schema is executed. The procedure is automatically executed as an autonomous transaction every time the audit trails are written. This means that any changes made ...