Oracle 11g x E-mail Shipping

[Trevisolli]

Environment information:
* Oracle version: 11g
* Operating system: Win 2003

Personal, good afternoon. [ 99] All right ???

Problem:
The upgrade was performed on a client, from version 10g to 11g recently and, the package that sends an e-mail stopped working , Bursting the following error:

Select all

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

I was researching about it, and I realized that some "new" packages should be implemented in the bank.

Doubt:
Has anyone underwent such a situation and, could guide in the process?

Thank you very much.

[dr_gori]

Is the email sent by a package of the bank itself? (Is it a standard package? Or is a customized customized?)

I always sent e-mail through commands from the operating system itself. (Mail, Mailx, etc) ... The bank simply passes the task of sending email to s.

[Trevisolli]

So Brother,

As the OS is different on each client (we are a softwareHouse and we have the most diverse types of customers), then we always use Oracle's own procedure UTL_SMTP .

However, Pro 11g had some changes.

[alef]

Trevis, beleza? I know it seems kind of rough my idea, but you already ferifed the firewall?

[Trevisolli]

So Brow beleza? quanto time ...
to ask for the support guys take a look, but, I read that is a new "feature" of 11, which should be implemented.

Let's do some tests on the approval base, according to the link:
http://www.oracle-base.com/articles/11g ... ntrol_list

Any novelty I place here for you .

[Trevisolli]

Personal,

Oracle [/b], I found a tutorial of this "New Feature" and, put below for those who need (in English):

---------------------------------- -------------------------------------------------- -
Access Control Lists for UTL_TCP / HTTP / SMTP
------------------------------ -------------------------------------------------- -----

You are probably familiar with the packages utl_tcp, utl_http and utl_smtp; They enable Communication Between Servers Outside The Realm of the Database. For instance, UTL_TCP IS USED to Establish TCP / IP Communication Between Two Hosts, Not Through Database Links. Similarly, utl_http is used to make an http request from the web server and utl_smtp is used to make smtp mail calls between hosts.

These Powerful Tools Are Used Occasionionly by Developers-For Instance, UTL_SMTP to Send emails from within the database, utl_http to pull web pages that can be massaged inside the pl / sql program, and so on . However, Theese Tools Present Huge Security Risks. Using UTL_TCP, Database User Can Go To Any Other Machine Approachable from That Host, Without Even Going To The System Prompt. This was the modus operandi of the voyager worm that rattled The Oracle User Community Just a year ago.

To Address This Risk, Many Experts Advise to Revoke The Privilege of "Run from Public" on These Packages. In My Project Lockdown Series, I recommended that Approach as well. But what if the Developers Want to execute Theese Packages for Legitimate Reasons?

Oracle Database 11g HAS A Novel Solution: You Can Grant The Execute Privilege Package to Anyone BUT CONTROL WHICH RESOURCES THEY CALL. For Instance, UTL_TCP CAN BE LIMITED TO CALLING ONLY A FEW IP ADDRESSES, MECHANISM KNOWN A ACCESS CONTROL LIST (ACL). If the Host is on the ACL, The User Can use in UTL_TCP; MORE execute privilege on UTL_TCP is not enough. Therefore It is impossible for rogue Process to overtake the UTL_TCP Package and Make Connections Illegitimately.

Let's See How It Works. FIRST, CREATE AN ACL:

Select all

 
begin 
        dbms_network_acl_admin.create_acl ( 
                acl             => 'utlpkg.xml', 
                description     => 'Normal Access', 
                principal       => 'CONNECT', 
                is_grant        => TRUE, 
                privilege       => 'connect', 
                start_date      => null, 
                end_date        => null 
        ); 
end; 

Here the main parameter => 'Connect' indicates that the ACL Applies to the Connect role. You can define the user or the role here. The ACL IS Created A File Called Utlpkg.xml.
After the Creation, you can check to make sure the acl was added:

Select all

 
SELECT any_path 
FROM resource_view 
WHERE any_path like '/sys/acls/%.xml'; 
 
