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.