Oracle DBMS_NETWORK_ACL_ADMIN
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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_UTILITY NOEXP$
DBA_XS_ACLS DUAL PLITBLM
DBA_XS_OBJECTS NACL$_HOST USER_NETWORK_ACL_PRIVILEGES
DBMS_ASSERT NACL$_HOST_EXP XS$ACE_LIST
DBMS_DATAPUMP NACL$_NAME_MAP 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
DBMS_SYS_ERROR    
Documented Yes
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.0.6
Security Model Owned by SYS with EXECUTE granted to the DBA and EXECUTE_CATALOG_ROLE roles and SYSRAC.
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
 
INSTANCE_EXPORT_ACTION
Undocumented dbms_network_acl_import.instance_export_action(
obj_name     IN  VACHAR2,
obj_schema   IN  VARCHAR2,
obj_type     IN  NUMBER,
tgt_version  IN  VARCHAR2,
action       OUT VARCHAR2,
alt_name     OUT VARCHAR2,
where_clause OUT VARCHAR2);
CREATE TABLE x AS
SELECT * FROM tab$;

DECLARE
 act VARCHAR2(60);
 alt VARCHAR2(60);
 wc VARCHAR2(120);
BEGIN
  dbms_network_acl_admin.instance_export_action('X', USER, 2, '12.1.0', act, alt, wc);
  dbms_output.put_line('ACT: ' || act);
  dbms_output.put_line('ALT: ' || alt);
  dbms_output.put_line('WC: ' || wc);
END;
/
ACTL SKIP
ALT:
WC:
 
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
DBMS_DEBUG_JDWP
DBMS_NETWORK_ACL_UTILITY
DBMS_XDBUTIL_INT
Packages
Security
UTL_HTTP
UTL_INADDR
UTL_MAIL
UTL_MAIL_INTERNAL
UTL_SMTP
UTL_TCP
Wallet
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved