What is
ACL?
Access Control List (ACL) is
a security mechanism introduced in Oracle 11g. Its security level setup on
users and roles to access data and what is the level of data access.
In
ACL:
users or roles are
called principals
operations are called privileges
operations are called privileges
An
ACL consists of a list of ACE (Access Control Entries)
An ACE grants or denies a privilege to
a principal. An ACL is in XML file format and each ACE is an XML
element. The file is created in the /sys/acls directory by default.
For
more details on ACL Please refer MOS document: Access
Control Lists and Security Classes.
How to
set up ACL for UTL_HTTP?
Step 1. Create a new ACL (if
required) or use an existing ACL to grant
"connect" privilege to the user that needs to run UTL_HTTP.
Step 2. Assign the ACL created
in Step1 to the network host to be accessed using UTL_HTTP.
ACL
Script:
begin
--
Step 1. Create a new ACL utl_http.xml.
--
Grant privilege 'connect' to user 'HR'
--
using the ACL
dbms_network_acl_admin.create_acl (
acl =>
'utl_http.xml',
description
=> 'HTTP Access',
principal =>
'HR',
is_grant =>
TRUE,
privilege =>
'connect'
);
--
Step 2. Assign the ACL created in Step 1
-- to
the network host for the web service
dbms_network_acl_admin.assign_acl (
acl =>
'utl_http.xml',
host =>
'*.webservice.com'
);
commit;
end;
/
Note: Using ‘*’ in the host name gives access
to any sub-domain on the host. If you want to restrict to specific sub-domains
only, put the exact name in the host instead of ‘*’.
Multiple hosts can be assigned to one ACL '192.168.1.100' & '192.168.1.*’
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl =>
'utl_http.xml',
host =>
'192.168.1.100',
lower_port =>
80,
upper_port =>
NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl =>
'utl_http.xml',
host =>
'192.168.1.*',
lower_port =>
NULL,
upper_port =>
NULL);
COMMIT;
END;
/
After setting up ACL as sys users in database, connect with
HR and run the PL/SQL script which uses web service.
Test ACL:
Testing can be performed with any PL SQL procedure witch calls
UTL_HTTPS
DECLARE
l_url VARCHAR2(50)
:= 'http://192.168.1.100:80';
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
BEGIN
--
Make a HTTP request and get the response.
l_http_request := UTL_HTTP.begin_request(l_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.end_response(l_http_response);
END;
/
Verification View:
As SYS User:
COLUMN
host FORMAT A30
COLUMN acl FORMAT
A30
SELECT
host, lower_port, upper_port, acl FROM dba_network_acls;
COLUMN acl FORMAT
A30
COLUMN
principal FORMAT 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;
With HR User:
COLUMN
host FORMAT A30
SELECT
host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
As SYS
user:
SELECT
DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege('test_acl_file.xml', 'TEST1',
'connect'), 1, 'GRANTED', 0, 'DENIED', NULL) privilege FROM dual;
COLUMN acl FORMAT
A30
COLUMN
host FORMAT A30
SELECT acl,
host, DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TEST2',
'connect'), 1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls;
SELECT *
FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('hostname.localdomain'));
SELECT
* FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('192.168.1.100'));
SELECT DBMS_NETWORK_ACL_UTILITY.domain_level('hostname.localdomain')
FROM dual;
SELECT DBMS_NETWORK_ACL_UTILITY.domain_level('192.168.1.100')
FROM dual;
SELECT
host, lower_port, upper_port, acl,
DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TEST1',
'connect'), 1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE
FROM dba_network_acls
WHERE host
IN (SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('10.1.10.191'))) ORDER
BY DBMS_NETWORK_ACL_UTILITY.domain_level(host) desc, lower_port, upper_port;
Regards,
Mallik
sql azure training
ReplyDeletevmware training
ServiceNow Online Training