Oracle11g new feature-security send mail error ora-24247 Solution

Access Control List for utl_tcp/HTTP/SMTP

You may be familiar with the utl_tcp, utl_http, and utl_smtp packages, which support communication between servers outside the database domain. For example, utl_tcp is used to establish TCP/IP communication between two hosts, rather than through database links. Similarly, utl_http is used to send HTTP requests from the Web server, and utl_smtp is used to send SMTP mail calls between hosts.

Developers occasionally use these powerful tools-for example, using utl_smtp to send emails from the database, and using utl_http to extract web pages that can be processed in PL/SQL programs. However, these tools bring huge security risks. With utl_tcp, the database user can reach any other computer that the host can reach, without even receiving system prompts. This was once a conventional Voyager Worm trick. The virus harassed the Oracle user community just a year ago.

To eliminate this risk, many experts suggest revoking the permissions to execute these packages from the Internet. I also recommended this method in my "Security Protection Project" series. But what if developers want to execute these packages for reasonable reasons?

Oracle Database 11GThere is a new solution: You can grant the execution permission package to anyone, but control the resources they can call. For example, utl_tcp can be limited to calling only a few IP addresses. This mechanism is called the access control list (ACL ). If the host is in the ACL, you can use it in utl_tcp. However, it is not enough to have the execution permission for utl_tcp. Therefore, malicious processes cannot replace the utl_tcp package and establish illegal connections.

Let's take a look at how it works. First, create an ACL:

begindbms_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;

The parameter principal => 'connect 'indicates that the ACL applies to the connect role. You can define a user or role here. This ACL is created as an utlpkg. xml file.

After creation, you can check to make sure that the ACL has been added:

SELECT any_pathFROM resource_viewWHERE any_path like ‘/sys/acls/%.xml‘;

The output result is as follows:

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 that the last line in the output result shows the ACL you just created. Next, add a permission for the ACL. In this example, you will try to restrict the ACL to user Scott. You can also define the start and end dates.

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

The host that will be subject to the ACL and other details are allocated:

begindbms_network_acl_admin.assign_acl (acl => ‘utlpkg.xml‘,host => ‘www.proligence.com‘,lower_port => 22,upper_port => 55);end;

In this example, you specify that "User Scott can only call the host www.proligence.com and can only use ports 22 to 55 ." Now let's give it a try:

SQL> grant execute on utl_http to scott2  /Grant succeeded.SQL> conn scott/tigerConnected.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 failedORA-06512:at "SYS.UTL_HTTP", line 1577ORA-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 calls the HTTP server on port 80. This operation is blocked because the server is out of the permitted range 22-55.

Now, add another rule to allow this communication:

1  begin2    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/tigerConnected.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>...

However, this rule applies only to www.proligence.com. If you call another Web site, the call fails because it is still ORA-24247. This is the most fine-grained level of security. If your enterprise needs to connect to the host www.proligence.com, you can block access to any other host while allowing this connection, so as to prevent malicious users from using this function to access all other hosts.

For more information about ACL, see dba_network_acls View:

select host, lower_port, upper_port, acl, aclidfrom dba_network_aclswhere ACL=‘/sys/acls/utlpkg.xml‘;HOST---------------------------------------LOWER_PORT UPPER_PORT---------- ----------ACL---------------------------------------ACLID--------------------------------prolin3.proligence.com80        100/sys/acls/utlpkg.xml35D4278980DE6407E040A8C0680037D6... and so on ...

In my opinion, this is Oracle Database 11GOne of the best new security features.