Scripts for Setting Oracle ACL

Understanding and configuring the ACL (Access Control List) in an Oracle database can be a challenge. It has functionality to control network access from the database which is important to understand to prevent abuse and security leaks, but can be a little un-clear.  Because of this, people often set their ACL to be too “loose” so that they don’t have to mess with it again.

If you have ever run across the ORA-24247 error, you can understand why.

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

While security is good, too much security prevents functionality. Oracle APEX requires access in order to communicate with e-Mail services, LDAP authentication, BI Publisher servers, and any other service type architecture.  It is better to understand your needs and set the security accordingly.

I have constructed a series of scripts to make seeing and changing ACL settings easier.  Each script has a purpose and is designed to accept user inputs.  Built in checks try to reduce input errors.

To run the scripts and make changes to the ACL setup of your database,  you will need to be logged into your database with a user that has the appropriate credentials (SYSDBA and the like).

 SQL> @acl_menu
================================================================================
============= Display Only =====================================================
== @acl_menu     = This file
== @show_hosts   = Server and port range assignemtns in ACL files
== @show_users   = User assignemtns to ACL files
== @show_valid   = Test for invalid ACL files
== @show_descrip = Show developer descriptions of ACL files
== @show_all     = Show above information about all the ACL files
== @show_test    = Quick port 80 test to google
================================================================================
============== Make Changes ====================================================
== @create_new_acl = Create an ACL file
== @drop_acl       = Remove an ACL file
== @add_user       = Prompt for User to assign to an ACL
== @remove_user    = Prompt for User to remove from an ACL
== @add_host       = Add a host definition to an ACL file
== @remove_host    = Remove a host definition from an ACL file
================================================================================

Download ACL Management Scripts

Let me know if you find these scripts useful, or if you have suggestions for improving them.

Additional References

APEX 4.2 ACL Post Installation Steps
Joel Kallman Application Express Network ACL
11.2 Managing Fine-Grained Access in PL/SQL Packages and Types

One thought on “Scripts for Setting Oracle ACL”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>