Section 6.4. FGA in Oracle Database 10g

6.4. FGA in Oracle Database 10g

This section describes the specific features and enhancements introduced for FGA in Oracle Database 10g.

6.4.1. Additional DML Statements

In Oracle9i Database, FGA supports the auditing of SELECT statements only; the DML statements such as INSERT, UPDATE, and DELETE cannot be audited. In Oracle Database 10g, DML statements can also be audited. A new statement_types parameter in the ADD_POLICY procedure of the DBMS_FGA package allows you to specify the statements you want audited. To continue with our previous example, suppose that we now want to capture all types of statementsSELECT, INSERT, UPDATE, and DELETEagainst the table EMP, but only when the audit conditions are satisfied as we described earlier. This can be accomplished by issuing the following:

     BEGIN        DBMS_FGA.add_policy (object_schema        => 'HR',                             object_name          => 'EMP',                             policy_name          => 'EMP_DML',                             audit_column         => 'SALARY, COMM',                             audit_condition      => 'SALARY >= 150000',                                                          statement_types      => 'SELECT, INSERT, DELETE, UPDATE'                            );     END;     /

The records go to the same audit trail table, FGA_LOG$, and will be visible through the same data dictionary view, DBA_FGA_AUDIT_TRAIL. To accommodate the three additional types of access (INSERT, UPDATE, and DELETE), a new column called STATEMENT_TYPE is available in the view. If this parameter is omitted, then only SELECT statements are recorded.

DELETE statements are always audited, regardless of the audit_column parameter. This is because a DELETE removes the entire row and implicitly references or affects all of the columns in the table.

Only simple predicates (i.e., those with just one condition) are allowed in the audit_condition when DML statements are audited by FGA. You cannot, for example, define a policy to audit DML with an audit_condition such as 'SALARY >= 150000 OR EMPID = 100'. If you create the policy, it will be successful; but the updates to the table will fail with the error ORA-28138: Error in Policy Predicate. Similarly, you cannot define a subquery in the audit_condition; it must be a simple predicate.

6.4.2. Additional Data Dictionary Views and Columns

In Oracle Database 10g, a number of additional items are captured in the FGA audit trail and are available in the DBA_FGA_AUDIT_TRAIL view. An additional view, FLASHBACK_TRANSACTION_QUERY, is also new in Oracle Database 10g.

6.4.2.1. The DBA_FGA_AUDIT_TRAIL view

New Oracle Database 10g columns in DBA_FGA_AUDIT_TRAIL include:

STATEMENT_TYPE

Type of statement (e.g., SELECT, INSERT, UPDATE, or DELETE) being audited.

EXTENDED_TIMESTAMP

In addition to the regular timestamp, an extended timestamp including microseconds, time zone information, etc., is also available in this column.

PROXY_SESSIONID

If a user connects as a proxy when granted the proxy privileges as in the following, the proxy session ID is captured in this column:

 ALTER USER seeta GRANT CONNECT THROUGH geeta;

GLOBAL_UID

If the user is an Enterprise user, such as one defined in LDAP, the user ID will differ from the regular Oracle user ID. That enterprise user or global user ID is captured in this column.

INSTANCE_NUMBER

In a Real Application Clusters (RAC) environment, each instance may have an independent session ID; thus, the combination of instance number and session ID denotes a unique user session. In previous releases, the instance number was not captured, but in Oracle Database 10g, it is. (Note that in the user-defined audit trail we built earlier, we captured this information, as well.)

OS_PROCESS

Operating system process ID of the user session. This ID might be vital in case we want to examine an associated trace file.

TRANSACTIONID

Transaction ID used in flashback queries. (To understand this column, you need to understand something about the flashback query feature, which is described earlier in this chapter.)

6.4.2.2. The FLASHBACK_TRANSACTION_QUERY view

Another new Oracle Database 10g view, FLASHBACK_TRANSACTION_QUERY, shows the transactions that have occurred in the database. It contains these columns:

XID

Each transaction is identified by a unique number. This column records it as a RAW datatype.

START_SCN

System Change Number (SCN) at the beginning of the transaction.

