Oracle PL/SQL for DBAs

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 ...