oracle 如何授予用户对特定架构的权限？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6955814/
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
how to grant user privilege on specific schema?
提问by kseen
The situation is I have two schemas: A and B. I have a restricted user that I need to give a privilege do SELECT queries in B schema and just it. How can I grant this user?
情况是我有两个模式：A 和 B。我有一个受限制的用户，我需要授予权限在 B 模式中执行 SELECT 查询，并且只是它。我如何授予此用户？
回答by Nimrod
You can't.
你不能。
The best you can do is grant user a 'select' privilege each table in schema b.
您能做的最好的事情是授予用户“选择”权限，即模式 b 中的每个表。
this query will generate the commands you'll need:
此查询将生成您需要的命令：
select 'grant select on A.'||table_name||' to B;'
from dba_Tables
where owner = 'A';
The problem with this, is in the case you will want to add new table to A. then you'll have to grant the privilege on it separately. it will not do it automatically..
问题在于，如果您想将新表添加到 A，那么您必须单独授予它的权限。它不会自动执行..
回答by Kevin Burton
You may find you do not have access to dba_tables,
The following block of code run in the owning schema (a) will grant permissions to all tables, to the user b
您可能会发现您无权访问dba_tables，以下代码块在拥有架构 (a) 中运行将授予所有表的权限，给用户 b
BEGIN
FOR t IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || t.table_name || ' TO b';
END LOOP;
END;
