2021-10-03

ACL #

home/ Professional
ACL זה משמש כרשימת השרתים שניתן להתחבר אליהם ישירות מהDB.
במדה ומעוניינים לגשת לאיזשהו שרות משרת אחר, יש להכניס את השרת ההוא לרשימת הACL.

DBMS_NETWORK_ACL_ADMIN #

The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).

Examples #

Example1 #

Grant the connect and resolve privileges for host www.us.oracle.com to SCOTT.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'WWW ACL',
                                    principal   => 'SCOTT',
                                    is_grant    => true,
                                    privilege   => 'connect');
 
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'SCOTT',
                                       is_grant  => true,
                                       privilege => 'resolve');
 
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => 'www.us.oracle.com');
END;
/
COMMIT;

Example 2 #

Grant the resolve privilege for www.us.oracle.com to ADAMS. Since an ACL for www.us.oracle.com exists already, just add the privilege for ADAMS.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'ADAMS',
                                       is_grant  => true,
                                       privilege => 'resolve');
END;
/
COMMIT;

Example 3 #

Assign the ACL www.xml to www-proxy.us.oracle.com so that SCOTT and ADAMS can access www-proxy.us.oracle.com also.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => 'www-proxy.us.oracle.com');
END;
/
COMMIT;

Example 4

Unassign the ACL from www.us.oracle.com so that no access to www.us.oracle.com is allowed.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.us.oracle.com');
END;
/
COMMIT;

Example 5 #

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

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_UTILITY.DOMAIN_LEVEL(host) desc, lower_port, 
                                               upper_port;

Example 6 #

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

SELECT host, 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;

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, the example does not include lower_port and upper_port columns in the query.

#acl #oracle #db