Fine-Grained Auditing for Real-World Problems, Part 3
By Arup Nanda

Now that you've mastered FGA in all types of environments, learn how it can do much more in Oracle Database 10g

In the previous two installments of this series, I introduced you to the concept of Fine-Grained Auditing (FGA) used to track select statements in Oracle9i Database and up. I also explained how to use the feature in complex environments such as within a web application via application contexts and client identifiers. These two articles have enough information for you to build a FGA setup for almost all types of database systems, however complex. In this third and final installment, I will explain the FGA enhancements Oracle Database 10g brings to the table.

FGA in Oracle9i Database

Let's briefly recap the benefits provided by FGA. For a complete discussion, please see Part 1 and Part 2 of this series.

Regular auditing (via the AUDIT statement) records the statement used—such as SELECT or INSERT—and who issued it, from which terminal, when, and much more. However, the most important piece of information — which particular record was changed and the change in the data itself — is not captured. Instead, many users write triggers to capture the data value before and after the change and record them in user-defined tables. But because triggers are possible only on DML statements such as insert, update, and delete, one major area of access — the SELECT statement — can't be audited via this route.

Hence the value of FGA: it captures SELECT statements only. Along with triggers and the Log Miner tool, FGA provides a mechanism for auditing all types of value changes and non-change related data accesses.

FGA not only fills the void in auditing SELECT statements, but also provides some other interesting fringe benefits that make the DBA's job easier. For example, FGA allows a user-defined procedure to execute whenever the specified audit condition occurs, so you could consider it to be q trigger on a SELECT statement -- a facility that is otherwise unavailable. This trick can be very useful — for example, to send an email to a security auditor whenever someone selects payroll records for employees earning more than $1 million; to generate the audit records in user-defined tables, which can be manipulated without restriction unlike the SYS owned FGA_LOG$ table; using the audit trails to identify type of data access to find out the best possible indexing mechanism, and much more.

For this and other reasons, FGA is among most important tools in the DBA's toolbox. However, in Oracle9i Database, it lacks one important feature: availability beyond the SELECT statement.

All Types of DML

In Oracle Database 10g, FGA has become complete — it can audit all types of DML statements, not just SELECT.

Let's take a look at an example. In Part 1 of this series, I introduced a table named ACCOUNTS. The FGA policy defined on that table was:

begin
   dbms_fga.add_policy (
      object_schema   => 'BANK',
      object_name     => 'ACCOUNTS',
      policy_name     => 'ACCOUNTS_ACCESS',
      audit_column    => 'BALANCE',
      audit_condition => 'BALANCE >= 11000'
  );
end;

Under Oracle 9i Database, this policy could only audit SELECT statements. In Oracle Database 10g, however, you can extend it to include INSERT, UPDATE, and DELETE as well. You would do so by specifying a new parameter:

statement_types => 'INSERT, UPDATE, DELETE, SELECT'

This parameter will enable auditing on all the included statement types. You might even consider creating separate policies for each statement type, which would allow you to enable or disable policies at will—specifically, to control the generation of audit trails to manage the space occupied by them. By default, however, the statement_type parameter audits only SELECT statements.

After adding the policy with the new parameter, issuing the following statement:

update accounts set balance = 1200 where balance >= 3000;

causes a record to go into the FGA_LOG$ table. Note that this record is inserted by an autonomous transaction; the record is there even if you roll-back the update statement. You can check for the existence of the trail from another session.

select lsqltext from fga_log$;

LSQLTEXT
--------------------------------------------------------
update accounts set balance = 3100 where balance >= 3000 

This row also contains all other relevant details such as table name, policy name, and transaction id.

Comparison with Trigger Approach

So, what can FGA do that the old trigger-based approach doesn't?

Prior to Oracle Database 10g, DML statement auditing was done in a trigger similar to the following (note: this is not true code, just a representative example):

CREATE TRIGGER XXXXX
ON Table
AFTER INSERT OR UPDATE OR DELETE
FOR EACH ROW
BEGIN
   INSERT INTO AUDIT_LOG
   Old Value, New Value, Time .....
END