The output is:  
ANY_PATH 
---------------------------------------------------------------------------- 
/sys/acls/ANONYMOUS/ANONYMOUS3553d2be53ca40e040a8c0680777c_acl.xml 
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f93feb8dde040a8c068075b7_acl.xml 
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f944b8dde040a8c068075b7_acl.xml 
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f948b8dde040a8c068075b7_acl.xml 
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f94cb8dde040a8c068075b7_acl.xml 
/sys/acls/all_all_acl.xml 
/sys/acls/all_owner_acl.xml 
/sys/acls/bootstrap_acl.xml 
/sys/acls/ro_all_acl.xml 
/sys/acls/ro_anonymous_acl.xml 
/sys/acls/utlpkg.xml 

Note The Last Line In The Output, Which Shows The ACL You Just Created . Next, add a privilege to this ACL. In this example, you are trying to limit this ACL to the user scott. You can Also Define Start and End Dates.

Select all

 
begin 
  dbms_network_acl_admin.add_privilege (  
  acl 		=> 'utlpkg.xml', 
  principal 	=> 'SCOTT', 
  is_grant 	=> TRUE,  
  privilege 	=> 'connect',  
  start_date 	=> null,  
  end_date 	=> null);  
end; 

Assign Hosts And Other Details That Will Be Subject to This ACL:


99] in this example, you are specifying that "The User Scott Can Call Only The Host www.proligence.com ND Only for The Ports 22 Through 55, and Not Outside It."
Now, Let's Try It:

Select all

 
SQL> grant execute on utl_http to scott 
  2  / 
  
Grant succeeded. 
  
SQL> conn scott/tiger 
Connected. 
SQL> select utl_http.request('http://www.proligence.com') from dual; 
select utl_http.request('http://www.proligence.com') from dual 
       * 
ERROR at line 1: 
ORA-29273: HTTP request failed 
ORA-06512: at "SYS.UTL_HTTP", line 1577 
ORA-24247: network access denied by access control list (ACL) 
ORA-06512: at line 1 

Note the error "ORA-24247: Network Access Denied by Access Control List (ACL). " The User Called The HTTP Server on Port 80, Which Is Outside The Allowed Range 22-55. Therefore The Action Was Prevented.
Now, add Another Rule to Allow The Communication:

Select all

 
  1  begin 
  2    dbms_network_acl_admin.assign_acl ( 
  3    acl => 'utlpkg.xml', 
  4    host => 'www.proligence.com', 
  5    lower_port => 1, 
  6    upper_port => 10000); 
  7* end; 
  8  / 
  
PL/SQL procedure successfully completed. 
  
SQL> conn scott/tiger 
Connected. 
SQL> select utl_http.request('http://www.proligence.com') from dual; 
  
UTL_HTTP.REQUEST('HTTP://WWW.PROLIGENCE.COM') 
-------------------------------------------------------------------------------- 
</iframe><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
 
<HTML><HEAD><TITLE>Proligence Home</TITLE> 
<META http-equiv=Content-Language content=en-us> 
... 

But this works for www.proligence.com, Othing else. If you call a Different Website, It Will Fail With the Ora-24247 Again. This is Security on the most granular level. IF Your Business Needs To Connect to The Host www.proligence.com, or Can Allow That Yet Prevent Access to Any Other Host, Preventing Malicious User from Using That Facility To Get To All Other Hosts.

to find out the details of the ACL, Query The View DBA_NETWORK_ACLS:

Select all

 
select host, lower_port, upper_port, acl, aclid 
from dba_network_acls  
where ACL='/sys/acls/utlpkg.xml'; 
 
HOST 
--------------------------------------- 
LOWER_PORT UPPER_PORT 
---------- ---------- 
ACL 
--------------------------------------- 
ACLID 
-------------------------------- 
prolin3.proligence.com 
        80        100 
/sys/acls/utlpkg.xml 
35D4278980DE6407E040A8C0680037D6 

... AND ON ...

For My Money, this is One of the Best New Security Features of Oracle Database 11g.

------------------------------------ -------------------------------------------------- -----------------------------------------
source : http://www.oracle.com/technology/pub/ar ... urity.html. ---------------------------------------- -------------------------------------------------- -------------------------------------