Search This Blog

Tuesday, September 13, 2011

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

In oracle 11gr1 or higher version new security policy implemented.
while executing UTL_SMTP package received following error message:


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

Reason : New security policy implemented.

Solution:
1. Login with SYSDBA user
2. create procedure
3. grant access to particular user.

[oracle@fakorapd bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 12 17:20:14 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on

SQL> SQL> show user
USER is "SYS"

SQL> create or replace procedure mailserver_acl(
  aacl       varchar2,
  2    3    acomment   varchar2,
  4    aprincipal varchar2,
  5    aisgrant   boolean,
  6    aprivilege varchar2,
  7    aserver    varchar2,
  8    aport      number)
  9  is
 10  begin
  begin
 11   12      DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
 13       dbms_output.put_line('ACL dropped.....');
 14    exception
 15      when others then
 16        dbms_output.put_line('Error dropping ACL: '||aacl);
      dbms_output.put_line(sqlerrm);
 17   18    end;
 19    begin
 20      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,apr                                                                             ivilege);
 21      dbms_output.put_line('ACL created.....');
 22    exception
 23      when others then
 24        dbms_output.put_line('Error creating ACL: '||aacl);
 25        dbms_output.put_line(sqlerrm);
 26    end;
 27    begin
 28      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
 29      dbms_output.put_line('ACL assigned.....');
 30    exception
 31      when others then
 32        dbms_output.put_line('Error assigning ACL: '||aacl);
 33        dbms_output.put_line(sqlerrm);
 34    end;
 35    commit;
 36    dbms_output.put_line('ACL commited.....');
 37  end;
 38  /

Procedure created.

SQL> show errors
No errors.



SQL> begin
  mailserver_acl(
    'mailserver_acl.xml',
    'ACL for used Email Server to connect',
    'OEMSYS', --(user name/schema)
    TRUE,
    'connect',
    'fakexc.dummy.local', --(your exchange mail server name)
    25); --default port number
end;
/  2    3    4    5    6    7    8    9   10   11
/


Error dropping ACL: mailserver_acl.xml
ORA-31001: Invalid resource handle or path name "/sys/acls/mailserver_acl.xml"
ACL created.....
ACL assigned.....
ACL commited.....

PL/SQL procedure successfully completed.


NOTE: 
1. ORA-31001 can be ignore first time, because procedure first drop then create ACL.
2. Replace above Italic value as per your environment.






1 comment:

Muhammad Abdul Halim said...

New things. very nice taj . Thanks

Regards
Halim