This trigger captures the old and new values and populates the AUDIT_LOG table. If need be, it can also be made an autonomous transaction. The biggest problem is that the trigger is fired for each row, not once per statement. For instance, the following statement:

update accounts set balance = 1200 where balance >= 3000;

fires for all 10,000 records, inserting 10,000 rows into the audit table. This approach could seriously undermine the performance of the update statement and may even cause it to fail due to space problems in the audit trail. Using a statement trigger doesn't help either, because it can't capture any new or old values of individual records. In contrast, in the FGA approach, only one record is created and the insert executes only once per statement, not once per row—affecting performance negligibly, if at all.

In FGA, you can specify the relevant columns to limit generation of audit trails only when those columns are accessed. In triggers the functionality exists through the use of the WHERE of the trigger definition. However, therein lies a very important difference — in triggers the columns are checked only when they are changed, not merely accessed. In FGA, the auditing kicks in whenever the columns are accessed, whether they're changed or not. This characteristic makes FGA more versatile than triggers.

Another advantage is the applicability of the FGA facility. Sometimes, the INSTEAD OF triggers defined on a view update it on the base table; another INSTEAD OF trigger can't capture the changes made by the other trigger and hence they cannot be recorded. FGA, however, is established on views or tables and captures the changes regardless of where they come from — user statements or triggers.

So are there any situations in which triggers are a better choice than FGA? There may be two:

• Remember, FGA inserts the audit trails using an autonomous transaction, which is committed within its own context. If the DML statement fails or is rolled back, the inserted trail record is not rolled back. If a user updates something but does not commit, the change is not made but the audit trail is created anyway. This may lead to several false positive entries in the audit trail, a potentially undesirable situation. Subsequent analysis of the table using SCN numbers captured through flashback queries will probably reveal this problem, but the process may be complicated. But if this risk is not acceptable, then a trigger-based approach is preferred over FGA.
• FGA records the SQL statements issued by the user and the SCN number, but not the values before and after the change. A separate facility must be used to extract those values using flashback queries from the tables. Because the flashback queries depend on the information contained in UNDO segments, which is limited, the facility may not extract the old values from points that far into the past. A trigger-based approach captures the change at the source, hence the recording of old and new values is guaranteed.

FGA Behavior During a Change

Data changes all the time, so it could potentially become applicable in the audit condition when it wasn't so earlier (or vice versa). This issue gives rise to some interesting questions about FGA behavior in different scenarios. Consider our example, where the FGA policy has been defined on UPDATE with the condition is BALANCE >= 3000 and audit column is BALANCE.

Scenario 1

Before: BALANCE = 1000

User issues:

update accounts set balance = 1200 where ACCOUNT_NO = ....

The old and new balances are less than 3,000, and the audit condition is not satisfied; hence this statement will not be audited.

Scenario 2

Before: BALANCE = 1000

User issues:

update accounts set balance = 3200 where ACCOUNT_NO = ....

The new balance is more than 3,000, and the audit condition is satisfied; hence this statement will be audited.

Scenario 3

Before: BALANCE = 3200

User issues:

update accounts set balance = 1200 where ACCOUNT_NO = ....

The new balance is less than 3,000 but the old balance was more. Hence the audit condition is satisfied and this statement will be audited.

Scenario 4

User inserts a row with BALANCE < 3000.

insert into accounts values (9999,1200,'X');

Because the balance 1,200 does not satisfy the audit condition, the statement is not audited. If the value of the balance column were greater than or equal to 3,000, it would have been audited.

Scenario 5

User inserts a row with null value in balance.

insert into accounts (account_no, status) values (9997, 'X');

Because the balance is null, and the column does not have any default value, the audit condition is not satisfied (the comparison NULL >= 3000 results in FALSE) and the statement is not audited. Important note: Should the column have had a default value of more than 3,000, the statement still would not have been audited, even though the balance column value of the inserted row is greater than 3,000.

All Relevant Columns?

Consider a policy defined on the table ACCOUNTS as follows.

