允许用户访问 Oracle 中其他模式的正确方法

Question

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

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

我希望 Bob 能够完全访问 Alice 的架构（即所有表），但我不确定要运行什么授权，以及是以 sysdba 身份还是以 Alice 身份运行它。

我也很高兴听到有关参考资料的任何好的建议 - 似乎无法从 Internet 或我桌上的“Oracle Database 10g 完整参考”中获得对此问题的良好答案。

Answer 1

AFAIK，您需要一次执行一项赠款对象。

通常，您会使用脚本来执行此操作，大致如下：

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

对于其他数据库对象也类似。

您可以在需要发出授权的每个模式中放置一个包，该包将通过 EXECUTE IMMEDIATE 执行每个 GRANT 语句的所有调用。

例如

   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;

Answer 2

有很多事情需要考虑。 当您说访问时，是否要在表前添加其他用户名前缀？ 您可以使用公共同义词，以便隐藏原始所有者（如果这是一个问题）。 然后授予同义词的权限。

您还想尽可能提前计划。 稍后，您是否希望 Frank 也能够访问 Alice 的架构？ 您不希望必须重新授予 N 个表的权限。 使用数据库角色将是更好的解决方案。 例如，将选择权限授予角色“ALICE_TABLES”，当其他用户需要访问权限时，只需授予他们该角色的权限即可。 这有助于组织您在数据库内进行的资助。

Answer 3

如果您有不同的所有者，另一个解决方案：

BEGIN

  FOR x IN (SELECT owner||'.'||table_name ownertab
            FROM   all_tables
            WHERE  owner IN ('A', 'B', 'C', 'D'))
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON '||x.ownertab||' TO other_user';
  END LOOP;
END;