当前位置：文江博客话题详情

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

发布于 2025-01-05 17:03:11 字数 381 浏览 1 评论 0原文

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

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

grant select on TABLENAME to READROLE;

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

创建或替换触发器 osmm_grant_on_creation
在 OSMM.SCHEMA 上创建之后
开始
 //此处授予
结束

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

原文

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:

CREATE OR REPLACE TRIGGER osmm_grant_on_creation
AFTER CREATE ON OSMM.SCHEMA
BEGIN
    //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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

古镇旧梦 2025-01-12 17:03:11

它可能比您想象的还要复杂。 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

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.

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

A procedure that grants the privilege

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;

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

回复收藏 0 原文

长安忆 2025-01-12 17:03:11

你可能需要做类似的事情：

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

但我无法检查它是否有效

you probabply need to do something like:

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

but I can't check if it works

回复收藏 0 原文

~没有更多了~