Customize Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorized as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customized advertisements based on the pages you visited previously and to analyze the effectiveness of the ad campaigns.

No cookies to display.

Access Control List (ACL) in Oracle Database

Jun 5, 2020

Share this post

Introduction:

An ACL, as the name infers, is basically a list of who can access what and with which privileges.

    • Who denote for Principal of an ACL/User/Role or Public.
    • What denote for Host/Port ranges.
    • Which denote for Connect or Resolve or both Connect and Resolve.

So, connect allows to connect to a host and send/receive data , where as resolve allows to look up hostnames / ip addresses with UTL_INADDR package.

New Network ACL’s are expansion of the acl facilities of the XDB subsystem. 11g introduced a new security measure called Access Control Lists (ACL) and all network access is blocked by default.

 ACL’s in Oracle databases:

Database Versions

11g

12c

18c

19c

Definition ACL’s are first introduced in 11g , To have Fine-Grained access control for the packages
UTL_SMTP, UTL_TCP, UTL_MAIL, UTL_HTTP and UTL_INADDR to access external network resources by the users.
Network ACL’s are known as Real Application Security ACLs in 12c and existing ACLs are migrated from XML DB ACLs and renamed during upgrade. Real Application Security encompasses access control lists (ACLs) and
supports grants, denials, and various conflict resolution methods.
Real Application Security encompasses access control lists (ACLs) and
supports grants, denials, and various conflict resolution methods.
Deprecated subprograms There are no deprecated features for this release

Deprecated sub-programs

ADD_PRIVILEGE Procedure
ASSIGN_ACL Procedure
ASSIGN_WALLET_ACL Procedure
CHECK_PRIVILEGE Function
CHECK_PRIVILEGE_ACLID Function
CREATE_ACL Procedure
DELETE_PRIVILEGE Procedure
DROP_ACL Procedure
UNASSIGN_ACL Procedure
UNASSIGN_WALLET_ACL Procedure

There are no deprecated features for this release There are no deprecated features for this release

Syntax for operations on ACL:

 

Create ACL

Assign ACL

Unassign ACL

BEGIN
DBMS_NETWORK_ACL_ADMIN

.create_acl (
acl => ‘test.xml’,
description => ‘test’,
principal => ‘APPS’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);
commit;
end;
/

BEGIN
DBMS_NETWORK_ACL

_ADMIN.assign_acl (
acl => ‘test.xml’,
host => ‘test.myblog.com’,
lower_port => 80,
upper_port => 80);
end;
/

begin
dbms_network_acl_admin.unassign_acl(
acl        => ‘test.xml’,
host       => ‘test.myblog.com’,
lower_port => 80,
upper_port => 80
);
end;
/

Add privilege

Drop ACL

Delete privilege

BEGIN
DBMS_NETWORK_ACL_

ADMIN.add_privilege (
acl => ‘test.xml’,
principal => ‘APPS’,
is_grant => FALSE,
privilege => ‘resolve’,
position => NULL,
start_date => NULL,
end_date => NULL);
COMMIT;
END;
/

begin
DBMS_NETWORK_ACL_

ADMIN.drop_acl (acl => ‘test.xml’);
COMMIT;
END;
/

begin
dbms_network_acl_admin.delete_

privilege(‘test.xml’, ‘APPS’, NULL, ‘connect’);
end;
/

How to migrate ACL’s 1.Check for existing Network ACLs before the upgrade.
2.Preserve existing network ACLs and privileges (DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES) in an intermediate staging table.
3.Preserving the existing privileges in a table enables you to restore them if the automatic migration fails, or if you want to roll back an upgrade.

Note:

From 11.1.0.7 , We can regenerate the ACL Creation Script Based on the Contents Of DBA_NETWORK_ACLS (Doc ID 1634275.1)