How to Set Network ACL in 11g Database

  • by

For 10g database users, there could be no issues to communicate with external network using public UTL_INADDR synonym to resolve hostname. But for 11g database users, there is a problem to use UTL_INADDR, the synonym is still public, but you may have no "right" to communicate with outside world.

Let's see a case in 10g database, UTL_INADDR is a public synonym.

$ sqlplus / as sysdba
SQL> SELECT owner, object_type, status FROM dba_objects WHERE object_name='UTL_INADDR';

OWNER                          OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SYS                            PACKAGE BODY        VALID
SYS                            PACKAGE             VALID
PUBLIC                         SYNONYM             VALID

An user HR in 10g database can resolve the hostname as following.

$ sqlplus hr/hr
SQL> select UTL_INADDR.get_host_name() from dual;

UTL_INADDR.GET_HOST_NAME()
--------------------------------------------------------------------------------
primary01.example.com

Since 11g database introduces Access Control List (ACL) to control the limited network resource and prevent security leaks, there is no more open like 10g was. Hence, in 11g database, it won't work in the first place.

$ sqlplus hr/hr
SQL> select UTL_INADDR.get_host_name() from dual;
select UTL_INADDR.get_host_name() from dual
       *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

There are two basic privileges in ACL to allow users to communicate with external network, one is resolve, which has the ability to resolve hostname, domain name and IP address; the other is connect, which has the ability to act as a client to connect an external host through network protocols, i.e. SMTP.

Setting Up ACL

If this is the first time that a user ask for specific network function, DBA must creates an ACL first.

  1. Create an ACL: In this case, we create an ACL with a initial user HR, and the privilege is resolve.
  2. BEGIN
      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
      acl => 'Connect_Access.xml',
      description => 'Connect Network',
      principal => 'HR',
      is_grant => TRUE,
      privilege => 'resolve',
      start_date => NULL,
      end_date => NULL);
    END;
    /

    Note that, principal is the username who initially asked for the privilege, and it must be in upper case.

  3. Assign the ACL to a specific network: We open the widest scope '*' to users.
  4. BEGIN
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'Connect_Access.xml',
      host => '*',
      lower_port => NULL,
      upper_port => NULL);
    END;
    /

    If there is any other user asks for a privilege to connect, you can add a privilege to the ACL for this user.

  5. Add another user to this ACL: We give user SH a privilege of connect.
  6. BEGIN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
      acl => 'Connect_Access.xml',
      principal => 'SH',
      is_grant => TRUE,
      privilege => 'connect',
      position => NULL,
      start_date => NULL,
      end_date => NULL);
    END;
    /

After creating the ACL, let's make sure the setting matched our expectation.

$ sqlplus / as sysdba
SQL> column acl format a30;
SQL> SELECT acl FROM DBA_NETWORK_ACLS;

ACL
------------------------------
/sys/acls/Connect_Access.xml

SQL> column principal format a5;
SQL> column privilege format a10;
SQL> SELECT acl, principal, privilege FROM DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINC PRIVILEGE
------------------------------ ----- ----------
/sys/acls/Connect_Access.xml   HR    resolve
/sys/acls/Connect_Access.xml   SH    connect

Testing ACL

The above result is correct, so we can test the privileges.

  1. Test the privilege resolve of user HR.
  2. SQL> select UTL_INADDR.get_host_name() from dual;

    UTL_INADDR.GET_HOST_NAME()
    --------------------------------------------------------------------------------
    primary01.example.com

    It succeed to resolve a hostname.

  3. Test the privilege connect of user SH.
  4. Here we use an anonymous PL/SQL block to test the function, which is an example of using UTL_SMTP provided by Oracle 11g Documentation, we just modified it for testing purpose.

    DECLARE
      c UTL_SMTP.CONNECTION;

      PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
      BEGIN
        UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
      END;

    BEGIN
      c := UTL_SMTP.OPEN_CONNECTION('ms1.hinet.net');
      UTL_SMTP.HELO(c, 'foo.com');
      UTL_SMTP.MAIL(c, 'edchenlogic@gmail.com');
      UTL_SMTP.RCPT(c, 'edchenlogic@gmail.com');
      UTL_SMTP.OPEN_DATA(c);
      send_header('From',    '"From Blog Tester" <edchenlogic@gmail.com>');
      send_header('To',      '"To Blog Tester" <edchenlogic@gmail.com>');
      send_header('Subject', 'This is a test for network connection');
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'If you receive this mail, you are able to connect external network.');
      UTL_SMTP.CLOSE_DATA(c);
      UTL_SMTP.QUIT(c);
    EXCEPTION
      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
        BEGIN
          UTL_SMTP.QUIT(c);
        EXCEPTION
          WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
            NULL; -- When the SMTP server is down or unavailable, we don't have
                  -- a connection to the server. The QUIT call will raise an
                  -- exception that we can ignore.
        END;
        raise_application_error(-20000,
          'Failed to send mail due to the following error: ' || sqlerrm);
    END;
    /

    It works, and the testing email as:

    ACL Connect Test

    ACL Connect Test

Since HR has only resolve privilege, he will get errors as following if he execute the same block of code.

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 10

Leave a Reply

Your email address will not be published. Required fields are marked *