A. value base auditing
The standard database auditing just described can catch the fact that a command was executed against a table, but not necessarily the rows that were affected. For example, issuing AUDIT INSERT ON HR.EMPLOYEES will cause an audit record to be generated whenever a row is inserted into the named table, but the record will not include the actual values of the row that was inserted. On occasion, you may want to capture these. This can be done by using Value-based auditing. It used database triggers for its functioning.
When a user inserts, updates, or deletes data from a table with the appropriate trigger attached, the trigger works in the background to copy audit information toa table that is designed to contain the audit information. Value-based auditing tends to degrade performance more than standard database auditing because the audit trigger code must be executed each time the insert, update, or delete operation occurs.
--creation of trigger_table
SQL> create table system.audit_employees (osuser long, changedt date, ipaddr varchar2(90), newsalary varchar2(500));
Table created.
SQL>
--creation of trigger
SQL> CREATE OR REPLACE TRIGGER system.hrsalary_audit
2 AFTER UPDATE OF salary
3 ON hr.employees
4 REFERENCING NEW AS NEW OLD AS OLD
5 FOR EACH ROW
6 BEGIN
7 IF :old.salary != :new.salary THEN
8 INSERT INTO system.audit_employees
9 VALUES (sys_context('userenv','os_user'), sysdate,
10 sys_context('userenv','ip_address'),
11 :new.employee_id ||
12 ' salary changed from '||:old.salary||
13 ' to '||:new.salary);
14 END IF;
15 END;
16 /
Trigger created.
SQL>
--now if anyone change the salary in the hr.employees table the entry will be made in the system's audit_employees table.
SQL> update hr.employees set salary=4500 where employee_id=124;
1 row updated.
--entries done in the audit table.
SQL> select * from audit_employees;
OSUSER CHANGEDT IPADDR NEWSALARY
------------------------------ --------- ------------ -------------------------------------
KSNDMCPC2012004\Admin 16-FEB-13 124 salary changed from 5800 to 4500
Fine grained auditing
Database auditing can capture all accesses to a table, whether SELECT or DML
operations. But it cannot distinguish between rows, even though it might well be that
only some rows contain sensitive information.
Fine-grained auditing, or FGA, can be configured to generate audit records only when
certain rows are accessed, or when certain columns of certain rows are accessed. It can
also run a block of PL/SQL (through audit handler) code when the audit condition is breached. FGA is configured with the package DBMS_FGA. To create an FGA audit policy,
use the ADD_POLICY procedure, which takes these arguments:
OBJECT_SCHEMA:- The name of the user who owns the object to be audited. This
defaults to the user who is creating the policy.
OBJECT_NAME:- The name of the table to be audited.
POLICY_NAME:- Every FGA policy created must be given a unique name.
AUDIT_CONDITION:- An expression to determine which rows will generate an audit
record. If left NULL, access to any row is audited.
AUDIT_COLUMN:- A list of columns to be audited. If left NULL, then access to any
column is audited.
HANDLER_SCHEMA:- The username that owns the procedure to run when the audit
condition is met. Default is the user who is creating the policy.
HANDLER_MODULE:- A PL/SQL procedure to execute when the audit condition is met.
ENABLE:- By default, this is TRUE: the policy will be active and can be
disabled with the DISABLE_POLICY procedure. If FALSE, then the ENABLE_POLICY procedure must be used to activate the policy.
STATEMENT_TYPES:- One or more of SELECT, INSERT, UPDATE, or DELETE to define
which statement types should be audited. Default is SELECT only.
AUDIT_TRAIL:- Controls whether to write out the actual SQL statement and its
bind variables to the FGA audit trail. The default is to do so.
AUDIT_COLUMN_OPTS:- Determines whether to audit if a statement addresses any or all
of the columns listed in the AUDIT_COLUMNS argument. Options
are DBMS_FGA.ANY_COLUMNS, the default, or DBMS_FGA_ALL_COLUMNSThe other DBMS_FGA procedures are to enable, disable, or drop FGA policies.
To see the results of fine-grained auditing, query the DBA_FGA_AUDIT_TRAIL view.
For example for FGA auditing click on following link
http://vishwanath-dbahelp.blogspot.in/2011/09/setting-fga-at-schema-level.html
