UTL_MAIL

ORA-06502 ORA-24247 calling UTL_MAIL from Oracle 11gR2

                   

Environment: Oracle database 11.2.0.3.0, Oracle Linux 6.2
Sending e-mails from within the Oracle database using the UTL_MAIL PL/SQL package used to be quite easy in Oracle 10g. However, in Oracle 11gR2, things have changed.
Suppose that you created the following wrapper procedure in PL/SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

CREATE OR REPLACE PROCEDURE UTILS.SEND_MAIL (    p_sender       IN   VARCHAR2,    p_recipients   IN   VARCHAR2,    p_cc           IN   VARCHAR2 DEFAULT NULL,    p_bcc          IN   VARCHAR2 DEFAULT NULL,    p_subject      IN   VARCHAR2,    p_message      IN   VARCHAR2,    p_mime_type    IN   VARCHAR2 DEFAULT 'text/plain; charset=us-ascii' ) IS  BEGIN    UTL_MAIL.SEND (sender          => p_sender,                   recipients      => p_recipients,                   cc              => p_cc,                   bcc             => p_bcc,                   subject         => p_subject,                   message         => p_message,                   mime_type       => p_mime_type                  ); EXCEPTION    WHEN OTHERS    THEN       RAISE; END send_mail; /

To get this procedure working on Oracle 11g, there are several steps you need to take.
First, you need to actually install the UTL_MAIL package. It’s not installed by default on 11g:

1 2 3 4 5 6 7

$ sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 27 14:49:33 2012 SQL> connect / as sysdba Connected. SQL> @?/rdbms/admin/utlmail.sql SQL> @?/rdbms/admin/prvtmail.plb SQL> grant execute on utl_mail to public;

Next, you need to add the address and port of the e-mail server to the “smtp_out_server” initialization parameter. If you do not do this, you will receive a “ORA-06502: PL/SQL: numeric or value error” error when you try to use the UTL_MAIL package.
Execute the following with user SYS as SYSDBA:

1	SQL> alter system set smtp_out_server = 'mymailserver@mydomain.com:25' scope=both;

Finally, you need to create an Access Control List (ACL) for your e-mail server and grant the necessary users access to this ACL. Without an ACL, you will receive the following error: “ORA-24247: network access denied by access control list (ACL)“.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

BEGIN    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (     acl          => 'mail_access.xml',     description  => 'Permissions to access e-mail server.',     principal    => 'PUBLIC',     is_grant     => TRUE,     privilege    => 'connect');    COMMIT; END;   BEGIN    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (     acl          => 'mail_access.xml',     host         => 'mymailserver@mydomain.com',     lower_port   => 25,     upper_port   => 25     );    COMMIT; END;

After these steps, you should be able to successfully send e-mails from within the database:

begin
utils.send_mail(
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘matthiash@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;
*Action:
anonymous block completed
Matthias

Filed under Oracle 11g, Oracle Linux Tagged with access control list, ACL, dbms_network_acl_admin, mail, ORA-06502, ORA-24247, oracle 11g, smtp_out_server, utl_mail

ORA-24247 during LDAP authentication from APEX 4.1.1 on Oracle 11gR2

April 24, 2012 1 Comment

Environment: APEX 4.1.1, Oracle database 11.2.0.3.0, Oracle Linux 6.2
In Oracle database 11g, access to external network resources has been more restricted than in previous versions. Access to network resources is now controlled through ACL’s (Access Control Lists). This can lead to various problems when you migrate APEX applications from a server running Oracle 10g to one running 11g.For example, if you wrote your own LDAP authentication functions using the built-in DBMS_LDAP package, you will receive the following error message when you try to authenticate to LDAP:
ORA-24247: network access denied by access control list (ACL)
This is because the owner of the authentication function lacks access to the required network resources. You can easily test this with the following piece of PL/SQL code:

1 2 3 4 5

declare l_session dbms_ldap.session; begin l_session := dbms_ldap.init('windowsdc.mydomain.com',389); end;

In this case, “windowsdc.mydomain.com” is a Windows LDAP server running Microsoft’s Active Directory.
To grant access to a specific network resource in 11g, you first need to create a ACL (Access Control List). I executed this with SYS as SYSDBA:

1 2 3 4 5 6 7 8 9

BEGIN    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (     acl          => 'ldap_access.xml',     description  => 'Permissions to access LDAP servers.',     principal    => 'MATTHIASH',     is_grant     => TRUE,     privilege    => 'connect');    COMMIT; END;

In this example, “ldap_access.xml” is the name of my ACL, and “MATTHIASH” is the name of the user account which needs access to the LDAP server. This account owns my custom LDAP authentication function.
Next, you need to add the LDAP server to the ACL we just created (don’t forget to COMMIT):

1 2 3 4 5 6 7 8 9

BEGIN    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (     acl          => 'ldap_access.xml',     host         => 'windowsdc.mydomain.com',     lower_port   => 389,     upper_port   => 389     );    COMMIT; END;

You can check the ACL using the following queries:

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;