Oracle Fine-Grained Access Control for UTL_* Packages -- ACL

Oracle Database 11g provides a mechanism to refine the level of access to the network
access packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR.

You can use the DBMS_NETWORK_ACL_ADMIN package to facilitate management of the UTL_*

network access packages as in the following steps:

We we ill get  ORA-24247   if  Acl is missing  

12c method of Creating Acl 

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

host => '*',

ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),

principal_name => 'USERNAME',

principal_type => xs_acl.ptype_db));

END;

/

Pre 12c Method  Of Creating Acl 

1) Create an Access Control List (ACL): 

All ACL definitions are stored in XML DB in the form of  XML documents. The ACL XML files reside in the /sys/acls directory of the XML DB
repository. Following is an example of using the CREATE_ACL procedure to create an XML
file called dba.xml:

begin
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
 ACL => 'dba.xml', -- case sensitive
 DESCRIPTION=> 'Network Access Control for the DBAs',
 PRINCIPAL => 'SCOTT', -- user or role the privilege is granted or denied(upper case)
 IS_GRANT => TRUE, -- privilege is granted or denied
 PRIVILEGE => 'connect', -- or 'resolve' (case sensitive)
 START_DATE => null, -- when the access control entity ACE will be valid
 END_DATE => null); -- ACE expiration date (TIMESTAMP WITH TIMEZONE format)
end;

Regarding the PRIVILEGE parameter, the database user needs the connect privilege to an
external network host computer if he or she is connecting using the UTL_TCP, UTL_HTTP,
UTL_SMTP, and UTL_MAIL utility packages. To resolve a host name that was given as a host IP
address, or the IP address that was given as a host name, with the UTL_INADDR package,
grant the database user the resolve privilege.

You can then query the RESOURCE_VIEW view to find the dba.xml ACL in the /sys/acls
directory:

select ANY_PATH
from RESOURCE_VIEW
where ANY_PATH LIKE '/sys/acls/dba%'

Too may entries in the ACL may lead to significant XML DB performance drop because ACL
are checked for each access to Oracle XML DB repository. As general rule of thumb, ACL
check operations perform best when the number of ACEs in the ACL is at 16 entries or less.

2) Add Access Control Entries: 

Once you create the initial ACL, you can continue to add more
privileges to the XML file. The following example will add the user RAMI to the dba.xml file
and grant him network access:
begin

 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
 ACL => 'dba.xml',
 PRINCIPAL => 'RAMI',
 IS_GRANT => TRUE, 
 PRIVILEGE => 'connect',
 START_DATE => null, -- if the time interval is defined,
 END_DATE => null); -- the ACE will expire after the specified date range
end;
/
COMMIT;

In ACL, the security entries are evaluating in order precedence. If you have two contradicting
entries in the list, the first one in the order will take effect. You can control the order number
of an added entry as follows:

begin
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
 POSITION => 1, -- on the top
 ACL => 'dba.xml', 

 PRINCIPAL => 'SAMI',
 IS_GRANT => FALSE, 

 PRIVILEGE => 'connect',
 START_DATE => null, END_DATE => null);
end;

3) Assign Hosts: 

The ASSIGN_ACL procedure is used to authorize access to one or more
network hosts as follows:

begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
 ACL => 'dba.xml', HOST => 'dbaexpert.com',
 LOWER_PORT => 80, UPPER_PORT => 443);
end;
COMMIT;

The lower port and the upper port define the lower and the upper boundaries of the allowable
port range. They should be set for connect privileges not resolve privileges.

4) Validate that the ACL permissions worked accordingly. 

Following is an example to test the code in the previous step.

select UTL_HTTP.REQUEST('http://www.ahmedbaraka.com') from dual;

If the sufficient ACL privileges or ACL assignments are not provided, you will receive the ORA24247 error.

Access Control Lists Maintenance

Use DELETE_PRIVILEGE to remove an access control entry from the XML file.

exec DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE( ACL=>'dba.xml', PRINCIPAL=> 'RAMI');

 

The UNASSIGN_ACL procedure allows you to manually drop ACL assignments. It uses the same parameter list as the ASSIGN_ACL procedure, with any NULL parameters acting as wildcards.

BEGIN

  DBMS_NETWORK_ACL_ADMIN.unassign_acl (

    acl         => 'test_acl_file.xml',

    host        => '192.168.2.3', 

    lower_port  => 80,

    upper_port  => NULL); 

  COMMIT;

