Need to edge out the competition for your dream job? Train for certifications today.
-- FGA example // mrjoltcola
-- Use the EMP schema (SCOTT/TIGER) to demonstrate fine-grained auditing
-- Create SCOTT schema if you haven't yet
-- Check database to see if both AUDITING and/or FGA support is setup
show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:\ORACLE\ADMIN\DEV\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
-- We don't have to have standard auditing on, but to use standard auditing, but set AUDIT_TRAIL above
-- to db_extended so we get fine grained audit plus SQL_BIND, (bind vars) and SQL_TEXT auditing
-- which is important for this purpose. Then restart database
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
-- To use FGA, we use FGA policies
-- Add a SALARY policy to the EMP table
-- Run as SYS or another user with execute on package dbms_fga
-- Default action is to audit SELECT but with 10g we can add DML actions by adding to statement_types parameter
-- Setup a policy to audit access of Employee Salaries
audit_condition => NULL, -- TRUE
audit_column => 'SAL',
statement_types => 'SELECT,INSERT,UPDATE,DELETE'
-- Scott selects salaries and then gives self a raise
select * from emp;
update emp set sal = 30000 where ename = 'SCOTT';
-- DBA connects and checks audit trail
connect / as sysdba
-- Find who ran what against EMP table
select timestamp, db_user, sql_text from dba_fga_audit_trail where object_name = 'EMP';
/* You will see...
01-APR-09 SCOTT select * from emp
01-APR-09 SCOTT update emp set sal = 30000 where ename = 'SCOTT'
Open in new window
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.