Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
From 11gR1, access to external network services in Oracle databases is done through the Access Control List (ACL). It is possible to perform operations with the package named DBMS_NETWORK_ACL_ADMIN.
In order to use the following PL / SQL packages, network ACL must be created and authorized. Otherwise, “ORA-24247: network access denied access control list (ACL)” error will occur.
• UTL_TCP
• UTL_HTTP
• UTL_SMTP
• UTL_MAIL
With the following procedure, we create a “network acl” called “network_services.xml” for SCOTT user.
We give connect and resolve privileges to the corresponding user. Thus, the SCOTT user will be able to receive service from all hosts.
With DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE, other users can also be authorized for this “network acl”.
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL can authorize other hosts.
It is also possible to authorize individual hosts instead of authorizing for all hosts.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => 'network_services.xml', description => 'NETWORK ACL', principal => 'SCOTT', is_grant => true, privilege => 'connect'); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( acl => 'network_services.xml', principal => 'SCOTT', is_grant => true, privilege => 'resolve'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'network_services.xml', host => '*'); COMMIT; END; |
You can use the following query to query whether the host is authorized:
1 | SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('*')); |
For defined ACL and information:
1 2 | select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS; select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES; |
