Friday, August 15, 2008

Auditing selects on a table in Oracle - including FGA

# Audit all selects by a user.

alter system set audit_trail='db,extended';
audit select by robert;

#Audit all selects on a table.

audit select on gateway.userhome by session;

3. Use FGA. Only audit selects on a specified table by a specified user if they include a specified attribute. This does need to check the audit_condition for every select, so will have a small impact to performance even if it does not save an audit record.

begin
dbms_fga.add_policy (
object_schema=>'ROBERT',
object_name=>'TEST_TABLE',
policy_name=>'TEST_FGA',
audit_column => 'SECRET',
statement_types => 'SELECT'
audit_condition=> 'sys_context(''USERENV'', ''SESSION_USER'') = ''ROBERT''',
);
end;
/