Access Control List (ACL) in Oracle Database

Introduction:

ACL’s in Oracle databases:

Syntax for operations on ACL:

Database Versions

11g

12c

18c

19c

Jun 5, 2020

Share this post

issues-after-qlikview-version-upgrade-on-passive-node-of-production-servers

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.

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.

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

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.

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; /