不错的一篇文章，值得一看

6.5. FGA and Other Oracle Auditing Techniques

In this section, I describe how FGA stacks up against other types of auditing technologies provided by Oraclein particular, triggers and traditional auditingand why you might choose one approach over another.

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

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 data is changed or not. If data is changed first and then rolled back, triggers will not capture the change, unless you are using autonomous transactions. FGA inserts records into the audit trails using autonomous transactions, so those accesses are recorded anyway. In highly secure environments, this may be a requirement.

Number of trail entries

After a change takes place, row triggers will insert one record into the audit trail for each row changed. With FGA, only one record will be inserted for one statement, regardless of how many rows are affected. This approach limits the size of the trail.

Relevant columns

If you want only certain columns to be tracked and not others, you can use the WHERE clause of the triggers; for example, to track changes to the SALARY column only, you can recode the trigger as:

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

With FGA, you can simply specify the parameter:

    audit_columns => 'SALARY'

Both achieve the same thingthat is, they track changes only if the SALARY column is accessed. But there is an important difference. If the SALARY column is not changed, but is simply accessed in a predicate, FGA will capture this fact but triggers will not.

Views

Now suppose that there is a view named VW_EMP on the EMP table. The view is available to end users but the table is not, so you might have defined a set of INSTEAD OF triggers to manipulate the table when users manipulate the data in the view. However, if an INSTEAD OF trigger changes a data element, the auditing triggers defined on the table will not be able to see that change and thus will not be able to track it. However, FGA will capture any change, regardless of how it is made.

Is FGA always better than triggers at tracking granular changes? Not necessarily. In most cases, FGA does easily provide the fine-grained auditing mechanism necessary to satisfy your requirements, but there are some cases in which triggers work better than FGA:

Eliminate the need for a larger undo segment

When the user Scott changes a piece of data, how do you know what the value of the data was before the change? As discussed earlier, you can use Oracle's flashback query feature to get the old value from the table using the SCN number captured in the audit trail . For example, to determine the value of SALARY before Scott changed it, you would issue:

    SELECT salary FROM emp AS OF scn 14122310350 WHERE empid = 100;

This change occurred on June 6th and today is June 20th, 14 days after the change. Because the flashback query uses undo segments to reconstruct the data, the data must be available there. In this case, because you are flashing back the data 14 days into the past, you would have had to set the UNDO_RETENTION_PERIOD initialization parameter to 14 days. This, in turn, requires that the undo tablespace be large enough to hold that much undo information. In most organizations, having such a large undo tablespace may pose an operational problem. Another complication is that the undo information will be lost if the database is shut down.

If you use triggers, rather than FGA, to capture such changes, the old values can easily be stored in the audit trail itself, and you won't need such a large undo tablespace. The old values will also persist across database shutdowns. In such cases, you will probably find that using triggers makes more sense than using FGA.

Store selective data

Undo information is captured for all changes to the databasenot just changes you are interested in. In a very active database, the undo generation could be quite significant, and it's possible that the information you need may be flushed out of undo segments before it is used in the flashback query.

As mentioned in the previous item, with triggers, the data will be immediately stored in the tables, so it will never be flushed out.

Avoid audit entries on rollback

Under some circumstances, FGA will generate a much larger and less useful audit trail than you would end up with using triggers. Consider the situation in which Scott issues the following DML statement:

    UPDATE hr.emp SET salary = 14000 WHERE empid = 100;

If the EMP table has an FGA policy defined on it, this statement generates an audit trail entry. Because FGA trails are entered as autonomous transactions, the entry in the audit trail is committed regardless of what happens in Scott's transaction. Now, suppose that Scott issues a rollback instead of committing the update. In effect, he did not update the row; nevertheless, the entry in the audit trail is committed and is not rolled back. The result is that false positives are created for audit entries.

If you use a trigger instead, the insertion of the audit entry is part of the same transaction, so it will be rolled back if the main transaction rolls back, thus eliminating the possibilities of false positives. If a system does a lot of changes and then rolls back frequently, you will end up with a large audit trail under FGAand most of it will be false. It makes sense to use triggers in such cases.

