Oracle 生成批量 Grant 语句的 SQL

如果这里有A, B 两个Schema,

 

你想在A里面访问B的表的时候不需要在前面加上  ‘B.’ 这样的字符

 

那么你就需要授予A访问B的权限。但是这必须是一个表一个表授予的，当B里面表多了的时候，写这些 Grant 语句也是一件麻烦的事情。

 

 

今天再帮助同事解决这个问题的时候，我突然找到了一个懒人专用的办法。。。哈哈。。。。。

 

 

在B下面运行一下Sql:

 

select 'grant DELETE,INSERT,SELECT,UPDATE on B.'||table_name||' to A;' from user_tables

 

生成

 

grant DELETE,INSERT,SELECT,UPDATE on B.APP_HRZN_RANGE to A;                
grant DELETE,INSERT,SELECT,UPDATE on B.APP_HRZN_RANGE_SET to A;            
grant DELETE,INSERT,SELECT,UPDATE on B.APP_MKTGRP_ALT_INFLTR to A;         
grant DELETE,INSERT,SELECT,UPDATE on B.APP_MKTGRP_ITEM_PRI_REF to A;       
grant DELETE,INSERT,SELECT,UPDATE on B.APP_MWF_COMPARABLE_CFG to A;        
grant DELETE,INSERT,SELECT,UPDATE on B.APP_MWF_PRICE_CMPNT_CFG to A;       
grant DELETE,INSERT,SELECT,UPDATE on B.APP_RESOURCE to A;                  
grant DELETE,INSERT,SELECT,UPDATE on B.APP_RESOURCES_BK to A;              
grant DELETE,INSERT,SELECT,UPDATE on B.APP_RESOURCE_BK2 to A;              
grant DELETE,INSERT,SELECT,UPDATE on B.APP_SD_COST_TYPE to A;              
grant DELETE,INSERT,SELECT,UPDATE on B.APP_SD_CUST_COST_MAP to A;          
grant DELETE,INSERT,SELECT,UPDATE on B.APP_USDH to A;                      
grant DELETE,INSERT,SELECT,UPDATE on B.APP_USDH_CD to A;                   
grant DELETE,INSERT,SELECT,UPDATE on B.APP_USDH_CD_TSG to A;               
grant DELETE,INSERT,SELECT,UPDATE on B.APP_USDH_CD_TSG_TS to A;            
grant DELETE,INSERT,SELECT,UPDATE on B.CD_PKG to A;                        
grant DELETE,INSERT,SELECT,UPDATE on B.CD_PKG_MGF to A;                    
grant DELETE,INSERT,SELECT,UPDATE on B.CD_SUBS to A;                       
grant DELETE,INSERT,SELECT,UPDATE on B.COMMON_LOOKUP to A;                 
grant DELETE,INSERT,SELECT,UPDATE on B.ITEM_UOM_CONVERSION to A;           
grant DELETE,INSERT,SELECT,UPDATE on B.LOCALE_TEXT to A;                   
grant DELETE,INSERT,SELECT,UPDATE on B.MGF to A;                           
grant DELETE,INSERT,SELECT,UPDATE on B.MGF_INFLTR to A;                    
grant DELETE,INSERT,SELECT,UPDATE on B.MGF_PUB_USER to A;                  
grant DELETE,INSERT,SELECT,UPDATE on B.MODULE to A;                        
grant DELETE,INSERT,SELECT,UPDATE on B.MODULE_SCIENCE_GROUP_USAGE to A;    
grant DELETE,INSERT,SELECT,UPDATE on B.PHYSICAL_COLUMN to A;               
grant DELETE,INSERT,SELECT,UPDATE on B.PHYSICAL_TABLE to A;                
grant DELETE,INSERT,SELECT,UPDATE on B.RPT_TO_MGF to A;                    
grant DELETE,INSERT,SELECT,UPDATE on B.USER_DEFINITION to A;               
grant DELETE,INSERT,SELECT,UPDATE on B.USER_PREFERENCE to A;               
grant DELETE,INSERT,SELECT,UPDATE on B.ZZ_DROPPED_CPM_BUG7977 to A;        
grant DELETE,INSERT,SELECT,UPDATE on B.CONFIG_PARAM to A; 

 

 

 

然后再在 System 用户下面去运行这些 grant语句。

 

搞定。。。

 

当然sometimes可能你不能用grant 语句去实现访问（例如当你要访问的对象是View的时候），那么你就需要你可以换一种SQL 语句，那就是 

 

select 'CREATE OR REPLACE SYNONYM  B.'||view_name||' FOR A.'||view_name||';' from user_views

 

一样，搞定