让用户访问 Oracle 中其他模式的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/198952/
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
Correct way to give users access to additional schemas in Oracle
提问by Jacob
I have two users Bob and Alice in Oracle, both created by running the following commands as sysdba from sqlplus:
我在 Oracle 中有两个用户 Bob 和 Alice，这两个用户都是通过从 sqlplus 以 sysdba 身份运行以下命令创建的：
create user $blah identified by $password; grant resource, connect, create view to $blah;
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.
我希望 Bob 能够完全访问 Alice 的模式（即所有表），但我不确定要运行什么授权，以及是否以 sysdba 或 Alice 身份运行它。
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.
很高兴听到关于参考资料的任何好的指针——似乎无法从 Internet 或我办公桌上的“Oracle 数据库 10g 完整参考”中得到一个好的答案。
回答by cagcowboy
AFAIK you need to do the grants object one at a time.
AFAIK 你需要一次做一个授权对象。
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.
与其他 db 对象类似。
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.
您可以在每个架构中放置一个包，您需要从中发出授权，从中通过 EXECUTE IMMEDIATE 调用每个 GRANT 语句。
e.g.
例如
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;
回答by Brett McCann
There are many things to consider. When you say access, do you want to prefix the tables with the other users name? You can use public synonyms so that you can hide the original owner, if that is an issue. And then grant privs on the synonym.
有很多事情需要考虑。当你说访问时，你想用其他用户名作为表的前缀吗？如果这是一个问题，您可以使用公共同义词，以便您可以隐藏原始所有者。然后授予同义词的特权。
You also want to plan ahead as best you can. Later, will you want Frank to be able to access Alice's schema as well? You don't want to have to regrant privileges on N number of tables. Using a database role would be a better solution. Grant the select to role "ALICE_TABLES" for example and when another user needs access, just grant them privilege to the role. This helps to organize the grants you make inside the DB.
您还希望尽可能提前计划。稍后，您是否希望 Frank 也能够访问 Alice 的模式？您不想重新授予对 N 个表的权限。使用数据库角色将是更好的解决方案。例如，将选择授予角色“ALICE_TABLES”，当另一个用户需要访问时，只需授予他们对该角色的权限。这有助于组织您在数据库中所做的授权。
回答by arnep
Another solution if you have different owner:
如果您有不同的所有者，则另一种解决方案：
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;
