将 Oracle 中的 DDL 授予特定用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3670822/
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
GRANT DDL in Oracle to specific user
提问by Damian Leszczyński - Vash
How to grant DDL privileges in oracle ?
如何在oracle中授予DDL权限？
On database I've users SCHEMA_1, SCHEMA_2 and SCHEMA_3
在数据库上我有用户 SCHEMA_1、SCHEMA_2 和 SCHEMA_3
and now i want to from schema_1 be able to do DDL only on SCHEMA_2
现在我想从 schema_1 只能在 SCHEMA_2 上做 DDL
Is the grant is possible from SCHEMA_2 level or system only ?
是否只能从 SCHEMA_2 级别或系统获得授权？
回答by Adam Musch
Oracle doesn't work that way. You'd have to grant CREATE ANY [OBJECT_TYPE]to that user and have a system event trigger which restricts them from working in the schemas you don't want them to.
Oracle 不是这样工作的。您必须授予CREATE ANY [OBJECT_TYPE]该用户并拥有一个系统事件触发器，以限制他们在您不希望他们使用的模式中工作。
Warning: Undocumented / underdocumented features of DBMS_STANDARD are used.
警告：使用了 DBMS_STANDARD 的未记录/未记录的功能。
CREATE OR REPLACE TRIGGER schema_1_on_schema_2
before DDL on DATABASE
as
has_dba_priv number;
n number;
stmt ora_name_list_t;
BEGIN
-- exit if user is object owner
if ora_dict_obj_owner = ora_login_user then
return
end if;
-- exit if user has dba directly
select count(*)
into has_dba_priv
from dba_role_privs
where granted_role = 'DBA'
and grantee = ora_login_user;
if has_dba_priv <> 0 then
return;
end if;
-- exit if action is an automatic recompile
stmt := null;
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
if stmt like 'ALTER % COMPILE REUSE SETTINGS%' then
return;
end if;
-- you should probably organize this into a database table of permitted
-- schema_x can affect schema_y, but this is a "basic" example
if (ora_dict_obj_owner = 'SCHEMA_2')
and (ora_login_user = 'SCHEMA_1') then
null;
else
raise_application_error (-20000, 'User ' || ora_login_user ||
' is not permitted to execute DDL against ' || ora_dict_obj_owner);
end if;
end;
回答by Gary Myers
A better way might be to embed the schema_2 DDL into procedures and grant execute on those procedures to schema_1. A fuller explanation of your requirements may lead to fuller / better answers.
更好的方法可能是将 schema_2 DDL 嵌入到过程中，并将对这些过程的执行授予 schema_1。对您的要求进行更全面的解释可能会得到更全面/更好的答案。
