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

2022-01-01 00:00:00 oracle triggers ddl-trigger

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

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:

将 TABLENAME 上的选择授予 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

结束

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

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

相关文章