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:
- 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.
- 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 😊
