在Oracle中，给用户访问额外模式的正确方法是什么？

Question

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

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

我希望 Bob 完全访问 Alice 的模式（即所有表），但我不确定应该授予什么权限以及是作为 sysdba 还是 Alice 运行。

很高兴听取任何好的指向参考资料的建议 - 无论是来自互联网还是我的桌子上放着的 "Oracle Database 10g The Complete Reference"。

- Jacob

---

[tag:sql-grant] 标签正在添加中，以与响应 MSO 问题 Split "revoke" tag into "sql-revoke" and "token-revoke" 创建的 [tag:sql-revoke] 标签并行。随着时间的推移，[tag:grant] 标签将被删除。 - Jonathan Leffler

Answer 1

据我所知，您需要逐个处理授予对象。

通常，您将使用脚本来完成此操作，类似于以下内容：

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;

- cagcowboy

Answer 2

有很多事情要考虑。当您说访问时，是否希望在其他用户名称前缀表名? 如果需要隐藏原始所有者，可以使用公共同义词。然后在同义词上授予权限。

您还希望尽可能提前计划。稍后，您是否还希望Frank能够访问Alice的模式? 您不希望在N个表上重新授予权限。使用数据库角色是更好的解决方案。例如，授予选择角色“ALICE_TABLES”，当另一个用户需要访问时，只需向他们授予角色权限即可。这有助于组织您在DB内进行的授予。

- Brett

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;

- arnep