Oracle10g Fine-Grained Auditing Policy statement_type audit_condition dbms_fga.add

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Oracle 10g Apply a Fine-Grained Auditing Policy

BEGIN
dbms_fga.add_policy(
object_schema   => ‘HR’,
object_name     => ‘EMPLOYEES’,
policy_name     => ‘EMP_AUDIT,
audit_condition => ‘SALARY > 1500’,
audit_column    => ‘COMMISSION_PCT’,
handler_schema => ‘EMP_SCHEMA’,
handler_module => ‘ALERT_HR’,
enable          => TRUE,
statement_types => ‘SELECT,UPDATE,DELETE’);
END

The statement_type parameter is new in Oracle 10g since the select statement is the only available default option in Oracle9i. The audit_condition parameter is not required. However, if you do not specify an audit condition, the database will audit all statements that access the object. Delete statements are always audited because all columns are deleted together.

The following statement will not be audited since we are not auditing INSERT statements:

SQL> insert into employees
2 (employee_id, first_name, email, hire_date, job_id)
3 values (1234, ’DAN’, ’DLIU’, TO_DATE (‘11/30/2001’,’MM/DD/YYYY’), ’DBA’);

The following statement will not be audited since the update statement does not access the audited column commission_pct:

SQL> update employees
2 set salary = salary + 1000
3 where department_id = 30;

The following statement will not be audited since none of the rows returned by this statement meet the audit condition (salary > 1500).

SQL> select first_name, last_name, salary, commission_pct
2 from employees
3 where salary < 1500;

The following statement will be audited because:

Get the complete Oracle10g story:

The above text is an excerpt from "Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration", by Rampant TechPress. Written by top Oracle experts, this book has a complete online code deport with ready to use scripts.

To get the code instantly, click here:

http://rampant-books.com/book_2003_2_oracle10g.htm

Need an Oracle Mentor?

BEI is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle question.

Why risk an unplanned outage? You can now get telephone access to Don Burleson or any of his Oracle Certified DBAs with more than 20 years of full-time IT experience. Click here for details:

http://www.dba-oracle.com/service_oracle_backup.htm