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

Hi,

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

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

 

 

 

 

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

 

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)

 

 

 

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 read the following articles.

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!