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

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 statements—SELECT, INSERT, UPDATE, and DELETE—against 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.

Warning

Only simple predicates (i.e., those with just one condition) are ...

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