When you create access control lists for network connections, you should create one access control list dedicated to a group of common users, for example, users who need access to a particular application that resides on a specific host computer. For ease of administration and for good system performance, do not create too many access control lists. Network hosts accessible to the same group of users should share the same access control list.
To create the access control list by using the DBMS_NETWORK_ACL_ADMIN package, follow these steps:
■ Step 1: Create the Access Control List and Its Privilege Definitions ■ Step 2: Assign the Access Control List to One or More Network Hosts
Managing Fine-Grained Access to External Network Services
Step 1: Create the Access Control List and Its Privilege Definitions
Use the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL procedure to create the content of the access control list. It contains a name of the access control list, a brief description, and privilege settings for one user or role that you want to associate with the access control list. In an access control list, privileges for each user or role are grouped together as an access control entry (ACE). An access control list must have the privilege settings for at least one user or role.
The syntax for creating an access control list is as follows:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'file_name.xml', description => 'file description', principal => 'user_or_role', is_grant => TRUE|FALSE, privilege => 'connect|resolve',
start_date => null|timestamp_with_time_zone, end_date => null|timestamp_with_time_zone); END;
In this specification:
■ acl: Enter a name for the access control list XML file. Oracle Database creates this
file relative to the /sys/acls directory in the XML DB Repository in the database. Include the .xml extension. For example:
acl => 'us-example-com-permissions.xml',
■ description: Enter a brief description of the purpose of this file. For example:
description => 'Network connection permission for ACCT_MGR role',
■ principal: Enter the first user account or role being granted or denied permissions. For example:
principal => 'ACCT_MGR',
Enter the name of the user account or role in case sensitive characters. For example, if the database stores the role name ACCT_MGR in all capital letters, entering it in mixed or lower case will not work. You can find the user accounts and roles in the current database instance by querying the DBA_USERS and DBA_ ROLES data dictionary views. Typically, user names and roles are stored in upper-case letters.
If you want to enter multiple users or grant additional privileges to this user or role, use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure (described next) after you have created this access control list XML file.
■ is_grant: Enter either TRUE or FALSE, to indicate whether the privilege is to be
granted or denied. For example:
is_grant => TRUE,
Note: You cannot import or export the access control list settings by using the Oracle Database import or export utilities such as Oracle Data Pump.
Managing Fine-Grained Access to External Network Services
Configuring Privilege and Role Authorization 4-51
■ privilege: Enter either connect or resolve. This setting is case sensitive, so always enter it in lowercase. For example:
privilege => 'connect',
The connect privilege grants the user permission to connect to a network service at an external host. The resolve privilege grants the user permission to resolve a network host name or an IP address.
A database user must have the connect privilege to connect to an external network host computer if he or she is connecting using the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL utility packages. To resolve the host name that was given a host IP address, or the IP address that was given a host name, with the UTL_ INADDR package, grant the database user the resolve privilege instead.
You can use the data dictionary views described in "Finding Information About Access Control Lists" on page 4-61 to find more information about existing privileges and network connections.
■ start_date: (Optional) Enter the start date for the access control entry (ACE), in TIMESTAMP WITH TIME ZONE format (YYYY-MM-DD HH:MI:SS.FF TZR). When specified, the access control entry will be valid only on or after the specified date. The default is null. For example, to set a start date of February 28, 2008, at 6:30 a.m. in San Francisco, California, U.S., which is in the Pacific time zone:
start_date => '2008-02-28 06:30:00.00 US/Pacific',
The NLS_TIMESTAMP_FORMAT initialization parameter sets the default timestamp format. See Oracle Database Reference for more information.
■ end_date: (Optional) Enter the end date for the access control entry (ACE), in TIMESTAMP WITH TIME ZONE format (YYYY-MM-DD HH:MI:SS.FF TZR). When specified, the access control entry expires after the specified date. The end_date setting must be greater than or equal to the start_date setting. The default is null.
For example, to set an end date of December 10, 2008, at 11:59 p.m. in San Francisco, California, U.S., which is in the Pacific time zone:
end_date => '2008-12-10 23:59:00.00 US/Pacific');
To add more users or roles to the access control list, or grant additional privileges to one user or role, use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure. The syntax is as follows: BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => 'file_name.xml', principal => 'user_or_role', is_grant => TRUE|FALSE, privilege => 'connect|resolve', position => null|value,
start_date => null|timestamp_with_time_zone, end_date => null|timestamp_with_time_zone); END;
/
As you can see, the parameters to add the privilege are the similar to those in the CREATE_ACL procedure, except that description is not included and the position parameter, which sets the order of precedence for multiple users or roles, was added. Because you now are adding more than one user or role, you may want to consider
Managing Fine-Grained Access to External Network Services
setting their precedence. "Setting the Precedence of Multiple Users and Roles in One Access Control List" on page 4-60 provides more information.
Other DBMS_NETWORK_ACL_ADMIN procedures that are available for this step are DELETE_ PRIVILEGE and DROP_ACL.
At this stage, you have created an access control list that defines the privileges needed to connect to a network host. However, the access control list has no effect until you complete Step 2: Assign the Access Control List to One or More Network Hosts.
Step 2: Assign the Access Control List to One or More Network Hosts
After you create the access control list, then you are ready to assign it to one or more network host computers. You can use the DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL procedure to do so. For example: BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'file_name.xml', host => 'network_host', lower_port => null|port_number, upper_port => null|port_number); END;
/
In this specification:
■ acl: Enter the name of the access control list XML file (from Step 1: Create the Access Control List and Its Privilege Definitions) to assign to the network host. Oracle Database creates this file relative to the /sys/acls directory in the XML DB Repository in the database. Include the .xml extension. For example:
acl => 'us-example-com-permissions.xml',
■ host: Enter the network host to which this access control list will be assigned. This setting can be a name or IP address of the network host, or you can enter
localhost. Host names are case-insensitive. For example:
host => 'us.example.com',
If you specify localhost, and if the host name has not been specified with the UTL_INADDR and UTL_HTTP PL/SQL packages in situations in which the local host is assumed, then these packages will search for and use the ACL that has been assigned localhost for the host setting.
See the following sections for more information about how network host computers in access control list assignments work:
– "Specifying a Group of Network Host Computers" on page 4-56
– "Checking Privilege Assignments That Affect User Access to a Network Host" on page 4-57
– "Precedence Order for a Host Computer in Multiple Access Control List Assignments" on page 4-56
– "Precedence Order for a Host in Access Control List Assignments with Port Ranges" on page 4-57
■ lower_port: (Optional) For TCP connections, enter the lower boundary of the port range. Use this setting for the connect privilege only; omit it for the resolve
Managing Fine-Grained Access to External Network Services
Configuring Privilege and Role Authorization 4-53
privilege. The default is null, which means that there is no port restriction (that is, the ACL applies to all ports). The range is between 1 and 65535.
For example:
lower_port => 80,
■ upper_port: (Optional) For TCP connections, enter the upper boundary of the port range. Use this setting for connect privileges only; omit it for resolve privileges. The default is null, which means that there is no port restriction (that is, the ACL applies to all ports). The range is between 1 and 65535.
For example:
upper_port => 3999);
If you enter a value for the lower_port and leave the upper_port at null (or just omit it), Oracle Database assumes the upper_port setting is the same as the lower_ port. For example, if you set lower_port to 80 and omit upper_port, the upper_ port setting is assumed to be 80.
The resolve privilege in the access control list takes no effect when a port range is specified in the access control list assignment.
Only one access control list 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.
Depending on how you create and maintain the access control list, the two steps may overlap. For example, you can create an access control list that has privileges for five users in it, and then apply it to two host computers. Later on, you can modify this access control list to have different or additional users and privileges, and assign it to different or additional host computers.
All access control list changes, including the assignment to network hosts, are transactional. They do not take effect until the transaction is committed.
You can find information about existing privileges and network connections by using the data dictionary views described in Table 4–6, " Data Dictionary Views That Display Information about Access Control Lists" on page 4-62.
For information about using the DBMS_NETWORK_ACL_ADMIN package, see Oracle Database PL/SQL Packages and Types Reference.