Embed Size (px)
DESCRIPTION
Tutorial sobre cómo realizar una auditoría de datos en Oracle Database (por Josep Lluis Maicas - Altran España). Se evaluaran diferentes estrategias en base a los siguientes requerimientos: R1. No obligar a las tablas a auditar a que dispongan de los atributos propios de auditoría: quién, cuándo y cómo. R2. Poder granular la auditoria a los requerimientos que determine el negocio, pudiendo auditar sólo algunas columnas o filas. R3. Sólo auditar las operaciones que han finalizado con commit. R4. Pensado para poder mantener la información auditada durante años. R5. Rapidez en las consultas sobre la información auditada. R6. Sin necesidad de tener privilegios de DBA para su configuración, dado que hay entornos en el Cliente (Integración, PreProducción y Producción) que no permiten disponer de este privilegio a las empresas desarrolladoras de aplicaciones.
Citation preview
Altran España
www.altran.es - www.altran360.es
Auditoría de datos en Oracle
Josep LLuis Maicas (Altran España)
Tutorial
23 de julio de 2014
Conócenos en:
Web: www.altran.es
Blog: www.altran360.es
LinkedIn: https://www.linkedin.com/company/altran-espana
Twitter: @altran_es
Si quieres más información sobre nuestros productos y servicios, puedes contactar con
©altran | Auditoría de datos en Oracle |
2/20
Índice
1 Introducción .............................................................................................................. 3
2 Estrategias de auditoría para el histórico de datos....................................................... 4
2.1 A nivel de aplicación ............................................................................................................................. 4
2.2 Mediante AUDIT y FGA .......................................................................................................................... 4
2.3 Mediante Flashback............................................................................................................................... 5
2.4 Mediante Oracle Total Recall ................................................................................................................. 5
2.5 Comparativa estrategias ........................................................................................................................ 6
3 Configuración de Auditoria a nivel de aplicación ......................................................... 8
3.1 Introducción ......................................................................................................................................... 8
3.2 Requerimientos ..................................................................................................................................... 8
3.3 Estrategia utilizada ............................................................................................................................... 8
3.4 Automatización del proceso de configuración........................................................................................ 9
3.4.1 Script de generación ...................................................................................................................... 9
3.4.2 Scripts generados .......................................................................................................................... 9
3.4.3 Ejecución de la configuración ...................................................................................................... 10
3.4.4 Ejecución del Undo (opcional) ...................................................................................................... 10
4 Anexos ................................................................................................................... 11
4.1 Script de generación: Gen_Scripts_Auditoria.sql ................................................................................... 11
4.2 Ejemplo de Scripts generados .............................................................................................................. 16
4.2.1 1_auditado.sql ............................................................................................................................ 16
4.2.2 2_auditor.sql ............................................................................................................................... 16
4.2.3 3_auditado.sql ............................................................................................................................ 16
4.2.4 undo_auditado.sql ....................................................................................................................... 17
4.2.5 undo_auditor.sql ......................................................................................................................... 18
4.3 Bibliografía consultada ........................................................................................................................ 18
©altran | Auditoría de datos en Oracle |
3/20
1 Introducción
El objetivo del artículo es dar a conocer varias estrategias para auditar los datos de las
tablas de BD a fin saber quién, cuándo y qué valores han cambiado.
Se evaluaran diferentes estrategias en base a los siguientes requerimientos:
R1. No obligar a las tablas a auditar a que dispongan de los atributos propios de
auditoría: quién, cuándo y cómo.
R2. Poder granular la auditoria a los requerimientos que determine el negocio,
pudiendo auditar sólo algunas columnas o filas.
R3. Sólo auditar las operaciones que han finalizado con commit.
R4. Pensado para poder mantener la información auditada durante años.
R5. Rapidez en las consultas sobre la información auditada.
R6. Sin necesidad de tener privilegios de DBA para su configuración, dado que hay
entornos en el Cliente (Integración, PreProducción y Producción) que no permiten
disponer de este privilegio a las empresas desarrolladoras de aplicaciones.
Para ello, se presentarán diversas estrategias que aporta Oracle y se seleccionará una de
ellas, la que se ha considerado que cumple mejor con los requerimientos mencionados.
Para ésta, se aportará una solución completa, ejemplificándolo en un caso de uso.
Quedan fuera del alcance del documento evaluar estrategias de auditoria que se salga del
objetivo de auditar los datos de las tablas.
©altran | Auditoría de datos en Oracle |
4/20
2 Estrategias de auditoría para el histórico de datos
2.1 A nivel de aplicación
Es el modo de auditoría que se realiza a nivel de aplicación. Este es personalizado
mediante programación, lo que hace que sea independiente a las versiones de Oracle y que
permite granular la auditoria a las necesidades del negocio.
Se genera mediante triggers que captan las operaciones DML sobre las tablas de interés y
vuelcan esa información, enriquecida con información de contexto, en las tablas destino de
la auditoria.
Se suele hacer una correspondencia de 1 a 1 entre tabla auditada y tabla de auditoria, de
manera que se si quieren auditar 10 tablas habrá 10 tablas de auditoria donde se
almacenará el histórico de cambios de los datos con la información añadida de contexto
que interese.
Las tablas de auditoria se suelen dejar en un esquema diferenciado del esquema propietario
de las tablas, a fin de que el esquema propietario no pueda alterar la información auditada.
2.2 Mediante AUDIT y FGA
Oracle proporciona desde sus primeras versiones la posibilidad de auditar las acciones DML
realizadas sobre tablas de la BD mediante AUDIT, y en posterioridad mediante Fine Grained
Auditing (FGA).
En ambos métodos se puede auditar las instrucciones lanzadas contra la BD y las bind
varibles empleadas en ello. El inconveniente es que no se saben los valores finales con los
que han quedado los registros afectados.
Por ejemplo, si se lanza:
update emp set hiredate = hiredate + sysdate;
Se inserta un único registro en la auditoria con dicha instrucción. El inconveniente es que
no queda constancia del valor que tenía antes ni después cada registro, de manera que
reconstruir los valores de dicho campo a una fecha en el pasado no es operativo.
A parte, estos métodos graban la auditoria con independencia de si ha hecho COMMIT o
ROLLBACK, cosa que sólo interesan los que han acabado en COMMIT.
A parte, en ambas estrategias todos los registros quedan almacenados en dos tablas del
esquema SYS, aud$ y fga_log$, haciendo que la consulta sobre los mismos pueda ser lenta
cuando la información almacenada sea de varios años para varias tablas. También se ha de
tener en cuenta de mover el tablespace donde se almacenan los datos de dichas tablas, ya
©altran | Auditoría de datos en Oracle |
5/20
que por defecto es SYSTEM y, en caso de llenarse el mismo, provocaría graves problemas en
la BD.
2.3 Mediante Flashback
Visto que auditar datos (no operaciones) mediante AUDIT y FGA no aporta una solución
convincente, Oracle aporta otra estrategia, llamada Flashback para poder consultar los
registros de una tabla a un momento anterior en el tiempo.
Está estrategia sí que da respuesta a poder consultar los cambios que ha sufrido un registro
a lo largo del tiempo, almacenando sólo los cambios finalizados en commit. No obstante,
presenta una serie de inconvenientes como:
- Las tablas a auditar requieren que ya estén almacenando la información de contexto
necesaria para la finalidad de auditoria.
En caso de añadir estas columnas en las tablas para almacenar la información de
contexto, optando por un trigger para informar sus valores, comentar que sólo es
posible en operaciones de Inserción y Modificación, ya que no es posible añadir
información extra en el registro cuando se efectúa un borrado del mismo.
- Se almacena en el tablespace de UNDO, el cual no está pensado para almacenar
grandes cantidades de información histórica de datos. Dicho tablespace está
pensado para la consistencia transaccional, no como un histórico de cambios de los
datos.
2.4 Mediante Oracle Total Recall
A partir de la versión 11g es posible almacenar los cambios que se producen en los
registros en tablas internas gestionadas por la propia BD, mediante Flashback Data Archive
(FBDA). Ahora se crean tablas internas para cada tabla a auditar en el propio esquema del
usuario auditado, con la ventaja que dichas tablas internas no son manipulables por ningún
tipo de usuario, ni tan solo por el administrador.
El inconveniente que presenta es que de por sí tampoco aporta la información de contexto
de auditoria que se requiere, a menos que la tabla de origen ya la incorporé.
Para solventar este problema, en la versión 12c se aporta nuevas prestaciones para poder
consultar información de contexto sobre quién y cuándo se realizaron los cambios en los
registros. Para ello, hay que indicarle que guarde información de contexto:
©altran | Auditoría de datos en Oracle |
6/20
Para luego poder correlacionar la información histórica con la de contexto. Para ello hay
que hacer una join entre una nueva tabla, llamada SYS.SYS_FBA_CONTEXT_AUD, y la tabla
interna de Oracle donde se guarda la auditoria de la tabla auditada.
2.5 Comparativa estrategias
Req. A nivel
Aplicación Audit FGA Flashback Oracle Total Recall
R1 Sí Sí Sí No Sí
R2 Sí No Sí No No
R3 Sí No No Sí Sí
R4 Sí No No No Sí
R5 Sí No No No Sí
R6 Sí No No No No
©altran | Auditoría de datos en Oracle |
7/20
Aunque la opción de Oracle Total Recall, utilizando FBDA, da cumplimiento a la mayoría de
los requerimientos mencionados en el inicio del artículo a partir de la versión de Oracle
12c, se aporta la solución mediante la primera estrategia al estar ésta disponible para todas
las versiones y permitir máxima flexibilidad en lo que se quiere auditar.
©altran | Auditoría de datos en Oracle |
8/20
3 Configuración de Auditoria a nivel de aplicación
3.1 Introducción
El objetivo es auditar los datos de las tablas que se deseen de un esquema, conocido como
esquema ‘auditado’, dejando el resultado de los datos auditados en otro esquema,
conocido como esquema ‘auditor’.
El auditor contendrá los datos de auditoría proporcionado desde varios esquemas
auditados. Tanto los esquemas auditados como el auditor podrán tener como nombre de
esquema el que se desee.
El auditor sólo tendrá capacidad para consultar los datos de todas las tablas auditadas,
mientras que los esquemas auditados solo tendrán capacidad para insertar los datos de
auditoria de sus tablas en el esquema del auditor.
3.2 Requerimientos
Debe de existir un esquema auditor y, como mínimo, un esquema auditado. Dichos
esquemas han de tener privilegios para hacer GRANT SELECT en el primer caso y GRANT
INSERT en el segundo.
3.3 Estrategia utilizada
Para cada tabla a auditar del esquema auditado se creará un trigger que registrará las
operaciones DML que se insertarán en una tabla del esquema auditor.
Nomenclaturas utilizadas:
Trigger en esquema auditado: AUD_<nombre_tabla_auditada>_TRG
Tabla de auditoria en esquema auditor: AUD_<nombre_tabla_auditada>
La tabla de auditoria contendrá las mismas columnas que la tabla de origen. A parte, se le
añadirán:
aud_usr VARCHAR2(50)
Almacenará el usuario de aplicación que se haya le pasado en el CONTEXT de Oracle
desde la aplicación.
aud_usr := LOWER(sys_context('USERENV', 'CLIENT_IDENTIFIER'));
En caso de que no se le haya pasado, tomará como usuario el usuario de la conexión
física de BD
aud_usr:= UPPER(sys_context('USERENV','SESSION_USER'));
aud_dml VARCHAR2(1)
Contendrá el tipo de operación DML realizada: I(nsert), U(pdate), D(elete)
©altran | Auditoría de datos en Oracle |
9/20
aud_fec DATE DEFAULT SYSDATE
Fecha con minutos y segundos del instante en que se ha generado la acción DML.
3.4 Automatización del proceso de configuración
Para configurar la auditoria en las tablas se proporciona un script sql que lo que hace a su
vez es generar otros scripts para configurar el esquema auditado y el esquema auditor.
Se proporciona también otros scripts para hacer el UNDO (retroceso) de toda la
configuración realizada.
3.4.1 Script de generación
El script de generación aportado es Gen_Scripts_Auditoria.sql.
Este se tiene que lanzar contra el esquema auditado. Para ello, se le deben facilitar dos
parámetros:
Nombre del esquema que actuará como auditor
Lista de las tablas a auditar (separadas por coma)
Ejemplo:
sqlplus scott/tiger@desorcl1 @Gen_Scripts_Auditoria.sql AUDITOR ‘EMP,DEPT’
Esquema auditado SCOTT
Parámetros:
o Nombre del esquema que actuará como auditor AUDITOR
o Lista de las tablas a auditar (separadas por coma) EMP,DEPT
3.4.2 Scripts generados
Como resultado del lanzamiento anterior se generarán los siguientes scripts SQL:
Para configurar la auditoria:
1_auditado.sql
2_auditor.sql
3_auditado.sql
En caso que se desee, también se aportan los scripts para deshacer los cambios efectuados
en la configuración de la auditoria:
undo_auditado.sql
undo_auditor.sql
©altran | Auditoría de datos en Oracle |
10/20
3.4.3 Ejecución de la configuración
Ejemplo de utilización para el esquema auditado ‘SCOTT’ y como esquema auditor
‘AUDITOR’
Para la configuración de la auditoría:
sqlplus scott/tiger@desorcl1 @1_auditado.sql
sqlplus auditor/auditor@desorcl1 @2_auditor.sql
sqlplus scott/tiger@desorcl1 @3_auditado.sql
3.4.4 Ejecución del Undo (opcional)
En caso de querer deshacer los cambios efectuados:
sqlplus scott/tiger@desorcl1 @undo_auditado.sql
sqlplus auditor/auditor@desorcl1 @undo_auditor.sql
©altran | Auditoría de datos en Oracle |
11/20
4 Anexos
4.1 Script de generación: Gen_Scripts_Auditoria.sql
SET SERVEROUTPUT ON FORMAT WRAPPED SET LONGC 300 SET LINESIZE 300 SET ECHO OFF SET TERM OFF SET VER OFF SET FEEDBACK OFF -- -------------------------------------------------------------------------------- -- Generación scripts para AUDITADO (1ª parte) -- -------------------------------------------------------------------------------- spool 1_auditado.sql DECLARE p_auditor VARCHAR2(50) := '&1'; p_tablas VARCHAR2(1000) := '&2'; auditor VARCHAR2(50); tablas VARCHAR2(2000); lv_tab_length BINARY_INTEGER; list_tablas DBMS_UTILITY.lname_array; CURSOR c1 (tabla VARCHAR2) IS SELECT LOWER(column_name) as column_name FROM user_tab_columns WHERE table_name = tabla ORDER BY column_id; BEGIN -- Quitar posibles espacios en blanco y poner en mayúsculas auditor := UPPER(REPLACE(p_auditor,' ')); tablas := UPPER(REPLACE(p_tablas,' ')); DBMS_UTILITY.COMMA_TO_TABLE (list => tablas, tablen => lv_tab_length, tab => list_tablas); FOR i IN 1 .. lv_tab_length LOOP DBMS_OUTPUT.put_line ('GRANT SELECT ON '||list_tablas (i)||' TO '||auditor||';'); END LOOP; DBMS_OUTPUT.put_line ('EXIT'); END; / spool off; -- -------------------------------------------------------------------------------- -- Generación scripts para AUDITOR -- -------------------------------------------------------------------------------- spool 2_auditor.sql DECLARE
©altran | Auditoría de datos en Oracle |
12/20
p_tablas VARCHAR2(1000) := '&2'; auditado VARCHAR2(50); tablas VARCHAR2(2000); str VARCHAR2 (20000); prefix VARCHAR2 (5) := 'AUD'; lv_tab_length BINARY_INTEGER; list_tablas DBMS_UTILITY.lname_array; CURSOR c1 (tabla VARCHAR2) IS SELECT LOWER(column_name) as column_name FROM user_tab_columns WHERE table_name = tabla ORDER BY column_id; BEGIN -- Obtener el nombre del usuario auditado SELECT user INTO auditado FROM DUAl; -- Quitar posibles espacios en blanco y poner en mayúsculas tablas := UPPER(REPLACE(p_tablas,' ')); DBMS_UTILITY.COMMA_TO_TABLE (list => tablas, tablen => lv_tab_length, tab => list_tablas); FOR i IN 1 .. lv_tab_length LOOP DBMS_OUTPUT.put_line ('PROMPT Tratando tabla '||prefix||'_'||list_tablas (i)); DBMS_OUTPUT.put_line ('CREATE TABLE '||prefix||'_'||list_tablas (i)||' AS (SELECT * FROM '||auditado||'.'||list_tablas (i)||' WHERE 1=0)'||';'); DBMS_OUTPUT.put_line ('ALTER TABLE '||prefix||'_'||list_tablas (i)||' ADD (aud_usr VARCHAR2(50),aud_dml VARCHAR2(1),aud_fec DATE DEFAULT SYSDATE);'); DBMS_OUTPUT.put_line ('GRANT INSERT ON '||prefix||'_'||list_tablas (i)||' TO '||auditado||';'); DBMS_OUTPUT.new_line; END LOOP; DBMS_OUTPUT.put_line ('EXIT'); END; / spool off; -- -------------------------------------------------------------------------------- -- Generación scripts para AUDITADO (2ª parte) -- -------------------------------------------------------------------------------- spool 3_auditado.sql DECLARE p_auditor VARCHAR2(50) := '&1'; p_tablas VARCHAR2(1000) := '&2'; auditor VARCHAR2(50); tablas VARCHAR2(2000); str VARCHAR2(20000); prefix VARCHAR2 (5) := 'AUD'; lv_tab_length BINARY_INTEGER; list_tablas DBMS_UTILITY.lname_array;
©altran | Auditoría de datos en Oracle |
13/20
CURSOR c1 (tabla VARCHAR2) IS SELECT LOWER(column_name) as column_name FROM user_tab_columns WHERE table_name = tabla ORDER BY column_id; BEGIN -- Quitar posibles espacios en blanco y poner en mayúsculas auditor := UPPER(REPLACE(p_auditor,' ')); tablas := UPPER(REPLACE(p_tablas,' ')); DBMS_UTILITY.COMMA_TO_TABLE (list => tablas, tablen => lv_tab_length, tab => list_tablas); FOR i IN 1 .. lv_tab_length LOOP DBMS_OUTPUT.put_line ('CREATE SYNONYM '||prefix||'_'||list_tablas (i)||' FOR '||auditor||'.'||prefix||'_'||list_tablas (i)||';'); END LOOP; DBMS_OUTPUT.new_line; FOR i IN 1 .. lv_tab_length LOOP DBMS_OUTPUT.put_line('CREATE OR REPLACE TRIGGER '||prefix||'_'||list_tablas (i)||'_TRG '); DBMS_OUTPUT.put_line(' AFTER INSERT OR UPDATE OR DELETE'); DBMS_OUTPUT.put_line(' ON '||list_tablas (i)); DBMS_OUTPUT.put_line(' REFERENCING NEW AS New OLD AS Old '); DBMS_OUTPUT.put_line(' FOR EACH ROW'); DBMS_OUTPUT.put_line('DECLARE'); DBMS_OUTPUT.put_line(' modo VARCHAR2(1);'); DBMS_OUTPUT.put_line(' usu VARCHAR2(50);'); DBMS_OUTPUT.put_line('BEGIN'); DBMS_OUTPUT.put_line(' IF INSERTING THEN'); DBMS_OUTPUT.put_line(' modo := ''I'';'); DBMS_OUTPUT.put_line(' ELSIF UPDATING THEN'); DBMS_OUTPUT.put_line(' modo := ''U'';'); DBMS_OUTPUT.put_line(' ELSE'); DBMS_OUTPUT.put_line(' modo := ''D'';'); DBMS_OUTPUT.put_line(' END IF;'); DBMS_OUTPUT.put_line(' usu := LOWER(sys_context(''USERENV'', ''CLIENT_IDENTIFIER''));'); DBMS_OUTPUT.put_line(' IF usu IS NULL THEN'); DBMS_OUTPUT.put_line(' usu := UPPER(sys_context(''USERENV'',''SESSION_USER''));'); DBMS_OUTPUT.put_line(' END IF;'); DBMS_OUTPUT.put_line(' IF DELETING THEN'); str := ' INSERT INTO '||prefix||'_'||list_tablas (i)||' ('; FOR c1rec IN c1 (list_tablas (i)) LOOP str := str||c1rec.column_name||','; END LOOP; str := str||'aud_usr,aud_dml)'; DBMS_OUTPUT.put_line(str); str := ' VALUES ('; FOR c1rec IN c1 (list_tablas (i)) LOOP
©altran | Auditoría de datos en Oracle |
14/20
str := str||':old.'||c1rec.column_name||','; END LOOP; str := str||'usu,modo);'; DBMS_OUTPUT.put_line(str); DBMS_OUTPUT.put_line(' ELSE '); str := ' INSERT INTO '||prefix||'_'||list_tablas (i)||' ('; FOR c1rec IN c1 (list_tablas (i)) LOOP str := str||c1rec.column_name||','; END LOOP; str := str||'aud_usr,aud_dml)'; DBMS_OUTPUT.put_line(str); str := ' VALUES ('; FOR c1rec IN c1 (list_tablas (i)) LOOP str := str||':new.'||c1rec.column_name||','; END LOOP; str := str||'usu,modo);'; DBMS_OUTPUT.put_line(str); DBMS_OUTPUT.put_line(' END IF;'); DBMS_OUTPUT.put_line('END;' ); DBMS_OUTPUT.put_line('/'); DBMS_OUTPUT.new_line; END LOOP; DBMS_OUTPUT.put_line ('EXIT'); END; / spool off; -- -------------------------------------------------------------------------------- -- Generación scripts para UNDO AUDITOR -- -------------------------------------------------------------------------------- spool undo_auditor.sql DECLARE p_auditor VARCHAR2(50) := '&1'; p_tablas VARCHAR2(1000) := '&2'; auditor VARCHAR2(50); auditado VARCHAR2(50); tablas VARCHAR2(2000); prefix VARCHAR2 (5) := 'AUD'; lv_tab_length BINARY_INTEGER; list_tablas DBMS_UTILITY.lname_array; CURSOR c1 (tabla VARCHAR2) IS SELECT LOWER(column_name) as column_name FROM user_tab_columns
©altran | Auditoría de datos en Oracle |
15/20
WHERE table_name = tabla ORDER BY column_id; BEGIN -- Obtener el nombre del usuario auditado SELECT user INTO auditado FROM DUAl; -- Quitar posibles espacios en blanco y poner en mayúsculas auditor := UPPER(REPLACE(p_auditor,' ')); tablas := UPPER(REPLACE(p_tablas,' ')); DBMS_UTILITY.COMMA_TO_TABLE (list => tablas, tablen => lv_tab_length, tab => list_tablas); FOR i IN 1 .. lv_tab_length LOOP DBMS_OUTPUT.put_line ('PROMPT Deshaciendo cambios para tabla '||prefix||'_'||list_tablas (i)); DBMS_OUTPUT.put_line ('DROP TABLE '||prefix||'_'||list_tablas (i)||';'); DBMS_OUTPUT.new_line; END LOOP; DBMS_OUTPUT.put_line ('EXIT'); END; / spool off; -- -------------------------------------------------------------------------------- -- Generación scripts para UNDO AUDITADO -- -------------------------------------------------------------------------------- spool undo_auditado.sql DECLARE p_auditor VARCHAR2(50) := '&1'; p_tablas VARCHAR2(1000) := '&2'; auditor VARCHAR2(50); tablas VARCHAR2(2000); prefix VARCHAR2 (5) := 'AUD'; lv_tab_length BINARY_INTEGER; list_tablas DBMS_UTILITY.lname_array; CURSOR c1 (tabla VARCHAR2) IS SELECT LOWER(column_name) as column_name FROM user_tab_columns WHERE table_name = tabla ORDER BY column_id; BEGIN -- Quitar posibles espacios en blanco y poner en mayúsculas auditor := UPPER(REPLACE(p_auditor,' ')); tablas := UPPER(REPLACE(p_tablas,' ')); DBMS_UTILITY.COMMA_TO_TABLE (list => tablas, tablen => lv_tab_length, tab => list_tablas); FOR i IN 1 .. lv_tab_length LOOP DBMS_OUTPUT.put_line ('PROMPT Deshaciendo cambios para tabla '||prefix||'_'||list_tablas (i)); DBMS_OUTPUT.put_line ('REVOKE SELECT ON '||list_tablas (i)||' FROM '||auditor||';'); DBMS_OUTPUT.put_line ('DROP SYNONYM '||prefix||'_'||list_tablas (i)||';');
©altran | Auditoría de datos en Oracle |
16/20
DBMS_OUTPUT.put_line ('DROP TRIGGER '||prefix||'_'||list_tablas (i)||'_TRG '); DBMS_OUTPUT.new_line; END LOOP; DBMS_OUTPUT.put_line ('EXIT'); END; / spool off; EXIT
4.2 Ejemplo de Scripts generados
4.2.1 1_auditado.sql
GRANT SELECT ON EMP TO AUDITOR; GRANT SELECT ON DEPT TO AUDITOR; EXIT
4.2.2 2_auditor.sql
PROMPT Tratando tabla AUD_EMP CREATE TABLE AUD_EMP AS (SELECT * FROM SCOTT.EMP WHERE 1=0); ALTER TABLE AUD_EMP ADD (aud_usr VARCHAR2(50),aud_dml VARCHAR2(1),aud_fec DATE DEFAULT SYSDATE); GRANT INSERT ON AUD_EMP TO SCOTT; PROMPT Tratando tabla AUD_DEPT CREATE TABLE AUD_DEPT AS (SELECT * FROM SCOTT.DEPT WHERE 1=0); ALTER TABLE AUD_DEPT ADD (aud_usr VARCHAR2(50),aud_dml VARCHAR2(1),aud_fec DATE DEFAULT SYSDATE); GRANT INSERT ON AUD_DEPT TO SCOTT; EXIT
4.2.3 3_auditado.sql
CREATE SYNONYM AUD_EMP FOR AUDITOR.AUD_EMP; CREATE SYNONYM AUD_DEPT FOR AUDITOR.AUD_DEPT; CREATE OR REPLACE TRIGGER AUD_EMP_TRG AFTER INSERT OR UPDATE OR DELETE ON EMP REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE modo VARCHAR2(1); usu VARCHAR2(50); BEGIN IF INSERTING THEN modo := 'I'; ELSIF UPDATING THEN modo := 'U'; ELSE modo := 'D'; END IF; usu := LOWER(sys_context('USERENV', 'CLIENT_IDENTIFIER'));
©altran | Auditoría de datos en Oracle |
17/20
IF usu IS NULL THEN usu := UPPER(sys_context('USERENV','SESSION_USER')); END IF; IF DELETING THEN INSERT INTO AUD_EMP (empno,ename,job,mgr,hiredate,sal,comm,deptno,aud_usr,aud_dml) VALUES (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno,usu,modo); ELSE INSERT INTO AUD_EMP (empno,ename,job,mgr,hiredate,sal,comm,deptno,aud_usr,aud_dml) VALUES (:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno,usu,modo); END IF; END; / CREATE OR REPLACE TRIGGER AUD_DEPT_TRG AFTER INSERT OR UPDATE OR DELETE ON DEPT REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE modo VARCHAR2(1); usu VARCHAR2(50); BEGIN IF INSERTING THEN modo := 'I'; ELSIF UPDATING THEN modo := 'U'; ELSE modo := 'D'; END IF; usu := LOWER(sys_context('USERENV', 'CLIENT_IDENTIFIER')); IF usu IS NULL THEN usu := UPPER(sys_context('USERENV','SESSION_USER')); END IF; IF DELETING THEN INSERT INTO AUD_DEPT (deptno,dname,loc,aud_usr,aud_dml) VALUES (:old.deptno,:old.dname,:old.loc,usu,modo); ELSE INSERT INTO AUD_DEPT (deptno,dname,loc,aud_usr,aud_dml) VALUES (:new.deptno,:new.dname,:new.loc,usu,modo); END IF; END; / EXIT
4.2.4 undo_auditado.sql
PROMPT Deshaciendo cambios para tabla AUD_EMP REVOKE SELECT ON EMP FROM AUDITOR; DROP SYNONYM AUD_EMP; DROP TRIGGER AUD_EMP_TRG PROMPT Deshaciendo cambios para tabla AUD_DEPT REVOKE SELECT ON DEPT FROM AUDITOR;
©altran | Auditoría de datos en Oracle |
18/20
DROP SYNONYM AUD_DEPT; DROP TRIGGER AUD_DEPT_TRG EXIT
4.2.5 undo_auditor.sql
PROMPT Deshaciendo cambios para tabla AUD_EMP DROP TABLE AUD_EMP; PROMPT Deshaciendo cambios para tabla AUD_DEPT DROP TABLE AUD_DEPT; EXIT
4.3 Bibliografía consultada
http://www.oracle-base.com/articles/10g/auditing-10gr2.php
http://www.ajpdsoft.com/modules.php?name=News&file=article&sid=415
http://www.toadworld.com/platforms/oracle/w/wiki/5954.fine-grained-auditing-
vs-regular-auditing.aspx
http://www.morganslibrary.org/reference/flash_archive.html
http://www.dbi-services.com/index.php/blog/entry/oracle-database-12c-
flashback-data-archive-fda-new-features
https://community.oracle.com/thread/688251?start=0&tstart=0
http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60security-
176069.html
http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr003.htm
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&frm=1&source=web&cd=1&cad
=rja&uact=8&ved=0CB4QFjAA&url=http%3A%2F%2Fwww.ioug.org%2Fd%2Fdo%2F357
7&ei=8kGXU_7YFcz20gWxkYHACg&usg=AFQjCNHPAwrDrVQRPkeIyf48nm-
LS8eXJg&bvm=bv.68693194,d.d2k
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_fl.
htm
http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10577/d_audit_mg
mt.htm
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_fl.
htm
http://www.filibeto.org/sun/lib/nonsun/oracle/11.2.0.1.0/E11882_01/appdev.112/
e10471/adfns_flashback.htm
http://www.oracle.com/database/total-recall.html
Conócenos en:
Web: www.altran.es
Blog: www.altran360.es
LinkedIn: https://www.linkedin.com/company/altran-espana
Twitter: @altran_es
Si quieres más información sobre nuestros productos y servicios, puedes contactar
©altran | Auditoría de datos en Oracle |
20/20
