Oracle PL/SQL for DBAs

FGA Compared with Triggers

Traditionally, database changes resulting from DML statements have been audited using triggers. Row-level triggers on DML statements such as INSERT, UPDATE, and DELETE are able to capture the username, the timestamp, what changed, and other information. Here is an example of how a trigger is used to capture changes on the EMP table.

    CREATE OR REPLACE TRIGGER tr_ar_iud_emp
       AFTER INSERT OR DELETE OR UPDATE
       ON emp
       FOR EACH ROW
    BEGIN
       INSERT INTO audit_trail
            VALUES (USER, SYSDATE, 'EMP', USERENV ('ip_address')
                  -- ... and so on ...
                   );
    END;
    /

Starting with Oracle Database 10g, FGA can also record such information for DML statements—the timestamp of the change, the IP address, and more. Does FGA thus obviate the need for triggers? Not quite. As you will see in the following sections, each approach has its advantages and disadvantages.

The case for FGA

First let’s look at FGA. FGA-based auditing has some distinct benefits over triggers:

Tracking of non-DML queries

The first benefit is one I’ve noted a number of times in this chapter: FGA can track and record users when they select from a table, not just when they update it. Triggers do not fire when someone issues a SELECT, so they are useless if you need to audit SELECTs.

Ease of coding

Triggers require extensive coding and maintenance based on your specific requirements. FGA, on the other hand, requires little coding.

Differing events

Triggers are fired only when data changes, whereas FGA audits regardless of whether ...