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