Section 6.3. Administering FGA

6.3. Administering FGA

So far I have talked only about how you can set up FGA on a table. As a DBA, you will also be responsible for managing and administering the overall FGA configuration.

6.3.1. The DBA_AUDIT_POLICIES View

To see the FGA policies that have already been defined in the database, you can query the data dictionary view DBA_AUDIT_POLICIES, which contains the following columns:

OBJECT_SCHEMA

Name of the schema that owns the table or view on which the policy has been defined.

OBJECT_NAME

Name of the table or view on which the policy has been defined.

POLICY_NAME

Name of the policy.

POLICY_TEXT

If the policy has a conditionfor example, "SALARY>=150000 OR EMPID=100"it is shown here.

POLICY_COLUMN

If the policy has relevant columns (e.g., the audit trail is generated only if the relevant columns are referenced), they are shown here.

PF_SCHEMA

If a policy has a handler module, its owner's name is shown here.

PF_FUNCTION

If the handler module is defined and is a standalone procedure, its name is shown here. If the handler module is a packaged procedure, the name of the procedure is shown here and the name of the package is shown in the column PF_PACKAGE.

PF_PACKAGE

See the description of PF_FUNCTION above.

ENABLED

Indicates whether the policy is enabled (YES or NO).

In Oracle Database 10g, this view has several additional columns:

SEL

INS

UPD

DEL

AUDIT_TRAIL

POLICY_COLUMN_OPTIONS

These are described later in the "FGA in Oracle Database 10g" section.

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

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

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

6.3.2.3. The DISABLE_POLICY procedure

Sometimes you may just want to suspend the writing of the audit trail temporarilyfor 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;     /

6.3.2.4. 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 operations are complete, you can enable the policy again by using the ENABLE_POLICY procedure in the DBMS_FGA package as follows:

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