Transfer Oracle ACLs to another db

Acl tanımlarını bir db’den başka bir db’ye taşımak veya yedeğini almak için Oracle’ın metalinkte bulunduğu aşağıdaki scriptini kullanabiliriz;

 

declare
v_param_list varchar2(2000);

cursor rec_c ( i_ACLID dba_network_acl_privileges.ACLID%type,
i_ACL dba_network_acl_privileges.ACL%type ) is
select rownum POSITION,ACL,PRINCIPAL,
decode(privilege,'use-cli','use-client-certificates','use-pas','use-passwords',
privilege) PRIVILEGE,IS_GRANT,INVERT,decode(START_DATE,null,'null',
'to_timestamp_tz('''||to_char(START_DATE,'YYYYMMDDHH24MISSXFFTZR')||''',
''YYYYMMDDHH24MISSXFF TZR'')') START_DATE,decode(END_DATE,null,'null',
'to_timestamp_tz('''||to_char(END_DATE,'YYYYMMDDHH24MISSXFFTZR'||''',
''YYYYMMDDHH24MISSXFF TZR'')')) END_DATE from dba_network_acl_privileges a 
where a.ACLID = i_ACLID and a.ACL = i_ACL ;

rec rec_c%rowtype ;

begin
 for i in ( select distinct ACLID,ACL from dba_network_acl_privileges ) loop
 open rec_c ( i.ACLID , i.acl ) ;
 fetch rec_c into rec;
 v_param_list:='acl=>'''||substr(rec.acl,instr(rec.acl,'/',-1)+1)||'''';
 v_param_list:=v_param_list||',description=>'''||substr(rec.acl,11,length
 (rec.acl))||'''';
 v_param_list:=v_param_list||',principal=>'''||rec.principal||'''';
 v_param_list:=v_param_list||',privilege=>'''||rec.privilege||'''';
 v_param_list:=v_param_list||',is_grant=>'||rec.is_grant;
 v_param_list:=v_param_list||',start_date=>'||rec.END_DATE;
 v_param_list:=v_param_list||',end_date=>'||rec.END_DATE||');';
 dbms_output.put_line('exec dbms_network_acl_admin.create_acl('||v_param_list);
 -- fetch rec_c into rec ; NOT FETCHING HERE TO AVOID DUPLICATES 
 while rec_c%FOUND loop
 v_param_list:='acl=>'''||substr(rec.acl,instr(rec.acl,'/',-1)+1)||'''';
 v_param_list:=v_param_list||',principal=>'''||rec.principal||'''';
 v_param_list:=v_param_list||',is_grant=>'||rec.is_grant;
 v_param_list:=v_param_list||',privilege=>'''||rec.privilege||'''';
 dbms_output.put_line('exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('||v_param_list);
 fetch rec_c into rec ;
 end loop;
 close rec_c ;

for rec2 in (select HOST,decode(LOWER_PORT,null,'null',to_char(LOWER_PORT))
LOWER_PORT,decode(UPPER_PORT,null,'null',to_char(UPPER_PORT))UPPER_PORT,
ACL,ACLID from dba_network_acls 
 where acl = i.acl and ACLID= i.aclid) loop
 v_param_list:='acl=>'''||substr(rec2.acl,instr(rec2.acl,'/',-1)+1)||''',
host=>'''||rec2.host||''',lower_port=>'||rec2.lower_port||',upper_port=>
'||rec2.upper_port||');';
 dbms_output.put_line('exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('||v_param_list);
 end loop;

end loop;
end;
/

 

Ek not:  add_privilige yaparken scriptin verdiği satırlar hata verdiğinden scriptten aşağıdaki satırları çıkardım;

v_param_list:=v_param_list||',position=>'||rec.POSITION;
v_param_list:=v_param_list||',start_date=>'||rec.END_DATE;
v_param_list:=v_param_list||',end_date=>'||rec.END_DATE||');';

 

Referans:

How To Get The Script To Create An ACL Based On the Contents Of DBA_NETWORK_ACLS (Doc ID 1634275.1)