| 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;
/ |
