{"id":1224,"date":"2015-04-15T12:09:52","date_gmt":"2015-04-15T10:09:52","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=1224"},"modified":"2016-08-05T08:40:59","modified_gmt":"2016-08-05T06:40:59","slug":"ora-24247-con-el-paquete-stmp","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/ora-24247-con-el-paquete-stmp\/","title":{"rendered":"ORA-24247  con el paquete STMP"},"content":{"rendered":"<p>Hoy vamos a ver como resolver errores ORA-24247  en la version 11g . Aunque desde aqu\u00ed siempre hemos mantenido (y mantendremos) que la base de datos no deber\u00eda ser la responsable de enviar correos o conectarse a <em>\u00abvete a saber donde\u00bb<\/em> hay entornos en los que nos vemos obligados a ello.<\/p>\n<h2>\u00bfQue ocurre si nos encontramos con un error de este tipo ?<\/h2>\n<p><a href=\"http:\/\/clemente.pamplona.name\/dba\/wp-content\/uploads\/2015\/04\/error-ora-ORA-24247.jpg.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/clemente.pamplona.name\/dba\/wp-content\/uploads\/2015\/04\/error-ora-ORA-24247.jpg.jpg\" alt=\"error-ora-ORA-24247.jpg\" width=\"433\" height=\"120\" class=\"aligncenter size-full wp-image-1225\" srcset=\"http:\/\/clemente.pamplona.name\/dba\/wp-content\/uploads\/2015\/04\/error-ora-ORA-24247.jpg.jpg 433w, http:\/\/clemente.pamplona.name\/dba\/wp-content\/uploads\/2015\/04\/error-ora-ORA-24247.jpg-300x83.jpg 300w\" sizes=\"auto, (max-width: 433px) 100vw, 433px\" \/><\/a><\/p>\n<pre>\r\nERROR at line 1:\r\nORA-24247: network access denied by access control list (ACL)\r\nORA-06512: at \"SYS.UTL_TCP\", line 17\r\nORA-06512: at \"SYS.UTL_TCP\", line 246\r\nORA-06512: at \"SYS.UTL_SMTP\", line 115\r\nORA-06512: at \"SYS.UTL_SMTP\", line 138\r\nORA-06512: at \"XXX\", line 36\r\nORA-06512: at line 1\r\n<\/pre>\n<p>Este error es debido a que no contamos con una ACl para el servicio de red, las ACLs son un elemento nuevo de Oracle 11g (11.1.0.6) que establece un filtrado mas detallado (fine-grained access) y que controla el acceso a algunos paquetes como el UTL_SMTP o el UTL_HTTP.<\/p>\n<h2>\u00bfcomo lo solicionamos?<\/h2>\n<p>Sencillamente tenemos que crear una ACL que nos permita acceder al puerto 25 de nuestro servidor de correo.<br \/>\nEn este caso lo haremos para<\/p>\n<ul>\n<li><b> SMTP Server<\/b> 127.0.0.1  (localhost de la BBDD)\n<li><b> Puerto<\/b> 25  ( puerto estandard de correo)\n<li> <b> usuarios <\/b> Usuario1 y usuario2\n<\/ul>\n<p>As\u00ed pues , ejecutaremos :<\/p>\n<pre>\r\nbegin\r\n--creamos la ACL\r\nDBMS_NETWORK_ACL_ADMIN.CREATE_ACL\r\n   (acl => 'send_mail.xml',\r\n   description => '\r\n   send_mail ACL',\r\n   principal => 'USUARIO1',\r\n   is_grant => true, \r\n   privilege => 'connect');\r\n-- Asignamos privilegios  al usuario elegido \r\nDBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(\r\n   acl => 'send_mail.xml',\r\n   principal => 'USUARIO1',\r\n   is_grant  => true,\r\n   privilege => 'connect');\r\nDBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(\r\n   acl => 'send_mail.xml',\r\n   principal => 'USUARIO1',\r\n   is_grant  => true,\r\n   privilege => 'resolve');\r\n-- Asignamos provilegios  al usuario elegido \r\nDBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(\r\n   acl => 'send_mail.xml',\r\n   principal => 'USUARIO2',\r\n   is_grant  => true,\r\n   privilege => 'connect');\r\n--Asignamos recursos \r\n dbms_network_acl_admin.assign_acl (\r\n   acl => 'send_mail.xm',\r\n   host => '127.0.0.1',\r\n   lower_port => 25,\r\n  upper_port => 25);\r\nEND;\r\n\/\r\nCOMMIT;<\/pre>\n<p>Si queremos ver las ACL creadas <\/p>\n<pre>\r\nSELECT * FROM dba_network_acls;\r\n<\/pre>\n<p>Y los privilegios de cada una de ellas <\/p>\n<pre>\r\nSELECT acl\r\n      ,principal\r\n      ,privilege\r\n      ,is_grant\r\n      ,invert\r\n      ,start_date\r\n      ,end_date\r\n  FROM dba_network_acl_privileges;\r\n<\/pre>\n<p>Como siempre , mas informaci\u00f3n en oracle en las notas:<\/p>\n<ul>\n<li> Informaci\u00f3n del paquete <a href=\"http:\/\/docs.oracle.com\/cd\/B28359_01\/appdev.111\/b28419\/d_networkacl_adm.htm#BABCGEAE\">DBMS_NETWORK_ACL_ADMIN<\/a>\n<li> <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=365309761285645&#038;parent=DOCUMENT&#038;sourceId=754909.1&#038;id=557070.1&#038;_afrWindowMode=0&#038;_adf.ctrl-state=aqjmxckx0_361\">Doc ID 557070.1<\/a>ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher\n<li> <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=364601561169289&#038;id=1137673.1&#038;displayIndex=4&#038;_afrWindowMode=0&#038;_adf.ctrl-state=aqjmxckx0_271\">Doc ID 1137673.1<\/A> Master Note For PL\/SQL UTL_SMTP and UTL_MAIL Packages<br \/>\n","protected":false},"excerpt":{"rendered":"<p>Hoy vamos a ver como resolver errores ORA-24247 en la version 11g . Aunque desde aqu\u00ed siempre hemos mantenido (y mantendremos) que la base de datos no deber\u00eda ser la responsable de enviar correos o conectarse a \u00abvete a saber &hellip; <a href=\"http:\/\/clemente.pamplona.name\/dba\/ora-24247-con-el-paquete-stmp\/\">Sigue leyendo <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,60,66],"tags":[249,207,254,57,144,220,222,221,223,224],"class_list":["post-1224","post","type-post","status-publish","format-standard","hentry","category-11g","category-12c","category-errores-ora","tag-11g","tag-11r2","tag-12c","tag-ora","tag-ora-06512","tag-ora-24247","tag-sys-utl_smt","tag-sys-utl_tcp","tag-utl_http","tag-utl_smtp"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/1224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=1224"}],"version-history":[{"count":8,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/1224\/revisions"}],"predecessor-version":[{"id":1497,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/1224\/revisions\/1497"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=1224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=1224"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=1224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}