Hi list. I just want to send to you an sql file containing tools for audit the UPDATE and DELETE
statements in a database by saving all the modifications made by a network/system/database user.
I hope you find it useful
El Santi
=====
Santiago Cassina
Responder a: ct***@yahoo.com
__________________________________________________ _______
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
/* Expañol */
/* El tan preciado trigger de auditorías, habría que pegarle una chequeada y mejorarlo un poguito más */
/* Santiago Cassina - Salta - Argentina - 26 de Agosto de 2004 */
/* mail: sc******@yahoo.com - ct***@hotmail.com */
/* Este trigger se ejecuta ANTES de un UPDATE, DELETE o INSERT y modifica una función previamente creada, la cual se ejecuta */
/* después de la acción. Esta función guarda la fecha y hora de la acción, el OID del registro, el nombre del usuario, el nombre del esquema, */
/* el nombre de la tabla, el nombre del campo afectado, los datos que tenía antes de la modificación y los datos luego de */
/* la modificación del registro, también el ID del proceso por si se quiere obtener más información desde el archivo.log de postgresql */
/* como ser la IP de la placa de red y otras cosillas. En caso de un DELETE, solamente modifica la columna "estado" de la tabla */
/* afectada asignándole un "*", o sea que el DELETE realmente no se realiza, sino que se marca el registro borrado, para una buena */
/* implementación de este método se deben filtrar los registros a mostrar en nuestra aplicación para ocultar aquellos que */
/* ya han sido "borrados" agregando la cláusula "where estado<>'*'" */
/* NOTA 1: si el administrador de la base de datos es el que va a realizar múltiples updates se recomienda deshabilitar este */
/* trigger ya que hará crecer la tabla de auditorías de manera abrupta, pues guardará TODOS los cambios que se realicen */
/* NOTA 2: al usar phpPgAdmin, quise borrar un registro haciendo click en el link "Eliminar" en un registro, y el trigger */
/* no se ejecutó como esperaba, ni borró el registro ni actualizó la columna "estado" con un "*". */
/* Supongo que será un error de "compatibilidad" del phpPgAdmin con este trigger :-) Realmente no sé que pasa :-P */
/* Fin Expañol */
/* English Translation */
/* THE TRANSLATION TO ENGLISH... My apologies... I hope you find it useful :-) */
/* The most wanted trigger!!! You maybe want to re-check and modify... be free! */
/* Santiago Cassina - Salta - Argentina - August, 26 - 2004 */
/* mail: sc******@yahoo.com - ct***@hotmail.com */
/* This trigger is executed before an UPDATE, DELETE or INSERT statement modifying a previously created function, wich is executed */
/* after the statement. This function saves, in the audit table, the statement's date and time, */
/* the record OID, the user name, the schema name, the affected table name, the affected field name, */
/* the data in the field before the statement execution and the data in the field after the statement execution */
/* and (maybe the most important thing) the process ID (PID) in case you want to obtain extra info about */
/* IP, MAC and other things (it's up to you and your brain capacity :-). Don't forget to modify the postgresql.conf */
/* to obtain the desired log-level. In case the user executes a DELETE statement, it saves in the "estado" field of */
/* the affected table a "*" character, indicating the field has been erased (Not phisically but logically for the "dummy user". */
/* Obviously you must filter the records with an "*" in the "estado" field if you don't want a user deletes the same register again and again. */
/* Note 1: be careful! If the database administrator runs an large update with this trigger, may experience a slow performance */
/* because the trigger is saving ALL activitie into the audit table, so... disable it, execute the update and enable it again! */
/* Note 2: using phpPgAdmin I wanted to delete a record by clicking on the "Delete" link on the record and... the trigger does nothing??? */
/* The trigger will not delete the record, will not update the "estado" field with an "*" character */
/* I guess it's a "compatibility" error between phpPgAdmin and this trigger :-) I really don't know :-P */
/* End of English Translation */
/* Creación del lenguaje */
/* We create the language */
CREATE OR REPLACE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
'$libdir/plpgsql' LANGUAGE C;
DROP LANGUAGE plpgsql CASCADE;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler;
/* Creamos el esquema */
/* We create the scheme */
CREATE SCHEMA auditorias AUTHORIZATION admin;
/* Creación de la función pdauditar inicial, ésta será modificada por la función pdcrear */
/* We create the initial pdauditar function, this function is going to be modified by the pdcrear function */
CREATE OR REPLACE FUNCTION auditorias.pdauditar () RETURNS TRIGGER AS '
BEGIN
RAISE EXCEPTION ''?A?N NO SE HA INICIALIZADO LA TABLA %!'', TG_RELNAME;
END;
' LANGUAGE 'plpgsql';
/* Creación de la función pdcrear, ésta es la que modifica pdauditar */
/* We create the pdcrear function, this function is going to modify the pdauditar function on the statement execution */
CREATE OR REPLACE FUNCTION auditorias.pdcrear () RETURNS TRIGGER
AS '
DECLARE
idaudit integer;
audschema varchar := ''auditorias.'';
consulta varchar;
k integer;
nuevo record;
cols record;
gatillo varchar := '''';
funcion varchar := '''';
esquema varchar := '''';
antes text;
id integer;
columna varchar;
viejo varchar;
BEGIN
SELECT INTO idaudit COUNT(*)+1 from auditorias.audit;
IF idaudit > 1 THEN
SELECT INTO idaudit max(idaccion)+1 from auditorias.audit;
END IF;
--AQUI COMIENZA LA FUNCION pdauditar
--HERE STARTS THE pdauditar FUNCTION
FUNCION := ''CREATE OR REPLACE FUNCTION auditorias.pdauditar() RETURNS TRIGGER AS ''''
DECLARE
CONSULTA varchar;
fecha timestamp;
tipo varchar;
usuario varchar;
esquema varchar;
id integer;
pid integer;
query varchar;
columna varchar;
BEGIN
select into fecha current_timestamp(0);
select into usuario session_user;
select into pid pg_backend_pid();
select into esquema nspname FROM pg_class bc,
pg_namespace ns WHERE
bc.relnamespace = ns.oid AND bc.oid = TG_RELID;
IF TG_OP = ''''''''INSERT'''''''' THEN
tipo:=''''''''A'''''''';
select into id new.oid;
END IF;
IF TG_OP = ''''''''UPDATE'''''''' THEN
tipo:=''''''''M'''''''';
select into id old.oid;
END IF;'';
--AQUI LA DEJAMOS POR UN TIEMPO
--HERE WE STOP THE CREATION FOR A WHILE
IF TG_OP = ''DELETE'' THEN
select into esquema nspname FROM pg_class bc, pg_namespace ns WHERE
bc.relnamespace = ns.oid AND bc.oid = TG_RELID;
select into id old.oid;
--AQUI, EN CASO DE UN DELETE, SE ACTUALIZA LA COLUMNA "ESTADO" DE LA TABLA Y SE FINALIZA EL TRIGGER
--IN CASE OF A DELETE STATEMENT, THE FUNCTION UPDATES THE "ESTADO" FIELD ON THE TABLE AND ENDS THE TRIGGER EXECUTION
FUNCION := ''update '' || esquema || ''.'' || TG_RELNAME || '' set estado=''''*''''
where oid = '' || old.oid;
EXECUTE FUNCION;
RETURN OLD;
ELSE
IF TG_OP = ''UPDATE'' THEN
viejo:=''OLD.'';
END IF;
IF TG_OP = ''INSERT'' THEN
viejo:=''NEW.'';
END IF;
FOR cols IN select attname as nombre, attnum as numero from pg_attribute
where attrelid = TG_RELID
and NOT attisdropped
and attnum > 0
LOOP
--LA PARTE COMPLEJA DE LA FUNCION
--THE COMPLEX... THE HARD... THE UGLY...
FUNCION := FUNCION || ''
select into columna attname as nombre from pg_attribute
where attrelid = TG_RELID
and NOT attisdropped
and attnum = '' || cols.numero || '';
IF NEW.'' || cols.nombre || ''<>'' || viejo || cols.nombre || '' THEN
CONSULTA := ''''''''INSERT INTO '' || audschema || ''audit
values ('' || idaudit || '','''''''' || quote_literal(tipo)
|| '''''''','''''''' || quote_literal(fecha)
|| '''''''','''''''' || quote_literal(usuario)
|| '''''''','''''''' || quote_literal(esquema
|| ''''''''.'''''''' || TG_RELNAME) || '''''''','''''''' || id
|| '''''''','''''''' || quote_literal(columna)
|| '''''''','''''''' || quote_literal('' || viejo || cols.nombre || '')
|| '''''''','''''''' || quote_literal(NEW.'' || cols.nombre || '')
|| '''''''','''''''' || pid
|| '''''''')'''''''';
select into query current_query from pg_stat_activity where procpid = pid;
--SI QUEREMOS VER LA FUNCION SIN EJECUTARLA NO DEBEREMOS COMENTAR LA SIGUIENTE LINEA
--IF WE WANT TO SEE THE FUNCTION STRING, JUST UN-COMMENT THE NEXT LINE
--RAISE EXCEPTION ''''''''PID: %, Query: %'''''''',pid,FUNCION;
EXECUTE CONSULTA;
END IF;'';
END LOOP;
FUNCION := FUNCION || '' RETURN NEW;
END;'''' LANGUAGE plpgsql SECURITY INVOKER;'';
--SI QUEREMOS VER LA FUNCION SIN EJECUTARLA NO DEBEREMOS COMENTAR LA SIGUIENTE LINEA
--IF WE WANT TO SEE THE FUNCTION STRING, JUST UN-COMMENT THE NEXT LINE
--RAISE EXCEPTION ''FUNCION: %'',funcion;
EXECUTE FUNCION;
RETURN NEW;
END IF;
--EXECUTE CONSULTA;
--RAISE EXCEPTION ''FUNCION: %'',funcion;
END;
'
LANGUAGE plpgsql;
/* Español */
/* Estructura de la tabla audit, la tabla que guarda todas las modificaciones hechas, deberemos crear el esquema "auditorias" */
/* idaccion: el número de acción ejecutada... autonumérico :-P */
/* tipo: el tipo de accion: M: Modificacion, A: Inserción */
/* fecha: la fecha y hora de la acción */
/* usuario: el nombre del usuario que realizó la modificación */
/* tabla: el esquema y la tabla modificada */
/* campo: el nombre del campo modificado */
/* antes: la información que contenía el campo antes de la modificación */
/* despues: la información del campo después de la modificación */
/* pid: el id del proceso que ejecutó la acción */
/* Fin Español */
/* English Translation */
/* The audit table structure, this table is going to save all the modifications done to our "secure" tables */
/* We must have the "auditorias" scheme created */
/* idaccion: autonumeric :-P */
/* tipo: action type: M: Update, A: Insert */
/* fecha: date and time */
/* usuario: user name */
/* tabla: scheme and table names */
/* campo: field name */
/* antes: the data in the field before the statement execution */
/* despues: the data in the field after the statement execution */
/* pid: the statement execution process ID */
/* End of English Translation */
CREATE TABLE auditorias.audit (
idaccion integer DEFAULT '0' NOT NULL,
tipo character(1) DEFAULT '' NOT NULL,
fecha timestamp without time zone NOT NULL,
usuario character varying DEFAULT '' NOT NULL,
tabla character varying(20) DEFAULT '' NOT NULL,
id character varying(20) DEFAULT '' NOT NULL,
campo character varying,
antes text,
despues text,
pid integer
);
/* Agregamos la columna "estado", obviamente la tabla no debe tener ésta columna */
/* We add the "estado" field, obviously it hasn't to be on the table already... */
ALTER TABLE ESQUEMA.TABLA ADD COLUMN estado character;
/* Eliminamos el NULL de la columna "estado" */
/* Change the value NULL of the "estado" field */
update ESQUEMA.TABLA set estado = '';
/* Restringimos los valores NULL de la columna "estado" */
/* We set to "NOT NULL" the "estado" field*/
ALTER TABLE ESQUEMA.TABLA ALTER COLUMN estado SET NOT NULL;
/* Estas dos lineas deberan correrse para cada tabla que se desee auditar */
/* This two lines set the table "secure", just replace "ESQUEMA.TABLA" string with the desired "SCHEME.TABLE" */
create trigger tgauditar after insert or update on ESQUEMA.TABLA for each row execute procedure auditorias.pdauditar();
create trigger tgcrear before insert or delete or update on ESQUEMA.TABLA for each row execute procedure auditorias.pdcrear();
/* Informe de Tablas, Columnas y Tipos de datos de cada columna */
SELECT nspname, relname as tabla,
attname as columna,
attnum as numero, typname as tipodatos
FROM
pg_class bc,
pg_attribute ta,
pg_namespace ns,
pg_type ty
WHERE
ta.attrelid = bc.oid
and ta.attnum > 0
and not ta.attisdropped
and relam = 0
and bc.relnamespace = ns.oid
and bc.relname not like 'pg_%'
and ta.atttypid = ty.oid
order by nspname, relname, attnum;
/* Informe de Esquemas y Tablas */
SELECT nspname as esquema, nspacl as aclesquema, relname as tabla, reltuples as filas, relacl as acltabla,
usename as propietario
FROM
pg_class bc,
pg_namespace ns,
pg_shadow us
WHERE
bc.relnamespace = ns.oid
-- and ns.nspname = 'rurbano' /*SCHEME*/
and relam = 0
-- AND bc.relname = 'cuadras' /*RELATION*/
and nspname not like 'pg_%'
and us.usesysid = bc.relowner
order by nspname, relname
;
--select * from pg_constraint where
/*bc.reltuples = cantidad de filas*/
/* Los dos informes anteriores pero ahora cruzados en uno solo */
SELECT nspname as esquema, nspacl as aclesquema, relname as tabla, reltuples as filas, relacl as acltabla,
usename as propietario, attname as columna,
attnum as numero, typname as tipodatos
FROM
pg_class bc,
pg_attribute ta,
pg_namespace ns,
pg_shadow us,
pg_type ty
WHERE
ta.attrelid = bc.oid
and ta.attnum > 0
and not ta.attisdropped
and bc.relnamespace = ns.oid
-- and ns.nspname = 'rurbano' /*SCHEME*/
and relam = 0
-- AND bc.relname = 'cuadras' /*RELATION*/
and nspname not like 'pg_%'
and us.usesysid = bc.relowner
and ta.atttypid = ty.oid
order by nspname, relname, attnum
;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
