--首先是CPR賬號
--授權表上的讀寫許可權
select 'grant all on '||owner||'.'||table_name||' to hisuser;' from dba_tables
where owner = 'CPR';
--授權檢視上的讀寫許可權
select 'grant all on '||owner||'.'||view_name||' to hisuser;' from dba_views
where owner = 'CPR';
--授權函式和儲存過程的讀寫許可權
select 'grant execute on '||owner||'.'||name||' to hisuser;' from dba_source
where owner = 'CPR' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE') ;
--授權序列的讀寫許可權
select 'grant all on '||sequence_owner||'.'||sequence_name||' to hisuser;' from dba_sequences where sequence_owner = 'CPR' ;
--建立同義詞
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms
where table_owner='CPR' ;
select 'create or replace public synonym '||view_name||' for '||owner||'.'||view_name||' ;' from dba_views
where owner = 'CPR' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;
--然後是system賬號
--授權表上的讀寫許可權
select 'grant all on '||owner||'.'||table_name||' to hisuser;' from dba_tables
where owner = 'SYSTEM' and table_name NOT LIKE '%$%';
--授權檢視上的讀寫許可權
select 'grant all on '||owner||'.'||view_name||' to hisuser;' from dba_views
where owner = 'SYS';
--授權函式和儲存過程的讀寫許可權
select DISTINCT 'grant execute on '||owner||'.'||name||' to hisuser;' from dba_source
where owner = 'SYS' and type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','TYPE') AND name NOT LIKE '%$%'
--授權序列的讀寫許可權
select 'grant all on '||sequence_owner||'.'||sequence_name||' to hisuser;' from dba_sequences where sequence_owner = 'SYSTEM' AND sequence_name NOT LIKE '%$%';
--建立同義詞
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms
where table_owner='SYS' and synonym_name NOT LIKE '%$%';
select 'create or replace public synonym '||view_name||' for '||owner||'.'||view_name||' ;' from dba_views
where owner = 'SYS' and (owner NOT LIKE '%$%' OR view_name NOT LIKE '%$%') ;

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

相關文章