begin
   dbms_fga.add_policy (
      object_schema   => 'ANANDA',
      object_name     => 'ACCOUNTS',
      policy_name     => 'ACCOUNTS_SEL',
      audit_column    => 'ACCOUNT_NO, BALANCE',
      audit_condition => 'BALANCE >= 3000',
      statement_types => 'SELECT'
  );
end;

As you can see, the policy is defined on the columns ACCOUNT_NO and BALANCE. Assuming the balance for account 9995 is 3,200, if the user issues the following statement:

select balance from accounts where account_no = 9995;

the statement will be audited, since the balance column is chosen and the balance is 3,200, greater than 3,000, satisfying the audit condition. The statement will trigger an audit regardless of which of the three columns are selected.

In some cases the combination of columns may be of importance, but not a specific column. For instance, if a user wants to find out the total balance in the bank, she issues:

select sum(balance) from accounts;

This query is fairly innocent; it does not specifically identify an account holder and the account balance. Acme Bank security policy might not require this query to be audited. However, the query

select balance from accounts where account_no = 9995

must be audited, as it specifically identifies an account. By default all statements, regardless of the combination of columns used, are audited. This will create a large number of unneeded audit trail entries and perhaps some space constraint problems. To limit them, you can specify auditing to kick in only when the desirable combinations of columns are used in the query. While defining the policy, you can use a new parameter:

audit_column_opts => DBMS_FGA.ALL_COLUMNS

This parameter will make the policy create audit trail entries only when both the columns ACCOUNT_NO and BALANCE are accessed in the query. For instance, the following query will produce an audit trail entry.

select account_no, balance from accounts;

But, this one will not.

select account_no from accounts;

Use of this parameter will limit the amount of auditing to a more manageable size. If the default behavior—that is, auditing when any of the columns are selected—is desired, then you can use the different value for the same parameter.

audit_column_opts => DBMS_FGA.ANY_COLUMNS

Capturing Bind Variables

With Oracle Database 10g, additional pertinent information can be written to the regular audit trails such as the values of the bind variables used in the query. You perform this task by placing the initialization parameter

audit_trail = DB_EXTENDED

In FGA audit trails, it may or may not make sense to have the values of the bind variable. If you want to stop recording the values, you can use another parameter in the add_policy() procedure as follows.

audit_trail => DB

By default, the bind variables are captured and the value of this parameter is DB_EXTENDED.

Putting It All Together

Now that you've learned several new FGA parameters in Oracle Database 10g, let's see how the declaration of the policy creation script looks now.

Here we define four different policies corresponding to four types of statements. The policy for SELECT statements is shown below; here we have chosen not to record the values of the bind variables and trigger an audit only if both columns ACCOUNT_NO and BALANCE are used in a query.

begin
   dbms_fga.add_policy (
      object_schema     => 'ANANDA',
      object_name       => 'ACCOUNTS',
      policy_name       => 'ACCOUNTS_SEL',
      audit_column      => 'ACCOUNT_NO, BALANCE',
      audit_condition   => 'BALANCE >= 3000',
      statement_types   => 'SELECT',
      audit_column_opts => DBMS_FGA.ALL_COLUMNS,
      audit_trail       => DB
  );
end;

Likewise, we will create similar policies for INSERT, UPDATE, and DELETE statements. They can be turned on or off at will.

Combining Regular and Fine-Grained Auditing

In Oracle Database 10g, regular auditing has been vastly improved as well. Implemented by the AUDIT command, regular auditing can now capture a lot of other useful information, such as:

• Extended, granular timestamp
• Operating system process ID
• Transaction identifier (when the audit trail is generated by a data modifying transaction, such as via an update, the transaction id is recorded here, which can be joined later with the view DBA_TRANSACTION_QUERY to identify the exact statement, its undo SQL, the row id, and much more)
• SQL Statement Text
• Values of bind variables
• SCN at the time of change.

As you can see, in terms of content and capability, regular auditing resembles the fine-grained version. As a DBA, however, you are interested in knowing all audit entries, not just one. A new view, DBA_COMMON_AUDIT_TRAIL, combines regular and FGA trails. Use the following query to check them both:

select * from dba_common_audit_trail;

This view is a union of DBA_AUDIT_TRAIL and DBA_FGA_AUDIT_TRAIL, with relevant information from each. From the data dictionary, the view is created as shown below.

