如何创建授予权限的Oracle触发器 [英] How do I create a Oracle trigger that grants permissions

问题描述

我想做一些概念上简单的事情，但实际上似乎要复杂得多.

I want to do something that's conceptually simple but seems to be a lot more complex in reality.

基本上，每当为数据库中的几个用户创建一个新表时，我都想授予角色一个选择权限.基本上是这样:

Basically, whenever a new table is created for a couple of users in our database, I want to grant select permissions to a role. Basically this:

grant select on TABLENAME to READROLE;

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

So far my trigger looks something like this:

创建或替换触发器osmm_grant_on_creation

CREATE OR REPLACE TRIGGER osmm_grant_on_creation

在OSMM.SCHEMA上创建之后

AFTER CREATE ON OSMM.SCHEMA

开始

    //grant goes here

END

问题是，我无法通过获取新创建表的名称并通过触发器将其引用到授予来弄清楚如何将两者结合在一起. 有什么建议?谢谢.

Problem is, I can't figure out how to join the two together by getting the name of the newly created table and referencing it through the trigger to the grant. Any suggestions? Thanks.

推荐答案

它可能比您想的还要复杂. GRANT语句是DDL，这意味着它发出隐式提交，这意味着您不能将其直接放入触发器中.您的触发器将需要提交一份作业，该作业在触发事务提交后将在单独的会话中运行，而该事务实际上会进行授权.这意味着您必须使用较旧的DBMS_JOB包来计划作业，因为更现代的DBMS_SCHEDULER也会隐式提交.

It's likely more complex than you're even thinking. The GRANT statement is DDL which means that it issues implicit commits which means that you cannot put it in a trigger directly. Your trigger would need to submit a job which ran in a separate session after the triggering transaction committed which would actually do the grant. And that means that you have to use the older DBMS_JOB package to schedule the job since the more modern DBMS_SCHEDULER also implicitly commits.

由于您首先不应该在Oracle中动态创建表，因此这种授予的合适位置是您最初用来创建表的构建脚本中.依靠触发器来执行诸如赠款之类的事情只会使构建更加困难，因为由于触发器的不同，在两个不同的环境中运行完全相同的脚本可能会产生两个不同的结果.

Since you shouldn't be creating tables on the fly in Oracle in the first place, the proper place for this sort of grant is in the build scripts that you run to create the table in the first place. Relying on triggers to do things like grants just tends to make it more difficult to do builds properly because running exactly the same script in two different environments may generate two different results because of differences in the trigger.

但是，如果您决定走这条路，那么您可能想要类似的东西

If you're determined to go down this path, however, you'd probably want something like

授予特权的过程

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;

还有一个触发器，该触发器提交了一个调用此过程的作业

And a trigger that submits a job that calls this procedure

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，则会收到错误消息

If you were to try to issue DDL in the schema-level trigger itself, you'd get an 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

这篇关于如何创建授予权限的Oracle触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！