If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error:<\/p>\n
ORA-24247: network access denied by access control list (ACL)<\/strong><\/p>\n The cause according to oracle is that<\/p>\n “No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.”<\/p>\n This error is common after an upgrade to Oracle 11. before oracle 11, using network resources via packages like utl_tcp, utl_smtp, utl_mail, utl_http, and utl_inaddr exposed the database to a serious security threat because once the user is granted with permission to use those packages there was no other limitation to connect to any computer.<\/p>\n Since Oracle 11, oracle introduced a fine grained access to network services using access control lists (ACL).<\/p>\n This new feature gave the DBA a better control on which user can connect to which computer<\/p>\n In order to solve ORA-24247 you will need to:<\/p>\n 1) Create an acl <\/strong>(if it is not already created)<\/p>\n 2) Add privileges <\/strong> to the user using the network resources<\/p>\n 3) Assign the acl <\/strong>to a specific address<\/p>\n 1)<\/strong> run the following query to check if an ACL exists<\/p>\n If the computer you are trying to connect to is not listed under host, you will need to create an acl<\/strong>:<\/p>\n acl => 'http_permissions.xml', -- or any other name<\/p>\n description => 'HTTP Access',<\/p>\n principal => 'SCOTT', -- the user name trying to access the network resource<\/p>\n is_grant => TRUE,<\/p>\n privilege => 'connect',<\/p>\n start_date => null,<\/p>\n end_date => null<\/p>\n );<\/p>\n end;<\/p>\n \/<\/p>\n <\/code><\/p>\n This will create the acl and grant SCOTT the connect privilege.<\/p>\n 2)<\/strong> IF the acl exists run the following query to verify the user is granted with the appropriate privilege<\/p>\n <\/code><\/p>\n In order to use UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL the user will need the connect privilege<\/p>\n principal => 'SCOTT',<\/p>\n is_grant => true,<\/p>\n privilege => 'connect');<\/p>\n end;<\/p>\n \/<\/p>\n <\/code><\/p>\n If you need to resolve a host name from a host IP you will need the resolve grant as well.<\/p>\n principal => 'SCOTT',<\/p>\n is_grant => true,<\/p>\n privilege => 'resolve');<\/p>\n end;<\/p>\n \/<\/p>\n commit;<\/p>\n <\/code><\/p>\n 3) <\/strong> The final step is to assign the acl<\/strong> to a specific target<\/p>\n acl => 'http_permissions.xml',<\/p>\n host => 'NETWORK ADDRESS', \/*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*\/<\/p>\n lower_port => 80,<\/p>\n upper_port => 80<\/p>\n );<\/p>\n END;<\/p>\n <\/code><\/p>\n It is important to note that only one ACL can be assigned to any host computer. If you assign a new acl to a target the old acl gets unassigned.<\/p>\n However, the old acl is not dropped. So, this could cause confusion because even if the acl was already assigned, it is possible that a new assignment overrode it.<\/p>\n","protected":false},"excerpt":{"rendered":" If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error: ORA-24247: network access denied by access control list (ACL) The cause according to oracle is that “No access control list (ACL) has been assigned to the target host or the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[66,65,107],"class_list":["post-159","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-acl","tag-ora-24247-oracle-11","tag-oracle"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/159","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=159"}],"version-history":[{"count":2,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/159\/revisions"}],"predecessor-version":[{"id":161,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/159\/revisions\/161"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=159"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=159"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=159"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} SELECT *<\/code><\/p>\n <\/code><\/p>\n FROM dba_network_acls;<\/code><\/p>\n
\nbegin<\/code><\/p>\ndbms_network_acl_admin.create_acl (<\/p>\ncommit;<\/code><\/p>\n SELECT *<\/code><\/p>\nFROM dba_network_acl_privileges<\/p>\n where principal='SCOTT';<\/code><\/p>\n begin<\/code><\/p>\nDBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',<\/p>\n commit;<\/code><\/p>\n begin<\/code><\/p>\nDBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',<\/p>\n <\/code><\/p>\n BEGIN<\/code><\/p>\ndbms_network_acl_admin.assign_acl (<\/p>\n <\/code><\/p>\n