Fine-Grained Auditing

With Oracle9i Database Release 9.0.1, Oracle introduced another level of auditing in fine-grained auditing (FGA). Originally, FGA was only possible for SELECT statements. Other DML wasn’t supported (the alternative was to use database triggers on insert, update, and delete statements). FGA has been significantly enhanced in Oracle Database 10g by allowing audits to extend to all DML statements.

FGA’s extension to the standard auditing was actually borne out of the Oracle Consulting work done in the government division. FGA is designed to solve some of the issues illuminated in the previous “Caveats” section. Audit policies sometime require another level of fidelity not currently supported in the standard auditing. The auditing can be more selectively enabled to occur only when certain conditions arise and specific columns are queried. One of the practical aspects of FGA is that it makes the auditing functions behave more like intrusion detection systems. That is, you can set up your preventive security controls as normal. Then you can use the FGA as a safety net to catch things that fall through. You’ll see this come to life in the upcoming examples.

In security practice, there is no such thing as perfect security. It’s not whether you will be compromised, but rather when you will be. You can only hope to have the auditing enabled.

Audit Conditions

When FGA was originally introduced, it offered four major advantages over standard auditing: a Boolean condition check, the SQL capture ability, a column-sensitivity feature, and an event handler. The first advantage allowed the auditing to occur only when a specific condition was met. That is, at execution time, the audit policy would test a Boolean condition. If the condition was met, then and only then would the audit occur.

This has enormous advantages. It’s very flexible because the Boolean conditions can be anything specified in SQL, including a comparison of the results of function calls. Another advantage is that the conditional auditing helps to eliminate unnecessary audits.

In standard auditing, you could audit anytime someone queried a table. However, you couldn’t specify any exemptions to this based on specific conditions. Perhaps you are concerned with privacy issues, and you set up your access control to prevent users from seeing other users’ records. In standard auditing, there is no inherent way to validate this policy is being accurately enforced. You could set up auditing for selects on your table. However, the problem is that the audit records would only indicate a user selected from the table and would not indicate whether they were able to access another user’s records. You might be able to derive the result set of the query by looking at the SQL captured, but this would prove cumbersome and unreliable. Assuming users are supposed to access the table to see their records, auditing SELECT statements to meet this requirement is ineffective.

With FGA, you can complement the security policy by auditing only when a user is accessing someone else’s records. The database allows you to specify a condition that when met will audit.

Note 

You don’t care why or how they circumvented our security policy; auditing is not prevention—it is detection.

Enabling FGA

Enabling FGA is completely different from enabling standard auditing. FGA doesn’t rely on any initialization parameters. Instead, you control FGA via the DBMS_FGA package. Consider the following in which you want to audit SCOTT.EMP. Assume you want to audit only when a user is accessing another user’s records. This is done by calling the ADD-POLICY procedure and specifying the condition as seen next. If you leave the condition NULL, then the audit will always occur. This is a new feature; in Oracle9i Database, to ensure auditing would always occur, you had to define a condition that was always true such as ‘1=1’.

sec_mgr@KNOX10g> BEGIN   2    DBMS_FGA.add_policy   3                (object_schema      => 'SCOTT',   4                 object_name        => 'EMP',   5                 policy_name        => 'Example',   6                 audit_condition    => 'ENAME != USER');   7  END;   8  / PL/SQL procedure successfully completed.

To illustrate FGA at work, you can simply connect as SCOTT and issue queries. Before you do, it’s a good practice to ensure the cost-based analyzer is being used (especially for Oracle9i Databases). Strange results from FGA can occur if you don’t first issue an ANALYZE on the table. Prior to running this, the current audit records were deleted and the standard auditing on the EMP table was disabled:

scott@KNOX10g> ANALYZE TABLE emp COMPUTE STATISTICS; Table analyzed. scott@KNOX10g> SELECT sal, comm FROM emp   2   WHERE ename = 'SCOTT';        SAL       COMM ---------- ----------       3000 scott@KNOX10g> SELECT sal, comm FROM emp   2   WHERE deptno = 20;        SAL       COMM ---------- ----------        800       2975       3000       1100       3000

