• Author / Uploaded
• Guadalupe Serna Perez

• Categories
• Gestión de tecnología de la información
• Software de gestión de datos
• Datos
• Datos de computadora
• Gestión de datos

Citation preview

Auditoria en PostgreSQL Constantemente, en muchas organizaciones y por diversos motivos, se requiere llevar un control de la Base de Datos con respecto a las transacciones que se realizan diariamente, estás se le hace referencia a las operaciones INSERT, UPDATE y DELETE de una tabla en especifico. Obtener este tipo de registro nos ayuda analizar la información con fines estadísticos y ante todo cualquier aspecto de la seguridad, este proceso se basa en simples mecanismos que se encargan de registrar cada una de las operaciones que son ejecutadas. Las funciones que se les pueden dar a este peculiar mecanismo de seguridad son muchas, dependen de las necesidades que se puedan presentar en un determinado momento, en aspectos básicos se puede monitorear cualquier actividad sobre un registro determinado, es un mecanismo adicional de respaldo, detectar fraudes, entre otros. Existen dos formas de como llevar a cabo dicho control de registro, pero para este momento vamos a explicar el mas simple, en primer lugar utilizaremos el lenguaje PL/PgSQL para entender el funcionamiento básico y entender sus limitaciones, el mecanismo avanzado utiliza el lenguaje PL/TCL el cual es mas apropiado para dicha tarea por su gran robustez al ser utilizado en los Triggers. Repasamos la lógica de este mecanismo en una serie de pasos, así será mas fácil asimilar la idea: 1. Crear el lenguaje PL/PgSQL dentro de la Base de Datos deseada. 2. Crear la tabla llamada “tbl_audit” con su respectiva estructura que se menciona mas adelante, la cual se encarga de guardar cada transacción que es realizada. 3. Crear una función que se encarga de obtener los datos de la transacción, procesarlos para que puedan ser analizados e insertarlos en la tabla "tbl_audit". 4. Crear el Trigger / Disparador en cada tabla que se desee llevar un control de las transacciones, obviamente no se debe crear en la tabla "tbl_audit". 5. Por ultimo, realizar una serie de consultas para comprobar y entender su funcionamiento. Suficiente información para empezar, manos a la obra, de aquí en adelante se indican cuales son los pasos para implementar esta solución: Crear un lenguaje de consulta PL/PgSQL a dicha Base de Datos, para ello escribimos el siguiente comando desde la consola del sistema: createlang -h localhost -U postgres plpgsql Peliculas

NOTA: EN caso de que se requiera eliminar el lenguaje de la base de datos utilizamos el siguiente comando: droplang -h localhost -U postgres plpgsql Peliculas

Crear una tabla llamada “tbl_audit”, donde se guardan cada una de las transacciones realizadas: CREATE TABLE tbl_audit ( pk_audit serial NOT NULL, "TableName" character(45) NOT NULL, "Operation" char(1) NOT NULL, "OldValue" text, "NewValue" text, "UpdateDate" timestamp without time zone NOT NULL, "UserName" character(45) NOT NULL, CONSTRAINT pk_audit PRIMARY KEY (pk_audit)) WITH (OIDS=FALSE); ALTER TABLE tbl_audit OWNER TO postgres;

Creamos una función en PL/PgSQL que nos permite insertar los datos de aquellos registros que son afectados cada vez que se realiza una acción de tipo INSERT, UPDATE y DELETE en una tabla determinada, la cual es definida en la creación del Trigger. CREATE OR REPLACE FUNCTION fn_log_audit() RETURNS trigger AS $$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO tbl_audit ("TableName", "Operation", "OldValue", "NewValue", "UpdateDate", "UserName") VALUES (TG_TABLE_NAME, 'D', OLD, NULL, now(), USER); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO tbl_audit ("TableName", "Operation", "OldValue", "NewValue", "UpdateDate", "UserName") VALUES (TG_TABLE_NAME, 'U', OLD, NEW, now(), USER); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO tbl_audit ("TableName", "Operation", "OldValue", "NewValue", "UpdateDate", "UserName") VALUES (TG_TABLE_NAME, 'I', NULL, NEW, now(), USER); RETURN NEW; END IF; RETURN NULL; END; $$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION fn_log_audit() OWNER TO postgres;

Crear el Trigger en todas las tablas menos en "tbl_audit", para este caso de ejemplo usamos la tabla tbl_atributos, indicando que será ejecutado el trigger antes de la ejecución de una instrucción INSERT, UPDATE y DELETE para cada registro y le asignamos la función anterior. CREATE TRIGGER tbl_atributos_tg_audit AFTER INSERT OR UPDATE OR DELETE ON tbl_atributos FOR EACH ROW EXECUTE PROCEDURE fn_log_audit(); Por ultimo realizaremos una serie de consultas para poner en practica el registro de transacciones: INSERT INTO tbl_atributos (fk_tipo, nombre) VALUES (1, 'Femenido'); UPDATE tbl_atributos SET nombre = 'Masculino' WHERE pk_atributo = 2; DELETE FROM tbl_atributos WHERE pk_atributo = 2; Al hacer una selección de los datos de la tabla tbl_audit podemos observar en cada uno de los registros la acción que se le realizó a una determinada tabla, cuales son los datos antiguos y nuevos como la fecha de cuando fueron registrados. Por otro lado se registra el usuario que realizo la acción sobre los datos, en este caso si el usuario que se conecto al manejador de base de datos es "postgres", será el responsable de los registros alterados, para corregir este "posible defecto" de poder utilizar otro "valor" que represente a un "usuario real del sistema" puede utilizar las variables de sesión que se mencionaron en un post anterior y ajusta la función "fn_log_audit".

http://www.swapbytes.com/2010/09/como-implementar-auditoria-simple-en.html