Oracle Database Administration: The Essential Refe

Row-Level Auditing

Oracle’s built-in audit capability stops at the table level. That is, you can audit who has accessed a table, but not which row was accessed. However, Oracle has provided a mechanism that lets you build your own row-level audit facility. Through the use of after-insert, after-update, or after-delete triggers, you can capture this information yourself and write it out to a separate table. The following trigger is an example of an update trigger.

If you need to audit SELECT access to an individual row, you will have to do this from within your application. Here is an example trigger used to audit row-level inserts, updates, and deletes:

CREATE OR REPLACE TRIGGER log_actions                        
AFTER INSERT OR UPDATE OR DELETE                
ON generic_table                
FOR EACH ROW          
DECLARE               
   action_id char(1); 
   event_id int;  
   table_key varchar(80);                       
   timestamp date;    
   table_name varchar(30);                      
   username varchar(30);                        
BEGIN                 
   SELECT event_seq.nextval INTO event_id FROM dual;              
   SELECT sysdate INTO timestamp FROM dual;     
   SELECT user INTO username FROM dual;         
   table_name := 'generic_table;                  
   IF DELETING THEN   
        action_id := 'D';                       
        table_key := to_char(generic_table.primary_key);    
   END IF;            
   IF UPDATING THEN   
        action_id := 'U';                       
        table_key := to_char(generic_table.primary_key);    
   END IF;            
   IF INSERTING THEN  
        action_id := 'I';                       
        table_key := TO_CHAR(generic_table.primary_key); END IF; rem rem The debugging lines can be left in. They will not have an effect rem unless serveroutput is turned on in SQL*Plus rem dbms_output.enable(10000); dbms_output.put_line('tablename ...