Database Reference
In-Depth Information
F New value of a field or tuple, is old value also needed here?
F Is it enough to record the user doing the change, or is the IP address and other
connection information also needed?
F How secure (tamper-proof) must the auditing info be? For example, does it need to be
kept separately, away from the database being audited?
Based on answers to the aforementioned questions, you can start selecting the right auditing
method from the ones presented next.
How to do it...
Collecting data changes from server log
F Set log_statement = 'mod' or to 'all' in the server log
F Collect all INSERT , UPDATE , DELETE , and TRUNCATE commands from log
F Alternatively, just set up a way to store the logs on either the database server or copy
them to another host
Collecting changes using triggers
F Write trigger function to collect new (and if needed also old) values from tuples and
save them to auditing table(s)
F Add such triggers to tables for which changes need to be tracked
F Sample (modified from "A PL/pgSQL Trigger Procedure For Auditing" sample in
postgreSQL manual)
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation text NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS
$emp_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'DEL', now(), user,
OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
 
Search WWH ::




Custom Search