5.25.2011

Oracle 11g upgrade aftermath - ORA-24247: network access denied by access control list (ACL)

Following a successful Oracle 10g Clusterware and databases upgrade to Oracle 11g R2, some of the database user defined procedures execution were failed with the 'ORA-24247: network access denied by access control list (ACL)' error and it was brought to our notice instantly.

With a little bit of research over the internet about the 'ORA-24247' error,  it was clear that it is an expected behavior post 11g upgrade if  any of Oracle supplied package, like, UTL_TCP, UTL_HTTP, UTL_SMTP or UTL_MAIL is being referred or used in any user defined database procedures/packages/functions. This behavior is largely due to an enhanced security level with the Access Control Lists(ACL) with Oracle 11g to access External Network Services.

The problem can be resolved by explicitly granting the user or role in the question to access the External network Services by making use of the new Oracle supplied package, DBMS_NETWORK_ACL_ADMIN.

The following example demonstrates the steps that are required to address the error:

BEGIN   dbms_network_acl_admin.create_acl(acl => 'http_service_acl.xml',                                     description => 'HTTP ACL Access',                                     principal   => 'USER_NAME',                                     is_grant    => TRUE,                                     privilege   => 'connect',                                     start_date  => null,                                     end_date    => null);   dbms_network_acl_admin.add_privilege(acl=> 'http_service_acl.xml',                                     principal  => 'USER_NAME',                                    is_grant   => TRUE,                                     privilege  => 'resolve',                                     start_date => null,                                     end_date   => null);   dbms_network_acl_admin.assign_acl(acl => 'http_service_acl.xml',                                     host       => '*',                                     lower_port => 8801,                                     upper_port => 8810);   commit; END;


In a nutshell, the above script does the following (ensure the user got the DBA privileges to execute the above script successfully on the database):
  • Creates a new ACL with and grants the 'connect' privilege to the specified user.
  • Grants the 'resolve' privilege to the user.
  • Finally assign the ACL to specific (or open) host with defined ports.
 You can subsequently query the dictionary table dba_network_acls to extract the ACL configured information.

In order to modify the host and port information, you must unassign the ACL using the UNASSIGN_ACL procedure.

Punchline:
Ensure you go through the above exercise after you upgrade your 10g database to Oracle 11g, if the above mentioned Oracle Supplied packages are referred.

References
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#BABBJCJD
MOS: [ID 1209644.1]


No comments: