如何创建授予权限的Oracle触发器

Question

我想做一些概念上简单但在现实中似乎要复杂得多的事情。

基本上，每当在我们的数据库中为几个用户创建一个新表时，我都希望授予角色选择权限。基本上是这样的：

grant select on TABLENAME to READROLE;

到目前为止，我的触发器看起来像这样：

创建或替换触发器osm_grant_on_CREATE

在OSMM.SCHEMA 上创建后

开始

    //grant goes here

结束

问题是，我不知道如何通过获取新创建的表的名称并通过触发器将其引用到grant来将两者连接在一起。有什么建议吗？谢谢

Answer 1

它可能比你想象的更复杂。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

Answer 2

您可能需要执行以下操作：

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

但我无法检查它是否工作