select 'grant select on '||table_name from all_tables where tablespace_name='USERS';上述语句可以获得授权select所有users表空间的table的权限的语句,可以先spool c:\test.sql,然后修改执行test.sql即可
更正一下 select 'grant select on '||table_name||' to user_name' from all_tables where tablespace_name='USERS';
或者写成一个存储过程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')
select 'grant select on '||table_name||' to user_name' from all_tables where tablespace_name='USERS';
(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')