Oracle - 如何授予一个用户对另一个用户的对象的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/865586/
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
Oracle - How to grant to a user the rights to another user's objects
提问by George Mauer
I need to give to user TARGETUSER the rights to select/insert/update to all tables of user SOURCEUSER (I can figure this all out from here) and the ability to run all their stored procedures.
我需要授予用户 TARGETUSER 选择/插入/更新用户 SOURCEUSER 的所有表的权限（我可以从这里弄清楚这一切）以及运行所有存储过程的能力。
Basically, I wouldn't complain if I can give TARGETUSER the ability for all non-ddl activity with SOURCE_USER's objects. How do I do this?
基本上，如果我可以为 TARGETUSER 赋予 SOURCE_USER 对象的所有非 ddl 活动的能力，我不会抱怨。我该怎么做呢？
回答by DCookie
You can write a simple procedure to do this:
您可以编写一个简单的过程来执行此操作：
BEGIN
FOR Rec IN (SELECT object_name, object_type FROM all_objects WHERE owner='SOURCEUSER' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
IF Rec.object_type IN ('TABLE','VIEW') THEN
EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCEUSER.'||Rec.object_name||' TO TARGETUSER';
ELSIF Rec.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SOURCEUSER.'||Rec.object_name||' TO TARGETUSER';
END IF;
END LOOP;
END;
Not sure exactly what else you're asking for. You can modify the above to add additional grants and/or object_types for the privileges you want to provide to targetuser. As @stili implies, you can do a lot with roles, but be careful - some permissions do not work when granted via roles.
不确定你还要求什么。您可以修改上述内容，为要提供给目标用户的权限添加额外的授权和/或 object_types。正如@stili 暗示的那样，您可以对角色做很多事情，但要小心 - 通过角色授予某些权限时不起作用。
回答by stili
To generate SQL script you could use the following, similar to the solution suggested by DCookie
要生成 SQL 脚本，您可以使用以下内容，类似于 DCookie 建议的解决方案
SELECT 'GRANT SELECT, UPDATE, DELETE, INSERT ON ' || table_name || ' TO other_user;'
FROM all_tables WHERE owner = 'other_user';
UNION ALL
SELECT 'GRANT EXECUTE ON ' || object_name || ' TO other_user;'
FROM all_objects
WHERE owner = 'other_user'
AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE');
Generally, I would suggest using roles to avoid granting access rights for each user.
通常，我建议使用角色来避免为每个用户授予访问权限。
If using roles, run the following SQL as user you are copying roles from. You could also include other options like admin_optionand default_role.
如果使用角色，请以要从中复制角色的用户身份运行以下 SQL。您还可以包括其他选项，例如admin_option和default_role。
SELECT 'GRANT ' || granted_role || ' TO other_user;'
FROM user_role_privs;
Alternatively you could query dba_role_privsto get the roles of a specific user:
或者，您可以查询dba_role_privs以获取特定用户的角色：
SELECT 'GRANT ' || granted_role || ' TO other_user;'
FROM dba_role_privs WHERE grantee = 'source_user';
