Cómo automatizar GRANTS en nuevas tablas y procedimientos usando Triggers DDL y DBMS_SCHEDULER

Cómo automatizar GRANTS en nuevas tablas y procedimientos usando Triggers DDL y DBMS_SCHEDULER

   junio 15, 2026       Luis Felipe

---

¿Cuántas veces te ha pasado? Alguien del equipo crea una nueva tabla o un procedimiento en el ambiente de desarrollo, pero los demás integrantes no pueden acceder a ella debido a que no tienen los privilegios necesarios y para completar el DBA está fuera de oficina o de vacaciones...

Asignar permisos manualmente es ineficiente y propenso a errores humanos. Hoy vamos a ver cómo automatizar por completo esta tarea mediante un Trigger DDL.

Sin embargo, si estás trabajando en Oracle 12c, hacerlo directamente dentro de un trigger te llevará de cabeza a un callejón sin salida (específicamente al temido ORA-04092). En este artículo te enseño cómo estructurar una solución asíncrona, limpia y segura utilizando Transacciones Autónomas y DBMS_SCHEDULER.

Cuando ejecutas un CREATE TABLE, Oracle realiza un COMMIT implícito inmediatamente antes y después del comando. Si intentas meter un GRANT (que es un comando DCL) directamente dentro de un Trigger DDL convencional, romperás el ciclo transaccional del motor, resultando en un error de bloqueo o un fallo de compilación.

Para solucionarlo en Oracle 12c, debemos aplicar estos dos conceptos clave:

1. Un Job asíncrono: Que se encargue de dar el permiso milisegundos después de que termine la creación del objeto.

2. Una Transacción Autónoma (PRAGMA AUTONOMOUS_TRANSACTION): Que le permita al trigger abrir una "mini-sesión" independiente para agendar el Job sin interferir con el CREATE principal.

Nota: Aunque en versiones antiguas se usaba DBMS_JOB, en Oracle 12c la recomendación oficial es usar el paquete moderno DBMS_SCHEDULER, ya que es mucho más robusto y se auto-gestiona de forma más eficiente.

Primero creamos un procedimiento auxiliar. Usamos SQL dinámico (EXECUTE IMMEDIATE) combinándolo con DBMS_ASSERT.ENQUOTE_NAME por seguridad frente a inyecciones de SQL.

Es fundamental incluir la cláusula AUTHID CURRENT_USER para que se ejecute bajo los privilegios del usuario actual en tiempo de ejecución.

CREATE OR REPLACE PROCEDURE sp_otorgar_grant (
    p_object_name IN VARCHAR2,
    p_object_type IN VARCHAR2
) AUTHID CURRENT_USER AS
    v_sql VARCHAR2(1000);
BEGIN
    -- Evaluamos el tipo de objeto para asignar el privilegio correcto
    IF p_object_type = 'TABLE' THEN
        v_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || dbms_assert.enquote_name(p_object_name) || ' TO ROL_TEST';
    ELSIF p_object_type = 'PROCEDURE' THEN
        v_sql := 'GRANT EXECUTE ON ' || dbms_assert.enquote_name(p_object_name) || ' TO ROL_TEST';
    END IF;
 
    -- Si el tipo de objeto aplica, ejecutamos el DCL
    IF v_sql IS NOT NULL THEN
        EXECUTE IMMEDIATE v_sql;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

Ahora creamos el Trigger a nivel de base de datos (AFTER CREATE ON DATABASE) para centralizar los permisos en un solo trigger. Gracias al PRAGMA AUTONOMOUS_TRANSACTION, este bloque puede ejecutar su propio COMMIT interno (necesario al crear un Job) sin afectar la transacción de la tabla que se está creando en ese instante.

CREATE OR REPLACE TRIGGER trg_auto_grant_objetos
AFTER CREATE ON SCHEMA
DECLARE
    -- Habilitamos la transacción independiente para evitar el ORA-04092
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_obj_type  VARCHAR2(30);
    v_job_name  VARCHAR2(100);
    v_what      VARCHAR2(2000);
