DBSecWorx: DBMS_NETWORK_ACL

Oracle DBMS_NETWORK_ACL_ADMIN Built-In Package Versions 9.0 - 19.3

Security Advisory

This package provides security for network accessing built-in PL/SQL packages such as UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR. The package controls their ability to access destinations via TCP/IP networks by means of defining and enforcing Access Control Lists.

If you are in any version of Oracle prior to 12cR1 or in any version of Oracle 12cR1 or above but not using the new container architecture the package and its associated ACLs is one of a very short list of options available for preventing the database's internal tools from being used to compromise security.

By comparison with Lockdown Profiles ... ACLs are fine grained. Lockdown Profiles are an on/off switch permitting or blocking all access. ACLs allow for fine tuning so that access may be made possible but only to specifically identified targets.

It is our opinion that any Oracle Database not using Access Control Lists is a database that is insecure and that the DBA team responsible for its care is guilty of malpractice. Sounds harsh perhaps but we worked on a breach involving a major medical center where PII and PHI data were shipped out of the database in a manner that could have been easily prevented with nothing more sophisticated than the few minutes it would have taken to create an ACL.

Recommended Security Rules

NEVER

Deploy any Oracle Database without creating an Access Control List constructed to limit all access to the minimum number of locations ... in most cases to eliminate any ability for the database to access any target except via encrypted SQL*Net.

WITH GREAT CARE

Grant access to this package to any person or process not carefully monitored.

CAUTIONS

The Access Control Lists created by this package are a potential vulnerability. Think through how you will protect them.

How Oracle Works

This section contains a test. This package is one of the most essential components of practicing good OpSec as it provides capabilities not available to a DBA anywhere else in IT. That said, to make proper use of any tool you must understand what it can do, what it cannot do, and what risks must be guarded against. Think of this package as a match. It can do very valuable things like starting a fire to give you warmth, to cook food, to boil water. But you know that match can also be used to burn down your house.

Review the functionality below and write down a list of issues that must be address to take advantage of this package's features.

Then, after you have it compiled ... highlight the area below this sentence with your mouse and see if you caught this one too.
The package writes xml files to the file system. You must protect the integrity of the files from tampering or deletion?

DBMS_NETWORK_ACL_ADMIN Package Information

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}\*'

Data Types TYPE aclid_table IS TABLE OF NUMBER INDEX BY BINARY INTEGER;

Dependencies

DBA_XS_ACES	DBMS_UTILITY	PLITBLM
DBA_XS_ACLS	DUAL	USER_NETWORK_ACL_PRIVILEGES
DBA_XS_OBJECTS	NACL$_HOST	XDB$ACL
DBMS_ASSERT	NACL$_HOST_EXP	XS$ACE_LIST
DBMS_DATAPUMP	NACL$_NAME_MAP	XS$ACE_TYPE
DBMS_NETWORK_ACL_UTILITY	NACL$_WALLET	XS$NAME_LIST
DBMS_RESULT_CACHE_INTERNAL	NACL$_WALLET_EXP	XS_ACL
DBMS_STANDARD	NOEXP$	XS_ADMIN_UTIL
DBMS_SYS_ERROR

Documented in Types & Packages 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-24247	access_denied_num
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 DBA, EXECUTE_CATALOG_ROLE, GSMADMIN_INTERNAL, GGSYS, and MDSYS

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

As seen in the demo, at right, an ACL must be created before this is run 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);

exec dbms_network_acl_admin.set_host_acl('*.mlib.org', acl=>'mlib-org-permissions.xml');

      *

      ERROR at line 1:

      ORA-46114: ACL name /sys/acls/mlib-org-permissions.xml not found.

      ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 1414

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

      ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 268

      ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 423

      ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 1403

      ORA-06512: at line 1

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');