Oracle PL/SQL for DBAs

Using FGA with Flashback Query

Consider the following scenario to understand why using FGA with Oracle’s flashback query feature might be useful. Suppose that I (the DBA) look at the audit trail and notice that this SQL statement was issued by the user Scott:

    SELECT salary FROM hr.emp WHERE empid = 100;

I happen to be represented by the employee number 100, and I realize with a shock that Scott has taken a peek at my salary! Now, Scott has always wanted my job, so it’s no surprise that he checked out my salary. But then I think to myself: I just got a series of raises, changing my salary from 12,000 to 13,000, then 14,000, and finally 15,000, which gives me pause to wonder: did Scott see my new or old salary? And if he saw the old one, which one did he actually see—12,000, 13,000, or 14,000? If I now issue the same query Scott issued, I will see the current, updated value—15,000—not the old value that was present when he issued his own query.

Fortunately, Oracle9i Database offers a feature known as flashback query to help solve this dilemma. Using this feature you can select a value as of a specific time in the past, even though that value may have been updated and committed. Let’s see how it works.

Suppose the salary was 12,000 at first.

    SQL> SELECT salary FROM emp WHERE empid = 100;

        SALARY
    ----------
         12000

Then it was changed to 13,000 on June 10, as per a raise approved by my boss, and entered into the HR database.

    SQL> UPDATE emp set salary = 13000 WHERE empid = 100; 1 row updated. ...