Access Control List (ACL)

Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. The CREATE_ACL procedure uses the following parameters to create a new ACL:

In  11g and beyond, Oracle has introduced the ability to restrict connections to specific hosts (or IP addresses).

The access control lists (ACL) are used to restrict the hosts that are allowed to connect to the Oracle database.

ACL's are created using the dbms_network_acl_admin and dbms_network_acl_utility packages.  Either package can be used to create and manage ACLs.

• acl - The name of the access control list XML file, generated relative to the "/sys/acls" directory in the XML DB Repository.
• description - A description of the ACL.
• principal - The first user account or role being granted or denied permissions. The text is case sensitive.
• is_grant - TRUE to grant, FALSE to deny the privilege.
• privilege - Use 'connect' for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access. Use 'resolve' for UTL_INADDR name/IP resolution. The text is case sensitive.
• start_date - Default value NULL. When specified, the ACL will only be active on or after the specified date.
• end_date - An optional end date for the ACL.

Note:  It is not possible that one server can have multiple services configured. If you try to do so then first configured service will be overwritten.

Creation of ACL Example:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',

                                    description => 'WWW ACL',

                                    principal   => 'SCOTT',

                                    is_grant    => true,

                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',

                                       principal => 'SCOTT',

                                       is_grant  => true,

                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',

                                    host => 'www.us.oracle.com');

END;

/

COMMIT;

Please find below sample scenario.

Error:

we have faced below error on DB: 

ORA-24247: network access denied by access control list (ACL)

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

ORA-06512: at "XMLUSER.SEND_FLOWMON_MSG_ORDFAIL", line 33

ORA-06512: at "XMLUSER.XML_ORDER_PROCESSOR", line 816

ORA-06512: at "XMLUSER.XML_ORDER_PROCESSOR", line 793

The DBA_NETWORK_ACLS view displays information about network and ACL assignments:

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL

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

101.29.77.147                                         /sys/acls/utl_http.xml

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL:

COLUMN acl FORMAT A30

COLUMN principal FORMAT A30

col ACL for a40

col PRINCIPAL for a30

col PRIVILEGE for 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;

ACL                            PRINCIPAL                      PRIVILE IS_GR START_DATE  END_DATE

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

/sys/acls/utl_http.xml         TESTUSER                        connect true

The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings:

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status

FROM   user_network_acl_privileges;

HOST                           LOWER_PORT UPPER_PORT PRIVILE STATUS

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

101.29.77.147                                         connect GRANTED

101.29.77.147                                         resolve GRANTED

Steps To Fix the above issue:

1. First check the privileges on the user whether connect privilege is granted or not using dba_network_acl_privileges view, in my case privilege is not there.
2. If not granted then follow below step.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => '/sys/acls/utl_http.xml',

principal => 'TESTUSER',

is_grant => true,

privilege => 'connect');

end;

/

commit;

If you need to resolve a host name from a host IP you will need the resolve grant as well.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'TESTUSER',

is_grant => true,

privilege => 'resolve');

end;

/

commit;

    3.The final step is to assign the ACL to a specific target.

BEGIN

dbms_network_acl_admin.assign_acl (

acl => 'http_permissions.xml',

host => 'NETWORK ADDRESS', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/

lower_port => 80,

upper_port => 80

);

END;

After above steps performed my issues has been resolved.

Hope this helps !!! Cheers !!!

Thank you 😊