BEGIN
    v_obj_type := DICTIONARY_OBJ_TYPE;
    -- Filtramos únicamente los objetos que nos interesan
    IF v_obj_type IN ('TABLE', 'PROCEDURE') THEN
        -- Generamos un nombre único y dinámico para el Job
        v_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('JOB_GRANT_');
        -- Definimos el bloque anónimo que ejecutará el Job en segundo plano
        v_what     := 'BEGIN sp_otorgar_grant(''' || DICTIONARY_OBJ_NAME || ''', ''' || v_obj_type || '''); END;';
        -- Lanzamos el Job de ejecución inmediata
        DBMS_SCHEDULER.CREATE_JOB (
            job_name        => v_job_name,
            job_type        => 'PLSQL_BLOCK',
            job_action      => v_what,
            start_date      => SYSTIMESTAMP + INTERVAL '3' SECOND,
            enabled         => TRUE,
            auto_drop       => TRUE -- El job se elimina automáticamente al finalizar
        );
        -- Confirmamos la creación del Job en su transacción independiente
        COMMIT;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

Nota: Es necesario que el usuario (esquema) donde fue creado el trigger necesita privilegios para crear jobs en la base de datos.

GRANT CREATE JOB TO USUARIO_TRIGGER;

 

Una peculiaridad de delegarle la tarea a DBMS_SCHEDULER es que, si algo falla (por ejemplo, el rol no existe o el objeto tiene un nombre extraño), el error no saltará en tu script de despliegue. Ocurrirá de manera silenciosa en segundo plano.

Para auditar y comprobar que los permisos se están asignando con éxito, puedes interrogar periódicamente a la vista del diccionario de datos:

SELECT log_date, job_name, status, error#
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name LIKE 'JOB_GRANT_%'
ORDER BY log_date DESC;

Si en la columna STATUS visualizas SUCCEEDED, felicidades: tus objetos en Oracle 12c ya se auto-gestionan solos.

Importante: Si vas a realizar un despliegue masivo en producción (un script que crea cientos de tablas de golpe), la generación masiva de Jobs individuales podría estresar momentáneamente el Scheduler de Oracle. En esos escenarios específicos, la buena práctica dicta deshabilitar temporalmente el trigger antes de la actualización y habilitarlo al finalizar:

-- Deshabilitar el trigger
ALTER TRIGGER trg_auto_grant_objetos DISABLE;
-- Habilitar el trigger
ALTER TRIGGER trg_auto_grant_objetos ENABLE;

En definitiva, automatizar la asignación de permisos mediante Transacciones Autónomas y el poder asíncrono de DBMS_SCHEDULER transforma una tarea manual, tediosa y propensa a fallos en un proceso invisible que eleva la robustez de tu infraestructura. Al implementar esta solución —asegurándote siempre de que tu usuario cuente con el privilegio directo CREATE JOB y configurando un ligero retraso de un par de segundos en el inicio del Job para sincronizarse con el COMMIT principal—, no solo eliminarás de raíz los molestos tickets de soporte por olvido de GRANTS, sino que llevarás tus despliegues en Oracle al siguiente nivel de madurez técnica y profesional.

Saludos.

Read More

Resolviendo las limitaciones del Auto Optimizer Stats Collection en Oracle

Resolviendo las limitaciones del Auto Optimizer Stats Collection en Oracle

   marzo 20, 2026       Luis Felipe

---

En el mundo de la administración de bases de datos, las estadísticas son la ruta de acceso que el optimizador (CBO) elegirá para ejecutar una sentencia SQL. Sin ellas, hasta la consulta más simple puede convertirse en una pesadilla de rendimiento. Oracle nos ofrece por defecto el Auto Optimizer Stats Collection, un job diseñado gestionar la recolección de estadísticas de forma integral y desatendida. Sin embargo, en entornos de Data Warehouse con volúmenes masivos de datos esto suele ser insuficiente.

Recientemente, en una de las bases de datos que administro, me enfrenté a un escenario crítico: el mantenimiento nativo de Oracle no lograba concluir dentro de la ventana programada de 4 horas. Esto derivaba en la presencia de estadísticas obsoletas (STALE) y el bloqueo de estadísticas en objetos específicos, provocando una degradación progresiva en el rendimiento global de la base de datos.

• El proceso se interrumpía dejando gran parte de los objetos sin procesar.

• Las estadísticas quedaban en un estado inconsistente, impidiendo actualizaciones manuales y disparando errores como el ORA-20005.

• Sin datos frescos, el optimizador elegía rutas de acceso ineficientes, aumentando drásticamente la latencia global del sistema.

Para romper este ciclo, decidimos tomar el control total mediante una automatización personalizada. Nuestra lógica aplica reglas de ingeniería de datos claras:

• El procedimiento identifica si el esquema contiene tablas particionadas o normales para aplicar el método de recolección correcto.

• Priorizamos la última partición vigente, asegurando que el optimizador (CBO) siempre tenga datos frescos de la carga reciente sin desperdiciar recursos en datos históricos estáticos.

• Implementamos una tabla de control para registrar cada inicio, fin y duración, permitiendo una auditoría que el job nativo no ofrece de forma tan detallada.

Importante: Antes de implementar esta solución es necesario desactivar la recolección automática de estadísticas de Oracle. 

Pero antes verificamos el estado actual de la tarea de recolección de estadísticas.

select client_name,status from dba_autotask_client where client_name='auto optimizer stats collection';

Procedemos a desactivar la tarea de recolección automática de Oracle.

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL
  );
END;
/

Verificamos nuevamente el estado actual de la tarea de recolección de estadísticas.

He compartido la solución completa (DDL de bitácora, procedimiento PL/SQL y configuración del Job) en mi repositorio de GitHub para que la comunidad pueda adaptarlo:

🔗Oracle-Stats-Automation

Puedes consultar el estado de tus estadísticas y el rendimiento del job mediante la tabla de bitácora:

select * from sys.gbd_bitacora_estat where grupo=5 order by 1,2 asc;

Esta solución fue desarrollada y validada con éxito en un entorno Oracle Database 12c, demostrando una estabilidad total tanto en la gestión de objetos particionados como en la integración con el Oracle Scheduler. Aunque el enfoque es estándar, se recomienda realizar pruebas previas en entornos de desarrollo antes de pasar a producción.

Read More