oracle 授予对表空间的选择、插入、更新权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6198387/
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
Grant Select, Insert, Update to a Tablespace
提问by Mikayil Abdullayev
I've got a lot of tables in a tablespace, nearly 100. I have to grant Select, Insert, Update privileges on all those tables to a user. Is it possible? When I write:
我在一个表空间中有很多表，将近 100 个。我必须向用户授予对所有这些表的选择、插入、更新权限。是否可以？当我写：
GRANT USE OF TABLESPACE MYTABLESPACE TO USERNAME
I get oracle error "invalid or missing privilege"
我收到 oracle 错误“无效或缺少特权”
回答by Tony Andrews
USE OF TABLESPACEis not a documented option, where did you find that?
USE OF TABLESPACE不是一个记录的选项，你在哪里找到的？
You can do this to allow a user to create objects in a tablespace:
您可以这样做以允许用户在表空间中创建对象：
alter user username quota [amount] on mytablespace;
To grant select, insert, update and delete on objects you have to run a separate grantcommand for each table:
要在对象上授予选择、插入、更新和删除权限，您必须grant为每个表运行单独的命令：
grant select, insert, update, delete on mytable1 to username;
....
回答by Frank Schmitt
Use the data dictionary view dba_tables (resp. all_tables, if you cannot access dba_tables):
使用数据字典视图 dba_tables（resp.all_tables，如果您无法访问 dba_tables）：
declare
l_SQL varchar2(4000);
begin
for cur in (
select * from dba_tables where tablespace_name = 'mytablespace')
loop
l_sql := 'grant select, insert, update on ' || cur.owner || '.' || cur.table_name || ' to myuser';
--dbms_output.put_line(l_SQL || ';');
execute immediate l_SQL;
end loop;
end;
If you just want to generate a script, comment out the execute immediate and un-comment the dbms_output.
如果只想生成脚本，请注释掉立即执行并取消注释 dbms_output。
