Access Control List (ACL) in Oracle

Posted on Updated on

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s