目前做数据迁移，有8套不同的环境，为了保护环境，每个环境中的表，视图等开发都不能修改，只能通过连接用户去查询。
每个环境中可能含有表，索引，序列，存储过程，函数等，所以一个一个写是不现实的，写了下面的动态脚本来自动生成相应的权限，然后创建对应的同义词。

脚本会生成两个sql脚本，一个是owner用户赋予权限使用的，另外一个脚本是connect用户使用的，创建了对应的同义词。

source_schema=$1
conn_schema=$2
sqlplus -s xxx/xx  < set feedback off
set pages 0
set linesize 150
spool owner_${source_schema}_grant.sql
select 'grant select on ${source_schema}.'||object_name||' to '||' ${conn_schema};' from all_objects where object_type in ('TABLE','TABLE PARTITION','VIEW','SEQUENCE') and owner=upper('$source_schema') group by  'grant select on ${source_schema}.'||object_name||' to '||' ${conn_schema};' ;
select 'grant execute on ${source_schema}.'||object_name||' to '||' ${conn_schema};' from all_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE') and owner=upper('$source_schema') group by 'grant execute on ${source_schema}.'||object_name||' to '||' ${conn_schema};';
spool off;
spool conn_${conn_schema}_syn.sql
select 'create synonym  ${conn_schema}.'||object_name||' for '||' ${source_schema}.'||object_name||';' from all_objects where object_type in ('TABLE','TABLE PARTITION','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE') and owner=upper('$source_schema')  group by 'create synonym  ${conn_schema}.'||object_name||' for '||' ${source_schema}.'||object_name||';';
spool off;
EOF
exit

运行结果如下：

grant select on cnvdbo8.aaaa to  cnvdbc8;                                                                                                
grant select on cnvdbo8.bbbb to  cnvdbc8;                                                                                           
grant select on cnvdbo8.cccc to  cnvdbc8;                                                                                                  

grant execute on cnvdbo8.dddd to  cnvdbc8;                                                                                               
grant execute on cnvdbo8.eeee  to  cnvdbc8;                                                                                       
grant execute on cnvdbo8.ffff to  cnvdbc8;


create synonym  cnvdbc8.aaaa for  cnvdbo8.aaaa;                                                                                   
create synonym  cnvdbc8.bbbb for  cnvdbo8.bbbb;                                                                                       
create synonym  cnvdbc8.cccc for  cnvdbo8.cccc;

本文转自ICT时空dbasdk的博客，原文链接：使用shell脚本生成只读权限的sql脚本 ，如需转载请自行联系原博主。