END;

/

Use the DROP_ACL procedure to remove the XML file from the /sys/acls directory as follows:

exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL ( ACL=>'dba.xml' ); 

Views : 

Pre-checks to ensure XML DB installed:

-- user XDB exists

select * from ALL_USERS order by username desc;

-- resource_view exists

select * from resource_view;

-- shows XML DB is installed

select * from dba_registry;

To display list of the ACLs created in the database, use the following query:

select HOST, LOWER_PORT, UPPER_PORT, ACL from DBA_NETWORK_ACLS

You can query the DBA_NETWORK_ACL_PRIVILEGES view to query network privileges granted or
denied for the access control list as follows:

select PRINCIPAL, PRIVILEGE, IS_GRANT
from DBA_NETWORK_ACL_PRIVILEGES
where ACL like '%dba.xml'

Logged on users can use the following query to see their access entries in the dba.xml file:

select HOST, LOWER_PORT, UPPER_PORT, STATUS privilege
from USER_NETWORK_ACL_PRIVILEGES
where HOST in
(select * from
 table(DBMS_NETWORK_ACL_UTILITY.DOMAINS('dbaexpert.com')))
and PRIVILEGE = 'connect'
order by DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) desc, LOWER_PORT;

COLUMN host FORMAT A30

COLUMN acl FORMAT A30

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls;

COLUMN acl FORMAT A30

COLUMN principal FORMAT A30

SELECT acl,

       principal,

       privilege,

       is_grant,

       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

FROM   dba_network_acl_privileges;

CONN test1/test1@db11g

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status

FROM   dba_network_acl_privileges;

-- NB: new dba_host_aces view

SELECT host,

       lower_port,

       upper_port,

       ace_order,

       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date,

       grant_type,

       inverted_principal,

       principal,

       principal_type,

       privilege

FROM   dba_host_aces

ORDER BY host, ace_order;

-- NB: new dba_host_acls view

SELECT HOST,

       LOWER_PORT,

       UPPER_PORT,

       ACL,

       ACLID,

       ACL_OWNER

FROM   dba_host_acls

ORDER BY host;

-- NB: dba_network_acls deprecated view in 12c

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls

ORDER BY host;

-- NB: dba_network_acl_privileges deprecated view in 12c

SELECT acl,

       principal,

       privilege,

       is_grant,

       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

FROM   dba_network_acl_privileges

ORDER BY acl, principal, privilege;

-- -----------------------------------------------------------------------------------

-- File Name    : https://oracle-base.com/dba/11g/network_acls_ddl.sql

-- Author       : Tim Hall

-- Description  : Displays DDL for all network ACLs.

-- Requirements : Access to the DBA views.

-- Call Syntax  : @network_acls_ddl

-- Last Modified: 28-JUL-2017

-- -----------------------------------------------------------------------------------

SET SERVEROUTPUT ON FORMAT WRAPPED LINESIZE 300

DECLARE

  l_last_acl       dba_network_acls.acl%TYPE                 := '~';

  l_last_principal dba_network_acl_privileges.principal%TYPE := '~';

  l_last_privilege dba_network_acl_privileges.privilege%TYPE := '~';

  l_last_host      dba_network_acls.host%TYPE                := '~';

  FUNCTION get_timestamp (p_timestamp IN TIMESTAMP WITH TIME ZONE)

    RETURN VARCHAR2

  AS

    l_return  VARCHAR2(32767);

  BEGIN

    IF p_timestamp IS NULL THEN

      RETURN 'NULL';

    END IF;

    RETURN 'TO_TIMESTAMP_TZ(''' || TO_CHAR(p_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') || ''',''DD-MON-YYYY HH24:MI:SS.FF TZH:TZM'')';

  END;

