Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

How to use Access Control Lists in Oracle

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Published:
Working with Network Access Control Lists in Oracle 11g (part 1)

Part 2: https://www.experts-exchange.com/A_9074.html


So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any other network service starts failing.  ORA-24247: network access denied by access control list (ACL) keeps showing up in your error logs.  What is going on?

11g introduced a new security measure called Access Control Lists (ACL) and by default, all network access is blocked!  An ACL, as the name implies, is simply a list of who can access what, and with which privileges.  These new Network ACL's are an extension of the acl facilities of the XDB subsytem. The "who" part is called the principal of an ACL and can be a user, a role or PUBLIC. The "what" is a host and range of ports.  The "which" is a either or both of CONNECT or RESOLVE.  Connect, as it sounds, allows you to connect to a host and send/receive data.  Resolve allows you to look up hostnames given ip addresses or vice versa with the UTL_INADDR package.

Granting access


First, I'll show you how you can fake your previous 10g "open" access.  I don't recommend this,  I'm posting it for comparison to later, more secure examples.

BEGIN
                          -------------------------------------------------------------
                          -------------------------------------------------------------
                          --  Script to create an ACL to mimic pre-11g open access
                          -------------------------------------------------------------
                          -------------------------------------------------------------
                          $IF DBMS_DB_VERSION.ver_le_10
                          $THEN
                              NULL; -- If you happen to run this on 10g it will do nothing
                          $ELSE
                              -- If we've already created the acl, remove it to create
                              -- a clean slate
                              BEGIN
                                  DBMS_NETWORK_ACL_ADMIN.drop_acl('Mimic_Pre11g_ACL.xml');
                              EXCEPTION
                                  WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
                                  THEN
                                      NULL;  -- It didn't exist, that's ok
                              END;
                      
                              -- Create the ACL and grant it to PUBLIC, allowing everyone to CONNECT to it.
                              DBMS_NETWORK_ACL_ADMIN.create_acl(
                                  acl           => 'Mimic_Pre11g_ACL.xml',
                                  description   => 'ACL that lets everyone connect to all ports of all servers',
                                  principal     => 'PUBLIC',
                                  is_grant      => TRUE,
                                  privilege     => 'connect'
                              );
                      
                              -- Also add the RESOLVE permission for everybody.
                              -- This will allow look up of all hostnames and ip addresses.
                              DBMS_NETWORK_ACL_ADMIN.add_privilege(
                                  acl         => 'Mimic_Pre11g_ACL.xml',
                                  principal   => 'PUBLIC',
                                  is_grant    => TRUE,
                                  privilege   => 'resolve'
                              );
                      
                              --Assign these privileges to all hosts and all ports
                              DBMS_NETWORK_ACL_ADMIN.assign_acl('Mimic_Pre11g_ACL.xml', '*');
                              COMMIT;
                          $END
                      END;

Open in new window


Now, as I said previously that's really not the best option.  So, instead, let's target some specific users.
I'll allow myself (SDS) to do host/ip look ups for any host.  That ACL might look like this...

BEGIN
                          --DBMS_NETWORK_ACL_ADMIN.drop_acl('my_ip_lookup_acl.xml'); 
                          DBMS_NETWORK_ACL_ADMIN.create_acl(
                              acl           => 'my_ip_lookup_acl.xml',
                              description   => 'ACL that lets users do ip/name look ups for any host',
                              principal     => 'SDS',
                              is_grant      => TRUE,
                              privilege     => 'resolve'
                          );
                      
                          DBMS_NETWORK_ACL_ADMIN.assign_acl('my_ip_lookup_acl.xml', '*');
                          COMMIT;
                      END;

Open in new window


If I want to let other users do look up as well, I simply call DBMS_NETWORK_ACL_ADMIN.add_privilege and list a new principal each time.  Do note, in each of these I specify the resolve privilege again.  Unlike user roles where you assign privileges in bulk to a bunch of users, ACL privileges are granted to each principal.  So there is some discipline required in maintaining ACL's.  This ACL, for example, is intended to be used for the resolving of host names and ip addresses; but, if someone was added with CONNECT, they'd be able to reach any host.  The bulk assignments, are in the hosts and ports associated with a list not in the individual connect/resolve privileges of each member.

