todos - ¿Cómo creo un activador de Oracle que otorga permisos?

oracle roles y privilegios

Question

Quiero hacer algo que es conceptualmente simple pero parece ser mucho más complejo en realidad.

Básicamente, cada vez que se crea una nueva tabla para un par de usuarios en nuestra base de datos, quiero otorgar permisos selectos a una función. Básicamente esto:

grant select on TABLENAME to READROLE;

Hasta ahora mi gatillo se ve así:

CREAR O REEMPLAZAR EL GATILLO osmm_grant_on_creation

DESPUÉS DE CREAR EN OSMM.SCHEMA

EMPEZAR

    //grant goes here

FIN

El problema es que no puedo entender cómo unir las dos juntas obteniendo el nombre de la tabla recién creada y haciendo referencia a ella a través del desencadenante de la concesión. ¿Alguna sugerencia? Gracias.

Answer 1

Es probable que sea más complejo de lo que piensas. La sentencia GRANT es DDL, lo que significa que emite confirmaciones implícitas, lo que significa que no puede ponerlo en un desencadenador directamente. Su desencadenante tendría que enviar un trabajo que se ejecutó en una sesión separada después de la transacción desencadenante comprometida, lo que realmente haría la concesión. Y eso significa que debe usar el paquete anterior DBMS_JOB para programar el trabajo, ya que el DBMS_SCHEDULER más moderno también lo confirma implícitamente.

Dado que no debe crear tablas sobre la marcha en Oracle en primer lugar, el lugar adecuado para este tipo de concesión es en los scripts de compilación que ejecuta para crear la tabla en primer lugar. Depender de los factores desencadenantes para hacer cosas como las concesiones solo hace que sea más difícil hacer las compilaciones correctamente porque ejecutar exactamente la misma secuencia de comandos en dos entornos diferentes puede generar dos resultados diferentes debido a las diferencias en el desencadenador.

Si estás decidido a seguir este camino, sin embargo, probablemente quieras algo como

Un procedimiento que otorga el privilegio

CREATE OR REPLACE PROCEDURE grant_select_to_readrole( p_table_name IN VARCHAR2 )
AS
BEGIN
  EXECUTE IMMEDIATE ''grant select on '' || p_table_name || '' to readrole'';
END;

Y un activador que envía un trabajo que llama a este procedimiento

CREATE OR REPLACE TRIGGER osmm_grant_on_creation
  AFTER CREATE ON OSMM.SCHEMA
AS
  l_jobno PLS_INTEGER;
BEGIN
  dbms_job.submit( l_jobno,
                   ''BEGIN grant_select_to_readrole( '''''' || ora_dict_obj_name || '''''' ); END;'',
                   sysdate + interval ''10'' second );
END;

Si tratara de emitir DDL en el disparador de nivel de esquema, obtendría un error

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger after_create_on_scott
  2    after create on schema
  3  declare
  4  begin
  5    execute immediate ''grant select on scott.emp to hr'';
  6* end;
SQL> /

Trigger created.

SQL> create table foo( col1 number );
create table foo( col1 number )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 3

Answer 2

probablemente deba hacer algo como:

CREATE OR REPLACE TRIGGER osmm_grant_on_creation

AFTER CREATE ON OSMM.SCHEMA
DECLARE
new_obj_name varchar2(30);
BEGIN
SELECT ora_dict_obj_name
INTO new_obj_name
FROM dual
WHERE ora_dict_obj_type = ''TABLE'';

execute immediate ''grant select on '' || new_obj_name || '' to READROLE'';
END

pero no puedo verificar si funciona