I have been working on a customer database hardening. Database security isn’t really my cup of tea but it seems to be very popular these days. One of the requirement from the customer was the ability to monitor the database operation (which is very easy with the simple Oracle Audit features).

After we did that, the customer needed another auditing. He has sensitive data in one of the tables and he wanted some kind of auditing of the table access. This is one of the more central of his application so he asked the application should be excluded from the monitoring.

My first thought was to use the simple auditing for that but a short research showed that once an object is being monitored, there is no exclude feature to it. Another solution that came to mind is to use the ancient Fine-grained audit (DBMS_FGA). I know I’m coming a bit late to the party, and I know Oracle 12c has new and exciting ways of auditing but the customer was using Oracle 11.2.0.3 and is not planning on upgrading soon.

So off we went and I create a very simple FGA policy. The policy said to do a very simple and basic monitoring: when someone is querying or modifying the table, we want to keep a record for that. The data will be stored to the FGA_LOGS$ table and will be queried with the DBA_FGA_AUDIT_TRAIL view (later we will setup an email alert, but this is an issue for a different post).

The audit policy creation command was:

Begin
    dbms_fga.add_policy(object_schema   => 'ZOHAR',
                        object_name     => 'MY_ SENSITIVE_TABLE',
                        policy_name     => 'SENSITIVE_POLICY',
                        statement_types => 'SELECT, INSERT, UPDATE, DELETE',
                        audit_condition => q'{USER != 'ZOHAR'}');
end;
/

After a few days running with the basic monitoring, we found there is another user that should be excluded. We changed the policy to include the second user:

Begin
    dbms_fga.add_policy(object_schema   => 'ZOHAR',
                        object_name     => 'MY_sensitive_TABLE',
                        policy_name     => 'SENSITIVE_POLICY',
                        statement_types => 'SELECT, INSERT, UPDATE, DELETE',
                        audit_condition => q'{USER != 'ZOHAR' and USER != 'ZOHAR_B'}');
end;
/

Well, that seems somewhat easy, isn’t it? Well, no it’s not.

Error ORA-28138

Even though this will not report an error, this command actually broke the application. When trying to insert records into our sensitive table, we hit error ora-28138 (Error in Policy Predicate) – while regular queries continued as usual. We immediately removed the policy and went back to the documentation. Under the explanation of what should go into the “audit_condition” parameter, we found out that it would require “simple” clause – but what is more simple than using the AND operator – and how can we work around the problem that we found?!

After googling for a little while and after scanning the MOS for known issues, we found this old document #832856.1.

In this document it specifically says that “it is not possible to use AND, OR or IN operators in the audit_condition – because the audit_condition can take only one argument”.
Well, that’s awkward – so what now? Fortunately for us, 832856.1 also describe a simple workaround. It says that by using a user defined function we can workaround most condition complexity.
Since the note is using one of the more complex functions, I will be trying to make it a bit more simple.

We defined a new function that validate the users to be audited. We are checking a list of users and if our user is not being audited, we will return 0. If the user is to be audited, it will return 1.

create or replace function validate_users (user_name in varchar2) return number as
begin
  -- list of users to exclude:
  if user_name in ('ZOHAR', 'ZOHAR_B') then 
    return 0;
  else
    return 1;
  end if;
end;
/ 

All we have to do is set the policy again:

Begin
    dbms_fga.drop_policy(object_schema  => 'ZOHAR',
                        object_name     => 'MY_sensitive_TABLE',
                        policy_name     => 'SENSITIVE_POLICY');

    dbms_fga.add_policy(object_schema   => 'ZOHAR',
                        object_name     => 'MY_sensitive_TABLE',
                        policy_name     => 'SENSITIVE_POLICY',
                        statement_types => 'SELECT, INSERT, UPDATE, DELETE',
                        audit_condition => q'{validate_users(user) = 1');
end;
/

And this solved this problem completely. Now will will continue to actually getting email alerts from the database when a violation is made…

Few more tips

Some other things that we should be aware are the following:

1. Do not include functions, which execute the auditable statement on the same base table, in theaudit_condition setting. This causes a recursion in the audit and will ultimately exust the database resources. Possible error messages: ORA-1000: maximum open cursors exceeded or ORA-00036: maximum number of recursive SQL levels (50) exceeded
2. Do not issue theENABLE_POLICY or DBMS_FGA.DISABLE_POLICY statement from a function in a policy’s condition
3. Be aware that this function has some performance impact so no crazy long functions.