Oracle Tips by Burleson

Chapter 13 Oracle 10g Enhancements

The query selected all the columns, including the relevant columns SSN and CLAIM_AMOUNT; hence the restrictions were applied.

• select count(*) from claims;

Not Restricted. The actual count of the records in the table is returned. Since the user did not specifically select any of the relevant columns, the restrictions were not placed.

• select claim_id from claims;

Not Restricted. Since the query has no reference to SSN or CLAIM_AMOUNT, which are the relevant columns, all the records will be returned.

• select claim_id from claims where SSN = ‘123456789’;

Restricted. Although the query did not specify the columns SSN or CLAIM_AMOUNT, the predicate has the SSN column; hence the restrictions are placed.

Auditing Enhancements

Oracle 10g supports a new type of auditing known as Extended Database Auditing. If specified, the regular AUDIT features also capture the exact SQL statements, even the bind variables. The option is specified at the instance level in the initialization file as

audit_trail = db_extended

The audit trails now provide a lot more information. The aud$ table has been expanded with several additional columns. Table 13.1 shows the additional columns of aud$ and their meaning.