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_list | meaning |
| http | Access restricted to the UTL_HTTP package and the HttpUriType type |
| http_proxy | Needed in conjunction with http if HTTP access is via a proxy |
| connect | Opens access to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and DBMS_LDAP packages and the HttpUriType type |
| resolve | ccess restricted to the UTL_INADDR packages |
| smtp | Access restricted to the UTL_SMTP and UTL_MAIL packages |
| jdwp | Enables 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:
- Oracle Database Real Application Security Administrator’s and Developer’s Guide
- Database Security Guide
- Implementing Database Network Access Control Lists in Oracle E-Business Suite Release 12.2 or 12.1 (Doc ID 2500511.1)
- https://oracle-base.com/articles/12c/fine-grained-access-to-network-services-enhancements-12cr1
JoeLi's TechLife 