Manage the Access Control Lists(ACL) privileges in Oracle

Access Control Lists is used to manage the security for fine-grained access control. Oracle provide security for fine-grained access control for table data and for resources in Oracle XML DB Repository for secure access by Oracle Fusion users. Fusion users are who which is not directly database user.
DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).
ACL is stored in XML DB. Location in XML DB is /sys/acl/

For fine-grained access control, We are using three dimensions:
(1) which users – Principals
(2) perform which operations – Privileges
(3) on which data – Objects

Check the ACL permission present in Oracle

COL ACL_OWNER FOR A12
COL ACL FOR A67
COL HOST FOR A34
col PRINCIPAL for a20
col PRIVILEGE for a13
select ACL_OWNER,ACL,HOST,LOWER_PORT,UPPER_PORT FROM dba_network_acls;
select ACL_OWNER,ACL,PRINCIPAL,PRIVILEGE from dba_network_acl_privileges;


Syntax for Creating ACL Commands

Syntax of creating the ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => "file_name.xml",
host => "network_host",
lower_port => null|port_number,
upper_port => null|port_number);
END;
----Example:
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'test.xml',
host => 'www.google.com',
lower_port => 80,
upper_port => 1000);
END;
/
Commit;


Example of Creating and checking the ACL permissions by different methods present in DBMS_NETWORK_ACL_ADMIN package
You can do it with one command as show above or separates commands as shown below:
1. Create an ACL and define Connect permission to Scott.

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
(acl => 'www.xml', ---filename of ACL file saved as .xml Extension
description => 'WWW ACL', --- Put the description of ACL
principal => 'SCOTT', --- Username for access
is_grant => true,
privilege => 'connect'); ---Permission or privileges
END;
/
commit;


2. Later add more privileges to user HR in created ACL

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'HR',
is_grant => true,
privilege => 'resolve');
END;
/
commit;


3. Assign a URL to ACL created.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => 'www.oracle.com');
END;
/
COMMIT;


4. Check the ACL permissions assigned to Scott.

SELECT host, lower_port, upper_port, acl,
DECODE(
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SCOTT', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) privilege
FROM dba_network_acls
WHERE host IN
(SELECT * FROM
TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.oracle.com')))
ORDER BY DBMS_NETWORK_ACL_UTLITITY.DOMAIN_LEVEL(host) desc, lower_port,upper_port;


5. Unassign the privileges from http://www.oracle.com

BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.us.oracle.com');
END;
/
Commit;