Going to the audit trail, you’ll notice that only the second query was recorded. This is good because the first query was, by our security policy, an allowable query:

sec_mgr@KNOX10g> EXEC show_aud PL/SQL procedure successfully completed. sec_mgr@KNOX10g> EXEC show_aud Who:  SCOTT What: SCOTT.EMP Where: 192.168.0.100:SQLPLUS.EXE When:  Mar-24 18:34 How:   SELECT sal, comm FROM emp  WHERE deptno = 20 -------------- End of Record --------------

The condition capability of FGA allows you a higher degree of fidelity in the auditing. The trick to making this most effective is to implement the Boolean checks within a separate PL/SQL function. Allowing the complexity to reside inside an external program allows for simplicity in adding and validating the audit policy.

For example, you might want your audit policy to audit only on weekends and off hours. You can create a program that returns true (represented by the number 1) if it is currently outside of normal operating hours (Monday through Friday, 8 A.M.–6 P.M.).

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION is_off_hours   2    RETURN BINARY_INTEGER   3  AS   4    l_return_val  BINARY_INTEGER;   5    l_day_number  VARCHAR2 (1)   6                          := TO_CHAR (SYSDATE, 'D');   7    l_hour        VARCHAR2 (2)   8                       := TO_CHAR (SYSDATE, 'HH24');   9  BEGIN  10    IF (   l_day_number IN ('1', '7')  11        OR l_hour < 8  12        OR l_hour > 18)  13    THEN  14      l_return_val := 1;  15    ELSE  16      l_return_val := 0;  17    END IF;  18  19    RETURN l_return_val;  20  END; 21  / Function created.

You can simply update the current policy and add the function as the audit condition. To do this, first drop the policy, and then add it again:

sec_mgr@KNOX10g> BEGIN   2    DBMS_FGA.drop_policy (object_schema    => 'SCOTT',   3                          object_name      => 'EMP',   4                          policy_name      => 'Example');   5    DBMS_FGA.add_policy   6      (object_schema      => 'SCOTT',   7       object_name        => 'EMP',   8       policy_name        => 'Example',   9       audit_condition    => 'SEC_MGR.IS_OFF_HOURS = 1');  10  END;  11  / PL/SQL procedure successfully completed.

The result is that your audit condition will then audit all selects on the EMP table after normal operating hours. You have time-sensitive auditing. Unfortunately, when you execute a query as SCOTT, it fails as follows:

scott@KNOX10g> select * from emp; select * from emp                 * ERROR at line 1: ORA-28112: failed to execute policy function

SCOTT doesn’t have execute privileges on the IS_OFF_HOURS function. To resolve this, grant execute on the function to SCOTT. Note that you specified the schema.function name when you registered the policy. This is also critical because the policy would fail to execute if the function couldn’t be resolved by the invoking user. (This could provide a useful and additional tool to protecting your database tables.)

sec_mgr@KNOX10g> GRANT EXECUTE ON is_off_hours TO scott; Grant succeeded. 

The privilege to execute the audit event handler function is of significance mostly because this behavior isn’t consistent with the policy functions used in Oracle’s DBMS_RLS feature, which provides row-level security (discussed in detail in Chapter 10).

Column Sensitivity

A second advantage FGA introduced was the notion of column sensitivity. Assume now that users are allowed to access other users’ records, just not the salary field within those records. In this case, your sensitive column is the SAL column. If the user’s query doesn’t touch this column, then you don’t audit.

The actual auditing will occur when both the sensitive column(s) is queried and the Boolean condition is met. In the case that no condition is specified, the auditing will occur only when the sensitive column is queried or manipulated.

As an example, the previous audit policy can be modified to audit when users query other users’ salaries. Therefore, you’ll have to specify both a condition and a sensitivity column:

