Setting up Network ACLs in Oracle 11gr SMTP server

From Oracle 11g network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR which can be used to access external network resources, are more restricted and secured. Oracle 11g introduced Fine-Grained Access to these packages by creating an Access Control List to use any external network resource through these packages. Before this any user who had an execute privilege on these packages was able to do anything to any network resource like web and local mail servers etc. But now a user needs a little more then just an execute privilege on the network packages.

Lets talk about using ACL in oracle 11g:

$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and   grantee = 'PUBLIC';

GRANTEE    TABLE_NAME     PRIVILEGE
---------- -------------- -------------
PUBLIC     UTL_HTTP       EXECUTE

-- By default access on UTL_HTTP is granted to PUBLIC.
-- Revoke from public and grant to specific user who needs it.

revoke execute on utl_http from public;
grant execute on utl_http to scott;

select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and   grantee in ('PUBLIC','SCOTT')

GRANTEE    TABLE_NAME     PRIVILEGE
---------- -------------- -------------
SCOTT      UTL_HTTP       EXECUTE

-- Now only SCOTT has execute rights on UTL_HTTP.

SQL> conn scott/tiger
Connected.

create or replace procedure getTitle(pUrl VARCHAR2)
is
  vResult CLOB;
begin
  vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
  vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
  dbms_output.put_line(vResult);
end;
/

SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
BEGIN getTitle('http://www.oracleflash.com'); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SCOTT.GETTITLE", line 5
ORA-06512: at line 1

As you may see that even in the presence of EXECUTE privilege, SCOTT is not able to access the web page using UTL_HTTP and has encountered an error "network access denied by access control list (ACL)". This literally means that the user is being denied access by the Access Control List.

How to configure Access Control List



We need to configure an Access Control List (ACL) and grant "connect" privilege on that ACL to user SCOTT. Then we need to assign host "www.oracleflash.com" to this ACL and any other host to which user SCOTT needs access.




DBMS_NETWORK_ACL_ADMIN.CREATE_ACL()
 creates a new Access Control List. Following are the parameters that it takes.






acl
 => Name of the Access Control List. This is a XML file which will be created in /sys/acls directory by default.


Description
 => Description of the ACL. 


Principal
 => Name of the user or role (case sensitive) to whom the permissions are being granted or denied.


is_grant
 => TRUE or FALSE, whether to grant access or deny access.


privilege
 => connect or resolve (lowercase always). Will the user be able to connect to the network resource or just could resolve the network address.


start_date
 => Start date (optional) of the access to the user.


end_date
 => End date (optional) of the access to the user.



SQL> conn / as sysdba
Connected.
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'oracleflash.xml',
    description  => 'Permissions to access http://www.oracleflash.com',
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

Add a privilege to Access Control List



First access to the ACL to any user is granted when the ACL is created with the CREATE_ACL procedure. If any other user or role needs permission on the ACL you may user the procedure ADD_PRIVILEGE.




DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE()
 Add access for more users or roles in an already existing ACL. It takes similar parameters as CREATE_ACL procedure except there is no description parameter and a new parameter position which is used in ADD_PRIVILEGE but not in CREATE_ACL.



The position parameter decides the precedence of the rights for multiple users. For example we grant access to a role ORACLEFLASH at position 1, grant this role to user HR and deny access to user HR at position 2 in ACL. The user HR will still be able to use the network resource because he is granted access via role ORACLEFLASH which takes precedence in the ACL. When granting access to multiple roles and user set the precedence appropriately.

create role oracleflash;

-- A role is created. Now we grant connect to this role on our ACL.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'oracleflash.xml',                
    principal    => 'ORACLEFLASH',
    is_grant     => TRUE, 
    privilege    => 'connect',
    position     => null);
   COMMIT;
END;
/

PL/SQL procedure successfully completed.


So far we have created an ACL and have granted connect access to user SCOTT and role ORACLEFLASH on this ACL. Now is the time to assign network hosts that this ACL can be used to access. In our case the host is "www.oracleflash.com".




Assign a network host to Access Control List






DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL()
 assigns a network host local or remote to an ACL. It takes the following parameters:






acl
 => Name of the Access Control List.


host
 => Name of the host. 


lower_port
 => Lower port (optional) from the range of ports allowed on this host.


upper_port
 => Upper port (optional) from the range of ports allowed on this host



Default for lower and upper port is null, which means all ports can be used on this host. And if you provide a port in lower_port and null in upper_port oracle assumes the upper_port=lower_port.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'oracleflash.xml',                
    host         => '*.oracleflash.com');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'oracleflash.xml',                
    host         => '*.oracle.com');
   COMMIT;
END;
/

PL/SQL procedure successfully completed.


