Script para la generación de tablas de auditorias y triggers relacionados para implantar las llamadas "Auditorias de Aplicación".
El script se ha de llamar desde una sesión SQL conectada como usuario con rol DBA (system) así:
@genera_auditorias.sql ESQUEMA
Donde ESQUEMA es el nombre del esquema en mayúsculas al que vamos a generar las tablas y triggers de auditorias.
NOTAS:
El script genera los nombres de las tablas de auditorias como AUDI_[nombre_tabla_original].
Las tablas las genera bajo el mismo esquema que las tablas originales.
El tablespace utilizado es USERS.
Añade a cada tabla de auditorias las columnas USER_ID, HOST, OS_USER, AUD_FEC y AUD_DML, que se informarán a través de los triggers de la forma:
USER_ID: Id del usuario de bbdd.
HOST: Host desde el que se establece la conexión cliente contra el servidor de bbdd.
OS_USER: Usuario de SO con el que se establece la conexión.
AUD_FEC: Fecha-hora del cambio (insert, update, delete).
AUD_DML: Tipo de cambio, U=Update, D=Delete, I=Insert.
Los triggers se generan de forma que si el cambio es un UPDATE o un DELETE, se registrará en la tabla de auditorias los valores anteriores de las columnas (:OLD), si el cambio es un INSERT se registrará en la tabla de auditorias correspondiente los nuevos valores insertados (:NEW).
El último paso del script es el lanzamiento de los comandos de creación de tablas y triggers.
-------------------------------------------------------------------------------
--
-- Script: GENERA_AUDITORIAS.SQL
--
-- Propósito: CREA LAS TABLAS DE AUDITORIAS RELACIONADAS CON LAS TABLAS DEL ESQUEMA QUE SE PASA COMO
-- PARÁMETRO Y CREA LOS TRIGGERS NECESARIOS PARA LA INFORMACIÓN DE LAS MISMAS.
--
-- Para: 8.1.7 o superior
--
-------------------------------------------------------------------------------
set serveroutput on size 1000000
set termout on
set verify off
set feedback off
set echo off
set heading off
set pagesize 0
set linesize 1000
set pause off
set wrap on
column comando format a200
SET TRIMSPOOL ON
SPOOL CREA_AUDITORIAS_&&1..SQL
SELECT 'CREATE TABLE &&1..AUDI_'||TABLE_NAME||' TABLESPACE USERS AS SELECT A.*,SYSDATE AUD_FEC, '||CHR(39)||'D'||CHR(39)||' AUD_DML FROM &&1.'||TABLE_NAME||' A WHERE 0=1;' COMANDO
FROM DBA_TABLES WHERE OWNER='&&1';
SELECT 'ALTER TABLE &&1..AUDI_'||TABLE_NAME||' ADD (USER_ID NUMBER,HOST varchar2(30),OS_USER varchar2(30));' COMANDO
FROM DBA_TABLES WHERE OWNER='&&1' ;
SPOOL OFF;
SPOOL CREA_TRIGGERS_&&1..SQL
declare
esquema varchar2(100) ;
nombre_tabla varchar2(100);
crea_tabla varchar2(100);
columna varchar2(100);
par1 varchar2(100);
par2 varchar2(100);
par3 varchar2(100);
par4 varchar2(100);
par5 varchar2(100);
par6 varchar2(100);
par7 number;
par8 varchar2(4000);
par9 date;
par10 varchar2(200);
par11 varchar2(1);
sin varchar2(100);
per varchar2(100);
tipo varchar2(100);
precision varchar2(100);
length varchar2(100);
scale varchar2(100);
nullable varchar2(100);
cadena varchar2(100);
cursor usuarios is
select username
from dba_users
where username IN ('&&1');
cursor tablas (usuario in varchar2) is
select a.table_name
from
dba_tables a,
dba_part_tables b,
dba_extents c
where a.owner = usuario and
a.table_name = b.table_name(+) and
a.owner=b.owner(+) and
b.table_name is null and
b.owner is null and
a.table_name=c.segment_name and
a.owner=c.owner
and a.duration is null
group by a.table_name;
cursor columnas (usuario in varchar2, tabla in varchar2) is
select column_name,data_type, data_precision,data_length,data_scale,nullable
from sys.dba_tab_columns
where table_name = tabla
and owner = usuario and column_name not in ('USER_ID','HOST','OS_USER','AUD_FEC','AUD_DML')
order by column_id;
begin
dbms_output.put_line('PROMPT Comienzo a crear las TRIGGERS');
dbms_output.put_line('-----------------------------------------------------------');
open usuarios;
loop
fetch usuarios into esquema;
exit when usuarios%notfound;
dbms_output.put_line('-----------------------------------------------------------');
dbms_output.put_line('PROMPT ESQUEMA ... ' || esquema);
open tablas(esquema);
loop
fetch tablas into nombre_tabla;
exit when tablas%notfound;
dbms_output.put_line('-----------------------------------------------------------');
dbms_output.put_line('PROMPT Creando TRIGGER ... AUDI_' || nombre_tabla);
dbms_output.put_line('-----------------------------------------------------------');
dbms_output.put_line('CREATE OR REPLACE TRIGGER &&1..AUDI_'||nombre_tabla||' AFTER INSERT OR UPDATE OR DELETE ON '||nombre_tabla||' FOR EACH ROW ');
dbms_output.put_line(' BEGIN');
dbms_output.put_line(' IF INSERTING THEN ');
dbms_output.put_line(' INSERT INTO AUDI_'||nombre_tabla);
open columnas(esquema, nombre_tabla);
loop
fetch columnas into columna,tipo,precision,length,scale,nullable;
exit when columnas%notfound;
if columnas%rowcount = 1 then
cadena := '(' ;
else
cadena := ',' ;
end if;
cadena := cadena || columna;
dbms_output.put_line(cadena);
end loop;
close columnas;
dbms_output.put_line(',USER_ID,HOST,OS_USER,AUD_FEC,AUD_DML) ');
dbms_output.put_line(' VALUES');
open columnas(esquema, nombre_tabla);
loop
fetch columnas into columna,tipo,precision,length,scale,nullable;
exit when columnas%notfound;
if columnas%rowcount = 1 then
cadena := '(' ;
else
cadena := ',' ;
end if;
cadena := cadena || ':NEW.'||columna;
dbms_output.put_line(cadena);
end loop;
close columnas;
dbms_output.put_line(',sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'SESSION_USERID'||CHR(39)||'),sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'HOST'||CHR(39)||'), sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'OS_USER'||CHR(39)||'),SYSDATE,'||CHR(39)||'I'||CHR(39)||'); ');
dbms_output.put_line('END IF; ');
dbms_output.put_line(' IF UPDATING THEN ');
dbms_output.put_line(' INSERT INTO AUDI_'||nombre_tabla);
open columnas(esquema, nombre_tabla);
loop
fetch columnas into columna,tipo,precision,length,scale,nullable;
exit when columnas%notfound;
if columnas%rowcount = 1 then
cadena := '(' ;
else
cadena := ',' ;
end if;
cadena := cadena || columna;
dbms_output.put_line(cadena);
end loop;
close columnas;
dbms_output.put_line(',USER_ID,HOST,OS_USER,AUD_fEC,AUD_DML) ');
dbms_output.put_line(' VALUES');
open columnas(esquema, nombre_tabla);
loop
fetch columnas into columna,tipo,precision,length,scale,nullable;
exit when columnas%notfound;
if columnas%rowcount = 1 then
cadena := '(' ;
else
cadena := ',' ;
end if;
cadena := cadena || ':OLD.'||columna;
dbms_output.put_line(cadena);
end loop;
close columnas;
dbms_output.put_line(',sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'SESSION_USERID'||CHR(39)||'),sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'HOST'||CHR(39)||'), sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'OS_USER'||CHR(39)||'),SYSDATE,'||CHR(39)||'U'||CHR(39)||'); ');
dbms_output.put_line('END IF; ');
dbms_output.put_line(' IF DELETING THEN ');
dbms_output.put_line(' INSERT INTO AUDI_'||nombre_tabla);
open columnas(esquema, nombre_tabla);
loop
fetch columnas into columna,tipo,precision,length,scale,nullable;
exit when columnas%notfound;
if columnas%rowcount = 1 then
cadena := '(' ;
else
cadena := ',' ;
end if;
cadena := cadena || columna;
dbms_output.put_line(cadena);
end loop;
close columnas;
dbms_output.put_line(',USER_ID,HOST,OS_USER,AUD_FEC,AUD_DML) ');
dbms_output.put_line(' VALUES');
open columnas(esquema, nombre_tabla);
loop
fetch columnas into columna,tipo,precision,length,scale,nullable;
exit when columnas%notfound;
if columnas%rowcount = 1 then
cadena := '(' ;
else
cadena := ',' ;
end if;
cadena := cadena || ':OLD.'||columna;
dbms_output.put_line(cadena);
end loop;
close columnas;
dbms_output.put_line(',sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'SESSION_USERID'||CHR(39)||'),sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'HOST'||CHR(39)||'), sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'OS_USER'||CHR(39)||'),SYSDATE,'||CHR(39)||'D'||CHR(39)||'); ');
dbms_output.put_line('END IF; ');
dbms_output.put_line('END ; ');
dbms_output.put_line('/');
end loop;
close tablas;
end loop;
close usuarios;
end;
/
SPOOL OFF;
-- CREACION DE LAS TABLAS Y TRIGGERS
@CREA_AUDITORIAS_&&1..SQL
@CREA_TRIGGERS_&&1..SQL
No hay comentarios:
Publicar un comentario