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

You will see an ORA-24247 error when your PL/SQL code relies on one of the network packages and there is no proper ACL created for it. Network access control lists (ACLs) is used to control network access from the database to services (hosts and ports) external to the database.

In early versions, access to external services is controlled based on whether a user was granted execute permissions on a specific package or not. For the use of the following packages it is mandatory to have an ACL for a user in place starting from 11g.

  • UTL_TCP
  • UTL_SMTP
  • UTL_MAIL
  • UTL_HTTP
  • UTL_INADDR
  • DBMS_LDAP

By default, only sys has a default ACL created which can access any host on any ports. You can check the following internal views.

  • DBA_NETWORK_ACLS
  • DBA_NETWORK_ACL_PRIVILEGES
SQL> select * from dba_network_acls;

HOST                 LOWER_PORT UPPER_PORT ACL                                                               ACLID            ACL_OWNER
-------------------- ---------- ---------- ----------------------------------------------------------------- ---------------- --------------------
*                                          NETWORK_ACL_B12A645D8C340BADE053A40C470A7D2B                      0000000080002712 SYS

SQL> 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;

ACL                                                PRINCIPAL            PRIVILEGE                      IS_GR START_DATE           END_DATE
-------------------------------------------------- -------------------- ------------------------------ ----- -------------------- --------------------
NETWORK_ACL_B12A645D8C340BADE053A40C470A7D2B       GGSYS                resolve                        true
NETWORK_ACL_B12A645D8C340BADE053A40C470A7D2B       GSMADMIN_INTERNAL    resolve                        true

Note starting from 12.1, the above views are deprecated. You should use the following two views.

  • DBA_HOST_ACLS
  • DBA_HOST_ACES
SQL> select * from DBA_HOST_ACLS;

HOST                           LOWER_PORT UPPER_PORT ACL                                                          ACLID            ACL_OWNER
------------------------------ ---------- ---------- ------------------------------------------------------------ ---------------- --------------------
*                                                    NETWORK_ACL_B12A645D8C340BADE053A40C470A7D2B                 0000000080002712 SYS

SQL> SELECT host, lower_port, upper_port, ace_order, principal, principal_type, privilege, INVERTED_PRINCIPAL, grant_type, TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   DBA_HOST_ACES;
HOST                 LOWER_PORT UPPER_PORT  ACE_ORDER PRINCIPAL            PRINCIPAL_T PRIVILEGE                      INV GRANT START_DATE           END_DATE
-------------------- ---------- ---------- ---------- -------------------- ----------- ------------------------------ --- ----- -------------------- --------------------
*                                                   1 GSMADMIN_INTERNAL    DATABASE    RESOLVE                        NO  GRANT
*                                                   2 GGSYS                DATABASE    RESOLVE                        NO  GRANT

We can use the following PL/SQL to create an access control entry. It will give the user TEST1 to use DBMS_LDAP package to access the LDAP server “joe.test.local” on the port 389.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'joe.test.local', 
    lower_port => 389,
    upper_port => 389,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'test1',
                              principal_type => xs_acl.ptype_db)); 
END;
/
  • xs$name_list is a variable size array (VARRAY) type of VARCHAR2(261).
  • xs$ace_type is function defined in the object type.xs$ace_type.
  • xs_acl.ptype_db is a constant with the value 2. It means the ace is for a database user or role.

The privilege_list specifies one or more privileges in a comma separated list as an array for xs$name_list.

privilege_listmeaning
httpAccess restricted to the UTL_HTTP package and the HttpUriType type
http_proxyNeeded in conjunction with http if HTTP access is via a proxy
connectOpens access to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and DBMS_LDAP packages and the HttpUriType type
resolveccess restricted to the UTL_INADDR packages
smtpAccess restricted to the UTL_SMTP and UTL_MAIL packages
jdwpEnables Java Debug Wire Protocol debugging operations

Note an ACL is created implicitly when adding an access control entry (ACE).

SQL> select * from DBA_HOST_ACLS;

HOST                 LOWER_PORT UPPER_PORT ACL                                                ACLID            ACL_OWNER
-------------------- ---------- ---------- -------------------------------------------------- ---------------- --------------------
*                                          NETWORK_ACL_B12A645D8C340BADE053A40C470A7D2B       0000000080002712 SYS
joe.test.local              389        389 NETWORK_ACL_BCEC089824025908E053A40C470A311E       0000000080002724 SYS


SQL> SELECT host, lower_port, upper_port, ace_order, principal, principal_type, privilege, INVERTED_PRINCIPAL, grant_type, TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   DBA_HOST_ACES;

HOST                 LOWER_PORT UPPER_PORT  ACE_ORDER PRINCIPAL            PRINCIPAL_T PRIVILEGE                      INV GRANT START_DATE           END_DATE
-------------------- ---------- ---------- ---------- -------------------- ----------- ------------------------------ --- ----- -------------------- --------------------
*                                                   1 GSMADMIN_INTERNAL    DATABASE    RESOLVE                        NO  GRANT
*                                                   2 GGSYS                DATABASE    RESOLVE                        NO  GRANT
joe.test.local              389        389          1 TEST1                DATABASE    CONNECT                        NO  GRANT

And of course you need to check if the user has the privilege to execute the packages:

SQL> SELECT grantee,table_name,privilege,owner FROM dba_tab_privs
WHERE table_name IN ('DBMS_DEBUG_JDWP'
                      ,'DBMS_LDAP'
                      ,'UTL_INADDR'
                      ,'UTL_HTTP'
                      ,'UTL_MAIL'
                      ,'UTL_SMTP'
                      ,'UTL_TCP');
					  
GRANTEE                        TABLE_NAME                     PRIVILEGE                      OWNER
------------------------------ ------------------------------ ------------------------------ --------------------
GSMADMIN_INTERNAL              UTL_TCP                        EXECUTE                        SYS
GSMADMIN_INTERNAL              UTL_HTTP                       EXECUTE                        SYS
PUBLIC                         UTL_INADDR                     EXECUTE                        SYS
ORACLE_OCM                     UTL_INADDR                     EXECUTE                        SYS
PUBLIC                         DBMS_DEBUG_JDWP                EXECUTE                        SYS
PUBLIC                         DBMS_LDAP                      EXECUTE                        SYS

6 rows selected.

References:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s