Oracle PL/SQL for DBAs

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 ...