1 package dbms_network_acl_admin is
2
3 /*
4 * DBMS_NETWORK_ACL_ADMIN is the PL/SQL package that provides the interface
5 * to administer the network ACL. The EXECUTE privilege on the package will
6 * be granted only to the DBA role by default.
7 */
8
9 ----------------
10 -- Exceptions --
11 ----------------
12 ace_already_exists EXCEPTION;
13 empty_acl EXCEPTION;
14 acl_not_found EXCEPTION;
15 acl_already_exists EXCEPTION;
16 invalid_acl_path EXCEPTION;
17 invalid_host EXCEPTION;
18 invalid_privilege EXCEPTION;
19 invalid_wallet_path EXCEPTION;
20 bad_argument EXCEPTION;
21 unresolved_principal EXCEPTION;
22 privilege_not_granted EXCEPTION;
23 PRAGMA EXCEPTION_INIT(ace_already_exists, -24243);
24 PRAGMA EXCEPTION_INIT(empty_acl, -24246);
25 PRAGMA EXCEPTION_INIT(acl_not_found, -46114);
26 PRAGMA EXCEPTION_INIT(acl_already_exists, -46212);
27 PRAGMA EXCEPTION_INIT(invalid_acl_path, -46059);
28 PRAGMA EXCEPTION_INIT(invalid_host, -24244);
29 PRAGMA EXCEPTION_INIT(invalid_privilege, -24245);
30 PRAGMA EXCEPTION_INIT(invalid_wallet_path, -29248);
31 PRAGMA EXCEPTION_INIT(bad_argument, -29261);
32 PRAGMA EXCEPTION_INIT(unresolved_principal, -46238);
33 PRAGMA EXCEPTION_INIT(privilege_not_granted, -01927);
34 ace_already_exists_num constant PLS_INTEGER := -24243;
35 empty_acl_num constant PLS_INTEGER := -24246;
36 acl_not_found_num constant PLS_INTEGER := -46114;
37 acl_already_exists_num constant PLS_INTEGER := -46212;
38 invalid_acl_path_num constant PLS_INTEGER := -46059;
39 invalid_host_num constant PLS_INTEGER := -24244;
40 invalid_privilege_num constant PLS_INTEGER := -24245;
41 invalid_wallet_path_num constant PLS_INTEGER := -29248;
42 bad_argument_num constant PLS_INTEGER := -29261;
43 unresolved_principal_num constant PLS_INTEGER := -46238;
44 privilege_not_granted_num constant PLS_INTEGER := -01927;
45
46 -- IP address mask: xxx.xxx.xxx.xxx
47 IP_ADDR_MASK constant VARCHAR2(80) := '([[:digit:]]+\.){3}[[:digit:]]+';
48 -- IP submet mask: xxx.xxx...*
49 IP_SUBNET_MASK constant VARCHAR2(80) := '([[:digit:]]+\.){0,3}\*';
50 -- Hostname mask: ???.???.???...???
51 HOSTNAME_MASK constant VARCHAR2(80) := '[^\.\:\/\*]+(\.[^\.\:\/\*]+)*';
52 -- Hostname mask: *.???.???...???
53 DOMAIN_MASK constant VARCHAR2(80) := '\*(\.[^\.\:\/\*]+)*';
54
55 /* Table of ACL IDs */
56 type aclid_table is table of number index by binary_integer;
57
58 /*--------------- API for ACL and privilege administration ---------------*/
59
60 /*
61 * Creates an access control list (ACL) with an initial privilege setting.
62 * An ACL must have at least one privilege setting. The ACL has no access
63 * control effect unless it is assigned to a network host.
64 *
65 * PARAMETERS
66 * acl the name of the ACL. Relative path will be relative to
67 * "/sys/acls".
68 * description the description attribute in the ACL
69 * principal the principal (database user or role) whom the privilege
70 * is granted to or denied from
71 * is_grant is the privilege is granted or denied
72 * privilege the network privilege to be granted or denied
73 * start_date the start date of the access control entry (ACE). When
74 * specified, the ACE will be valid only on and after the
75 * specified date.
76 * end_date the end date of the access control entry (ACE). When
77 * specified, the ACE will expire after the specified date.
78 * The end_date must be greater than or equal to the
79 * start_date.
80 * RETURN
81 * None
82 * EXCEPTIONS
83 *
84 * NOTES
85 * To remove the ACL, use DROP_ACL. To assign the ACL to a network host,
86 * use ASSIGN_ACL.
87 */
88 procedure create_acl(acl in varchar2,
89 description in varchar2,
90 principal in varchar2,
91 is_grant in boolean,
92 privilege in varchar2,
93 start_date in timestamp with time zone default null,
94 end_date in timestamp with time zone default null);
95
96 /*
97 * Adds a privilege to grant or deny the network access to the user. The
98 * access control entry (ACE) will be created if it does not exist.
99 *
100 * PARAMETERS
101 * acl the name of the ACL. Relative path will be relative to
102 * "/sys/acls".
103 * principal the principal (database user or role) whom the privilege
104 * is granted to or denied from
105 * is_grant is the privilege is granted or denied
106 * privilege the network privilege to be granted or denied
107 * position the position of the ACE. If a non-null value is given,
108 * the privilege will be added in a new ACE at the given
109 * position and there should not be another ACE for the
110 * principal with the same is_grant (grant or deny). If a null
111 * value is given, the privilege will be added to the ACE
112 * matching the principal and the is_grant if one exists, or
113 * to the end of the ACL if the matching ACE does not exist.
114 * start_date the start date of the access control entry (ACE). When
115 * specified, the ACE will be valid only on and after the
116 * specified date. The start_date will be ignored if the
117 * privilege is added to an existing ACE.
118 * end_date the end date of the access control entry (ACE). When
119 * specified, the ACE will expire after the specified date.
120 * The end_date must be greater than or equal to the
121 * start_date. The end_date will be ignored if the
122 * privilege is added to an existing ACE.
123 * RETURN
124 * None
125 * EXCEPTIONS
126 *
127 * NOTES
128 * To remove the privilege, use DELETE_privilege.
129 */
130 procedure add_privilege(acl in varchar2,
131 principal in varchar2,
132 is_grant in boolean,
133 privilege in varchar2,
134 position in pls_integer default null,
135 start_date in timestamp with time zone default null,
136 end_date in timestamp with time zone default null);
137
138 /*
139 * Delete a privilege.
140 *
141 * PARAMETERS
142 * acl the name of the ACL. Relative path will be relative to
143 * "/sys/acls".
144 * principal the principal (database user or role) for whom the
145 * privileges will be deleted
146 * is_grant is the privilege is granted or denied. If a null
147 * value is given, the deletion is applicable to both
148 * granted or denied privileges.
149 * privilege the privilege to be deleted. If a null value is given,
150 * the deletion is applicable to all privileges.
151 * RETURN
152 * None
153 * EXCEPTIONS
154 *
155 * NOTES
156 * Any ACE that does not contain any privilege after the deletion will
157 * be removed also.
158 */
159 procedure delete_privilege(acl in varchar2,
160 principal in varchar2,
161 is_grant in boolean default null,
162 privilege in varchar2 default null);
163
164 /*
165 * Drops an access control list (ACL).
166 *
167 * PARAMETERS
168 * acl the name of the ACL. Relative path will be relative to
169 * "/sys/acls".
170 * RETURN
171 * None
172 * EXCEPTIONS
173 *
174 */
175 procedure drop_acl(acl in varchar2);
176
177 /*--------- API for ACL assignment to network hosts and wallets ---------*/
178
179 /*
180 * Assigns an access control list (ACL) to a network host, and optionally
181 * specific to a TCP port range.
182 *
183 * PARAMETERS
184 * acl the name of the ACL. Relative path will be relative to
185 * "/sys/acls".
186 * host the host to which the ACL will be assigned. The host can be
187 * the name or the IP address of the host. A wildcard can be
188 * used to specify a domain or a IP subnet. The host or
189 * domain name is case-insensitive.
190 * lower_port the lower bound of a TCP port range if not NULL.
191 * upper_port the upper bound of a TCP port range. If NULL,
192 * lower_port is assumed.
193 * RETURN
194 * None
195 * EXCEPTIONS
196 *
197 * NOTES
198 * 1. The ACL assigned to a domain takes a lower precedence than the other
199 * ACLs assigned sub-domains, which take a lower precedence than the ACLs
200 * assigned to the individual hosts. So for a given host say
201 * "www.us.mycompany.com", the following domains are listed in decreasing
202 * precedences:
203 * - www.us.mycompany.com
204 * - *.us.mycompany.com
205 * - *.mycompany.com
206 * - *.com
207 * - *
208 * In the same way, the ACL assigned to an subnet takes a lower
209 * precedence than the other ACLs assigned smaller subnets, which take a
210 * lower precedence than the ACLs assigned to the individual IP addresses.
211 * So for a given IP address say "192.168.0.100", the following subnets
212 * are listed in decreasing precedences:
213 * - 192.168.0.100
214 * - 192.168.0.*
215 * - 192.168.*
216 * - 192.*
217 * - *
218 * 2. The port range is applicable only to the "connect" privilege
219 * assignments in the ACL. The "resolve" privilege assignments in an ACL
220 * have effects only when the ACL is assigned to a host without a port
221 * range.
222 * 3. For the "connect" privilege assignments, an ACL assigned to the host
223 * without a port range takes a lower precedence than other ACLs assigned
224 * to the same host with a port range.
225 * 4. When specifying a TCP port range, both lower_port and upper_port must
226 * not be NULL and upper_port must be greater than or equal to lower_port.
227 * The port range must not overlap with any other port ranges for the same
228 * host assigned already.
229 * 5. To remove the assignment, use UNASSIGN_ACL.
230 */
231 procedure assign_acl(acl in varchar2,
232 host in varchar2,
233 lower_port in pls_integer default null,
234 upper_port in pls_integer default null);
235
236 /*
237 * Unassign the access control list (ACL) currently assigned to a network
238 * host.
239 *
240 * PARAMETERS
241 * acl the name of the ACL. Relative path will be relative to
242 * "/sys/acls". If acl is NULL, any ACL assigned to the host
243 * will be unassigned.
244 * host the host remove the ACL assignment from. The host can be
245 * the name or the IP address of the host. A wildcard can be
246 * used to specify a domain or a IP subnet. The host or
247 * domain name is case-insensitive. If host is null, the ACL
248 * will be unassigned from any host. If both host and acl are
249 * NULL, all ACLs assigned to any hosts will be unassigned.
250 * lower_port if not NULL, the lower bound of a TCP port range for the
251 * host.
252 * upper_port the upper bound of a TCP port range. If NULL,
253 * lower_port is assumed.
254 * RETURN
255 * None
256 * EXCEPTIONS
257 *
258 */
259 procedure unassign_acl(acl in varchar2 default null,
260 host in varchar2 default null,
261 lower_port in pls_integer default null,
262 upper_port in pls_integer default null);
263
264 /*
265 * Assigns an access control list (ACL) to a wallet.
266 *
267 * PARAMETERS
268 * acl the name of the ACL. Relative path will be relative to
269 * "/sys/acls".
270 * wallet_path the directory path of the wallet to which the ACL will be
271 * assigned. The path is case-sensitive and of the format
272 * "file:<directory-path>".
273 * RETURN
274 * None
275 * EXCEPTIONS
276 *
277 * NOTES
278 * To remove the assignment, use UNASSIGN_WALLET_ACL.
279 */
280 procedure assign_wallet_acl(acl in varchar2,
281 wallet_path in varchar2);
282
283 /*
284 * Unassign the access control list (ACL) currently assigned to a wallet.
285 *
286 * PARAMETERS
287 * acl the name of the ACL. Relative path will be relative to
288 * "/sys/acls". If acl is NULL, any ACL assigned to the wallet
289 * will be unassigned.
290 * wallet_path the directory path of the wallet to which the ACL will be
291 * assigned. The path is case-sensitive and of the format
292 * "file:<directory-path>". If wallet_path is null, the ACL
293 * will be unassigned from any wallet.
294 * RETURN
295 * None
296 * EXCEPTIONS
297 *
298 */
299 procedure unassign_wallet_acl(acl in varchar2 default null,
300 wallet_path in varchar2 default null);
301
302 /*
303 * Check if a privilege is granted to or denied from the user in an
304 * access control list.
305 *
306 * PARAMETERS
307 * acl the name of the ACL. Relative path will be relative to
308 * "/sys/acls".
309 * aclid the object ID of the ACL.
310 * user the user to check against. If the user is NULL, the invoker
311 * is assumed. The username is case-sensitive as in the
312 * USERNAME column of the ALL_USERS view.
313 * privilege the network privilege to check
314 * RETURN
315 * 1 when the privilege is granted; 0 when the privilege is denied;
316 * NULL when the privilege is neither granted or denied.
317 * EXCEPTIONS
318 *
319 */
320 function check_privilege(acl in varchar2,
321 user in varchar2,
322 privilege in varchar2) return number;
323 function check_privilege_aclid(aclid in raw,
327 /*
324 user in varchar2,
325 privilege in varchar2) return number;
326
328 * Append an access control entry (ACE) to the access control list (ACL)
329 * of a network host. The ACL controls access to the given host from the
330 * database and the ACE specifies the privileges granted to or denied
331 * from the specified principal.
332 *
333 * PARAMETERS
334 * host the host. It can be the name or the IP address of the host.
335 * A wildcard can be used to specify a domain or a IP subnet.
336 * The host or domain name is case-insensitive.
337 * lower_port the lower bound of an optional TCP port range.
338 * upper_port the upper bound of an optional TCP port range. If NULL,
339 * lower_port is assumed.
340 * ace the ACE.
341 * RETURN
342 * None
343 * EXCEPTIONS
344 *
345 * NOTES
346 * - Duplicate privileges in the matching ACE in the host ACL will be
347 * skipped.
348 * - To remove the ACE, use REMOVE_HOST_ACE.
349 * - A host's ACL takes precedence over its domains' ACLs. For a given host
350 * say "www.us.mycompany.com", the following domains are listed in
351 * decreasing precedence:
352 * - www.us.mycompany.com
353 * - *.us.mycompany.com
354 * - *.mycompany.com
355 * - *.com
356 * - *
357 * An IP address' ACL takes precedence over its subnets' ACLs. For a given
358 * IP address say "192.168.0.100", the following subnets are listed in
359 * decreasing precedence:
360 * - 192.168.0.100
361 * - 192.168.0.*
362 * - 192.168.*
363 * - 192.*
364 * - *
365 * - An ACE with a "resolve" privilege can be appended only to a host's ACL
366 * without a port range.
367 * - When ACEs with "connect" privileges are appended to a host's ACLs
368 * with and without a port range, the one appended to the host with a
369 * port range takes precedence.
370 * - When specifying a TCP port range of a host, it cannot overlap with other
371 * existing port ranges of the host.
372 * - If the ACL is shared with another host or wallet, a copy of the ACL
373 * will be made before the ACL is modified.
374 */
375 procedure append_host_ace(host in varchar2,
376 lower_port in pls_integer default null,
377 upper_port in pls_integer default null,
378 ace in xs$ace_type);
379
380 /*
381 * Append access control entries (ACE) of an access control list (ACL) to
382 * the ACL of a network host.
383 *
384 * PARAMETERS
385 * host the host. It can be the name or the IP address of the host.
389 * upper_port the upper bound of an optional TCP port range. If NULL,
386 * A wildcard can be used to specify a domain or a IP subnet.
387 * The host or domain name is case-insensitive.
388 * lower_port the lower bound of an optional TCP port range.
390 * lower_port is assumed.
391 * acl the ACL to append from.
392 * RETURN
393 * None
394 * EXCEPTIONS
395 *
396 * NOTES
397 * - See APPEND_HOST_ACE.
398 */
399 procedure append_host_acl(host in varchar2,
400 lower_port in pls_integer default null,
401 upper_port in pls_integer default null,
402 acl in varchar2);
403
404 /*
405 * Remove privileges from access control entries (ACE) in the access control
406 * list (ACL) of a network host matching the given ACE.
407 *
408 * PARAMETERS
409 * host the host. It can be the name or the IP address of the
410 * host. A wildcard can be used to specify a domain or a
411 * IP subnet. The host or domain name is case-insensitive.
412 * lower_port the lower bound of an optional TCP port range.
413 * upper_port the upper bound of an optional TCP port range. If NULL,
414 * lower_port is assumed.
415 * ace the ACE.
416 * remove_empty_acl remove empty ACL also?
417 * RETURN
418 * None
419 * EXCEPTIONS
420 *
421 * NOTES
422 * - If the ACL is shared with another host or wallet, a copy of the ACL
423 * will be made before the ACL is modified.
424 */
425 procedure remove_host_ace(host in varchar2,
426 lower_port in pls_integer default null,
427 upper_port in pls_integer default null,
428 ace in xs$ace_type,
429 remove_empty_acl in boolean default false);
430
431 /*
432 * Append an access control entry (ACE) to the access control list (ACL)
433 * of a wallet. The ACL controls access to the given wallet from the
434 * database and the ACE specifies the privileges granted to or denied
435 * from the specified principal.
436 *
437 * PARAMETERS
438 * wallet_path the directory path of the wallet. The path is case-sensitive
439 * of the format "file:<directory-path>".
440 * ace the ACE.
441 * RETURN
442 * None
443 * EXCEPTIONS
444 *
445 * NOTES
446 * - Duplicate privileges in the matching ACE in the wallet ACL will be
447 * skipped.
448 * - To remove the ACE, use REMOVE_WALLET_ACE.
449 * - If the ACL is shared with another host or wallet, a copy of the ACL
450 * will be made before the ACL is modified.
451 */
452 procedure append_wallet_ace(wallet_path in varchar2,
453 ace in xs$ace_type);
454
455 /*
456 * Append access control entries (ACE) of an access control list (ACL) to the
457 * ACL of a wallet.
458 *
459 * PARAMETERS
460 * wallet_path the directory path of the wallet. The path is case-sensitive
461 * of the format "file:<directory-path>".
462 * acl the ACL to append from.
463 * RETURN
464 * None
465 * EXCEPTIONS
466 *
467 * NOTES
468 * - See APPEND_WALLET_ACE.
469 */
470 procedure append_wallet_acl(wallet_path in varchar2,
471 acl in varchar2);
472
473 /*
474 * Remove privileges from access control entries (ACE) in the access control
475 * list (ACL) of a wallet matching the given ACE.
476 *
477 * PARAMETERS
478 * wallet_path the directory path of the wallet. The path is
479 * case-sensitive of the format "file:<directory-path>".
480 * ace the ACE.
481 * remove_empty_acl remove empty ACL also?
482 * RETURN
483 * None
484 * EXCEPTIONS
485 *
486 * NOTES
487 * - If the ACL is shared with another host or wallet, a copy of the ACL
488 * will be made before the ACL is modified.
489 */
490 procedure remove_wallet_ace(wallet_path in varchar2,
491 ace in xs$ace_type,
492 remove_empty_acl in boolean default false);
493
494 /*
495 * Set the access control list (ACL) of a network host which controls access
496 * to the host from the database.
497 *
498 * PARAMETERS
499 * host the host. It can be the name or the IP address of the host.
500 * A wildcard can be used to specify a domain or a IP subnet.
501 * The host or domain name is case-insensitive.
502 * lower_port the lower bound of an optional TCP port range.
503 * upper_port the upper bound of an optional TCP port range. If NULL,
504 * lower_port is assumed.
505 * acl the ACL. Null to unset the host's ACL.
506 * RETURN
507 * None
508 * EXCEPTIONS
509 *
510 * NOTES
511 * - A host's ACL is created and set on-demand when an access control entry
512 * (ACE) is appended to the host's ACL. Users are discouraged from setting
513 * a host's ACL manually.
514 */
515 procedure set_host_acl(host in varchar2,
516 lower_port in pls_integer default null,
517 upper_port in pls_integer default null,
518 acl in varchar2);
519
520 /*
521 * Set the access control list (ACL) of a wallet which controls access to
525 * wallet_path the directory path of the wallet. The path is case-sensitive
522 * the wallet from the database.
523 *
524 * PARAMETERS
526 * and of the format "file:<directory-path>".
527 * acl the ACL. Null to unset the wallet's ACL.
528 * RETURN
529 * None
530 * EXCEPTIONS
531 *
532 * NOTES
533 * - A wallet's ACL is created and set on-demand when an access control
534 * entry (ACE) is appended to the wallet's ACL. Users are discouraged from
535 * setting a wallet's ACL manually.
536 */
537 procedure set_wallet_acl(wallet_path in varchar2,
538 acl in varchar2);
539
540 /* Internal functions */
541 function get_host_aclids(host in varchar2, port in number) return aclid_table
542 result_cache;
543 function get_wallet_aclid(wallet_path in varchar2) return number
544 result_cache;
545 procedure instance_export_action(obj_name in varchar2,
546 obj_schema in varchar2,
547 obj_type in number,
548 tgt_version in varchar2,
549 action out varchar2,
550 alt_name out varchar2,
551 where_clause out varchar2);
552 procedure instance_callout_imp(obj_name in varchar2,
553 obj_schema in varchar2,
554 obj_type in number,
555 prepost in pls_integer,
556 action out varchar2,
557 alt_name out varchar2);
558
559 end;