Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle ACL Tips

Oracle 11g New Features Tips by Burleson Consulting
June 29, 2008

Oracle 11g New Features Tips

% ACLs are stored in XML DB.
           XML DB must be installed for the use of ACLs !

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.

% The parameter principal specifies the first username granted the ACL and is            case sensitive!

% If you want to grant multiple users you must use the            DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure to add users.

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;

% It is possible to use wildcards in the hosts parameter. This allows access to            all hosts in the domain.
           Hostnames are case sensitive
           
You can use an IP address as well as  a DNS hostname

% Only one ACL can be assigned to a host or domain or IP subnet or port range            (if specified)!

% You can assign multiple hosts to the same ACL by calling            DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL multiple times

Oracle evaluated ACLs in the following sequence:

  1. fully qualified hostnames are evaluated

  2. hostnames with ports

  3. partial domain names and sub-domains 

% Do not modify the xml files with a text editor!

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.

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.

 

 
  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.