Oracle PL/SQL for DBAs

Additional DML Statements

In Oracle9i Database, FGA supports the auditing of SELECT statements only; the DML statements such as INSERT, UPDATE, and DELETE cannot be audited. In Oracle Database 10g, DML statements can also be audited. A new statement_types parameter in the ADD_POLICY procedure of the DBMS_FGA package allows you to specify the statements you want audited. To continue with our previous example, suppose that we now want to capture all types of statements—SELECT, INSERT, UPDATE, and DELETE—against the table EMP, but only when the audit conditions are satisfied as we described earlier. This can be accomplished by issuing the following:

    BEGIN
       DBMS_FGA.add_policy (object_schema        => 'HR',
                            object_name          => 'EMP',
                            policy_name          => 'EMP_DML',
                            audit_column         => 'SALARY, COMM',
                            audit_condition      => 'SALARY >= 150000',
                            
                            statement_types      => 'SELECT, INSERT, DELETE, UPDATE'
                           );
    END;
    /

The records go to the same audit trail table, FGA_LOG$, and will be visible through the same data dictionary view, DBA_FGA_AUDIT_TRAIL. To accommodate the three additional types of access (INSERT, UPDATE, and DELETE), a new column called STATEMENT_TYPE is available in the view. If this parameter is omitted, then only SELECT statements are recorded.

DELETE statements are always audited, regardless of the audit_column parameter. This is because a DELETE removes the entire row and implicitly references or affects all of the columns in the table.

Warning

Only simple predicates (i.e., those with just one condition) are ...