oracle 循环授权,oracle批量授權、同義詞

--對表授權

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'