sec_mgr@KNOX10g> BEGIN   2    DBMS_FGA.drop_policy (object_schema    => 'SCOTT',   3                          object_name      => 'EMP',   4                          policy_name      => 'Example');   5    DBMS_FGA.add_policy   6               (object_schema      => 'SCOTT',   7                object_name        => 'EMP',   8                policy_name        => 'Example',   9                audit_condition    => 'ENAME != USER',  10                audit_column       => 'SAL');  11  END;  12  / PL/SQL procedure successfully completed. 

Now, test the auditing by executing several queries. Again the current audit records were deleted prior to running the queries shown here. The comments indicate what should happen with respect to auditing:

scott@KNOX10g> -- Aggregate value on sensitive column causes audit scott@KNOX10g> SELECT SUM (sal) FROM scott.emp;   SUM(SAL) ----------      29025 scott@KNOX10g> -- This will not audit since ename = user scott@KNOX10g> SELECT sal FROM emp   2   WHERE ename = 'SCOTT';        SAL ----------       3000 scott@KNOX10g> -- Direct query on column will audit scott@KNOX10g> SELECT empno, job, sal   2    FROM emp   3   WHERE deptno = 10;      EMPNO JOB              SAL ---------- --------- ----------       7782 MANAGER         2450       7839 PRESIDENT       5000       7934 CLERK           1300 scott@KNOX10g> -- No Audit since SAL column is not queried scott@KNOX10g> SELECT empno, job   2    FROM emp   3   WHERE deptno = 10;      EMPNO JOB ---------- ---------       7782 MANAGER       7839 PRESIDENT       7934 CLERK scott@KNOX10g> -- No audit since no records are returned scott@KNOX10g> SELECT ename FROM emp   2   WHERE ename = 'KNOX'; no rows selected

Viewing the audit records, you see that the first two queries were audited as expected:

sec_mgr@KNOX10g> EXEC show_aud     PL/SQL procedure successfully completed. sec_mgr@KNOX10g> EXEC show_aud   Who:  SCOTT   What: SCOTT.EMP   Where: 192.168.0.100:SQLPLUS.EXE   When:  Mar-24 19:11   How:   SELECT SUM (sal) FROM scott.emp   -------------- End of Record --------------   Who:  SCOTT   What: SCOTT.EMP   Where: 192.168.0.100:SQLPLUS.EXE   When:  Mar-24 19:11   How:   SELECT empno, job, sal   FROM emp   WHERE deptno = 10   -------------- End of Record --------------     PL/SQL procedure successfully completed.

The value provided by the SQL capture shows that the first query really wasn’t harmful. There was no direct way for the user to determine the employee’s salary.

The lack of audit on the last query verifies that your auditing is selective to your condition and column. There is an important and the often overlooked reason for this: fine-grained audits occur only when at least one record is returned. There is no “WHENEVER NOT SUCCESSFUL” in FGA. At first this might seem like a bad idea. After all, there is no way to capture the equivalent of someone banging on a locked door. Unlike standard auditing, a user’s failed access doesn’t create an FGA audit record. However, this fact is very desirable when you consider that the audits only occur when something of interest has happened. In the preceding example, if there is a record in the audit trail, then the user did access another user’s salary.

Capturing SQL

The third jewel brought by FGA was the introduction of a concept called SQL capture. This concept has been extended to standard auditing in Oracle Database 10g when you have the AUDIT_TRAIL set to DB_EXTENEDED as was seen in the previous examples. It includes capturing the SQL text as well as the values for any bind variables.

The SQL capture capability is enormous in auditing. It clearly shows what statement caused the audit record to be written. That has an important translation to the real world. First, the queries often indicate the user’s intentions. If the query ends with “where ename = ‘KING’” then you know the user was targeting KING’s records.

The audited SQL can help the security administrator better conclude the actual method the user used when the audit was triggered. Ad hoc queries tend to look much different than packaged application queries, which in turn look different than queries issued by database tools. Administrators can sometimes identify the source by simply looking at the SQL construction.

