What is the Access Control List ( ACL ) and How to Create and Grant an ACL in Oracle Database

I will explain What is the Access Control List ( ACL ) and How to Create and Grant an ACL in Oracle Database in this article. Let’s review Access Control List ( ACL ) | Create and Grant an ACL.

 

Access Control List ( ACL )

An access control list in Oracle (ACL) is a list of access control entries to restrict the hosts that are allowed to connect to the Oracle database.

ACLs are created using dbms_network_acl_admin and dbms_network_acl_utility packages.

 

You can create an ACL for Public Users as follows. You can create for any user by changing PUBLIC with Related user.

 

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => '/sys/acls/utl_http.xml',
description => 'Allow mail',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

 

 

 

 

Access Control List ( ACL ) | Create and Grant an ACL

You can add “Connect” privilige as follows.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => '/sys/acls/utl_http.xml',
principal => 'PUBLIC',
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/

 

You can add “Resolve” privilige as follows.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => '/sys/acls/utl_http.xml',
principal => 'PUBLIC',
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/

 

You can Add Host and Port to assign network hosts to the ACL

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => '/sys/acls/utl_http.xml',
host => '192.168.63.63',
lower_port => 34,
upper_port => 63);
COMMIT;
END;
/

 

 

DBA_NETWORK_ACLS

You can list all ACLS and their hosts and ports in the Oracle database with the following query.

 

SELECT * FROM DBA_NETWORK_ACLS;

 

[MSD1]/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 28 15:18:21 2020
Version 18.7.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.7.0.0.0

SQL> desc DBA_NETWORK_ACLS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HOST                                      NOT NULL VARCHAR2(1000)
 LOWER_PORT                                         NUMBER(5)
 UPPER_PORT                                         NUMBER(5)
 ACL                                                VARCHAR2(4000)
 ACLID                                              RAW(8)
 ACL_OWNER                                          VARCHAR2(128)

 

 

 

 

DBA_NETWORK_ACL_PRIVILEGES

You can list All ACL and their priviliges using the following query.

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

 

 

SQL> desc DBA_NETWORK_ACL_PRIVILEGES;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACL                                                VARCHAR2(4000)
 ACLID                                              RAW(8)
 PRINCIPAL                                          VARCHAR2(128)
 PRIVILEGE                                          VARCHAR2(128)
 IS_GRANT                                           VARCHAR2(5)
 INVERT                                             VARCHAR2(5)
 START_DATE                                         TIMESTAMP(6)
 END_DATE                                           TIMESTAMP(6)
 ACL_OWNER                                          VARCHAR2(128)

 

Do you want to learn more Oracle Security, then Click this link and read the articles.