Get in contact with another Server – Access Control List (ACL) – Oracle, Apex and all the RESTful(l) Services

To avoid the following kind of Oracle Error ORA-24247: network access denied by access control list (ACL) you should read this article.

For security reasons, since Oracle 11 network services are disabled by default. To enable Apex for sending out emails, use other web services or print server you must use the DBMS_NETWORK_ACL_ADMIN package to grant certain privileges to the related hosts for the parsing schema and the Apex repository schema, like APEX_050000. It depends lastly which kind of package you use.

Please note there is a difference between Oracle 11g and Oracle 12c on how to grant the Access Control Lists (ACL). Let me point out the distinction.

All what i mention here is valid for the following objects:

DBMS_DEBUG_JDWP
DBMS_LDAP, APEX_LDAP
httpuritype
UTL_INADDR
UTL_HTTP
UTL_MAIL, APEX_MAIL
UTL_SMTP
UTL_TCP
WWV_FLOW_WEBSERVICES_API, APEX_WEB_SERVICE

As default these objects are granted to PUBLIC. Because some DBAs revoke the execute rights from PUBLIC you should check this behaviour on your database. For instance:

SELECT  grantee
       ,table_name
       ,privilege
       ,owner
  FROM dba_tab_privs
  WHERE table_name IN ('DBMS_DEBUG_JDWP'
                      ,'DBMS_LDAP'
                      ,'httpuritype'
                      ,'UTL_INADDR'
                      ,'UTL_HTTP' 
                      ,'UTL_MAIL'
                      ,'UTL_SMTP'
                      ,'UTL_TCP'
                      ,'WWV_FLOW_WEBSERVICES_API' );

By the way, if the object UTL_MAIL doesn’t exist (check it via DBA_OBJECTS), you can install it with SYS rights. Hence these two scripts have to execute for making UTL_MAIL available:

@$ORACLE_HOME/rdbms/admin/utlmail.sql @$ORACLE_HOME/rdbms/admin/prvtmail.plb

Is user PUBLIC or your parsing Schema not listet in the above Select statement for the objects you need, then execute as a user with the corresponding rights, for example SYS this statement:

GRANT EXECUTE ON object_name TO [parsing schema];

The owner of the package WWV_FLOW_WEBSERVICES_API is the Apex Schema, like APEX_050000. You can use the public synonym APEX_WEB_SERVICE in your Apex parsing schema to create web services. This Link has more information.
Let us start with the grants for an Oracle 12c database before we go over to an Oracle 11g. Please run all the following SQL snippets and SQL scripts as SYS user.

Grant privilegs for an Oracle 12c Database

With Oracle 12c an Access Control List (ACL) will never create directly. The call of the procedure DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE creates an ACL implicitly. This means, if an ACL already exists, then an Access Control Entry (ACE) will be appended. Otherwise, if there is no ACL then it will be created together with the ACE.

For example to create an ACL or append an ACE for a complete network access from database to all remote hosts, you have to set the host parameter to ‘*’. This is for sure a security risk but it is ok for a test or other circumstances.

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( 
      host => '*'
     ,lower_port   => null
     ,upper_port   => null
     ,ace  => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve')
                         ,principal_name => 'APEX_050000'
                         ,principal_type => xs_acl.ptype_db)
);

Repeat this SQL for your parsing schema in Apex.

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( 
      host => '*'
     ,ace  => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve')
                         ,principal_name => 'MY_PARSING_SCHEMA'
                         ,principal_type => xs_acl.ptype_db)
);

The default NULL values for lower and upper port are not required.
And that is it now. We need not more. The ACLs have been extremely simplified. Here are some parameter explanations.

host parameter

The host parameter can be a “host name” or an “IP address” of the host. You can use a wildcard to specify a domain or an IP subnet, for example:

‘*.oracle-web.com’
‘192.0.2.*’

lower_port/upper_port parameter

