Oracle DBMS_NETWORK_ACL_ADMIN
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Provides security for network related PL/SQL packages UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR
AUTHID DEFINER
Constants
Name Data Type Value
DOMAIN_MASK VARCHAR2(80) '\*(\.[^\.\*]+)*'
HOSTNAME_MASK VARCHAR2(80) '[^\.\*]+(\.[^\.\*]+)*'
IP_ADDR_MASK VARCHAR2(80) '([[:digit:]]+\.){3}[[:digit:]]+'
IP_SUBNET_MASK VARCHAR2(80) '([[:digit:]]+\.){0,3}\*'
 
Privilege VARCHAR2 'connect' or 'resolve' (case sensitive)
Data Types TYPE aclid_table IS TABLE OF NUMBER INDEX BY BINARY INTEGER;
Dependencies
DBA_XS_ACES DBMS_SYS_ERROR NOEXP$
DBA_XS_ACLS DBMS_UTILITY PLITBLM
DBA_XS_OBJECTS DUAL USER_NETWORK_ACL_PRIVILEGES
DBMS_ASSERT NACL$_HOST XS$ACE_LIST
DBMS_DATAPUMP NACL$_HOST_EXP XS$ACE_TYPE
DBMS_NETWORK_ACL_UTILITY NACL$_NAME_MAP XS$NAME_LIST
DBMS_RESULT_CACHE_INTERNAL NACL$_WALLET XS_ACL
DBMS_STANDARD NACL$_WALLET_EXP XS_ADMIN_UTIL
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-01927 privilege_not_granted
ORA-24243 ace_already_exists
ORA-24244 invalid_host
ORA-24245 invalid_privilege
ORA-24246 empty_acl
ORA-24248 invalid_wallet_path
ORA-29261 bad_argument
ORA-31001 acl_not_found_num
ORA-46059 invalid_acl_path
ORA-46114 acl_not_found
ORA-46212 acl_already_exists
ORA-46238 unresolved_principal
First Available 11.1
Security Model Owned by SYS with EXECUTE granted to GGSYS, MDSYS and to the DBA, EXECUTE_CATALOG_ROLE and  GSMADMIN_INTERNAL roles.
Source {$ORACLE_HOME}/rdbms/admin/dbmsnacl.sql
Subprograms
 
ADD_PRIVILEGE
Adds a privilege to grant or deny the network access to the user in an access control list (ACL)

Deprecated in 12.2: Use APPEND_HOST_ACE
dbms_network_acl_admin.add_privilege(
acl        IN VARCHAR2,
principal  IN VARCHAR2,
is_grant   IN BOOLEAN,
privilege  IN VARCHAR2,
position   IN PLS_INTEGER              DEFAULT NULL,
start_date IN TIMESTAMP WITH TIMESTAMP DEFAULT NULL,
end_date   IN TIMESTAMP WITH TIMESTAMP DEFAULT NULL);
exec dbms_network_acl_admin.add_privilege(acl => 'mlib-org-permissions.xml', principal => 'UWCLASS', is_grant => TRUE, privilege => 'connect');
 
APPEND_HOST_ACE
Append an access control entry (ACE) to the access control list (ACL) of a network host. The ACL controls access to the given host from the database and the ACE specifies the privileges granted to or denied from the specified principal. dbms_network_acl_admin.append_host_ace(
host       IN VARCHAR2,
lower_port in PLS_INTEGER DEFAULT NULL,
upper_port in PLS_INTEGER DEFAULT NULL,
ace        in xs$ace_type);
TBD
 
APPEND_HOST_ACL
Append access control entries (ACE) of an access control list (ACL) to the ACL of a network host dbms_network_acl_admin.append_host_acl(
host       IN VARCHAR2,
lower_port in PLS_INTEGER DEFAULT NULL,
upper_port in PLS_INTEGER DEFAULT NULL,
acl        IN VARCHAR2);
TBD
 
APPEND_WALLET_ACE
Append an access control entry (ACE) to the access control list (ACL) of a wallet. The ACL controls access to the given wallet from the database and the ACE specifies the privileges granted to or denied from the specified principal. dbms_network_acl_admin.append_wallet_ace(
wallet_path IN VARCHAR2,
ace         in xs$ace_type);
TBD
 
APPEND_WALLET_ACL
Append access control entries (ACE) of an access control list (ACL) to the ACL of a wallet dbms_network_acl_admin.append_wallet_acl(
wallet_path IN VARCHAR2,
acl         IN VARCHAR2);
TBD
 
ASSIGN_ACL
Assigns an access control list (ACL) to a network host, and optionally specific to a TCP port range

