使用shell脚本生成只读权限的sql脚本（r2第28天)

目前做数据迁移，有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;