you are a new oracle dba and you want to know how you can create ACL in a oracle 11G database. Below are some 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;
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 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;
HOST 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, the example
does not include lower_port and upper_port columns in the query.
