• Describir las Auditorias de Oracle.
• Conocer los Procesos ADD_POLICY, DROP_POLICY, ENABLE_POLICY y DISABLE_POLICY del paquete DBMS_FGA de SYS.
• Ver ejemplos prácticos.
• Conocer los Procesos ADD_POLICY, DROP_POLICY, ENABLE_POLICY y DISABLE_POLICY del paquete DBMS_FGA de SYS.
• Ver ejemplos prácticos.
_______________________________________________________________________
Políticas de Auditoria de Oracle
Oracle ofrece poderosas herramientas para el rastreo de todas las transacciones y/o consultas realizadas a sus tablas, esto se puede lograr con el uso del paquete DBMS_FGA el cual proporciona procesos de seguridad y control que permiten realizar las auditorias de forma precisa y certera. Debe tener pendiente que el uso de dicho paquete requiere los privilegios de administrador, ésto debido que las auditorias permiten el acceso a informaciones sensitivas del entorno del usuario y/o la aplicación del cual opera.
_______________________________________________________________________
Paquete DBMS_FGA
A continuación el detalle de los procesos que veremos en esta publicación:
- ADD_POLICY: Crea la política de auditoría de acuerdo a los parámetros proporcionados.
Sintaxis:
SYS.DBMS_FGA.ADD_POLICY( object_schema IN VARCHAR2 DEFAULT NULL, object_name IN VARCHAR2,
policy_name IN VARCHAR2,
audit_condition IN VARCHAR2 DEFAULT NULL,
audit_column IN VARCHAR2 DEFAULT NULL,
handler_schema IN VARCHAR2 DEFAULT NULL,
handler_module IN VARCHAR2 DEFAULT NULL,
enable IN BOOLEAN DEFAULT TRUE,
statement_types IN VARCHAR2 DEFAULT SELECT,
audit_trail IN BINARY_INTEGER DEFAULT NULL,
audit_column_opts IN BINARY_INTEGER DEFAULT ANY_COLUMNS,
policy_owner IN VARCHAR2 DEFAULT NULL
);
- DROP_POLICY: Elimina la política de auditoria especificada.
Sintaxis:
SYS.DBMS_FGA.DROP_POLICY( object_schema IN VARCHAR2, object_name IN VARCHAR2,
policy_name IN VARCHAR2
);
- ENABLE_POLICY: Habilita la política de auditoria especificada.
Sintaxis:
SYS.DBMS_FGA.ENABLE_POLICY( object_schema IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
enable IN BOOLEAN DEFAULT TRUE
);
- DISABLE_POLICY: Desactiva/deshabilita la política de auditoria especificada.
Sintaxis:
SYS.DBMS_FGA.DISABLE_POLICY( object_schema IN VARCHAR2, object_name IN VARCHAR2, policy_name IN VARCHAR2 );
_______________________________________________________________________
Ejemplo Funcional
Para nuestra prueba utilizamos la tabla EMPLOYEES del esquema HR; En caso de no contar con este esquema y/o table puede instalarlo siguiendo las instrucciones aportadas en esta publicación. Adicionalmente creamos la tabla hr.consultas_tab_employees, el procedimiento hr.prc_audit_employees y la política de auditoria.
Script Tabla hr.consultas_tab_employees:
BEGIN EXECUTE IMMEDIATE 'DROP TABLE hr.consultas_tab_employees'; EXCEPTION WHEN OTHERS THEN NULL; END; / CREATE TABLE hr.consultas_tab_employees ( usuario_db VARCHAR2(30) , fecha TIMESTAMP , query CLOB , valor_consulta VARCHAR2(500)
, terminal VARCHAR2(500)
, host VARCHAR2(500)
, ip VARCHAR2(500)
, programa VARCHAR2(500)
, usuario_os VARCHAR2(500)
, sid VARCHAR2(500)
, object_schema VARCHAR2(500)
, object_name VARCHAR2(500)
, policy_name VARCHAR2(500)
);
Script Procedimiento hr.prc_audit_employees: Notar que hacemos usado de la función SYS_CONTEXT, puede encontrar mas información sobre ella aquí.
CREATE OR REPLACE PROCEDURE hr.prc_audit_employees( object_schema VARCHAR2 , object_name VARCHAR2 , policy_name VARCHAR2 ) AS v_execution_user VARCHAR2(30); v_execution_date TIMESTAMP; v_execution_sql CLOB; v_valor_consulta VARCHAR2(500);
v_terminal_name VARCHAR2(500);
v_host_name VARCHAR2(500);
v_ip_address VARCHAR2(500);
v_program_name VARCHAR2(500);
v_os_user VARCHAR2(500);
v_sid_user VARCHAR2(500);
BEGIN v_execution_user := USER; v_execution_date := SYSTIMESTAMP; v_execution_sql := SYS_CONTEXT('USERENV','CURRENT_SQL'); v_valor_consulta := SYS_CONTEXT('USERENV','CURRENT_BIND');
v_terminal_name := SYS_CONTEXT('USERENV','TERMINAL');
v_host_name := SYS_CONTEXT('USERENV','HOST');
v_ip_address := SYS_CONTEXT('USERENV','IP_ADDRESS');
v_program_name := SYS_CONTEXT('USERENV','MODULE');
v_os_user := SYS_CONTEXT('USERENV','OS_USER');
v_sid_user := SYS_CONTEXT('USERENV','SID');
INSERT INTO hr.consultas_tab_employees ( usuario_db , fecha , query , valor_consulta , terminal , host , ip , programa , usuario_os , sid , object_schema , object_name , policy_name ) VALUES ( v_execution_user --usuario_db VARCHAR2(30) , v_execution_date --fecha TIMESTAMP , v_execution_sql --query CLOB , v_valor_consulta --valor_consulta VARCHAR2(500) , v_terminal_name --terminal VARCHAR2(500) , v_host_name --host VARCHAR2(500) , v_ip_address --ip VARCHAR2(500) , v_program_name --programa VARCHAR2(500) , v_os_user --usuario_os VARCHAR2(500) , v_sid_user --sid VARCHAR2(500) , object_schema --object_schema VARCHAR2(500) , object_name --object_name VARCHAR2(500) , policy_name --policy_name VARCHAR2(500) ); END prc_audit_employees; /
Script Auditoria AUDIT_EMPLOYEES:
SET SERVEROUTPUT ON BEGIN SYS.DBMS_FGA.DROP_POLICY( --Argument Name Value Type In/Out -------------------- ----------------- ------ object_schema => 'HR' --VARCHAR2 IN ,object_name => 'EMPLOYEES' --VARCHAR2 IN ,policy_name => 'AUDIT_EMPLOYEES' --VARCHAR2 IN ); EXCEPTION WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE( 'Auditoria AUDIT_EMPLOYEES no existe!' ); END; / BEGIN SYS.DBMS_FGA.ADD_POLICY( --Argument Name Value Type In/Out ---------------------- ----------------------- ------ object_schema => 'HR' --VARCHAR2 IN ,object_name => 'EMPLOYEES' --VARCHAR2 IN ,policy_name => 'AUDIT_EMPLOYEES' --VARCHAR2 IN ,audit_condition => 'EMPLOYEE_ID IS NOT NULL' --VARCHAR2 IN ,audit_column => 'EMPLOYEE_ID' --VARCHAR2 IN ,handler_schema => 'HR' --VARCHAR2 IN ,handler_module => 'PRC_AUDIT_EMPLOYEES' --VARCHAR2 IN ,enable => TRUE --VARCHAR2 IN ,statement_types => 'SELECT,INSERT,UPDATE,DELETE' --VARCHAR2 IN ); SYS.DBMS_OUTPUT.PUT_LINE( 'Auditoria AUDIT_EMPLOYEES creada con éxito.' ); EXCEPTION WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE( 'Error creando auditoria AUDIT_EMPLOYEES. Detalle: ' ||SQLERRM ); END; /
Tener presente que los anteriores Scripts fueron ejecutados con el usuario administrador SYSTEM. Ahora conectado desde con el usuario HR ejecutamos la siguiente consulta, notar el uso de la variable de entorno :emp para que se nos pida el valor a consultar:
Consultamos la tabla hr.consultas_tab_employees conectados con el usuario SYSTEM.
Nuevamente con HR insertados un registro en la tabla hr.employees:
Reconsultamos la tabla hr.consultas_tab_employees conectados con el usuario SYSTEM.
Como ultima prueba nos conectamos con HR mediante SQL PLUS y realizamos una consulta del nuevo empleado:
De igual forma verificamos con SYSTEM la anterior consulta en la tabla hr.consultas_tab_employees. Notar como en el campo programa aparece SQL*Plus.
Para nuestras pruebas creamos una tabla donde almacenamos las informaciones que entendemos importantes, de igual forma Oracle guarda un registro por cada auditoria realiza en la tabla sys.dba_fga_audit_trail, puede también hacer uso de ella:
Espero este contenido les haya sido de utilidad, si tienen preguntas o dudas no duden en dejárnoslas saber.