把某一用户所有表的权限授予其它用户

1、创建相应的用户，并授予必要权限           

create user oak identified by oak;

create user oaks identified by oaks;

grant create session to oaks;

grant create session to oak;

alter user oaks quota 50M on users ;

alter user oak quota 50M on users;
2、创建测试用表

create table oaks.oaks
(id number);
create table oaks.test
(id number);
insert into oaks.oaks values (7);

insert into oaks.test values (9);
commit

3、授权

3.1在sqlplus 下运行下面的语句

spool /tmp/grant_oak.sql

select 'grant all on oaks.'||table_name||' to oak;' from dba_tables where wner='OAKS';
select 'create synonym oak.'||table_name||' for oaks.'||table_name||';' from dba_tables where wner='OAKS';
spool off

3.2退出sqlplus编辑把grant_oak.sql的非授权语句删掉

3.3运行grant_oak.sql

sqlplus / as sysdba

@/tmp/grant_oak.sql

4、以oak连接测试

select * from test;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7419833/viewspace-231252/，如需转载，请注明出处，否则将追究法律责任。