This is also an invaluable tool because FGA and standard auditing can be used to validate that applications are issuing proper queries. Many times a user’s access, while originally believed to be malicious, may in fact have occurred because of a poorly written application. The SQL can show that the user’s query was written by the application. Therefore, the user either compromised the application, or the application has a bug. Either way, the audit has provided some very useful and practical information.

A caveat to the SQL capture is that it doesn’t capture the modified SQL that results from an applied Virtual Private Database policy.

Acting on the Audit

FGA adds the ability to invoke an event handler. In standard auditing, the records were written as they occurred, but there is no guarantee that they will be viewed and acted upon in a timely fashion. It’s possible that the DBA leaves at 6:00 P.M. Friday night. A hacker begins to poke around the system ten minutes later. Finally, on Sunday afternoon, the hacker has gathered enough information to do something destructive. The audit records were being written. Unfortunately, no one was around to hear the screams coming from the database. This is like having a burglar alarm that no one hears. The DBA returns Monday to a compromised database. It would have been nice if the DBA could have been alerted that something was happening while that something was happening.

With FGA, you do have the ability to invoke an event handler. In this manner, you can see FGA as analogous to a SELECT trigger. The event handler can do anything you like because you write the code.

One thing the event handler shouldn’t do is to write (redundant) audit data to a private audit table. I’ve seen this countless times as people fail to realize that the audit data is already being written. A variation of the theme is a good idea: you may find it useful to write the audit data to another database or to the file system in hopes of protecting the data from a malicious and privileged database administrator.

Writing the Event Handler

The event handler that is used in FGA accepts three parameters: the schema of the object being audited, the name of the object being audited, and the name of the auditing policy. Within the procedure, you can easily access the SQL that caused the auditing to occur. This is obtained by referencing the USERENV context called CURRENT_SQL.

To illustrate an implementation, the following example shows an event handler that sends an e-mail alerting the receiver of the audit. In the e-mail, the details of the audit record are given. The trick is that you don’t have to query the audit logs to get the SQL that caused the audit to occur.

sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE fga_notify (   2    object_schema  VARCHAR2,   3    object_name    VARCHAR2,   4    policy_name    VARCHAR2)   5  AS   6    l_message    VARCHAR2 (32767);   7    l_mailhost   VARCHAR2 (30)   8                    := '<your smtp server>';   9    l_mail_conn  UTL_SMTP.connection;  10    l_from       VARCHAR2 (30)  11                          := 'FGA_ADMIN@<your email domain>';  12    l_to         VARCHAR2 (30)  13                         := '<administrator email address>';  14  BEGIN  15    l_message :=  16         'User '  17      || USER  18      || ' successfully accessed '  19      || object_schema  20      || '.'  21      || object_name  22      || ' at '  23      || TO_CHAR (SYSDATE, 'Month DD HH24:MI:SS')  24      || ' with this statement:  "'  25      || SYS_CONTEXT ('userenv', 'current_sql')  26      || '"';  27    l_mail_conn :=  28           UTL_SMTP.open_connection (l_mailhost, 25);  29    UTL_SMTP.helo (l_mail_conn, l_mailhost);  30    UTL_SMTP.mail (l_mail_conn, l_from);  31    UTL_SMTP.rcpt (l_mail_conn, l_to);  32    UTL_SMTP.DATA (l_mail_conn,  33                      UTL_TCP.crlf  34                   || 'Subject: FGA Alert'  35                   || UTL_TCP.crlf  36                   || 'To: '  37                   || l_to  38                   || UTL_TCP.crlf  39                   || l_message);  40    UTL_SMTP.quit (l_mail_conn);  41  EXCEPTION  42    WHEN OTHERS  43    THEN  44      UTL_SMTP.quit (l_mail_conn);  45      raise_application_error  46          (-20000,  47              'Failed due to the following error: '  48           || SQLERRM);  49  END;  50  / Procedure created.

