怎么指定用户只能访问某一个表空间所有内容？

[frogshero]

不要对每一个表都执行grant select ,grant insert 吧？

[fuxia]

grant select，insert on 'select table_name from ' || tablespace_name to user_name;

[NinGoo]

select 'grant select on '||table_name from all_tables where tablespace_name='USERS';

上述语句可以获得授权select所有users表空间的table的权限的语句,可以先spool c:\test.sql,然后修改执行test.sql即可

[NinGoo]

更正一下
select 'grant select on '||table_name||' to user_name' from all_tables where tablespace_name='USERS';

[NinGoo]

或者写成一个存储过程

create or replace procedure grant_on_tablespace
(tb_name in varchar2,user_name in varchar2)
is
begin
for rec in (select table_name,OWNER
from all_tables
where tablespace_name=tb_name)
loop
execute immediate 'grant select on '||rec.table_name||' to '||user_name;
end loop;
end grant_on_tablespace;

/*调用*/

grant_on_tablespace ('tablespace_name','user_name')