Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page