NOTE: you may assign multiple hosts to one ACL, but you can't assign one host to multiple ACLs. If you do that then the previous assignment will be removed and new will become in effect.




Host Assignments:

Host assignment can be done in many ways. For example if you assign a host to an ACL like www.oracleflash.com, the users can only access www.oracleflash.com. But if you assign a host like *.oracleflash.com, the users can assign any sub-domain on the oracleflash.com. And *.com will grant access to the whole web using .com domains. You need to be careful with this as you may be granting access to more servers then you should.




DBMS_NETWORK_ACL_UTILITY.DOMAINS()
 package contains functions to help determine possible matching domains. The DOMAINS table function returns all possible references against a host, that may be specified in ASSIGN_ACL procedure, in order of precedence.



SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.oracleflash.com'));

COLUMN_VALUE
------------------------------------
www.oracleflash.com
*.oracleflash.com
*.com
*

SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('192.168.0.132'));

COLUMN_VALUE
------------------------------------
192.168.0.132
192.168.0.*
192.168.*
192.*
*


The precedence here means that if you have assigned all these hosts to the ACLs then which host entry will take precedence on others. The above query returns results in order of precedence.

We can verify the ACL's host assignment and privileges via two dictionary views, DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES.

column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com
/sys/acls/oracleflash.xml      *.oracle.com

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true
/sys/acls/oracleflash.xml      ORACLEFLASH          connect    true


Lets now see if the access is enabled or not.

SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
OracleFlash.com: Oracle Articles, Tutorials, Step by Step Install Guides, Scripts.

PL/SQL procedure successfully completed.

SQL> execute getTitle('http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm');
Configuring Privilege and Role Authorization

PL/SQL procedure successfully completed.


The user SCOTT is able to access both the oracleflash.com and oracle.com hosts. Lets see how the ACL grant to a role works.

SQL> conn / as sysdba
Connected.
SQL> grant execute on utl_http to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual;
select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual
              *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1


Even after granting the EXECUTE privilege on UTL_HTTP to user HR, it is not able to access the host www.oracleflash.com. This is because the user HR has no access on the ACL we created for oracleflash.com. Now we will grant the role ORACLEFLASH to user HR, which has access on the ACL for oracleflash.com and see what happens.

SQL> conn / as sysdba
Connected.
SQL> grant oracleflash to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,112) oracleflash
  2  from dual;

ORACLEFLASH
-----------------------------------------------------------------------------------------





This time the HR can access the web page as it has the role ORACLEFLASH, which has access on the ACL.




Cleaning Up the Access Control List




Remove a host from Access Control List

Following procedure can be used to remove a host from the ACL.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com
/sys/acls/oracleflash.xml      *.oracle.com

BEGIN
  DBMS_NETWORK_ACL_ADMIN.unassign_acl (
    acl         => 'oracleflash.xml',
    host        => '*.oracle.com'); 
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com





Delete a privilege from Access Control List

Following procedure can be used to delete a privilege from the ACL.

SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true
/sys/acls/oracleflash.xml      ORACLEFLASH          connect    true

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege ( 
    acl         => 'oracleflash.xml', 
    principal   => 'ORACLEFLASH',
    is_grant    => TRUE, 
    privilege   => 'connect');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml      SCOTT                connect    true





Drop an Access Control List

Following procedure can be used to drop the ACL.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml      *.oracleflash.com

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL ( 
    acl         => 'oracleflash.xml');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

no rows selected


See also
Managing Fine-Grained Access to External Network Services
ORA-24248 XMLDB extensible security not installed

Creating ACL for SMTP server:

BEGIN

   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (

 acl        => 'mailserver_acl.xml',

 host       => 'CAINCCHX01.EG01.yahoo.net',

 lower_port => 25,

 upper_port => 25);

 END;

 /

   COMMIT;

   BEGIN

   DBMS_NETWORK_ACL_ADMIN.drop_acl('mailserver_acl.xml');

   END;

   /

   commit;

 

   BEGIN

    DBMS_NETWORK_ACL_ADMIN.create_acl(

    acl           => 'mailserver_acl.xml',

    description   => 'ACL that lets me talk to the my email server',

    principal     => 'PPM_USER',

    is_grant      => TRUE,

    privilege     => 'connect'

    );

/

    DBMS_NETWORK_ACL_ADMIN.assign_acl(

    acl          => 'mailserver_acl.xml',

    HOST         => 'CAINCCHX01.EG01.Yahoo.net',

    lower_port   => 25,

    upper_port   => 25

    );



    COMMIT;

END;

/

References:
http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html
http://oracledbajourney.blogspot.com/2012/09/ora-24247-network-access-denied-by.html