Skip to content

Archive for

ORA-24247: network access denied by access control list (ACL)

 

SQL> begin
send_mail(‘test’,’azarmohds@gmail.com’);
end;
2 3 4
5 /
begin
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 19
ORA-06512: at “SYS.UTL_TCP”, line 280
ORA-06512: at “SYS.UTL_SMTP”, line 163
ORA-06512: at “SYS.UTL_SMTP”, line 199
ORA-06512: at “SCOTT.SEND_MAIL”, line 8
ORA-06512: at line 2

This error message means that no access control list has been assigned to the host you (your application) are trying to access, or no required privileges have been granted to the user by adding user to the ACL.

Solution :

Step 1:

SQL> grant execute on utl_http to scott;

Grant succeeded.

Step 2:  Conn as sysdba

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ‘scottdev.xml’,
description => ‘Permissions to access  mail’,
principal => ‘SCOTT’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

PL/SQL procedure successfully completed.

Step 3:

SQL> begin
2 DBMS_NETWORK_acl_ADMIN.ADD_PRIVILEGE(
3 acl => ‘scottdev.xml’,
4 principal => ‘SCOTT’,
5 is_grant => true,
6 privilege => ‘resolve’
7 );
8 COMMIT;
9 END;
10 /

PL/SQL procedure successfully completed.

Step 4:

SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ‘scottdev.xml’,
host => ‘*’);
COMMIT;
END;
/

 

Step 5:

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES

Step 6: Send mail as scott user

SQL> begin
send_mail(‘test’,’azarmohds@gmail.com’);
end; 2 3
4 /

PL/SQL procedure successfully completed.

 

Drop ACL :

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => ‘scottdev.xml”);
COMMIT;
END;
/

Advertisements

failed to create user while installing oracle 12c in windows server 2012 R2

When  I am trying to install oracle 12c software in windows server 2012 r2, Its asking the windows user credentials. So I had given the windows new user “Test” and password like ” Test@123″ . Oracle installer accepted this password but windows not accepted this password because of password poilcy.

  • Atleast 8 characters inlcude letter, number, symbol
  • Also Don’t put your password letter as same like user name. It should not be identical.

Example :

User : Test

password : P@ssw0rd