Deprecated in 12.2: APPEND_HOST_ACE
dbms_network_acl_admin.assign_acl(
acl        IN VARCHAR2,
host       IN VARCHAR2,
lower_port IN PLS_INTEGER DEFAULT NULL,
upper_port IN PLS_INTEGER DEFAULT NULL);
BEGIN
  dbms_network_acl_admin.assign_acl(
    acl => 'mlib-org-permissions.xml',
    host => '*.morganslibrary.org',
    lower_port => 80);
END;
/

set linesize 121
col description format a50
col security_class_ns format a30
col security_class_name format a20

SELECT description, security_class_ns, security_class_name
FROM xds_acl;
 
ASSIGN_WALLET_ACL
Assigns an access control list (ACL) to a wallet

Deprecated in 12.2: Use APPEND_WALLET_ACE
dbms_network_acl_admin.assign_wallet_acl(
acl         IN VARCHAR2,
wallet_path IN VARCHAR2);
BEGIN
  dbms_network_acl_admin.create_acl('mlib-org-permissions.xml', 'Demo Wallet ACL',
  'UWCLASS', TRUE, 'use-client-certificates');

  dbms_network_acl_admin.add_privilege('mlib-org-permissions.xml', 'UWCLASS', TRUE,
  'use-passwords');

  dbms_network_acl_admin.assign_wallet_acl('mlib-org-permissions.xml', 'file:/oracle/wallets/test_wallet');
END;
/
 
CHECK_PRIVILEGE
Check if a privilege is granted to or denied from the user in an access control list based on the ACL

Deprecated in 12.2
dbms_network_acl_admin.check_privilege(
acl       IN VARCHAR2,
user      IN VARCHAR2,
privilege IN VARCHAR2)
RETURN NUMBER;
See CREATE_ACL Demo Below
 
CHECK_PRIVILEGE_ACLID
Check if a privilege is granted to or denied from the user in an access control list based on the ID of the ACL

Deprecated in 12.2
dbms_network_acl_admin.check_privilege_aclid(
aclid     IN RAW,
user      IN VARCHAR2,
privilege IN VARCHAR2)
RETURN NUMBER;
SELECT DISTINCT aclid
FROM xds_acl;

SELECT dbms_network_acl_admin.check_privilege_aclid( '703A838DAF25441498620A98EC83C8F4', 'MORGANSLIBRARY', 'CONNECT')
FROM dual;

SELECT dbms_network_acl_admin.check_privilege_aclid('703A838DAF25441498620A98EC83C8F4', 'MORGANSLIBRARY', 'connect')
FROM dual;

SELECT NVL(dbms_network_acl_admin.check_privilege_aclid(aclid, 'UWCLASS', 'connect'), 0)
FROM xds_acl;
 
CREATE_ACL
Creates an access control list (ACL) with an initial privilege setting

Deprecated in 12.2: APPEND_HOST_ACE
dbms_network_acl_admin.create_acl(
acl         IN VARCHAR2,
description IN VARCHAR2,
principal   IN VARCHAR2,
is_grant    IN BOOLEAN,
privilege   IN VARCHAR2,
start_date  IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
end_date    IN TIMESTAMP WITH TIMEZONE DEFAULT NULL );
conn sys@pdbdev as sysdba

desc xds_acl

set pagesize 121
col description format a60

SELECT aclid, shared, description
FROM xds_acl;

SELECT any_path
FROM resource_view
WHERE any_path LIKE '/sys/acls/%.xml';

BEGIN
  dbms_network_acl_admin.create_acl(acl => 'mlib-org-permissions.xml',
  description => 'Network permissions for *.morganslibrary.org',
  principal => 'UWCLASS', is_grant => TRUE, privilege => 'connect');
END;
/

SELECT aclid, shared, description
FROM xds_acl;

SELECT any_path
FROM resoure_view
WHERE any_path LIKE '/sys/acls/%.xml';