BEGIN
                          DBMS_NETWORK_ACL_ADMIN.add_privilege(
                              acl         => 'my_ip_lookup_acl.xml',
                              principal   => 'LOOKUP_USER_1',
                              is_grant    => TRUE,
                              privilege   => 'resolve'
                          );
                          DBMS_NETWORK_ACL_ADMIN.add_privilege(
                              acl         => 'my_ip_lookup_acl.xml',
                              principal   => 'LOOKUP_USER_2',
                              is_grant    => TRUE,
                              privilege   => 'resolve'
                          );
                          COMMIT;
                      END;

Open in new window


Next, lets take a more controlled approach to letting a user connect to remote servers.  I will grant connect privilege to myself for my email server using the default SMTP port, 25.  In previous examples I've left out the 3rd and 4th parameters of the ASSIGN_ACL procedure  (lower_port and upper_port).  They will each default to NULL which means no limit on that end of the range.  So,  (NULL, NULL) would mean all ports,  (NULL, 1000) would mean all ports less than or equal to 1000, (400,499) would mean all ports between 400 and 499 inclusive,  (2000,null) would mean all ports greater than or equal to 2000.  In this example though, I want a single port, so the lower and upper bounds are the same, 25.

BEGIN
                          DBMS_NETWORK_ACL_ADMIN.create_acl(
                              acl           => 'my_smtp_server_acl.xml',
                              description   => 'ACL that lets me talk to the my email server',
                              principal     => 'SDS',
                              is_grant      => TRUE,
                              privilege     => 'connect'
                          );
                      
                          DBMS_NETWORK_ACL_ADMIN.assign_acl(
                              acl          => 'my_smtp_server_acl.xml',
                              HOST         => 'my_smtp_server.com',
                              lower_port   => 25,
                              upper_port   => 25
                          );
                      
                          COMMIT;
                      END;

Open in new window



Additionally, privileges can bet granted with timestamps.  So, lets say I needed to send email, but I didn't need that access in perpetuity.  In this next example I'll grant the same privilege I did above, but this time I'll only grant it for a single day:  November 1st. 2011.

BEGIN
                          DBMS_NETWORK_ACL_ADMIN.add_privilege(
                              acl           => 'my_smtp_server_acl.xml',
                              principal     => 'SDS',
                              is_grant      => TRUE,
                              privilege     => 'connect',
                              start_date    => timestamp '2011-11-01 00:00:00.00 US/Eastern',
                              end_date      => timestamp '2011-11-01 23:59:59.999999 US/Eastern'                         
                          );
                      
                          COMMIT;
                      END;

Open in new window



Removing access



Now that we've created some ACL's and assigned them, the next logical step is...how do you remove them?  The first example shows an example of DROP_ACL.  That, like a cascading delete, will remove the ACL, all principal privileges granted to it and any host/port assignments.  On a less drastic level, we can remove individual privileges.

BEGIN
                          DBMS_NETWORK_ACL_ADMIN.delete_privilege(
                              acl         => 'my_ip_lookup_acl.xml',
                              principal   => 'LOOKUP_USER_1',
                              is_grant    => TRUE,
                              privilege   => 'resolve'
                          );
                          COMMIT;
                      END;

Open in new window


We can also remove assignments.  Compared to object privileges this will look a little odd, the users (principals) will retain all of their connect and/or resolve privileges, but they be able to apply them to anything.  Sort of like having SELECT privilege but not associated with a table or view.  So, after running this block,  I would still have the CONNECT privilege granted within the ACL, but I couldn't use it because no host was assigned to connect to.

BEGIN
                          DBMS_NETWORK_ACL_ADMIN.unassign_acl(
                              acl          => 'my_smtp_server_acl.xml',
                              HOST         => 'my_smtp_server.com',
                              lower_port   => 25,
                              upper_port   => 25
                          );
                      
                          COMMIT;
                      END;

Open in new window


You may have noticed the IS_GRANT parameter on the privilege procedures above.  ACL's allow you to implement both white lists and black lists.  The previous examples showed how to create and remove white lists privileges.  Now we'll create a blacklist, you'll note the syntax is identical except the IS_GRANT is FALSE to deny a privilege from an ACL.

