add_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'SAL_SELECT_AUDIT',
audit_condition => 'instr(job_id,''_MAN'') > 0',
audit_column => 'SALARY'
);
end;
Chapter 9: Database Security and Auditing 339
Audit records for fine-grained auditing can be accessed with the data dictionary view DBA_
FGA_AUDIT_TRAIL. If you typically need to see both standard audit rows and fine-grained
auditing rows, the data dictionary view DBA_COMMON_AUDIT_TRAIL combines rows from
both types of audits.
To continue our example, the user TAMARA runs two SQL queries as follows:
SQL> select employee_id, first_name, last_name, email from hr.employees
2 where employee_id = 114;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- --------------
114 Den Raphaely DRAPHEAL
1 row selected.
SQL> select employee_id, first_name, last_name, salary from hr.employees
2 where employee_id = 114;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
114 Den Raphaely 11000
1 row selected.
The first query accesses a manager, but not the SALARY column. The second query is the
same as the first, but does access the SALARY column and therefore triggers the FGA policy, thus
generating one, and only one, row in the audit trail:
SQL> select to_char(timestamp,'mm/dd/yy hh24:mi') timestamp,
2 object_schema, object_name, policy_name, statement_type
3 from dba_fga_audit_trail
4 where db_user = 'TAMARA';
TIMESTAMP OBJECT_SCHEMA OBJECT_NAME POLICY_NAME STATEMENT_TYPE
-------------- -------------- -------------- ---------------- --------------
08/12/07 18:07 HR EMPLOYEES SAL_SELECT_AUDIT SELECT
1 row selected.
Pages:
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556