SELECT DECODE(
  dbms_network_acl_admin.check_privilege('mlib-org-permissions.xml',
  'MORGANSLIBRARY', 'resolve'), 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;

SELECT DECODE(
  dbms_network_acl_admin.check_privilege('mlib-org-permissions.xml',
  'UWCLASS', 'connect'), 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;
 
DELETE_PRIVILEGE
Deletes a privilege in an access control list (ACL)

Deprecated in 12.2: Use REMOVE_HOST_ACE
dbms_network_acl_admin.delete_privilege(
acl       IN VARCHAR2,
principal IN VARCHAR2,
is_grant  IN BOOLEAN  DEFAULT NULL,
privilege IN VARCHAR2 DEFAULT NULL);
BEGIN
  dbms_network_acl_admin.delete_privilege('mlib-org-permissions.xml', 'UWCLASS', NULL, 'connect');
END;
/
 
DROP_ACL
Drops an access control list (ACL)

Deprecated in 12.2
dbms_network_acl_admin.drop_acl(acl IN VARCHAR2);
SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE ANY_PATH LIKE '/sys/acls/%.xml';

BEGIN
  dbms_network_acl_admin.drop_acl('mlib-org-permissions.xml');
END;
/

SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE ANY_PATH LIKE '/sys/acls/%.xml';
 
GET_HOST_ACLIDS
Undocumented internal function dbms_network_acl_admin.get_host_aclids(
host IN VARCHAR2,
port IN NUMBER)
RETURN aclid_table RESULT_CACHE;
TBD
 
GET_WALLET_ACLID
Undocumented internal function dbms_network_acl_admin.get_wallet_aclid(wallet_path IN VARCHAR2)
RETURN NUMBER RESULT_CACHE;
TBD
 
INSTANCE_CALLOUT_IMP
Undocumented dbms_network_acl_import.instance_callout_imp(
obj_name   IN  VACHAR2,
obj_schema IN  VARCHAR2,
obj_type   IN  NUMBER,
prepost    IN  BINARY_INTEGER,
action     OUT VARCHAR2,
alt_name   OUT VARCHAR2);
CREATE TABLE x AS
SELECT * FROM tab$;

DECLARE
 act VARCHAR2(60);
 alt VARCHAR2(60);
BEGIN
  dbms_network_acl_admin.instance_callout_imp('X', USER, 2, 0, act, alt);
  dbms_output.put_line(act);
  dbms_output.put_line(alt);
END;
/

-- fails with ORA-31623: a job is not attached to this session via the specific handle which is to be expected
 
REMOVE_HOST_ACE
Remove privileges from access control entries (ACE) in the access control list (ACL) of a network host matching the given ACE dbms_network_acl_admin.remove_host_ace(
host             IN VARCHAR2,
lower_port       IN PLS_INTEGER DEFAULT NULL,
upper_port       IN PLS_INTEGER DEFAULT NULL,
ace              IN xs$ace_type,
remove_empty_acl IN BOOLEAN DEFAULT FALSE);
TBD
 
REMOVE_WALLET_ACE
Remove privileges from access control entries (ACE) in the access control list (ACL) of a wallet matching the given ACE dbms_network_acl_admin.remove_wallet_ace(
wallet_path      IN VARCHAR2,
ace              IN xs$ace_type,
remove_empty_acl IN BOOLEAN DEFAULT FALSE);
TBD
 
SET_HOST_ACL
Set the access control list (ACL) of a network host which controls access to the host from the database dbms_network_acl_admin.set_host_acl(
host       IN VARCHAR2,
lower_port IN PLS_INTEGER DEFAULT NULL,
upper_port IN PLS_INTEGER DEFAULT NULL,
acl        IN VARCHAR2);
TBD
 
SET_WALLET_ACL
Set the access control list (ACL) of a wallet which controls access to the wallet from the database dbms_network_acl_admin.set_wallet_acl(
wallet_path IN VARCHAR2,
acl         IN VARCHAR2);
TBD
 
UNASSIGN_ACL
Unassigns the access control list (ACL) currently assigned to a network host

Deprecated in 12.2: Use REMOVE_HOST_ACE
dbms_network_acl_admin.unassign_acl(
host       IN VARCHAR2,
lower_port IN PLS_INTEGER DEFAULT NULL,
upper_port IN PLS_INTEGER DEFAULT NULL);
BEGIN
 dbms_network_acl_admin.unassign_acl('*.mlib.org', 80);
END;
/
 
UNASSIGN_WALLET_ACL
Unassign the access control list (ACL) currently assigned to a wallet

Deprecated in 12.2: Use REMOVE_WALLET_ACE
dbms_network_acl_admin.unassign_wallet_acl(
acl         IN VARCHAR2 DEFAULT NULL,
wallet_path IN VARCHAR2 DEFAULT NULL);
exec dbms_network_acl_admin.unassign_wallet_acl('mlib-org-permissions.xml');

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_DEBUG_JDWP
DBMS_NETWORK_ACL_UTILITY
DBMS_XDBUTIL_INT
UTL_HTTP
UTL_INADDR
UTL_MAIL
UTL_MAIL_INTERNAL
UTL_SMTP
UTL_TCP
Wallet
XS_ACL
XS_ACL_INT
XS_ADMIN_UTIL
XS_ADMIN_UTIL_INT
XS_PRINCIPAL
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx