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 11
g 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:
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
- Oracle® Database Security Guide 11g Release 2 (11.2): Managing Fine-Grained Access in PL/SQL Packages and Types
- Oracle Base Article on this subject.
