如何创建授予权限的 Oracle 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9262539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a Oracle trigger that grants permissions
提问by Justin Cave
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;
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 hereEND
创建或替换触发器 osmm_grant_on_creation
在 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.
问题是，我无法弄清楚如何通过获取新创建的表的名称并通过触发器将其引用到授权来将两者连接在一起。有什么建议？谢谢。
回答by Justin Cave
It's likely more complex than you're even thinking. The GRANTstatement 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_JOBpackage to schedule the job since the more modern DBMS_SCHEDULERalso implicitly commits.
它可能比你想象的更复杂。该GRANT语句是 DDL，这意味着它发出隐式提交，这意味着您不能将其直接放入触发器中。您的触发器需要提交在触发事务提交后在单独会话中运行的作业，这实际上会进行授权。这意味着您必须使用较旧的DBMS_JOB包来安排作业，因为更现代的包DBMS_SCHEDULER也会隐式提交。
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.
由于您不应该首先在 Oracle 中动态创建表，因此此类授权的正确位置是在您首先运行以创建表的构建脚本中。依靠触发器来执行诸如授权之类的事情只会使正确构建变得更加困难，因为在两个不同的环境中运行完全相同的脚本可能会因触发器的差异而产生两种不同的结果。
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
如果您尝试在架构级触发器本身中发出 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
回答by A.B.Cade
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
但我无法检查它是否有效
