Oracle-如何授予用户对另一个用户对象的权限 [英] Oracle - How to grant to a user the rights to another user's objects

问题描述

我需要授予用户TARGETUSER选择/插入/更新用户SOURCEUSER的所有表的权限(我可以从

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提供SOURCE_USER对象的所有非DDL活动的能力，我就不会抱怨.我该怎么做?

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?

推荐答案

您可以编写一个简单的过程来做到这一点:

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;

不确定要问的是什么.您可以修改上面的内容，以添加其他授予和/或object_types，以添加您想要提供给targetuser的特权.就像@stili所暗示的那样，您可以对角色做很多事情，但是要小心-通过角色授予某些权限后，这些权限将不起作用.

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.

这篇关于Oracle-如何授予用户对另一个用户对象的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！