Access Control List (ACL) in Oracle

Over the past couple of weeks I have been looking at some PL/SQL code that allows me to post messages to my twitter account (@brendantierney). You may have seen these. They looked something link the following.

I found some scripts that does all the work for me at the SomeCodingHero blog. Although the blog had most of the code needed, it did need some corrections and changes necessary for my twitter customer details. I will have some blog posts on these over the coming weeks.

Before you can setup and use these scripts, you need to have setup and configured your database so that you are allowing the database to access websites outside of database. To do this you need to setup what is called the Access Control List. This allows you to setup fine grained access to external services. If you do not do this then you will get the error:

You need to setup the ACL if you are going to use any of the following UTL_STMP, UTL_TCP, UTL_MAIL, UTL_HTTP and UTL_INADDR. For the PL/SQL code to post the messages to Twitter we will be using the UTL_HTTP package.

OK, so here are the steps that I went through to get my ACL setup so that I can send twitter posts using PL/SQL in my Oracle 11.2.0.3 database running on my Dell Windows 7 laptop.

1.  To setup the ACL you need to log into the database as SYS or get your DBA to set this up for you.

2.  Create the ACL

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl          => 'Twitter.xmll',

    description  => 'ACL for Twitter',

    principal    => 'Twitter',  -- schema name

    is_grant     => TRUE,

    privilege    => 'connect',

    start_date   => SYSTIMESTAMP,

    end_date     => NULL);

  COMMIT;

END;

3.  Now you can what websites you are going to allow access to. In our case we want to access Twitter and the Twitter API.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
      acl => 'Twitter.xmll',
      host => 'twitter.com',
      lower_port => 80,
      upper_port => 80);

   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
      acl => 'Twitter.xmll',
      host => 'api.twitter.com',
      lower_port => 80,
      upper_port => 80);
END;

4.  Instead of having two ASSIGN_ACL statements I could have just used one like the follow, but I only found this out after I had done the above.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
      acl => 'Twitter.xmll',
      host => '*.twitter.com',
      lower_port => 80,
      upper_port => 80);
END;

5.  I was then ready to start running the PL/SQL scripts to get my Twitter setup and running to start posting tweets

Resources

Technical Note on using ACL by Tim Hall

Don Seiler (Pythian) Blog Post

Oracle Documentation – Fine Grain Access Control to External Services

Oracle Documentation – PL/SQL Package DBMS_NETWORK_ACL_ADMIN