select 'Standard Audit', SESSIONID,
    PROXY_SESSIONID, STATEMENTID, ENTRYID, EXTENDED_TIMESTAMP, GLOBAL_UID,
    USERNAME, CLIENT_ID, Null, OS_USERNAME, USERHOST, OS_PROCESS, TERMINAL,
    INSTANCE_NUMBER, OWNER, OBJ_NAME, Null, NEW_OWNER,
    NEW_NAME, ACTION, ACTION_NAME, AUDIT_OPTION, TRANSACTIONID, RETURNCODE,
    SCN, COMMENT_TEXT, SQL_BIND, SQL_TEXT,
    OBJ_PRIVILEGE, SYS_PRIVILEGE, ADMIN_OPTION, GRANTEE, PRIV_USED,
    SES_ACTIONS, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE,
    LOGOFF_DLOCK, SESSION_CPU
  from DBA_AUDIT_TRAIL
UNION ALL
select 'Fine Grained Audit', SESSION_ID,
    PROXY_SESSIONID, STATEMENTID, ENTRYID, EXTENDED_TIMESTAMP, GLOBAL_UID,
    DB_USER, CLIENT_ID, EXT_NAME, OS_USER, USERHOST, OS_PROCESS, Null,
    INSTANCE_NUMBER, OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, Null,
    Null, Null, STATEMENT_TYPE, Null, TRANSACTIONID, Null,
    SCN, COMMENT$TEXT, SQL_BIND, SQL_TEXT,
    Null, Null, Null, Null, Null,
    Null, Null, Null, Null, Null,
    Null, Null
  from DBA_FGA_AUDIT_TRAIL

FGA and Regular Auditing: Differences

If standard and fine-grained auditing are similar in Oracle Database 10g, you may well ask, in what circumstances would FGA be the better choice? Well, let's explore the differences.

• Standard auditing must be enabled at the database level using the parameter AUDIT_TRAIL. This parameter is not dynamic; you must restart the database to make it take effect. In contrast, FGA does not require any parameter change.
• Once in place on an object, standard auditing stays there. To deactivate it, you must remove the audit option using the NOAUDIT command. That can be inconvenient because dropping the audit option on a table also drops metadata information. FGA, however, can be temporarily disabled and enabled, without losing any metadata information.
• FGA can handle only four types of statements: SELECT, INSERT, UPDATE, and DELETE. Regular audit, in contrast, can handle many other statements and privileges, even session connections and disconnections.
• Standard audit creates only one record per session (by session) or one per each access to the object (by access). This modest footprint is important for controlling space inside the audit trail tables. FGA isn't as low-profile; it operates on a per-access basis — making the trail bigger.
• Standard auditing can be used to detect any attempts to break in, by recording the trail, and if the attempt was unsuccessful, the error code. FGA can't.
• Standard auditing can write to either database tables or an OS file. The latter is useful when an auditor, not the DBA, has access to the trails. In Windows, the non-DB audit trails are recorded in the Event Log and accessible differently. This option protects the integrity of the audit trails. FGA logs, however, are written only to the database table FGA_LOG$. You can create user-defined audit handlers in FGA to write to OS files, but their integrity is not assured.
• Standard auditing can be set up for default objects. This facility becomes extremely useful in cases where tables are created at runtime: The default auditing option enables auditing without the DBA's intervention. This is not possible in FGA; one must create a policy on an existing table, and that can happen only after the table has been created.
• In FGA, auditing is much more flexible--only when certain columns are accessed, when a certain condition is evaluated to true, and so on. That versatility comes in handy when you need to control the growth of the trail.
• SQL bind variables are captured by default in FGA. In regular auditing, the initialization parameter audit_trail must be set to db_extended to enable that.
• Privileges differ: regular auditing requires audit system or statement privilege; FGA needs only an execute privilege on dbms_fga package.

From the above comparison, you will understand why FGA may prove useful in certain cases. With the enhanced regular auditing features in Oracle Database 10g, some tasks previously considered impossible— capturing values of bind variables, for instance — become quite easy.