It's clear that FGA does not entirely replace a trigger-based approach to auditing; each has its place. When you are deciding where to implement FGA, consider these differences carefully and see which approach best fits your needs in each situation.

6.5.2. FGA Compared with Regular Auditing

Starting with Oracle Database 10g Release 1, Oracle's regular auditing (using the AUDIT command) has been enhanced so that it captures more information than it did in previous releasesfor example, the text of the SQL statement issued, the bind variables, and more. In many respects it looks identical to FGA. Does it, therefore, obviate the need for FGA? Not at all. Let's look at the differences between regular and fine-grained auditing.

Types of statements

Regular auditing can track many different types of statementsDML, DDL, session control statements, privilege management statements, and so on. FGA, on the other hand, can track only one statement (SELECT) in Oracle9i Database and four (SELECT, INSERT, UPDATE, DELETE) in Oracle Database 10g.

Special parameters

FGA runs out of the box without requiring any special parameters. The FGA_LOG$ table, the repository for FGA audit entries, is already present in the SYS schema. In contrast, regular auditing must first be enabled at the database level before individual objects can be audited. You do this by setting the initialization parameter AUDIT_TRAIL. Because this parameter is not dynamic, you must also restart the database for it to take effect.

Success or failure

Regular auditing can be set up so that auditing is done regardless of the success or failure of the user's action. With FGA, tracking is done only if the action is successful.

Disable/enable

FGA can be temporarily disabled and enabled at will, but regular auditing cannot be. To pause regular auditing, you have to use the NOAUDIT command on the object. Later on, if you want to reapply the audit settings, you will have to remember what you specified in the first place because that information is lost.

Selective row auditing

FGA records access each time a user issues a statement, regardless of the number of rows changed. Regular auditing has an option that allows you to specify that an audit entry be written on either a one-row-per-access or a one-row-per-session basis. Regular auditing thus has the potential to reduce the size of the audit trail.

Database tables or OS files

Regular auditing can be set up to record its trails on either database tables (AUDIT_TRAIL=DB) or operating system files (AUDIT_TRAIL=OS). The latter is useful in situations where an auditor, rather than the DBA, will be accessing the trails. Using operating system files is also helpful because the trails will then be able to be backed up using file backup tools. In Windows, if AUDIT_TRAIL=OS, the audit trails are recorded in the Event Log and are accessed very differently. Using the OS option is a way to protect the integrity of the audit trails. Unlike the regular audit trails, the FGA audit trails can only be written to the database table FGA_LOG$. You can create handler modules in FGA to write to files, but because such modules are writable by the Oracle software owner, they are not safe from potential abuse by the DBA.

Default objects

Regular auditing can be set up for default objects, which apply to yet-to-be-created objects. For instance, you could issue "AUDIT UPDATE ON DEFAULT;", which indicates that the database should turn on auditing on updates for all tables, even for tables that have not yet been created. When a new table is created, it is automatically put under audit control for updates. In FGA, you can create policies only on tables or views that already exist.

Selective columns

Regular auditing does not have a way to discriminate between granular actions, such as whether a particular column was accessed. With FGA, you can audit only particular, relevant columns, which makes it possible to keep your trails at a manageable size.

SQL text and bind variable capture

As I've shown, FGA captures the text of SQL statements issued and the values of any bind variables . This is the default behavior, which you can change by setting the audit_trail parameter in the ADD_POLICY procedure of the DBMS_FGA package . In regular auditing, however, you must set the AUDIT_TRAIL database initialization parameter to DB_EXTENDED to capture those values, and you will have to shut down and restart the database for it to take effect.

Privileges

To issue a regular auditing command, a user needs the AUDIT SYSTEM or AUDIT ANY system privilege . To use FGA, however, a user needs only the EXECUTE privilege on the DBMS_FGA package.

As you can see, FGA differs considerably from regular auditing even though in Oracle Database 10g, the trails from both are very similar. Because they are so similar, in Oracle Database 10g, a new view, DBA_COMMON_AUDIT_TRAIL, shows entries from both regular and FGA auditing together.