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 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 ...
Get Oracle Database Administration: The Essential Refe now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.