Now register this as the event handler for our FGA policy:

sec_mgr@KNOX10g> BEGIN   2    DBMS_FGA.drop_policy (object_schema    => 'SCOTT',   3                          object_name      => 'EMP',   4                          policy_name      => 'Example');   5    DBMS_FGA.add_policy   6               (object_schema      => 'SCOTT',   7                object_name        => 'EMP',   8                policy_name        => 'Example',   9                audit_condition    => 'ENAME != USER',  10                audit_column       => 'SAL',  11                handler_schema     => 'SEC_MGR',  12                handler_module     => 'FGA_NOTIFY');  13  END;  14  / PL/SQL procedure successfully completed.

As seen in Figure 8-2, when a user queries another user’s salary, an e-mail is sent to notify the DBA that this has happened. The DBA should then be able to react to the situation and handle it accordingly. The alerting capability is a critical element to helping administrators respond to incidents as they occur.

Figure 8-2: Alerts can be sent to administrators to notify them of event occurrences as they happen.

Invalid Event Handler in Oracle9i Database

In Oracle9i Database, there is an interesting occurrence that can be witnessed if the event handler throws an exception or is invalid. The database returns a result set that is the opposite of the condition specified. For example, if the condition is “ENAME != USER” meaning you want to audit when a user is accessing another user’s records, the database will only return records where “ENAME = USER”.

This is known as failsafe. The thinking is that the event handler must be doing something critical. Because the event handler can’t perform its function, the database will eliminate any potentially harmful records. It does this by reversing the condition.

In Oracle9i Database, this was a way to create a column-sensitive, row-level security capability. The following output shows what happens when you apply the same audit policy in an Oracle9i Database but have an invalid event handler:

system@ORA92> BEGIN   2    DBMS_FGA.add_policy   3               (object_schema      => 'SCOTT',   4                object_name        => 'EMP',   5                policy_name        => 'Example',   6                audit_condition    => 'ENAME != USER',   7                audit_column       => 'SAL',   8                handler_schema     => 'NO_SCHEMA',   9                handler_module     => 'UNDEFINED');  10  END;  11  / PL/SQL procedure successfully completed. system@ORA92> CONN scott/tiger Connected. scott@ORA92> SELECT COUNT (ename) FROM emp; COUNT(ENAME) ------------           14 scott@ORA92> SELECT ename, sal FROM emp; ENAME             SAL ---------- ---------- SCOTT            3000

You see that the user can access all records when the SAL column (your column-sensitive attribute) is not queried. Once the audit condition and column are specified, the database tries to invoke the event handler. There is no schema in the database or a procedure as defined by the policy. A trace file is generated indicating the error and the database reverses the condition from “ENAME != USER” to “ENAME = USER”. Consequently, the user gets only their record.

In Oracle Database 10g, there is native support for column-sensitive, row-level security. As such, the behavior in Oracle Database 10g is different. The queries aren’t modified with the inverse condition. That is, the queries execute as if no event handler were registered, and all rows are returned even when the SAL column is selected.

Caveats

While FGA is powerful, there are some challenges. First, problems with the event handler may not be obvious when the policy is established. In Oracle9i Database, the database reversed the condition in the FGA policy. The results might act as a clue that something has gone awry with your event handler. In Oracle Database 10g, there is no clue. The event handler is simply not called.

Recall the feature described earlier that prevents users from executing DML if the function in the FGA policy condition can’t be resolved or executed. This can be both helpful and hurtful. Assuming that you don’t want this to occur, it may be difficult to detect this ahead of time and difficult to rectify it afterwards.

While the audit provides a wealth of information, it doesn’t tell you what the user received as a result from the query. You only know that they got at least one record. This leads to another caveat—audits don’t occur when no records are returned. This can be good and bad. It’s good for indicating that something bad happened, but it’s bad in that there is no indication that someone is banging on a locked door. You can, however, combine standard auditing with FGA to accomplish this task.