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 ONgeneric_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 '||table_name); dbms_output.put_line('actionid '|| action_id); dbms_output.put_line('username '|| username); dbms_output.put_line('tablekey '|| table_key); dbms_output.put_line('eventid '|| event_cdr_id); dbms_output.put_line('timestamp '|| timestamp); INSERT INTO event_table_audit_t (event_id, timestamp, table_name, table_key, action) VALUES (event_id, timestamp, table_name, table_key, action_id); END; /
This example is based upon a production row-level auditing trigger. In this case, an audit trail record is written for every insert, update, and delete. If you use this example, please remember to replace the placeholders generic_table and primary_key with your actual table name and the columns in your primary key. We are also assuming that the primary key itself will never be updated.
A useful variant of this trigger would be to grab the old and new values for the columns being updated and to write those out to a table. This would allow you to know not only which row was changed, but also which columns and values were changed.