Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page