START_TIMESTAMP

Timestamp at the beginning of the transaction.

COMMIT_SCN

SCN at the time the commit was issued.

COMMIT_TIMESTAMP

Timestamp when the commit was issued.

LOGON_USER

Oracle user who logged in to the session.

UNDO_CHANGE#

SCN number of the undo operation.

OPERATION

Type of operation (e.g., INSERT, UPDATE, DELETE). If this is a PL/SQL block, this column shows DECLARE or BEGIN.

TABLE_NAME

Name of the table referenced in the transaction.

TABLE_OWNER

Owner of the above table.

ROW_ID

Rowid of the row affected or changed.

UNDO_SQL

SQL statement that can undo the changes made by the statement. If the original SQL is an INSERT, the UNDO_SQL is a DELETE, and so on.

Although the information in this view is mainly used for flashback query operation (another new feature in Oracle Database 10g), it is helpful in FGA, as well. The column XID in this view indicates the unique transaction identifier, which is also captured in the TRANSACTIONID column of the DBA_FGA_AUDIT_TRAIL view. Using this column, you can correlate the two views and thus find out everything about the transaction that caused the audit trail to be created.

6.4.3. Combination of Columns

In the previous example, I specified a list of columns as follows:

     audit_column    => 'SALARY, COMM'

This indicates that if a user accesses either the SALARY or the COMM column, the action is logged. In some cases, however, you may have a more specific requirement that asks for logging only if all of the columns named in the list are referenced, not just any one of them. For instance, in the EMP database, you may want FGA to write a trail only if someone accesses SALARY and EMPNAME together. That may be because if only one column is accessed, the action is not likely to uncover sensitive information (typically, the user needs a name to match to a salary). Suppose that the user issues this query:

     SELECT salary FROM hr.emp;

This displays the salaries of all of the employees, but without names next to salaries, the information is probably not very helpful. Similarly, suppose that the user issues:

     SELECT empname FROM hr.emp;

This returns employee names, but without the salary column, the salary information is protected. However, if the user issues:

     SELECT empname, salary FROM hr.emp;

this query will enable the user to see the salaries of all of the employees, the very information that is supposed to be protected. In this last case (but not in the first two), the audit trail provides meaningful information and should therefore be generated.

In Oracle9i Database, there was no way to specify a combination of columns as an audit condition. In Oracle Database 10g, this can be done through the audit_column_opts parameter in the ADD_POLICY procedure. By default, the value of that parameter is DBMS_FGA.ANY_COLUMNS, which triggers an audit trail if any of the columns is referenced. However, if you override that default by specifying DBMS_FGA.ALL_COLUMNS as the value, the audit trail is generated only if all of the columns are referenced. In our example, if I want an FGA policy that creates an audit record only if the user selects both the SALARY and the EMPNAME columns, I can create the policy as follows:

     BEGIN        DBMS_FGA.add_policy (object_schema          => 'HR',                  object_name            => 'EMP',                  policy_name            => 'EMP_DML',                  audit_column           => 'SALARY, COMM',                  audit_condition        => 'SALARY >= 150000 OR EMPID = 100',                  statement_types        => 'SELECT, INSERT, DELETE, UPDATE',                                    audit_column_opts      => DBMS_FGA.all_columns                   );     END;     /

This feature is extremely useful in focusing audit activity very tightly on the key conditions for an application (and reducing audit trail volume).

To accommodate this new feature, the DBA_AUDIT_POLICIES view in Oracle Database 10g has a number of additional columns, listed below:

SEL

Indicates whether the policy triggers an FGA audit trail if data is SELECTed (YES or NO).

INS

Same as above but for INSERTs.

UPD

Same as above but for UPDATEs.

DEL

Same as above but for DELETEs.

AUDIT_TRAIL

The columns SQL_TEXT and SQL_BIND are populated only if the parameter AUDIT_TRAIL is set to DB_EXTENDED (the default), as opposed to DB. This column displays the value of that parameter.

POLICY_COLUMN_OPTIONS

Indicates whether all the columns must be referenced in order to trigger an audit trail or any one of them.