Oracle PL/SQL for DBAs

Using DBMS_FGA Procedures

DBMS_FGA is a rather simple package, providing only four procedures that you use to administer fine-grained auditing in your database, ADD_POLICY, DROP_POLICY, DISABLE_POLICY, and ENABLE_POLICY.

The ADD_POLICY procedure

Remember that an FGA policy is not an object in the database, so the normal SQL operations do not apply to it. To administer it, you must use the ADD_POLICY procedure in the DBMS_FGA package . That procedure was described earlier in the chapter.

The DROP_POLICY procedure

To remove an FGA policy, use the DROP_POLICY procedure in the DBMS_FGA package. For example, to drop the policy we defined earlier on the EMP table, you might specify the following block of code.

    BEGIN
       DBMS_FGA.drop_policy (object_schema      => 'HR',
                             object_name        => 'EMP',
                             policy_name        => 'EMP_SEL'
                            );
    END;
    /

If the policy does not exist, it will throw the error ORA-28102: policy does not exist.

The DISABLE_POLICY procedure

Sometimes you may just want to suspend the writing of the audit trail temporarily—for example, when purging the audit trail or switching it to a different table. There is no need to drop a policy in such cases; you can disable it by using the DISABLE_POLICY procedure in the DBMS_FGA package as follows:

    BEGIN
       DBMS_FGA.disable_policy (object_schema      => 'HR',
                                object_name        => 'EMP',
                                policy_name        => 'EMP_SEL'
                               );
    END;
    /

The ENABLE_POLICY procedure

Once you have disabled a policy, it will remain on the object, but entries will not be written to the audit trail. After your maintenance ...