Objetivos
- 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.
Las Políticas de Auditoria en Oracle
Oracle ofrece herramientas avanzadas para el rastreo de todas las transacciones y consultas realizadas sobre sus tablas. Una de las más destacadas es el paquete DBMS_FGA, el cual permite implementar auditorías de manera precisa y confiable, garantizando procesos de seguridad y control en el entorno de la base de datos.
Es importante tener en cuenta que el uso de este paquete requiere privilegios de administrador, ya que las auditorías pueden implicar acceso a información sensible del usuario o de la aplicación en ejecución.
El Paquete DBMS_FGA
DBMS_FGA (Fine-Grained Auditing) es un paquete PL/SQL de Oracle diseñado para implementar auditorías detalladas sobre el acceso a datos sensibles. A diferencia de las auditorías tradicionales que registran operaciones generales (como INSERT o UPDATE sobre una tabla), DBMS_FGA permite auditar condiciones específicas dentro de una consulta, como el acceso a una columna determinada o el uso de ciertos filtros.
Características:
- Auditoría condicional: Solo se registra el acceso si se cumple una condición definida (por ejemplo,
salary > 10000). - Auditoría de columnas: Puedes auditar el acceso a columnas específicas en lugar de toda la tabla.
- Registro detallado: Captura información como el usuario, la sentencia SQL, la hora, y el identificador de sesión.
- Integración con políticas de seguridad: Ideal para cumplir con normativas como GDPR, HIPAA o PCI-DSS.
Sus Herramientas:
ADD_POLICY: Crea una política de auditoría sobre una tabla o vista.ENABLE_POLICY/DISABLE_POLICY: Activa / desactiva una política existente.DROP_POLICY: Elimina una política de auditoría.- La vista DBA_FGA_AUDIT_TRAIL es la interfaz oficial que Oracle proporciona para consultar los registros generados por las políticas de auditoría fina (Fine-Grained Auditing) creadas con el paquete DBMS_FGA.
Flujo de Auditoría con DBMS_FGA
El siguiente diagrama muestra cómo se genera un registro de auditoría en Oracle:
- Creación de la política con
DBMS_FGA.ADD_POLICY. - Ejecución de la consulta SQL sobre el objeto auditado.
- Evaluación de la condición definida en la política.
- Generación del registro si la condición se cumple.
- Consulta del reporte a través de la vista
DBA_FGA_AUDIT_TRAIL.
Ejemplo Funcional
Para nuestra prueba utilizamos la tabla EMPLOYEES del esquema HR. Adicionalmente creamos la tabla hr.consultas_tab_employees, el procedimiento hr.prc_audit_employees y la política de auditoria.
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)
);El anterior Script crea la tabla consultas_tab_employees para alojar los registros de la auditoria.
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
, v_execution_date
, v_execution_sql
, v_valor_consulta
, v_terminal_name
, v_host_name
, v_ip_address
, v_program_name
, v_os_user
, v_sid_user
, object_schema
, object_name
, policy_name
);
END prc_audit_employees;
/El procedimiento prc_audit_employees se encarga de capturar los valores de auditoria y los inserta en la tabla consultas_tab_employees.
SET SERVEROUTPUT ON
BEGIN
SYS.DBMS_FGA.DROP_POLICY(
object_schema => 'HR'
,object_name => 'EMPLOYEES'
,policy_name => 'AUDIT_EMPLOYEES'
);
EXCEPTION
WHEN OTHERS THEN
SYS.DBMS_OUTPUT.PUT_LINE('Auditoria AUDIT_EMPLOYEES no existe!');
END;
/
BEGIN
SYS.DBMS_FGA.ADD_POLICY(
object_schema => 'HR'
,object_name => 'EMPLOYEES'
,policy_name => 'AUDIT_EMPLOYEES'
,audit_condition => 'EMPLOYEE_ID IS NOT NULL'
,audit_column => 'EMPLOYEE_ID'
,handler_schema => 'HR'
,handler_module => 'PRC_AUDIT_EMPLOYEES'
,enable => TRUE
,statement_types => 'SELECT,INSERT,UPDATE,DELETE'
);
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;
/En caso de existir el script anterior re-crea la auditoria AUDIT_EMPLOYEES asociada a la tabla EMPLOYEES con el proceso PRC_AUDIT_EMPLOYEES como manejador.
SELECT
usuario_db
, fecha
, valor_consulta
, programa
, sid
, object_schema
, object_name
, policy_name
, TO_CHAR(query) script
FROM hr.consultas_tab_employees
ORDER BY fecha DESC;
Consultamos nuestra tabla consultas_tab_employees y vemos las distintas auditorias realizadas.
SELECT
session_id
, timestamp
, db_user
, object_schema
, object_name
, policy_name
, scn
, sql_text
FROM dba_fga_audit_trail
ORDER BY timestamp DESC;
De igual manera consultamos la vista dba_fga_audit_trail.