Oracle Handler Procedure Security Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Handler Procedure

The third set of parameters passed to dbms_fga.add_policy is related to handler procedures. When the audit condition is triggered on the table or view, the default action is to write the records into the audit trail. However, at the same time, the procedure defined as the handler procedure is executed, if mentioned. In the above case, the following is an example of the handler procedure as a parameter to the dbms_fga.add_policy procedure.

      handler_schema  => 'SECUSER',
      handler_module  => 'EMAILER'

Here a procedure named EMAILER, owned by SECUSER, is defined as the handler module. When the audit condition is satisfied on the table, the procedure EMAILER is automatically called. Imagine this as a trigger on the select statement. Ordinarily, triggers are based on some transactional statements such as insert, update, delete, etc.; but this is an example where the concept of triggers can be extended to the select statements, too.

The handler functions are useful in many cases. In the above definition, we can place some logic inside the procedure EMAILER to check other conditions, such as coming from a specific IP Address, and generating emails to alert someone on possible unauthorized access.

An important point to note here is that the handler procedure may be defined in another schema, not necessarily in the same one as that of the table. This ability is particularly well suited for secured applications since no user need be given execute privilege on this procedure, and hence no user can call this procedure independently to create serious damage.

The ability of the handler procedure to execute when an audit condition is satisfied can be exploited by creating a user-defined audit functionality as shown later in this chapter.

Multiple Policies

A table may have several FGA policies defined on it. All the policies are independently evaluated and handled. For all the policies in which the conditions evaluate to true, the corresponding handler functions are triggered. This may cause some confusion in handling the policies, but in most cases this provides flexibility to the setup. The individual policies can be enabled or disabled based on requirements, without affecting the others.

Managing Policies

All the administrative operations for policies are done using procedures defined in the supplied package dbms_fga. Here are the common operations.

Dropping Policies

The policies can be dropped by the following command.

begin
   dbms_fga.drop_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'CLAIM_AMOUNT');
end;

Potential Problem Due To Bug

You should be aware of a bug 2713401 that prevents the policy from being dropped. In this bug, attempts to drop the policy using the above statement will fail with “ORA-28102: policy does not exist” error message. Remember, this is a bug, so it might have been fixed in later releases. If the bug affects your particular release, please read on in this section; otherwise, please skip it.

In order to correct the problem, the best approach is to call Oracle Support and have them address the problem. In development systems, however, here is a solution you could try. We urge you to strongly consider against running it on production, as it is unsupported by Oracle, but it seems to work and can be attempted in development. The authors do not assume any responsibility for any damage caused by intentional application to production or otherwise.

As user SYS, issue the following:

begin
   dbms_fga.add_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'CLAIM_AMOUNT');
end;
/

Note we just created a policy with the same name. Ordinarily, it wouldn’t have worked, but it did, due to the bug. At this time, if you select the rows from fga$ table, you will see two rows for this policy name. One of them should be dropped as in the following.

DELETE FGA$
WHERE PNAME = ‘CLAIM_AMOUNT’
AND ROWNUM < 2;

Now the policy can be dropped normally using dbms_fga.drop_policy procedure.

Again, use this trick at your own risk, this is unsupported by Oracle Support.

Enabling/Disabling Policies

A policy can be disabled, without being dropped. This can be useful in several situations where the policy needs to be temporarily suspended.

begin
   dbms_fga.disable_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'CLAIM_AMOUNT');
end;

To enable it, use the corresponding:

begin
   dbms_fga.enable_policy (
      object_schema=>'CLAIM_SCHEMA',
      object_name=>'CLAIMS',
      policy_name=>'CLAIM_AMOUNT');
end;

These four procedures inside the dbms_fga package handle all operations inside the FGA framework.

User Defined Audit Handler

If a user queries the table, and the policy condition as well as the columns are satisfied, by default, the action is written into the table fga_log$ owned by SYS. However, it is not mandatory that the records be written into this table alone. In some cases, as we will see, it may be desirable to have a separate table to record the audit functionality.

Why a User Defined Audit Table

Maintainability – The file grained audit functionality, just like the regular audit, also needs sophisticated archival and purge setups, where the trails can be preserved for analysis later. The best method to achieve this is using a partitioned table for the audit trails. When the time comes to purge, the partition can be simply dropped. If it needs to be preserved, the partition can be converted to a table, and the table can be archived via export or via transportable tablespace. Since partitioning the table fga_log$ is not supported, the next best option is to create a user defined audit table and record there manually.

Extended Functionality – The audit handler can do a lot of things in addition to storing the audit record, such as send an e-mail when a certain audit event occurs, e.g. when someone selects from the highly sensitive procedure codes outside business hours. This functionality may seem a little extreme, but in situations where an immediate action is required based on some auditing event, the functionality can be implemented using the handler function.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.