Oracle acl что это
Although deprecated, the old functionality is retained for backwards compatibility, but it should be avoided as it is inferior to the new functionality.
Checking Privileges
In addition to the ACL and ACE views, privileges can be checked using the CHECK_PRIVILEGE and CHECK_PRIVILEGE_ACLID functions of the DBMS_NETWORK_ACL_ADMIN package. The are deprecated, but are still useful.
The DBMS_NETWORK_ACL_UTILITY package contains functions to help determine possible matching domains. The DOMAINS table function returns a collection of all possible references that may affect the specified host, domain, IP address or subnet, in order of precedence.
The DOMAIN_LEVEL function returns the level of the specified host, domain, IP address or subnet.
These functions may be useful for when querying the ACL views for possible matches to a specific host, domain, IP address or subnet. The following examples use two different methods to determine if the user TEST1 has access to the "http" and "resolve" privileges.
108.7 Summary of DBMS_NETWORK_ACL_ADMIN Subprograms
This table lists and briefly describes the DBMS_NETWORK_ACL_ADMIN package subprograms.
[DEPRECATED] Adds a privilege to grant or deny the network access to the user in an access control list (ACL)
Appends an access control entry (ACE) to the access control list (ACL) of a network host.
Appends access control entries (ACE) of an access control list (ACL) to the ACL of a network host
Appends an access control entry (ACE) to the access control list (ACL) of a wallet
Appends access control entries (ACE) of an access control list (ACL) to the ACL of a wallet
[DEPRECATED] Assigns an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
[DEPRECATED] Assigns an access control list (ACL) to a wallet
[DEPRECATED] Checks if a privilege is granted or denied the user in an access control list (ACL)
[DEPRECATED] Checks if a privilege is granted to or denied from the user in an ACL by specifying the object ID of the access control list
[DEPRECATED] Creates an access control list (ACL) with an initial privilege setting
[DEPRECATED] Deletes a privilege in an access control list (ACL)
[DEPRECATED] Drops an access control list (ACL)
Removes privileges from access control entries (ACE) in the access control list (ACL) of a network host matching the given ACE
Removes privileges from access control entries (ACE) in the access control list (ACL) of a wallet matching the given ACE
Sets the access control list (ACL) of a network host which controls access to the host from the database
Sets the access control list (ACL) of a wallet which controls access to the wallet from the database
[DEPRECATED] Unassigns the access control list (ACL) currently assigned to a network host
[DEPRECATED] Unassigns the access control list (ACL) currently assigned to a wallet
108.7.1 ADD_PRIVILEGE Procedure
This procedure adds a privilege to grant or deny the network access to the user. The access control entry (ACE) is created if it does not exist.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Name of the ACL. Relative path will be relative to "/sys/acls"
Principal (database user or role) to whom the privilege is granted or denied. Case sensitive.
Privilege is granted or denied.
Network privilege to be granted or denied
Position (1-based) of the ACE. If a non- NULL value is given, the privilege will be added in a new ACE at the given position and there should not be another ACE for the principal with the same is_grant (grant or deny). If a NULL value is given, the privilege will be added to the ACE matching the principal and the is_grant if one exists, or to the end of the ACL if the matching ACE does not exist.
Start date of the access control entry (ACE). When specified, the ACE will be valid only on and after the specified date. The start_date will be ignored if the privilege is added to an existing ACE.
End date of the access control entry (ACE). When specified, the ACE expires after the specified date. The end_date must be greater than or equal to the start_date . The end_date will be ignored if the privilege is added to an existing ACE.
To remove the permission, use the DELETE_PRIVILEGE Procedure.
108.7.2 APPEND_HOST_ACE Procedure
This procedure appends an access control entry (ACE) to the access control list (ACL) of a network host. The ACL controls access to the given host from the database and the ACE specifies the privileges granted to or denied from the specified principal.
The host, which can be the name or the IP address of the host. You can use a wildcard to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of an optional TCP port range
Upper bound of an optional TCP port range. If NULL , lower_port is assumed.
Duplicate privileges in the matching ACE in the host ACL will be skipped.
An IP address' ACL takes precedence over its subnets' ACLs. For a given IP address, say 192.168.0.100 , the following subnets are listed in decreasing precedence:
An ACE with a "resolve" privilege can be appended only to a host's ACL without a port range.
When ACEs with "connect" privileges are appended to a host's ACLs with and without a port range, the one appended to the host with a port range takes precedence.
When specifying a TCP port range of a host, it cannot overlap with other existing port ranges of the host.
If the ACL is shared with another host or wallet, a copy of the ACL will be made before the ACL is modified.
108.7.3 APPEND_HOST_ACL Procedure
This procedure appends access control entries (ACE) of an access control list (ACL) to the ACL of a network host.
The host, which can be the name or the IP address of the host. You can use a wildcard to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of an optional TCP port range
Upper bound of an optional TCP port range. If NULL , lower_port is assumed.
The ACL from which to append
Duplicate privileges in the matching ACE in the host ACL will be skipped.
An IP address' ACL takes precedence over its subnets' ACLs. For a given IP address, say 192.168.0.100 , the following subnets are listed in decreasing precedence:
An ACE with a "resolve" privilege can be appended only to a host's ACL without a port range.
When ACEs with "connect" privileges are appended to a host's ACLs with and without a port range, the one appended to the host with a port range takes precedence.
When specifying a TCP port range of a host, it cannot overlap with other existing port ranges of the host.- If the ACL is shared with another host or wallet, a copy of the ACL will be made before the ACL is modified.
108.7.4 APPEND_WALLET_ACE Procedure
This procedure appends an access control entry (ACE) to the access control list (ACL) of a wallet. The ACL controls access to the given wallet from the database and the ACE specifies the privileges granted to or denied from the specified principal.
Directory path of the wallet. The path is case-sensitive of the format file: directory-path .
Duplicate privileges in the matching ACE in the host ACL will be skipped.
If the ACL is shared with another host or wallet, a copy of the ACL is made before the ACL is modified.
108.7.5 APPEND_WALLET_ACL Procedure
This procedure appends access control entries (ACE) of an access control list (ACL) to the ACL of a wallet.
Directory path of the wallet. The path is case-sensitive of the format file: directory-path .
The ACL from which to append
Duplicate privileges in the matching ACE in the host ACL will be skipped.
To remove the ACE, use REMOVE_WALLET_ACE.
If the ACL is shared with another host or wallet, a copy of the ACL is made before the ACL is modified.
108.7.6 ASSIGN_ACL Procedure
This procedure assigns an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Name of the ACL. Relative path will be relative to " /sys/acls ".
Host to which the ACL is to be assigned. The host can be the name or the IP address of the host. A wildcard can be used to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of a TCP port range if not NULL
Upper bound of a TCP port range. If NULL , lower_port is assumed.
Only one ACL can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range. When you assign a new access control list to a network target, Oracle Database unassigns the previous access control list that was assigned to the same target. However, Oracle Database does not drop the access control list. You can drop the access control list by using the DROP_ACL Procedure. To remove an access control list assignment, use the UNASSIGN_ACL Procedure.
The ACL assigned to a domain takes a lower precedence than the other ACLs assigned sub-domains, which take a lower precedence than the ACLs assigned to the individual hosts. So for a given host, for example, "www.us.example.com", the following domains are listed in decreasing precedences:
In the same way, the ACL assigned to an subnet takes a lower precedence than the other ACLs assigned smaller subnets, which take a lower precedence than the ACLs assigned to the individual IP addresses. So for a given IP address, for example, "192.168.0.100", the following subnets are listed in decreasing precedences:
The port range is applicable only to the "connect" privilege assignments in the ACL. The "resolve" privilege assignments in an ACL have effects only when the ACL is assigned to a host without a port range.
For the "connect" privilege assignments, an ACL assigned to the host without a port range takes a lower precedence than other ACLs assigned to the same host with a port range.
When specifying a TCP port range, both lower_port and upper_port must not be NULL and upper_port must be greater than or equal to lower_port . The port range must not overlap with any other port ranges for the same host assigned already.
To remove the assignment, use UNASSIGN_ACL Procedure.
108.7.7 ASSIGN_WALLET_ACL Procedure
This procedure assigns an access control list (ACL) to a wallet.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Name of the ACL. Relative path will be relative to "/sys/acls "
Directory path of the wallet to which the ACL is to be assigned. The path is case-sensitive and of the format file: directory-path .
To remove the assignment, use the UNASSIGN_WALLET_ACL Procedure.
108.7.8 CHECK_PRIVILEGE Function
This function checks if a privilege is granted or denied the user in an ACL.
This procedure is deprecated in Oracle Database 12 c . The procedure remains available in the package only for reasons of backward compatibility.
Name of the ACL. Relative path will be relative to "/sys/acls".
User to check against. If the user is NULL , the invoker is assumed. The username is case-sensitive as in the USERNAME column of the ALL_USERS view.
Network privilege to check
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
108.7.9 CHECK_PRIVILEGE_ACLID Function
This function checks if a privilege is granted to or denied from the user in an ACL by specifying the object ID of the access control list.
This procedure is deprecated in Oracle Database 12 c . The procedure remains available in the package only for reasons of backward compatibility.
Object ID of the ACL
User to check against. If the user is NULL , the invoker is assumed. The username is case-sensitive as in the USERNAME column of the ALL_USERS view.
Network privilege to check
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
108.7.10 CREATE_ACL Procedure
This deprecated procedure creates an access control list (ACL) with an initial privilege setting. An ACL must have at least one privilege setting. The ACL has no access control effect unless it is assigned to the network target.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Name of the ACL. Relative path will be relative to "/sys/acls".
Description attribute in the ACL
Principal (database user or role) to whom the privilege is granted or denied. Case sensitive.
Privilege is granted or not (denied)
Start date of the access control entry (ACE). When specified, the ACE is valid only on and after the specified date.
End date of the access control entry (ACE). When specified, the ACE expires after the specified date. The end_date must be greater than or equal to the start_date .
The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).
The chapter contains the following topics:
For more information, see "Managing Fine-grained Access to External Network Services" in Oracle Database Security Guide
Assign an ACL to a Network
Access control lists are assigned to networks using the ASSIGN_ACL procedure, whose parameters are listed below:
- acl - The name of the access control list XML file.
- host - The hostname, domain, IP address or subnet to be assigned. Hostnames are case sensitive, and wildcards are allowed for IP addresses and domains.
- lower_port - Defaults to NULL. Specifies the lower port range for the 'connect' privilege.
- upper_port - Defaults to NULL. If the lower_port is specified, and the upper_port is NULL, it is assumed the upper_port matches the lower_port.
The code below shows the ACL created previously being assigned to a specific IP address and a subnet.
Only one ACL can be assigned to a specific host and port-range combination. Assigning a new ACL to a specific host and port-range results in the deletion of the previous assignment. You must take care when making a new assignment that you are not opening ports that were closed by a previous ACL assignment, or you could be opening yourself to attack. When wildcard usage causes overlapping assignments, the most specific assignment will take precedence, so an ACL assigned to 192.168.2.3:80 takes precedence over once assigned to 192.168.2.* etc.
The UNASSIGN_ACL procedure allows you to manually drop ACL assignments. It uses the same parameter list as the ASSIGN_ACL procedure, with any NULL parameters acting as wildcards.
115.1 DBMS_NETWORK_ACL_ADMIN Overview
The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network access control lists (ACL).
115.2 DBMS_NETWORK_ACL_ADMIN Deprecated Subprograms
Oracle recommends that you do not use deprecated subprograms in new applications. Support for deprecated features is for backward compatibility only
The following subprograms are deprecated with release Oracle Database 12 c :
Open ACE
From a security standpoint, it's not a good idea to allow complete network access from the database, but for testing features I sometimes find it useful to create an open ACE for a user.
115.4 DBMS_NETWORK_ACL_ADMIN Constants
The DBMS_NETWORK_ACL_ADMIN package defines constants to use specifying parameter values.
These are shown in the following table.
Table 115-1 DBMS_NETWORK_ACL_ADMIN Constants
IP address mask: xxx.xxx.xxx.xxx
IP subnet mask: xxx.xxx. *
Parameter Definitions
The parameters used in the procedures and functions above should be self explanatory, but we will cover them briefly here in case they are not obvious to you.
- host : Any valid host name or IP address. Wildcards are allowed.
- lower_port : Specific port number, or lower part of a range of ports.
- upper_port : Upper part of a range of ports. If NULL, it defaults to the lower_port value.
- ace : The access control entry, defined using the XS$ACE_TYPE type.
The XS$ACE_TYPE type has the following definition.
That looks a little complicated, but it most of the time you will only be using something like the following.
- privilege_list : The list of privileges available to the ACE.
- principal_name : The database user the ACE applies to.
- principal_type : You will always use XS_ACL.PTYPE_DB for these network ACEs as they apply to users and roles.
The privilege_list specifies one or more privileges in a comma separated list. The available privileges are shown below.
The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).
The chapter contains the following topics:
For more information, see "Managing Fine-grained Access to External Network Services" in Oracle Database Security Guide
Create an Access Control List (ACL)
Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. The CREATE_ACL procedure uses the following parameters to create a new ACL:
The following code creates two test users to act as principals, then creates a new ACL.
Once created, the ACL is visible in the "http://host:port/sys/acls/" directory.
Additional users or roles are added to the ACL using the ADD_PRIVILEGE procedure. Its parameter list is similar to the CREATE_ACL procedure, with the omission of the DESCRIPTION parameter and the addition of a POSITION parameter, which sets the order of precedence.
Each principal is defined as a separate access control element (ACE), within the ACL. When multiple principles are defined, they are evaluated in order from top to bottom, with the last relevant reference used to define the privilege. This means a role that denies access to a resource can be granted to a user, but if the user is defined as a principal further down the file, that definition will override the role definition for that user. Use the POSITION parameter to ensure privileges are evaluated in order.
Privileges are removed using the DELETE_PRIVILEGE procedure. If the IS_GRANT or PRIVILEGE parameters are NULL, all grants or privileges for the ACL and principal are removed.
ACLs are deleted using the DROP_ACL procedure.
Other Security Considerations
Oracle 12c has added a new level of granularity to the security of ACLs/ACEs, so the following comments are not directly relevant, but I think it's still worth going over them in case anyone is reading this without reading the 11g article first.
Thanks to Pete Finnigan for his input.
Other Security Considerations
Thanks to Pete Finnigan for his input.
107.1 DBMS_NETWORK_ACL_ADMIN Overview
The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network access control lists (ACL).
115.7 Summary of DBMS_NETWORK_ACL_ADMIN Subprograms
This table lists and briefly describes the DBMS_NETWORK_ACL_ADMIN package subprograms.
Table 115-3 DBMS_NETWORK_ACL_ADMIN Package Subprograms
[DEPRECATED] Adds a privilege to grant or deny the network access to the user in an access control list (ACL)
Appends an access control entry (ACE) to the access control list (ACL) of a network host.
Appends access control entries (ACE) of an access control list (ACL) to the ACL of a network host
Appends an access control entry (ACE) to the access control list (ACL) of a wallet
Appends access control entries (ACE) of an access control list (ACL) to the ACL of a wallet
[DEPRECATED] Assigns an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
[DEPRECATED] Assigns an access control list (ACL) to a wallet
[DEPRECATED] Checks if a privilege is granted or denied the user in an access control list (ACL)
[DEPRECATED] Checks if a privilege is granted to or denied from the user in an ACL by specifying the object ID of the access control list
[DEPRECATED] Creates an access control list (ACL) with an initial privilege setting
[DEPRECATED] Deletes a privilege in an access control list (ACL)
[DEPRECATED] Drops an access control list (ACL)
Removes privileges from access control entries (ACE) in the access control list (ACL) of a network host matching the given ACE
Removes privileges from access control entries (ACE) in the access control list (ACL) of a wallet matching the given ACE
Sets the access control list (ACL) of a network host which controls access to the host from the database
Sets the access control list (ACL) of a wallet which controls access to the wallet from the database
[DEPRECATED] Unassigns the access control list (ACL) currently assigned to a network host
[DEPRECATED] Unassigns the access control list (ACL) currently assigned to a wallet
115.7.1 ADD_PRIVILEGE Procedure
This procedure adds a privilege to grant or deny the network access to the user. The access control entry (ACE) is created if it does not exist.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 115-4 ADD_PRIVILEGE Function Parameters
Name of the ACL. Relative path will be relative to "/sys/acls"
Principal (database user or role) to whom the privilege is granted or denied. Case sensitive.
Privilege is granted or denied.
Network privilege to be granted or denied
Position (1-based) of the ACE. If a non- NULL value is given, the privilege will be added in a new ACE at the given position and there should not be another ACE for the principal with the same is_grant (grant or deny). If a NULL value is given, the privilege will be added to the ACE matching the principal and the is_grant if one exists, or to the end of the ACL if the matching ACE does not exist.
Start date of the access control entry (ACE). When specified, the ACE will be valid only on and after the specified date. The start_date will be ignored if the privilege is added to an existing ACE.
End date of the access control entry (ACE). When specified, the ACE expires after the specified date. The end_date must be greater than or equal to the start_date . The end_date will be ignored if the privilege is added to an existing ACE.
To remove the permission, use the DELETE_PRIVILEGE Procedure.
115.7.2 APPEND_HOST_ACE Procedure
This procedure appends an access control entry (ACE) to the access control list (ACL) of a network host. The ACL controls access to the given host from the database and the ACE specifies the privileges granted to or denied from the specified principal.
Table 115-5 APPEND_HOST_ACE Function Parameters
The host, which can be the name or the IP address of the host. You can use a wildcard to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of an optional TCP port range
Upper bound of an optional TCP port range. If NULL , lower_port is assumed.
Duplicate privileges in the matching ACE in the host ACL will be skipped.
An IP address' ACL takes precedence over its subnets' ACLs. For a given IP address, say 192.168.0.100 , the following subnets are listed in decreasing precedence:
An ACE with a "resolve" privilege can be appended only to a host's ACL without a port range.
When ACEs with "connect" privileges are appended to a host's ACLs with and without a port range, the one appended to the host with a port range takes precedence.
When specifying a TCP port range of a host, it cannot overlap with other existing port ranges of the host.
If the ACL is shared with another host or wallet, a copy of the ACL will be made before the ACL is modified.
115.7.3 APPEND_HOST_ACL Procedure
This procedure appends access control entries (ACE) of an access control list (ACL) to the ACL of a network host.
Table 115-6 APPEND_HOST_ACL Function Parameters
The host, which can be the name or the IP address of the host. You can use a wildcard to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of an optional TCP port range
Upper bound of an optional TCP port range. If NULL , lower_port is assumed.
The ACL from which to append
Duplicate privileges in the matching ACE in the host ACL will be skipped.
An IP address' ACL takes precedence over its subnets' ACLs. For a given IP address, say 192.168.0.100 , the following subnets are listed in decreasing precedence:
An ACE with a "resolve" privilege can be appended only to a host's ACL without a port range.
When ACEs with "connect" privileges are appended to a host's ACLs with and without a port range, the one appended to the host with a port range takes precedence.
When specifying a TCP port range of a host, it cannot overlap with other existing port ranges of the host.- If the ACL is shared with another host or wallet, a copy of the ACL will be made before the ACL is modified.
115.7.4 APPEND_WALLET_ACE Procedure
This procedure appends an access control entry (ACE) to the access control list (ACL) of a wallet. The ACL controls access to the given wallet from the database and the ACE specifies the privileges granted to or denied from the specified principal.
Table 115-7 APPEND_WALLET_ACE Function Parameters
Directory path of the wallet. The path is case-sensitive of the format file: directory-path .
Duplicate privileges in the matching ACE in the host ACL will be skipped.
If the ACL is shared with another host or wallet, a copy of the ACL is made before the ACL is modified.
115.7.5 APPEND_WALLET_ACL Procedure
This procedure appends access control entries (ACE) of an access control list (ACL) to the ACL of a wallet.
Table 115-8 APPEND_WALLET_ACL Function Parameters
Directory path of the wallet. The path is case-sensitive of the format file: directory-path .
The ACL from which to append
Duplicate privileges in the matching ACE in the host ACL will be skipped.
To remove the ACE, use REMOVE_WALLET_ACE.
If the ACL is shared with another host or wallet, a copy of the ACL is made before the ACL is modified.
115.7.6 ASSIGN_ACL Procedure
This procedure assigns an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 115-9 ASSIGN_ACL Function Parameters
Name of the ACL. Relative path will be relative to " /sys/acls ".
Host to which the ACL is to be assigned. The host can be the name or the IP address of the host. A wildcard can be used to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of a TCP port range if not NULL
Upper bound of a TCP port range. If NULL , lower_port is assumed.
Only one ACL can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range. When you assign a new access control list to a network target, Oracle Database unassigns the previous access control list that was assigned to the same target. However, Oracle Database does not drop the access control list. You can drop the access control list by using the DROP_ACL Procedure. To remove an access control list assignment, use the UNASSIGN_ACL Procedure.
The ACL assigned to a domain takes a lower precedence than the other ACLs assigned sub-domains, which take a lower precedence than the ACLs assigned to the individual hosts. So for a given host, for example, "www.us.example.com", the following domains are listed in decreasing precedences:
In the same way, the ACL assigned to an subnet takes a lower precedence than the other ACLs assigned smaller subnets, which take a lower precedence than the ACLs assigned to the individual IP addresses. So for a given IP address, for example, "192.168.0.100", the following subnets are listed in decreasing precedences:
The port range is applicable only to the "connect" privilege assignments in the ACL. The "resolve" privilege assignments in an ACL have effects only when the ACL is assigned to a host without a port range.
For the "connect" privilege assignments, an ACL assigned to the host without a port range takes a lower precedence than other ACLs assigned to the same host with a port range.
When specifying a TCP port range, both lower_port and upper_port must not be NULL and upper_port must be greater than or equal to lower_port . The port range must not overlap with any other port ranges for the same host assigned already.
To remove the assignment, use UNASSIGN_ACL Procedure.
115.7.7 ASSIGN_WALLET_ACL Procedure
This procedure assigns an access control list (ACL) to a wallet.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 115-10 ASSIGN_WALLET_ACL Procedure Parameters
Name of the ACL. Relative path will be relative to "/sys/acls "
Directory path of the wallet to which the ACL is to be assigned. The path is case-sensitive and of the format file: directory-path .
To remove the assignment, use the UNASSIGN_WALLET_ACL Procedure.
115.7.8 CHECK_PRIVILEGE Function
This function checks if a privilege is granted or denied the user in an ACL.
This procedure is deprecated in Oracle Database 12 c . The procedure remains available in the package only for reasons of backward compatibility.
Table 115-11 CHECK_PRIVILEGE Function Parameters
Name of the ACL. Relative path will be relative to "/sys/acls".
User to check against. If the user is NULL , the invoker is assumed. The username is case-sensitive as in the USERNAME column of the ALL_USERS view.
Network privilege to check
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
115.7.9 CHECK_PRIVILEGE_ACLID Function
This function checks if a privilege is granted to or denied from the user in an ACL by specifying the object ID of the access control list.
This procedure is deprecated in Oracle Database 12 c . The procedure remains available in the package only for reasons of backward compatibility.
Table 115-12 CHECK_PRIVILEGE_ACLID Function Parameters
Object ID of the ACL
User to check against. If the user is NULL , the invoker is assumed. The username is case-sensitive as in the USERNAME column of the ALL_USERS view.
Network privilege to check
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
115.7.10 CREATE_ACL Procedure
This deprecated procedure creates an access control list (ACL) with an initial privilege setting. An ACL must have at least one privilege setting. The ACL has no access control effect unless it is assigned to the network target.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 115-13 CREATE_ACL Procedure Parameters
Name of the ACL. Relative path will be relative to "/sys/acls".
Description attribute in the ACL
Principal (database user or role) to whom the privilege is granted or denied. Case sensitive.
Privilege is granted or not (denied)
Start date of the access control entry (ACE). When specified, the ACE is valid only on and after the specified date.
End date of the access control entry (ACE). When specified, the ACE expires after the specified date. The end_date must be greater than or equal to the start_date .
The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).
The chapter contains the following topics:
For more information, see "Managing Fine-grained Access to External Network Services" in Oracle Database Security Guide
107.7 Summary of DBMS_NETWORK_ACL_ADMIN Subprograms
This table lists and briefly describes the DBMS_NETWORK_ACL_ADMIN package subprograms.
Table 107-3 DBMS_NETWORK_ACL_ADMIN Package Subprograms
[DEPRECATED] Adds a privilege to grant or deny the network access to the user in an access control list (ACL)
Appends an access control entry (ACE) to the access control list (ACL) of a network host.
Appends access control entries (ACE) of an access control list (ACL) to the ACL of a network host
Appends an access control entry (ACE) to the access control list (ACL) of a wallet
Appends access control entries (ACE) of an access control list (ACL) to the ACL of a wallet
[DEPRECATED] Assigns an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
[DEPRECATED] Assigns an access control list (ACL) to a wallet
[DEPRECATED] Checks if a privilege is granted or denied the user in an access control list (ACL)
[DEPRECATED] Checks if a privilege is granted to or denied from the user in an ACL by specifying the object ID of the access control list
[DEPRECATED] Creates an access control list (ACL) with an initial privilege setting
[DEPRECATED] Deletes a privilege in an access control list (ACL)
[DEPRECATED] Drops an access control list (ACL)
Removes privileges from access control entries (ACE) in the access control list (ACL) of a network host matching the given ACE
Removes privileges from access control entries (ACE) in the access control list (ACL) of a wallet matching the given ACE
Sets the access control list (ACL) of a network host which controls access to the host from the database
Sets the access control list (ACL) of a wallet which controls access to the wallet from the database
[DEPRECATED] Unassigns the access control list (ACL) currently assigned to a network host
[DEPRECATED] Unassigns the access control list (ACL) currently assigned to a wallet
107.7.1 ADD_PRIVILEGE Procedure
This procedure adds a privilege to grant or deny the network access to the user. The access control entry (ACE) is created if it does not exist.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 107-4 ADD_PRIVILEGE Function Parameters
Name of the ACL. Relative path will be relative to "/sys/acls"
Principal (database user or role) to whom the privilege is granted or denied. Case sensitive.
Privilege is granted or denied.
Network privilege to be granted or denied
Position (1-based) of the ACE. If a non- NULL value is given, the privilege will be added in a new ACE at the given position and there should not be another ACE for the principal with the same is_grant (grant or deny). If a NULL value is given, the privilege will be added to the ACE matching the principal and the is_grant if one exists, or to the end of the ACL if the matching ACE does not exist.
Start date of the access control entry (ACE). When specified, the ACE will be valid only on and after the specified date. The start_date will be ignored if the privilege is added to an existing ACE.
End date of the access control entry (ACE). When specified, the ACE expires after the specified date. The end_date must be greater than or equal to the start_date . The end_date will be ignored if the privilege is added to an existing ACE.
To remove the permission, use the DELETE_PRIVILEGE Procedure.
107.7.2 APPEND_HOST_ACE Procedure
This procedure appends an access control entry (ACE) to the access control list (ACL) of a network host. The ACL controls access to the given host from the database and the ACE specifies the privileges granted to or denied from the specified principal.
Table 107-5 APPEND_HOST_ACE Function Parameters
The host, which can be the name or the IP address of the host. You can use a wildcard to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of an optional TCP port range
Upper bound of an optional TCP port range. If NULL , lower_port is assumed.
Duplicate privileges in the matching ACE in the host ACL will be skipped.
An IP address' ACL takes precedence over its subnets' ACLs. For a given IP address, say 192.168.0.100 , the following subnets are listed in decreasing precedence:
An ACE with a "resolve" privilege can be appended only to a host's ACL without a port range.
When ACEs with "connect" privileges are appended to a host's ACLs with and without a port range, the one appended to the host with a port range takes precedence.
When specifying a TCP port range of a host, it cannot overlap with other existing port ranges of the host.
If the ACL is shared with another host or wallet, a copy of the ACL will be made before the ACL is modified.
107.7.3 APPEND_HOST_ACL Procedure
This procedure appends access control entries (ACE) of an access control list (ACL) to the ACL of a network host.
Table 107-6 APPEND_HOST_ACL Function Parameters
The host, which can be the name or the IP address of the host. You can use a wildcard to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of an optional TCP port range
Upper bound of an optional TCP port range. If NULL , lower_port is assumed.
The ACL from which to append
Duplicate privileges in the matching ACE in the host ACL will be skipped.
An IP address' ACL takes precedence over its subnets' ACLs. For a given IP address, say 192.168.0.100 , the following subnets are listed in decreasing precedence:
An ACE with a "resolve" privilege can be appended only to a host's ACL without a port range.
When ACEs with "connect" privileges are appended to a host's ACLs with and without a port range, the one appended to the host with a port range takes precedence.
When specifying a TCP port range of a host, it cannot overlap with other existing port ranges of the host.- If the ACL is shared with another host or wallet, a copy of the ACL will be made before the ACL is modified.
107.7.4 APPEND_WALLET_ACE Procedure
This procedure appends an access control entry (ACE) to the access control list (ACL) of a wallet. The ACL controls access to the given wallet from the database and the ACE specifies the privileges granted to or denied from the specified principal.
Table 107-7 APPEND_WALLET_ACE Function Parameters
Directory path of the wallet. The path is case-sensitive of the format file: directory-path .
Duplicate privileges in the matching ACE in the host ACL will be skipped.
If the ACL is shared with another host or wallet, a copy of the ACL is made before the ACL is modified.
107.7.5 APPEND_WALLET_ACL Procedure
This procedure appends access control entries (ACE) of an access control list (ACL) to the ACL of a wallet.
Table 107-8 APPEND_WALLET_ACL Function Parameters
Directory path of the wallet. The path is case-sensitive of the format file: directory-path .
The ACL from which to append
Duplicate privileges in the matching ACE in the host ACL will be skipped.
To remove the ACE, use REMOVE_WALLET_ACE.
If the ACL is shared with another host or wallet, a copy of the ACL is made before the ACL is modified.
107.7.6 ASSIGN_ACL Procedure
This procedure assigns an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 107-9 ASSIGN_ACL Function Parameters
Name of the ACL. Relative path will be relative to " /sys/acls ".
Host to which the ACL is to be assigned. The host can be the name or the IP address of the host. A wildcard can be used to specify a domain or a IP subnet. The host or domain name is case-insensitive.
Lower bound of a TCP port range if not NULL
Upper bound of a TCP port range. If NULL , lower_port is assumed.
Only one ACL can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range. When you assign a new access control list to a network target, Oracle Database unassigns the previous access control list that was assigned to the same target. However, Oracle Database does not drop the access control list. You can drop the access control list by using the DROP_ACL Procedure. To remove an access control list assignment, use the UNASSIGN_ACL Procedure.
The ACL assigned to a domain takes a lower precedence than the other ACLs assigned sub-domains, which take a lower precedence than the ACLs assigned to the individual hosts. So for a given host, for example, "www.us.example.com", the following domains are listed in decreasing precedences:
In the same way, the ACL assigned to an subnet takes a lower precedence than the other ACLs assigned smaller subnets, which take a lower precedence than the ACLs assigned to the individual IP addresses. So for a given IP address, for example, "192.168.0.100", the following subnets are listed in decreasing precedences:
The port range is applicable only to the "connect" privilege assignments in the ACL. The "resolve" privilege assignments in an ACL have effects only when the ACL is assigned to a host without a port range.
For the "connect" privilege assignments, an ACL assigned to the host without a port range takes a lower precedence than other ACLs assigned to the same host with a port range.
When specifying a TCP port range, both lower_port and upper_port must not be NULL and upper_port must be greater than or equal to lower_port . The port range must not overlap with any other port ranges for the same host assigned already.
To remove the assignment, use UNASSIGN_ACL Procedure.
107.7.7 ASSIGN_WALLET_ACL Procedure
This procedure assigns an access control list (ACL) to a wallet.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 107-10 ASSIGN_WALLET_ACL Procedure Parameters
Name of the ACL. Relative path will be relative to "/sys/acls "
Directory path of the wallet to which the ACL is to be assigned. The path is case-sensitive and of the format file: directory-path .
To remove the assignment, use the UNASSIGN_WALLET_ACL Procedure.
107.7.8 CHECK_PRIVILEGE Function
This function checks if a privilege is granted or denied the user in an ACL.
This procedure is deprecated in Oracle Database 12 c . The procedure remains available in the package only for reasons of backward compatibility.
Table 107-11 CHECK_PRIVILEGE Function Parameters
Name of the ACL. Relative path will be relative to "/sys/acls".
User to check against. If the user is NULL , the invoker is assumed. The username is case-sensitive as in the USERNAME column of the ALL_USERS view.
Network privilege to check
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
107.7.9 CHECK_PRIVILEGE_ACLID Function
This function checks if a privilege is granted to or denied from the user in an ACL by specifying the object ID of the access control list.
This procedure is deprecated in Oracle Database 12 c . The procedure remains available in the package only for reasons of backward compatibility.
Table 107-12 CHECK_PRIVILEGE_ACLID Function Parameters
Object ID of the ACL
User to check against. If the user is NULL , the invoker is assumed. The username is case-sensitive as in the USERNAME column of the ALL_USERS view.
Network privilege to check
Returns 1 when the privilege is granted; 0 when the privilege is denied; NULL when the privilege is neither granted or denied.
107.7.10 CREATE_ACL Procedure
This deprecated procedure creates an access control list (ACL) with an initial privilege setting. An ACL must have at least one privilege setting. The ACL has no access control effect unless it is assigned to the network target.
This procedure is deprecated in Oracle Database 12 c . While the procedure remains available in the package for reasons of backward compatibility, Oracle recommends using the APPEND_HOST_ACE Procedure and the APPEND_WALLET_ACE Procedure.
Table 107-13 CREATE_ACL Procedure Parameters
Name of the ACL. Relative path will be relative to "/sys/acls".
Description attribute in the ACL
Principal (database user or role) to whom the privilege is granted or denied. Case sensitive.
Privilege is granted or not (denied)
Start date of the access control entry (ACE). When specified, the ACE is valid only on and after the specified date.
End date of the access control entry (ACE). When specified, the ACE expires after the specified date. The end_date must be greater than or equal to the start_date .
Access control lists can be created, amended and deleted in the XML DB repository directly using FTP or WebDav. In addition, Oracle provide the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages to allow ACL management from PL/SQL. These APIs are the subject of this article.
Checking Privileges
In addition to the ACL views, privileges can be checked using the CHECK_PRIVILEGE and CHECK_PRIVILEGE_ACLID functions of the DBMS_NETWORK_ACL_ADMIN package.
The DBMS_NETWORK_ACL_UTILITY package contains functions to help determine possible matching domains. The DOMAINS table function returns a collection of all possible references that may affect the specified host, domain, IP address or subnet, in order of precedence.
The DOMAIN_LEVEL function returns the level of the specified host, domain, IP address or subnet.
These functions may be useful for when querying the ACL views for possible matches to a specific host, domain, IP address or subnet.
108.3 DBMS_NETWORK_ACL_ADMIN Security Model
The EXECUTE privilege on the DBMS_NETWORK_ACL_ADMIN package is granted to the DBA role and to the EXECUTE_CATALOG_ROLE by default.
107.2 DBMS_NETWORK_ACL_ADMIN Deprecated Subprograms
Oracle recommends that you do not use deprecated subprograms in new applications. Support for deprecated features is for backward compatibility only
The following subprograms are deprecated with release Oracle Database 12 c :
107.3 DBMS_NETWORK_ACL_ADMIN Security Model
The EXECUTE privilege on the DBMS_NETWORK_ACL_ADMIN package is granted to the DBA role and to the EXECUTE_CATALOG_ROLE by default.
Append an Access Control Entry (ACE)
You will never create a host ACL directly. Instead, they are implicitly created when you append a host Access Control Entry (ACE) using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure. If you append a new ACE to a host that has no existing host ACL, a new host ACL is implicitly created. If the host already has an ACL, the new host ACE will be appended to the existing host ACL.
Once the host ACE is appended, we can see the details are visible using the old DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES views, which are deprecated in 12c.
We should really use the new DBA_HOST_ACLS and DBA_HOST_ACES views.
For the rest of the article, these general queries will be replaced with calls to the host_acls.sql and host_aces.sql scripts.
We can append another host ACE to the same host ACL by referencing the same host. Notice how we get two entries in the DBA_HOST_ACES view, but there is still only a single host ACL.
Host ACEs are removed using the DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE procedure. The REMOVE_EMPTY_ACL parameter determines if unused host ACLs should be removed, as shown below.
108.2 DBMS_NETWORK_ACL_ADMIN Deprecated Subprograms
Oracle recommends that you do not use deprecated subprograms in new applications. Support for deprecated features is for backward compatibility only
The following subprograms are deprecated with release Oracle Database 12 c :
107.4 DBMS_NETWORK_ACL_ADMIN Constants
The DBMS_NETWORK_ACL_ADMIN package defines constants to use specifying parameter values.
These are shown in the following table.
Table 107-1 DBMS_NETWORK_ACL_ADMIN Constants
IP address mask: xxx.xxx.xxx.xxx
IP subnet mask: xxx.xxx. *
Test the ACL
From this we can see that the TEST1 user was able to access the web page, while the TEST2 user was denied access by the ACL.
The default action of the server is to deny access to external network service, as shown by the following test on a new user.
This may cause some confusion when upgrading databases that access external network services from 10g to 11g. In these situations, it will be necessary to implement suitable access control lists before your original functionality is possible.
115.6 DBMS_NETWORK_ACL_ADMIN Examples
Grant the use_client_certificates and use_passwords privileges for wallet file:/example/wallets/hr_wallet to SCOTT .
Revoke the use_passwords privilege for wallet file:/example/wallets/hr_wallet from SCOTT .
115.3 DBMS_NETWORK_ACL_ADMIN Security Model
The EXECUTE privilege on the DBMS_NETWORK_ACL_ADMIN package is granted to the DBA role and to the EXECUTE_CATALOG_ROLE by default.
108.5 DBMS_NETWORK_ACL_ADMIN Exceptions
The following table lists the exceptions raised by the DBMS_NETWORK_ACL_ADMIN package.
ACE already exists
ACL already exists
Invalid ACL path
Invalid wallet path
Privilege not granted
108.6 DBMS_NETWORK_ACL_ADMIN Examples
Grant the use_client_certificates and use_passwords privileges for wallet file:/example/wallets/hr_wallet to SCOTT .
Revoke the use_passwords privilege for wallet file:/example/wallets/hr_wallet from SCOTT .
107.5 DBMS_NETWORK_ACL_ADMIN Exceptions
The following table lists the exceptions raised by the DBMS_NETWORK_ACL_ADMIN package.
Table 107-2 DBMS_NETWORK_ACL_ADMIN Exceptions
ACE already exists
ACL already exists
Invalid ACL path
Invalid wallet path
Privilege not granted
107.6 DBMS_NETWORK_ACL_ADMIN Examples
Grant the use_client_certificates and use_passwords privileges for wallet file:/example/wallets/hr_wallet to SCOTT .
Revoke the use_passwords privilege for wallet file:/example/wallets/hr_wallet from SCOTT .
Open ACL
From a security standpoint, it's not a good idea to allow complete network access from the database, but for testing features I sometimes find it useful to create an open ACL for an instance.
ACL Views
The DBA_NETWORK_ACLS , DBA_NETWORK_ACL_PRIVILEGES and USER_NETWORK_ACL_PRIVILEGES views display the current ACL settings. The expected output below assumes none of the delete/drop/unassign operations have been performed.
The DBA_NETWORK_ACLS view displays information about network and ACL assignments.
The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.
The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings.
Setup
In a multitenant environment, Access Control Entries (ACEs) can be created at the CDB or PDB level. For the examples in this article, all the host ACLs and host ACEs will be created at the PDB level. The following code creates two test users in a PDB.
115.5 DBMS_NETWORK_ACL_ADMIN Exceptions
The following table lists the exceptions raised by the DBMS_NETWORK_ACL_ADMIN package.
Table 115-2 DBMS_NETWORK_ACL_ADMIN Exceptions
ACE already exists
ACL already exists
Invalid ACL path
Invalid wallet path
Privilege not granted
Create New ACL based on an Existing ACL
You can create a new ACL based on an existing one using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACL procedure.
Notice we have two ACLs with similar ACEs associated with them.
108.1 DBMS_NETWORK_ACL_ADMIN Overview
The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network access control lists (ACL).
Test the ACL
From this we can see that the TEST1 user was able to access the web page, while the TEST2 user was denied access by the ACL.
The default action of the server is to deny access to external network service, as shown by the following test on a new user.
This may cause some confusion when upgrading databases that access external network services from 10g to 12c. In these situations, it will be necessary to implement suitable access control lists before your original functionality is possible.
108.4 DBMS_NETWORK_ACL_ADMIN Constants
The DBMS_NETWORK_ACL_ADMIN package defines constants to use specifying parameter values.
These are shown in the following table.
IP address mask: xxx.xxx.xxx.xxx
IP subnet mask: xxx.xxx. *
Читайте также: