ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle批量授權、同義詞

oracle批量授權、同義詞

原创 Linux操作系统 作者:wwd_wang 时间:2009-01-16 13:49:26 0 删除 编辑

--對表授權
SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE  '||' '|| 'ON PORTAL.' ||OBJECT_NAME ||' TO CHANNEL_ES;'
 FROM (select OBJECT_NAME from dba_objects where object_type='TABLE'and wner='PORTAL' ) ;
 
 --對視圖授權
 SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE  '||' '|| 'ON PORTAL.' ||OBJECT_NAME ||' TO CHANNEL_AP;'
 FROM (select OBJECT_NAME from dba_objects where object_type='VIEW'and wner='PORTAL' ) ;

--對FUNCTION授權
SELECT 'GRANT EXECUTE  '||' '|| 'ON MMIS.' ||OBJECT_NAME ||' TO MMIS_AP;'
 FROM (select OBJECT_NAME from dba_objects where object_type='FUNCTION'and wner='MMIS' ) ;
 
--結PROCEDURE授權
SELECT 'GRANT EXECUTE  '||' '|| 'ON MMIS.' ||OBJECT_NAME ||' TO MMIS_AP;'
 FROM (select OBJECT_NAME from dba_objects where object_type='PROCEDURE'and wner='MMIS' ) ;
 --對表賦同義詞
 select 'CREATE SYNONYM  CHANNEL_AP.'||TABLE_NAME||' FOR '||OWNER||'.'||TABLE_NAME||';' from dba_TABLES where wner='PORTAL'
AND TABLE_NAME NOT IN(
SELECT TABLE_NAME FROM DBA_SYNONYMS WHERE TABLE_OWNER='PORTAL'
AND WNER='CHANNEL_AP' )
 
 --對視圖賦同義詞
 select 'CREATE SYNONYM  CHANNEL_AP.'||VIEW_NAME||' FOR '||OWNER||'.'||VIEW_NAME||';' from dba_VIEWS where wner='PORTAL'
AND VIEW_NAME NOT IN(
SELECT VIEW_NAME FROM DBA_SYNONYMS WHERE TABLE_OWNER='PORTAL'
AND WNER='CHANNEL_AP' )

--對FUNCTION, ROCEDURE,'RIGGER 賦同義詞 
 select 'CREATE SYNONYM  MMIS_AP.'||OBJECT_NAME||' FOR '||OWNER||'.'||OBJECT_NAME||';'
  from DBA_OBJECTS where wner='MMIS' AND OBJECT_TYPE = 'FUNCTION'

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

上一篇: oracle常用SQL查詢
下一篇: 回滾段數據恢復
请登录后发表评论 登录
全部评论

注册时间:2008-05-08

  • 博文量
    236
  • 访问量
    198014