lower_port specifies the lower boundary and upper_port the upper boundary of the port range. The default is null, which means that there is no port restriction, i.e. the ACL applies to all ports. The range of port numbers is between 1 and 65535.
The resolve privilege in the privilege_list has no effect when a port range is specified in the ACL assignment.

ace parameter

As you can see in the above procedure call, ACE is defined by using the XS$ACE_TYPE constant. There are three parameter "privilege_list, principal_name and principal_type".

A privilege list can be:

connect	Grants the user permission to connect a network service to a host for all packages.
resolve	Resolves a network host name or IP address through the UTL_INADDR package.
smtp	Sends SMTP to a host through the UTL_SMTP and UTL_MAIL packages.
http	Makes an HTTP request to a host through the UTL_HTTP package and the HttpUriType type.
http_proxy	Makes an HTTP request through a proxy with UTL_HTTP package and HttpUriType type. You must include http_proxy in conjunction to the http privilege if the user makes the HTTP request through a proxy.
jdwp	Used for Java Debug Wire Protocol debugging operations for Java or PL/SQL stored procedures. See Configuring Network Access for Java Debug Wire Protocol Operations for more information. Used in conjunction with DBMS_DEBUG_JDWP.

A principal name can be:

Database User or Role

Include only one schema or role per DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE call, for example first the parsing schema and in a second call the APEX-Schema. This value is case insensistive, unless you enter it in double quotation marks (for example, ‘”APEX_050000′”).

A principal type can be:

XS_ACL.PTYPE_DB	Enter XS_ACL.PTYPE_DB for a database user or role.
XS_ACL.PTYPE_XS	Is used to specify an Oracle Database Real Application Security application user.

An Access Control List (ACL) has zero or many Access Control Entries (ACE). Both is selectable via these statements:

ACL:

select host, lower_port, upper_port, acl from dba_host_acls;

ACE:

select ace_order, privilege, grant_type, principal, host from dba_host_aces;

Grant privilegs for an Oracle 11g Database

The approach is quite different:

create an access control list, if the list has not been already created
add privileges to the user or the role which uses the network resources
assign the acl to a more or less specific address

Please have in mind, the Oracle documentation says, that these procedures are deprecated in Oracle Database 12c. The procedures remain available in the package for reasons of backward compatibility. Therefore if you have an Oracle 12c Database or above don’t use the following, subjacent procedures.

But suppose you have an Oracle 11gR1/2 or an Oracle XE 11gR1/2, in this case pursue the text. The ACLs will be saved in the internal XML-DB and we start with the creation of an Access Control List.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
     acl         => 'across_broder.xml' -- any name you want
    ,description => 'any HTTP access'
    ,principal   => 'HR'                -- is case sensitive.
    ,is_grant    => TRUE
    ,privilege   => 'connect'
  );
  COMMIT;
END;

Beside the connect privilege you can also add the resolve privilege. This is for resolving computer names into IP addresses. In other words, if you don’t use computer names but only IP addresses, resolve privilege is not necessary.

Anyway, now we want to add resolve for user/schema HR.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
     acl         => 'across_broder.xml' -- same ACL Name as in create_acl proc.
    ,principal   => 'HR'                -- is case sensitive.
    ,is_grant    => TRUE 
    ,privilege   => 'resolve'
  );
  COMMIT;
END;
/

And assigning one or more hosts to the ACL will be realized with:

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

This means, any request for oracle-web.com will be accepted. Furthermore it is possible to restrict the host(s) to a specific port range, like this for oracle.com

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl        => 'across_broder.xml'
   ,host       => '*.oracle.com'
   ,lower_port => 80   -- port 80 to 100 will be accepted
   ,upper_port => 100  -- port 80 to 100 will be accepted
  );
  COMMIT;
END;
/

The opposite of the ADD_PRIVILEGE procedure is:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE ( 
    acl         => 'across_broder.xml'
   ,principal   => 'HR' -- is case sensitive.
   ,privilege   => 'connect'
  );
  COMMIT;
END;
/

Connect privilege will be deleted for schema HR from across_broder.xml Access Control List.

