| 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' (case sensitive) |
|
| Dependencies |
| DBA_NETWORK_ACLS |
EQUALS_PATH |
| DBA_NETWORK_ACL_PRIVILEGES |
NET$_ACL |
| DBA_WALLET_ACLS |
PATH_VIEW |
| DBMS_NETWORK_ACL_UTILITY |
PLITBLM |
| DBMS_RESCONFIG |
RESOURCE_VIEW |
| DBMS_SYS_ERROR |
USER_NETWORK_ACL_PRIVILEGES |
| DBMS_XDB |
UTL_I18N |
| DBMS_XDBRESOURCE |
WALLET$_ACL |
| DBMS_XDBZ |
XDB$ACL |
| DBMS_XEVENT |
XDB$STRING_LIST_T |
| DUAL |
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 => 'mlib-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 => 'mlib-org-permissions.xml', host => '*.morganslibrary.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; |
| |
ASSIGN_WALLET_ACL (new 11.2.0.1)  |
| Assigns an access control list (ACL) to a wallet |
dbms_network_acl_admin.assign_wallet_acl(acl IN VARCHAR2, wallet_path IN VARCHAR2); |
BEGIN
dbms_network_acl_admin.create_acl('mlib-org-permissions.xml', 'Demo Wallet ACL',
'UWCLASS', TRUE, 'use-client-certificates');
dbms_network_acl_admin.add_privilege('mlib-org-permissions.xml', 'UWCLASS', TRUE,
'use-passwords');
dbms_network_acl_admin.assign_wallet_acl('mlib-org-permissions.xml', 'file:/oracle/wallets/test_wallet');
END;
/ |
| |
| 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', 'MORGANSLIBRARY', 'CONNECT')
FROM dual;
SELECT dbms_network_acl_admin.check_privilege_aclid(
'703A838DAF25441498620A98EC83C8F4', 'MORGANSLIBRARY', '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;
SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE ANY_PATH LIKE '/sys/acls/%.xml';
BEGIN
dbms_network_acl_admin.create_acl(acl => 'mlib-org-permissions.xml',
description => 'Network permissions for *.morganslibrary.org',
principal => 'UWCLASS', is_grant => TRUE, privilege => 'connect');
END;
/
SELECT aclid, shared, description
FROM xds_acl;
SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE ANY_PATH LIKE '/sys/acls/%.xml';
SELECT DECODE(
dbms_network_acl_admin.check_privilege('mlib-org-permissions.xml',
'MORGANSLIBRARY', 'resolve'), 1, 'GRANTED', 0, 'DENIED', NULL) PRIVILEGE
FROM DUAL;
SELECT DECODE(
dbms_network_acl_admin.check_privilege('mlib-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('mlib-org-permissions.xml',
'UWCLASS', NULL, 'connect');
END;
/ |
| |
| DROP_ACL |
| Drops an access control list (ACL) |
dbms_network_acl_admin.drop_acl(acl IN VARCHAR2); |
SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE ANY_PATH LIKE '/sys/acls/%.xml';
BEGIN
dbms_network_acl_admin.drop_acl('mlib-org-permissions.xml');
END;
/
SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE ANY_PATH LIKE '/sys/acls/%.xml'; |
| |
| 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('*.mlib.org', 80);
END;
/ |
| |
| UNASSIGN_WALLET_ACL (new 11.2.0.1) |
| Unassign the access control list (ACL) currently assigned to a wallet |
dbms_network_acl_admin.unassign_wallet_acl(
acl IN VARCHAR2 DEFAULT NULL,
wallet_path IN VARCHAR2 DEFAULT NULL); |
| exec dbms_network_acl_admin.unassign_wallet_acl('mlib-org-permissions.xml'); |
