81 DBMS_NETWORK_ACL_UTILITY

Using DBMS_NETWORK_ACL_UTILITY

• Examples

Examples

The DOMAINS Function in this package returns all the domains a host belongs to. It can be used in conjunction with the CHECK_PRIVILEGE_ACLID Function in the DBMS_NETWORK_ACL_ADMIN pacakge to determine the privilege assignments affecting a user's permission to access a network host. The function DOMAIN_LEVEL Function in this package returns the level of each domain and can be used to order the ACL assignments by their precedence.

Example 1

For example, for SCOTT's permission to connect to www.us.oracle.com:

SELECT host, lower_port, upper_port, acl,
     DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SCOTT', 'connect'),
            1, 'GRANTED', 0, 'DENIED', null) privilege
     FROM dba_network_acls
    WHERE host IN
      (SELECT * FROM
         TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.oracle.com')))
   ORDER BY DBMS_NETWORK_ACL_UTLITITY.DOMAIN_LEVEL(host) desc, lower_port, 
                                               upper_port;


   HOST                 LOWER_PORT UPPER_PORT         ACL          PRIVILEGE
   -------------------- ---------- ---------- -------------------- ---------
   www.us.oracle.com            80         80 /sys/acls/www.xml    GRANTED
   www.us.oracle.com          3000       3999 /sys/acls/www.xml    GRANTED
   www.us.oracle.com                          /sys/acls/www.xml    GRANTED
   *.oracle.com                               /sys/acls/all.xml
   *                                          /sys/acls/all.xml

Example 2

For example, for SCOTT's permission to do domain name resolution for www.us.oracle.com:

SELECT host, lower_port, upper_port, acl,
     DECODE(
          DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'SCOTT', 'resolve'),
            1, 'GRANTED', 0, 'DENIED', null) privilege
     FROM dba_network_acls
    WHERE host IN
      (SELECT * FROM
         TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.oracle.com'))) and
      lower_port IS NULL AND upper_port IS NULL
   ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) desc;


   HOST                 LOWER_PORT UPPER_PORT         ACL          PRIVILEGE
   -------------------- ---------- ---------- -------------------- ---------
   www.us.oracle.com                          /sys/acls/www.xml    GRANTED
   *.oracle.com                               /sys/acls/all.xml
   *                                          /sys/acls/all.xml  
 

Note that the "resolve" privilege takes effect only in ACLs assigned without any port range (when lower_port and upper_port are NULL). For this reason, we do not include lower_port and upper_port columns in the query.

Summary of DBMS_NETWORK_ACL_UTILITY Subprograms

DOMAIN_LEVEL Function

This function returns the domain level of the given host name, domain, or subnet.

Syntax

DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL (
    host  IN VARCHAR2) 
  RETURN NUMBER;

Parameters

Return Values

The domain level of the given host, domain, or subnet.

Examples

SELECT DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL('www.us.oracle.com') FROM DUAL;

DOMAINS Function

For a given host, this function returns the domains whose ACL assigned will be used to determine if a user has the privilege to access the given host or not. When the IP address of the host is given, return the subnets instead.

Syntax

DBMS_NETWORK_ACL_UTILITY.DOMAINS (
    host  IN VARCHAR2) 
  RETURN DOMAIN_TABLE PIPELINED;

Parameters

Return Values

The domains or subnets for the given host.

Examples

SELECT * FROM TABLE (DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.oracle.com'));