BEGIN

  FOR i IN (SELECT a.acl,

                   a.host,

                   a.lower_port,

                   a.upper_port,

                   b.principal,

                   b.privilege,

                   b.is_grant,

                   b.start_date,

                   b.end_date

            FROM   dba_network_acls a

                   JOIN dba_network_acl_privileges b ON a.acl = b.acl

            ORDER BY a.acl, a.host, a.lower_port, a.upper_port)

  LOOP

    IF l_last_acl <> i.acl THEN

      -- First time we've seen this ACL, so create a new one.

      l_last_host := '~';

      DBMS_OUTPUT.put_line('-- -------------------------------------------------');

      DBMS_OUTPUT.put_line('-- ' || i.acl);

      DBMS_OUTPUT.put_line('-- -------------------------------------------------');

      DBMS_OUTPUT.put_line('BEGIN');

      DBMS_OUTPUT.put_line('  DBMS_NETWORK_ACL_ADMIN.drop_acl (');

      DBMS_OUTPUT.put_line('    acl          => ''' || i.acl || ''');');

      DBMS_OUTPUT.put_line('  COMMIT;');

      DBMS_OUTPUT.put_line('END;');

      DBMS_OUTPUT.put_line('/');

      DBMS_OUTPUT.put_line(' ');

      DBMS_OUTPUT.put_line('BEGIN');

      DBMS_OUTPUT.put_line('  DBMS_NETWORK_ACL_ADMIN.create_acl (');

      DBMS_OUTPUT.put_line('    acl          => ''' || i.acl || ''',');

      DBMS_OUTPUT.put_line('    description  => ''' || i.acl || ''',');

      DBMS_OUTPUT.put_line('    principal    => ''' || i.principal || ''',');

      DBMS_OUTPUT.put_line('    is_grant     => ' || i.is_grant || ',');

      DBMS_OUTPUT.put_line('    privilege    => ''' || i.privilege || ''',');

      DBMS_OUTPUT.put_line('    start_date   => ' || get_timestamp(i.start_date) || ',');

      DBMS_OUTPUT.put_line('    end_date     => ' || get_timestamp(i.end_date) || ');');

      DBMS_OUTPUT.put_line('  COMMIT;');

      DBMS_OUTPUT.put_line('END;');

      DBMS_OUTPUT.put_line('/');

      DBMS_OUTPUT.put_line(' ');

      l_last_acl := i.acl;

      l_last_principal := i.principal;

      l_last_privilege := i.privilege;

    END IF;

    IF l_last_principal <> i.principal 

    OR (l_last_principal = i.principal AND l_last_privilege <> i.privilege) THEN

      -- Add another principal to an existing ACL.

      DBMS_OUTPUT.put_line('BEGIN');

      DBMS_OUTPUT.put_line('  DBMS_NETWORK_ACL_ADMIN.add_privilege (');

      DBMS_OUTPUT.put_line('    acl       => ''' || i.acl || ''',');

      DBMS_OUTPUT.put_line('    principal => ''' || i.principal || ''',');

      DBMS_OUTPUT.put_line('    is_grant  => ' || i.is_grant || ',');

      DBMS_OUTPUT.put_line('    privilege => ''' || i.privilege || ''',');

      DBMS_OUTPUT.put_line('    start_date   => ' || get_timestamp(i.start_date) || ',');

      DBMS_OUTPUT.put_line('    end_date     => ' || get_timestamp(i.end_date) || ');');

      DBMS_OUTPUT.put_line('  COMMIT;');

      DBMS_OUTPUT.put_line('END;');

      DBMS_OUTPUT.put_line('/');

      DBMS_OUTPUT.put_line(' ');

      l_last_principal := i.principal;

      l_last_privilege := i.privilege;

    END IF;

    IF l_last_host <> i.host||':'||i.lower_port||':'||i.upper_port THEN

      DBMS_OUTPUT.put_line('BEGIN');

      DBMS_OUTPUT.put_line('  DBMS_NETWORK_ACL_ADMIN.assign_acl (');

      DBMS_OUTPUT.put_line('    acl         => ''' || i.acl || ''',');

      DBMS_OUTPUT.put_line('    host        => ''' || i.host || ''',');

      DBMS_OUTPUT.put_line('    lower_port  => ' || NVL(TO_CHAR(i.lower_port),'NULL') || ',');

      DBMS_OUTPUT.put_line('    upper_port  => ' || NVL(TO_CHAR(i.upper_port),'NULL') || ');');

      DBMS_OUTPUT.put_line('  COMMIT;');

      DBMS_OUTPUT.put_line('END;');

      DBMS_OUTPUT.put_line('/');

      DBMS_OUTPUT.put_line(' ');

      l_last_host := i.host||':'||i.lower_port||':'||i.upper_port;

    END IF;

  END LOOP;

END;

/

References :

https://docs.oracle.com/database/121/ARPLS/d_networkacl_adm.htm#ARPLS74575

How to setup ACL on 12c and later (Doc ID 2357072.1)

Using ACEs to Set Up ACLs on 12c and above (To Remedy ORA-24247 Errors) (Doc ID 2267848.1)