What is ACL? and How to configure ACL?

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

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