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)