Oracle中將一個使用者的所有物件授權給另一個使用者

xypincle發表於2017-02-19
Oracle物件

  1. --首先是CPR賬號
  2.     --授權表上的讀寫許可權
  3.     select 'grant all on '||owner||'.'||table_name||' to hisuser;' from dba_tables
  4.     where owner = 'CPR';
  5.     
  6.     --授權檢視上的讀寫許可權
  7.     select 'grant all on '||owner||'.'||view_name||' to hisuser;' from dba_views
  8.     where owner = 'CPR';

  10.     --授權函式和儲存過程的讀寫許可權
  11.     select 'grant execute on '||owner||'.'||name||' to hisuser;' from dba_source
  12.     where owner = 'CPR' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE') ;

  14.     --授權序列的讀寫許可權
  15.     select 'grant all on '||sequence_owner||'.'||sequence_name||' to hisuser;' from dba_sequences where sequence_owner = 'CPR' ;

  17.     --建立同義詞
  18.     select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms
  19.     where table_owner='CPR' ;
  20.     select 'create or replace public synonym '||view_name||' for '||owner||'.'||view_name||' ;' from dba_views
  21.     where owner = 'CPR' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;

  23. --然後是system賬號
  24.     --授權表上的讀寫許可權
  25.     select 'grant all on '||owner||'.'||table_name||' to hisuser;' from dba_tables
  26.     where owner = 'SYSTEM' and table_name NOT LIKE '%$%';
  27.     
  28.     --授權檢視上的讀寫許可權
  29.     select 'grant all on '||owner||'.'||view_name||' to hisuser;' from dba_views
  30.     where owner = 'SYS';    

  32.     --授權函式和儲存過程的讀寫許可權
  33.     select DISTINCT 'grant execute on '||owner||'.'||name||' to hisuser;' from dba_source
  34.     where owner = 'SYS' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE') AND name NOT LIKE '%$%'

  36.     --授權序列的讀寫許可權
  37.     select 'grant all on '||sequence_owner||'.'||sequence_name||' to hisuser;' from dba_sequences where sequence_owner = 'SYSTEM' AND sequence_name NOT LIKE '%$%';

  39.     --建立同義詞
  40.     select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms
  41.     where table_owner='SYS' and synonym_name NOT LIKE '%$%';
  42.     
  43.     select 'create or replace public synonym '||view_name||' for '||owner||'.'||view_name||' ;' from dba_views
  44.     where owner = 'SYS' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28878983/viewspace-2133888/，如需轉載，請註明出處，否則將追究法律責任。

相關文章