The opposite of the ASSIGN_ACL procedure is:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(
     acl   => 'across_broder.xml'
    ,host  => '*.oracle-web.com'
  ); 
  COMMIT;
END;
/

The assignment for *.oracle-web.com will be removed from the across_broder.xml.

And lastly the opposite of the CREATE_ACL procedure is:

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

across_broder.xml does not longer exist.

To check if a privilege is granted or denied for an user in an ACL, you can use this function:

SELECT DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(
          'across_broder.xml'
         ,'HR'  -- is case sensitive.
         ,'connect') from dual;

It returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.

A nice script to get in contact with a certain host, like “*.oracle-web.com”:

CREATE OR REPLACE PROCEDURE create_acl_proc 
IS 
BEGIN
   $IF DBMS_DB_VERSION.VER_LE_10_2 $THEN
      NULL;
   $ELSIF DBMS_DB_VERSION.VER_LE_11 $THEN
      BEGIN
         DBMS_NETWORK_ACL_ADMIN.DROP_ACL( acl => 'across_broder.xml' );
      EXCEPTION
         WHEN OTHERS THEN
           NULL;
      END;
      
      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( 
         acl         => 'across_broder.xml'
        ,description => 'connects for HR user'
        ,principal   => 'HR' -- adjust principal to your requirements
        ,is_grant    => true
        ,privilege   => 'connect' 
      );
	
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( 
         acl       => 'across_broder.xml'
        ,principal => 'HR'   -- adjust principal to your requirements
        ,is_grant  => true
        ,privilege => 'resolve' 
      );
	
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
         acl  => 'across_broder.xml'
        ,host => '*.oracle-web.com'  -- adjust host to your requirements
      );
   $END
END;
/

The parameter Prinicipal can be a user or a role. If you want open the gate for all users, set principal to ‘PUBLIC’. Please be aware, this is most likely a security risk.

More information are accessible via these two DBA views:

SELECT * FROM DBA_NETWORK_ACLS;

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

Open the service doors for Apex in an Oracle 11 database

To give access for print server, to use web services or sending out emails from Apex execute the following script:

DECLARE
  v_acl_path VARCHAR2(4000);
BEGIN
  -----------------------------------------------------------------
  -- Look for the ACL currently assigned to '*' (means all hosts) 
  -- and give APEX_050000 the "connect" privilege 
  -- if APEX_050000 does not have the privilege yet.
  -----------------------------------------------------------------
  SELECT ACL
    INTO v_acl_path
    FROM DBA_NETWORK_ACLS
    WHERE HOST   = '*'
    AND LOWER_PORT IS NULL
    AND UPPER_PORT IS NULL;
		
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(v_acl_path
                                          ,'APEX_050000'
                                          ,'connect') IS NULL THEN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
       v_acl_path
      ,'APEX_050000'
      ,TRUE
      ,'connect'
    );
  END IF;
	
EXCEPTION  
  --------------------------------------------------------
  -- When no ACL has been assigned to '*', then do it yet
  --------------------------------------------------------
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
       'across_broder.xml'
       ,'ACL that lets certain schemas connect to everywhere'
       ,'APEX_050000'
       ,TRUE
       ,'connect'
    );
		
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( 
        acl       => 'across_broder.xml'
       ,principal => 'APEX_050000'    
       ,is_grant  => true
       ,privilege => 'resolve'
    );
     
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
        'across_broder.xml'
        ,'*'
    );
END;
/
commit;

And again, as mentioned in the beginning, the Apex Schema, like APEX_050000 and the parsing schema need the grant ACL privileges to work without any errors like ORA-24247: network access denied by access control list (ACL).

Grant privilegs for an Oracle 12c Database

host parameter

lower_port/upper_port parameter

ace parameter

Grant privilegs for an Oracle 11g Database

Open the service doors for Apex in an Oracle 11 database

You Might Also Like

Updating Application Express

APEX Administrator Role

Relation between Apex Application, Database Scheme and Workspace

Leave a Reply Cancel reply