Oracle ACL Tips

The creation of ACLs is a two step procedure.

The first step is to create the actual ACL and define the privileges for it:

The general syntax 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;

The value connect for the parameter privilege includes resolve! This is necessary for the package UTL_INTADDR.

Here is an example for an ACL:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl => 'sysdba-ch-permissions.xml',
  description => "Permissions for sysdba network',
  principal => "LUTZ',
  is_grant => TRUE,
  privilege => 'connect');
END;

This creates an xml file which holds a list of users and privileges. This container is located under /sys/acl/ in the XML DB.

The second step is to assign network hosts to the ACL.

After the creation of the ACL you can add hosts to it:

Below again you find the general syntax:

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;

And here is an example:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl => 'sysdba-ch-permissions.xml',
  host => "*.sysdba.ch',
  lower_port => 80,
  upper_port => null);
END;

Oracle evaluated ACLs in the following sequence:

1. fully qualified hostnames are evaluated

2. hostnames with ports

3. partial domain names and sub-domains 

The data dictionary views related to ACLs are dba_network_acls and dba_"user_network_acl_privileges:

LUTZ AS SYSDBA @ orcl SQL> DESC dba_network_acls

 Name                         Null"    Type
 ---------------------------- -------- ------------------------------------
 HOST                         NOT NULL VARCHAR2(1000)
 LOWER_PORT                            NUMBER(5)
 UPPER_PORT                            NUMBER(5)
 ACL                                   VARCHAR2(4000)
 ACLID                        NOT NULL RAW(16)

LUTZ AS SYSDBA @ orcl SQL> DESC dba_network_acl_privileges

 Name                        Null"    Type
 ------------------------------------ ----------------------------------------
 ACL                                   VARCHAR2(4000)
 ACLID                                 RAW(16)
 PRINCIPAL                             VARCHAR2(4000)
 PRIVILEGE                             VARCHAR2(7)
 IS_GRANT                              VARCHAR2(5)
 INVERT                                VARCHAR2(5)
 START_DATE                            TIMESTAMP(9) WITH TIME ZONE
 END_DATE                              TIMESTAMP(9) WITH TIME ZONE

How to create and manage ACLs with OEM

The friends of graphical interfaces can also create and manage ACLs. There is an interface to the XML DB integrated into the Enterprise Manager.

% Access Control Lists are an XML DB functionality.

You find the link for the ACLs in the SCHEMA pane in Database Control 11g:

Other Network Security Features

The listener is secured by default in 11g.

It is not possible to manage the listener from remote any more without a password or Class of Secure Transports (COST).

Only the local user who started the listener can stop it in 11g.

% Still we have a default listener with the name LISTENER and port 1521!   

In  11g and beyond, Oracle has introduced the ability to restrict connections to specific hosts (or IP addresses). 

The access control lists (ACL) are used to restrict the hosts that are allowed to connect to the Oracle database.  

ACL"s are created using the dbms_network_acl_admin and dbms_network_acl_utility packages.  Either package can be used to create and manage ACLs.   

Here is an example for an ACL:

 BEGIN  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'sysdba-ch-permissions.xml',
description => "Permissions for sysdba network',  
principal => "LUTZ',  
is_grant => TRUE,  
privilege => 'connect');
END;  

The ACL is then added to the "http://host:port/sys/acls/" directory.

Please note that you will encounter an ORA-24247  error if it relies on one of the network packages and no proper ACL has been created.