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