Fine-Grained Access in PL/SQL Packages

   With Oracle 11g there is a new set or privileges. You can configure user access control to external network services and wallets through the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR PL/SQL packages, the DBMS_LDAP PL/SQL package, and the HttpUriType type.

To configure fine-grained access control to external network services, you create an access control list (ACL), which is stored in Oracle XML DB. You can create the access control list by using Oracle XML DB itself, or by using the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY PL/SQL packages.

This feature enhances security for network connections because it restricts the external network hosts that a database user can connect to using the PL/SQL network utility packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR, the DBMS_LDAP PL/SQL package, and the HttpUriType type. Otherwise, an intruder who gained access to the database could maliciously attack the network, because, by default, the PL/SQL utility packages are created with the EXECUTE privilege granted to PUBLIC users.

If you have upgraded from a release before Oracle Database 11g Release 1 (11.1), and your applications depend on PL/SQL network utility packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR, the DBMS_LDAP PL/SQL package, or the HttpUriType type, then the following error may occur when you try to run the application:

ORA-24247: network access denied by access control list (ACL) 

You cannot import or export the access control list settings
by using the Oracle Database import or export utilities
such as Oracle Data Pump.

 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.

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: 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. 
• principal: User account or role being granted or denied permissions. User account or role in case sensitive characters, entering it in mixed or lower case will not work.
  If you want to enter multiple users or grant additional privileges to this user or role, use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure.
  
• is_grant: Either TRUE or FALSE, to indicate whether the privilege is to be granted or denied.
• privilege: Enter either connect or resolve. Case sensitive, always it in lowercase. 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 needs the connect privilege to an external network host computer if he or she is connecting using the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, the DBMS_LDAP package, and the HttpUriType type. 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.
• 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.
  
• end_date: (Optional) Enter the end date for the ACE.

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;

You can grant the privilege explicitly or you can grant it to a role.

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: the name of the access control list XML file 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.
• host: 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. Host names are case insensitive. 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.
• lower/upper_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 privilege. The default is null, which means that there is no port restriction (that is, the ACL applies to all ports). The range of port numbers is between 1 and 65535.
   

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 unassigns the previous access control list that was assigned to the same target. However, Oracle 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.


All access control list changes, including the assignment to network hosts, are transactional. They do not take effect until the transaction is committed.

Example

Assuming we want to give an access to PL/SQL package in schema SCOTT for sending out emails using specific mail gateway named mail.acme.org, no time constraints. Because it is for SMPT access, we are interested in accessing the port number 25.



DECLARE
  acl_already_exists    EXCEPTION;
  PRAGMA EXCEPTION_INIT (acl_already_exists, -31003);
BEGIN
    BEGIN
        DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          => 'sendmail.xml',
        description  => 'Permissions for sending report emails',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'connect');

    EXCEPTION
        WHEN acl_already_exists THEN
            DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
            acl         => 'sendmail.xml',
            principal   => 'SCOTT',
            is_grant    => TRUE,
            privilege   => 'connect');
    END;

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl         => 'sendmail.xml',
    host        => 'mail.acme.com',
    lower_port  => 25,
    upper_port  => 25);
END;
/



COMMIT;

COL acl FORMAT A25
COL host FORMAT A15
COL principal FORMAT A16
COL is_grant FORMAT A8
COL privilege FORMAT A12
SET LINES 120

SELECT acl,host,lower_port ,upper_port FROM DBA_NETWORK_ACLS;
SELECT acl,principal,privilege,is_grant,invert

FROM DBA_NETWORK_ACL_PRIVILEGES;

Revoking privileges would be just:
 

BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
      acl => 'sendmail.xml'
  );
END;
/
COMMIT;

Views

SELECT * FROM dba_network_acls;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

 

Further Readings

1. Oracle® Database Security Guide 11g Release 2 (11.2):  Managing Fine-Grained Access in PL/SQL Packages and Types 
2. Oracle Base Article on this subject.