Oracle DBMS_NETWORK_ACL_ADMIN
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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_NETWORK_ACLS DBMS_RESULT_CACHE_INTERNAL PLITBLM
DBA_NETWORK_ACL_PRIVILEGES DBMS_STANDARD USER_NETWORK_ACL_PRIVILEGES
DBA_XS_ACES DBMS_SYS_ERROR XS$ACE_LIST
DBA_XS_ACLS DBMS_UTILITY XS$ACE_TYPE
DBA_XS_OBJECTS DUAL XS$NAME_LIST
DBMS_ASSERT NACL$_HOST XS_ACL
DBMS_NETWORK_ACL_IMPORT NACL$_NAME_MAP XS_ADMIN_UTIL
DBMS_NETWORK_ACL_UTILITY NACL$_WALLET  
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
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) 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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 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 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 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 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 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 RESOURCE_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) 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) 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 (new 12.1)
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 (new 12.1)
Undocumented internal function dbms_network_acl_admin.get_wallet_aclid(wallet_path IN VARCHAR2)
RETURN NUMBER RESULT_CACHE;
TBD
 
INSTANCE_CALLOUT_IMP (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 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 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

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