公众号：杨建荣的学习笔记 · 作者：r2第28天 · 发布：2014-07-09 08:01:03 · 原文链接

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

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

source_schema=$1
conn_schema=$2
sqlplus -s xxx/xx <<EOF
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;