Manage the Access Control Lists(ACL) privileges in Oracle

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.