The "BLOCKED_USER" will be unable to do ip hostname look ups.
BEGIN
                          DBMS_NETWORK_ACL_ADMIN.add_privilege(
                              acl         => 'my_ip_lookup_acl.xml',
                              principal   => 'BLOCKED_USER',
                              is_grant    => FALSE,
                              privilege   => 'resolve'
                          );
                          COMMIT;
                      END;

Open in new window


Additional Resources


All of the ACL information is maintained within the xml based XDB schema and can be accessed by querying the dictionary and resource views in that schema.  Oracle has provided more traditional data dictionary views though to will parse the xml for you and display the results...

SELECT * FROM dba_network_acls;
                      HOST                 LOWER_PORT  UPPER_PORT   ACL                                ACLID                                                            
                      ------------------------------------------------------------------------------------------------------
                      *                                             /sys/acls/superusers.xml           1DA763004CEE455C959BD7966D901F9C
                      my_smtp_server.com   25          25           /sys/acls/my_smtp_server_acl.xml   65002399A99242AC829C612F769B7705
                      
                      
                      SELECT * FROM dba_network_acl_privileges;
                      
                      ACL                                 ACLID                               PRINCIPAL PRIVILEGE  IS_GRANT  INVERT  START_DATE                             END_DATE     
                      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                              
                      /sys/acls/my_smtp_server_acl.xml    65002399A99242AC829C612F769B7705    SDS       resolve    true      false
                      /sys/acls/my_smtp_server_acl.xml    65002399A99242AC829C612F769B7705    SDS       connect    true      false   2011/10/28 00:00:00.000000000 -04:00   2011/10/28 23:59:59.999999000 -04:00
                      /sys/acls/superusers.xml            1DA763004CEE455C959BD7966D901F9C    SDS       connect    true      false
                      /sys/acls/superusers.xml            1DA763004CEE455C959BD7966D901F9C    SDS       resolve    true      false

Open in new window


The DBMS_NETWORK_ACL_UTILITY  package also lets you do inquiries on your ACL's to compare hosts to each other and check if a host is part of a domain.  I recognize the possible uses of this package; but, in practice I've not really found it to be very useful.  As such, I will leave pursuit of that package as an exercise to the reader: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25788/d_networkacl_utl.htm


Known Issues


BUG:
There is a known bug you might encounter after upgrading your database from 10g. If you encounter the this error ORA-46105: Securityclass http://xmlns.oracle.com/plsql:network cannot be loaded. try running the following query, if it returns no rows then you may have the bug.

SELECT null
                        FROM xdb.xs$securityclass
                       WHERE EXTRACTVALUE(object_value, '/securityClass/@targetNamespace') = 'http://xmlns.oracle.com/plsql'
                         AND EXTRACTVALUE(object_value, '/securityClass/@name') = 'network';

Open in new window


The fix is basically rerunning the xdb portion of the upgrade and restarting the database.  For more information, see Oracle Support Doc ID 1168180.1.  Note, the support doc suggests a similar but different query to the one above.  You can use either, but the one I posted is slightly more efficient.

RESTRICTION:
From the Oracle Database Security Guide: You cannot import or export the access control list settings by using the Oracle Database import or export utilities such as Oracle Data Pump.

So, if you copy users from one database to another you will need to recreate your ACL's for those users.


Conclusion

Many applications will never need to use the various network services available in pl/sql.  For those that do though, ACL's are an important new tool with which to familiarize yourself.  As mentioned above, the biggest mental hurdle for me was getting used to privileges not being associated with a target as they are in the rest of Oracle.  However, once you get that straight in your head.  The syntax and maintenance is fairly straight forward.

I hope found this helpful.
Thanks for reading!
8
54,727 Views
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT

Comments (2)

Annette Wilson, MSISSr. Programmer Analyst

Commented:
Thank you sdstuber,  
This is good information.  I hope can get the dba's help on this.  

If I cannot, isn't there another email method that I can use that doesn't require SMTP?

How can I restructure the procedure and include the query data in an email for the other method?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
thank you for reading, I'm glad you liked it.

ACL's block all network traffic, so, even if you used a non smtp server, or moved to another port you would still need to have an ACL opened for you.

Your last comment seems to be about a question and not this article.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.