ACL ile ilgili izinlerin ilgili user için verilmesi gerekmektedir;
grant execute on utl_http to USER;
grant execute on utl_smtp to USER;
grant execute on utl_tcp to USER;Fakat bu yetkileri verdiğiniz halde ORA-24247: Network access denied by access control list (ACL) tips bu hatayı oluyorsanız ACL de bir sorununuz var demektir. Benim durumumda başka bir sunucuya export import ile taşıma yapılmış fakat ACL ler taşınmamış, eski sunucudan ACL lerin DDL leri alınıp yeni sunucuda tekrar oluşturulduğunda sorun ortadan kalkacaktır.
ACL ddl Create Script;
SET SERVEROUTPUT ON FORMAT WRAPPED LINESIZE 300
DECLARE
l_last_acl dba_network_acls.acl%TYPE := '~';
l_last_principal dba_network_acl_privileges.principal%TYPE := '~';
l_last_privilege dba_network_acl_privileges.privilege%TYPE := '~';
l_last_host dba_network_acls.host%TYPE := '~';
FUNCTION get_timestamp (p_timestamp IN TIMESTAMP WITH TIME ZONE)
RETURN VARCHAR2
AS
l_return VARCHAR2(32767);
BEGIN
IF p_timestamp IS NULL THEN
RETURN 'NULL';
END IF;
RETURN 'TO_TIMESTAMP_TZ(''' || TO_CHAR(p_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') || ''',''DD-MON-YYYY HH24:MI:SS.FF TZH:TZM'')';
END;
BEGIN
FOR i IN (SELECT a.acl,
a.host,
a.lower_port,
a.upper_port,
b.principal,
b.privilege,
b.is_grant,
b.start_date,
b.end_date
FROM dba_network_acls a
JOIN dba_network_acl_privileges b ON a.acl = b.acl
ORDER BY a.acl, a.host, a.lower_port, a.upper_port)
LOOP
IF l_last_acl <> i.acl THEN
-- First time we've seen this ACL, so create a new one.
l_last_host := '~';
DBMS_OUTPUT.put_line('-- -------------------------------------------------');
DBMS_OUTPUT.put_line('-- ' || i.acl);
DBMS_OUTPUT.put_line('-- -------------------------------------------------');
DBMS_OUTPUT.put_line('BEGIN');
DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.drop_acl (');
DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''');');
DBMS_OUTPUT.put_line(' COMMIT;');
DBMS_OUTPUT.put_line('END;');
DBMS_OUTPUT.put_line('/');
DBMS_OUTPUT.put_line(' ');
DBMS_OUTPUT.put_line('BEGIN');
DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.create_acl (');
DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
DBMS_OUTPUT.put_line(' description => ''' || i.acl || ''',');
DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
DBMS_OUTPUT.put_line(' COMMIT;');
DBMS_OUTPUT.put_line('END;');
DBMS_OUTPUT.put_line('/');
DBMS_OUTPUT.put_line(' ');
l_last_acl := i.acl;
l_last_principal := i.principal;
l_last_privilege := i.privilege;
END IF;
IF l_last_principal <> i.principal
OR (l_last_principal = i.principal AND l_last_privilege <> i.privilege) THEN
-- Add another principal to an existing ACL.
DBMS_OUTPUT.put_line('BEGIN');
DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.add_privilege (');
DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
DBMS_OUTPUT.put_line(' COMMIT;');
DBMS_OUTPUT.put_line('END;');
DBMS_OUTPUT.put_line('/');
DBMS_OUTPUT.put_line(' ');
l_last_principal := i.principal;
l_last_privilege := i.privilege;
END IF;
IF l_last_host <> i.host||':'||i.lower_port||':'||i.upper_port THEN
DBMS_OUTPUT.put_line('BEGIN');
DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.assign_acl (');
DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
DBMS_OUTPUT.put_line(' host => ''' || i.host || ''',');
DBMS_OUTPUT.put_line(' lower_port => ' || NVL(TO_CHAR(i.lower_port),'NULL') || ',');
DBMS_OUTPUT.put_line(' upper_port => ' || NVL(TO_CHAR(i.upper_port),'NULL') || ');');
DBMS_OUTPUT.put_line(' COMMIT;');
DBMS_OUTPUT.put_line('END;');
DBMS_OUTPUT.put_line('/');
DBMS_OUTPUT.put_line(' ');
l_last_host := i.host||':'||i.lower_port||':'||i.upper_port;
END IF;
END LOOP;
END;
/Buradan çıkan ddl ler ile ACL ler yeniden oluşturulduğunda sorun ortadan kalkacaktır.
Veya Suppor Dökümanı (MOS ID 1634275.1) Bunu da kullanabilirsiniz;
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||'''';
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||');';
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;
/Output
exec dbms_network_acl_admin.create_acl(acl=>'internalmail.xml',description=>'internalmail.xml',principal=>'CONNECT',privilege=>'connect',is_grant=>true,start_date=>null,end_date=>null);
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'internalmail.xml',principal=>'CONNECT',is_grant=>true,privilege=>'connect',position=>1,start_date=>null,end_date=>null);
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'internalmail.xml',principal=>'QUALITY',is_grant=>true,privilege=>'connect',position=>2,start_date=>null,end_date=>null);
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'internalmail.xml',host=>'10.70.0.104',lower_port=>1,upper_port=>10000);
Bir yanıt yazın