授予用户访问Oracle中其他架构的正确方法 [英] Correct way to give users access to additional schemas in Oracle

问题描述

我在Oracle中有两个用户Bob和Alice，都是通过从sqlplus中以sysdba身份运行以下命令来创建的:

I have two users Bob and Alice in Oracle, both created by running the following commands as sysdba from sqlplus:

   create user $blah identified by $password;
   grant resource, connect, create view to $blah;

我希望Bob可以完全访问Alice的架构(即所有表)，但是我不确定要运行什么授权以及是否以sysdba或Alice的身份运行它.

I want Bob to have complete access to Alice's schema (that is, all tables), but I'm not sure what grant to run, and whether to run it as sysdba or as Alice.

很高兴听到有关参考资料的任何很好的指示-似乎无法从Internet或坐在我的"Oracle Database 10g完整参考"中获得对此的良好答案.桌子.

Happy to hear about any good pointers to reference material as well -- don't seem to be able to get a good answer to this from either the Internet or "Oracle Database 10g The Complete Reference", which is sitting on my desk.

推荐答案

AFAIK，您需要一次做一个Grants对象.

AFAIK you need to do the grants object one at a time.

通常，您将使用脚本来执行此操作，类似于:

Typically you'd use a script to do this, something along the lines of:

SELECT 'GRANT ALL ON '||table_name||' TO BOB;'
FROM   ALL_TABLES
WHERE  OWNER = 'ALICE';

与其他数据库对象类似.

And similar for other db objects.

您可以在需要颁发许可的每个模式中放置一个包，通过该包，将通过EXECUTE IMMEDIATE调用每个GRANT语句.

You could put a package in each schema that you need to issue the grant from which will go through all call each GRANT statement via an EXECUTE IMMEDIATE.

例如

   PROCEDURE GRANT_TABLES
   IS
   BEGIN

      FOR tab IN (SELECT table_name
                  FROM   all_tables
                  WHERE  owner = this_user) LOOP
         EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||tab.table_name||' TO other_user';
      END LOOP;
   END;

这篇关于授予用户访问Oracle中其他架构的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！