Как создать триггер Oracle, предоставляющий разрешения

Вероятно, это сложнее, чем вы даже думаете. Оператор GRANT является DDL, что означает, что он выполняет неявные фиксации, что означает, что вы не можете напрямую поместить его в триггер. Ваш триггер должен будет отправить задание, которое будет выполняться в отдельном сеансе после совершения запускающей транзакции, которая фактически выполнит грант. А это значит, что вы должны использовать более старый пакет DBMS_JOB для планирования задания, поскольку более современный DBMS_SCHEDULER также неявно фиксирует.

Поскольку вы не должны создавать таблицы на лету в Oracle в первую очередь, правильное место для такого рода предоставления находится в сценариях сборки, которые вы запускаете для создания таблицы в первую очередь. Использование триггеров для выполнения таких действий, как предоставление грантов, только усложняет правильную сборку, потому что запуск одного и того же скрипта в двух разных средах может привести к двум разным результатам из-за различий в триггерах.

Однако, если вы полны решимости пойти по этому пути, вам, вероятно, понадобится что-то вроде

Процедура, предоставляющая привилегию

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;

И триггер, который отправляет задание, вызывающее эту процедуру

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;

Если бы вы попытались запустить DDL в самом триггере уровня схемы, вы бы получили сообщение об ошибке.

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

вам, вероятно, нужно сделать что-то вроде:

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

но не могу проверить работает ли