CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_NETWORK_ACL_ADMIN
Version 11.1
 
General Information
Purpose Provides security for network related PL/SQL packages UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR
Source {$ORACLE_HOME}/rdbms/admin/dbmsnacl.sql
First Available 11.1

Constants

Name

Data Type

Value

IP_ADDR_MASK VARCHAR2(80) '([[:digit:]]+\.){3}[[:digit:]]+'
IP_SUBNET_MASK VARCHAR2(80) '([[:digit:]]+\.){0,3}\*'
HOSTNAME_MASK VARCHAR2(80) '[^\.\*]+(\.[^\.\*]+)*'
DOMAIN_MASK VARCHAR2(80) '\*(\.[^\.\*]+)*'
 
ace_already_exists_num

PLS_INTEGER

-24243
invalid_host_num PLS_INTEGER -24244
invalid_privilege_num PLS_INTEGER -24245
empty_acl_num PLS_INTEGER -24246
bad_argument_num PLS_INTEGER -29261
acl_not_found_num PLS_INTEGER -31001
invalid_acl_path_num PLS_INTEGER -46059
 
Privilege

VARCHAR2

'connect' or 'resolve'

Dependencies
DBMS_NETWORK_ACL_ADMIN_INT NET$_ACL
DBMS_NETWORK_ACL_UTILITY PATH_VIEW
DBMS_RESCONFIG PLITBLM
DBMS_SYS_ERROR RESOURCE_VIEW
DBMS_XDB USER_NETWORK_ACL_PRIVILEGES
DBMS_XDBUTIL_INT XDB$ACL
DUAL XDB$STRING_LIST_T
EQUALS_PATH XMLTYPE

Exceptions
Error Code Exception Name
-24243 ace_already_exists
-24246 empty_acl
-31001 acl_not_found
-46059 invalid_acl_path
-24244 invalid_host
-24245 invalid_privilege
-29261 bad_argument
Security Model GRANT execute ON dbms_network_acl_admin TO <schema_name>;
GRANT execute ON dbms_network_acl_admin TO uwclass;
 
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 => 'psoug-org-permissions.xml', principal => 'UWCLASS', is_grant => TRUE, privilege => 'connect');
 
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 => 'psoug-org-permissions.xml',
  host => '*.psoug.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;
 
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
 
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', 'PSOUG', 'CONNECT')
FROM dual;

SELECT dbms_network_acl_admin.check_privilege_aclid( '703A838DAF25441498620A98EC83C8F4', 'PSOUG', '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 / as sysdba

desc xds_acl

set pagesize 121
col description format a60

SELECT aclid, shared, description
FROM xds_acl;

BEGIN
  dbms_network_acl_admin.create_acl(acl => 'psoug-org-permissions.xml',
  description => 'Network permissions for *.psoug.org',
  principal => 'UWCLASS', is_grant => TRUE, privilege => 'connect');
END;
/

SELECT aclid, shared, description
FROM xds_acl;

SELECT DECODE(
  dbms_network_acl_admin.check_privilege('psoug-org-permissions.xml',
  'PSOUG', 'resolve'), 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;

SELECT DECODE(
  dbms_network_acl_admin.check_privilege('psoug-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('psoug-org-permissions.xml',
  'UWCLASS', NULL, 'connect');
END;
/
 
DROP_ACL
Drops an access control list (ACL) dbms_network_acl_admin.drop_acl(acl IN VARCHAR2);
BEGIN
  dbms_network_acl_admin.drop_acl('psoug-org-permissions.xml');
END;
/

SELECT aclid, shared, description, inheritance_type
FROM xds_acl;
 
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('*.psoug.org', 80);
END;
/
 
Related Topics
DBMS_NETWORK_ACL_UTILITY
DBMS_XDBUTIL_INT
UTL_HTTP
UTL_INADDR
UTL_MAIL
UTL_SMTP
UTL_TCP
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [55 users online]    © 2010 psoug.org