<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

How to use Access Control Lists in Oracle

Published on
53,545 Points
38,345 Views
7 Endorsements
Last Modified:
Awarded
Working with Network Access Control Lists in Oracle 11g (part 1)

Part 2: http://www.e-e.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!
7
Comment
Author:sdstuber
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
 

Administrative Comment

by:mbizup
Awarded Editors' Choice.

mbizup
EE Page Editor
0
 

Expert Comment

by:Annette Wilson, MSIS
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?
0
 
LVL 74

Author Comment

by:sdstuber
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.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Join & Write a Comment

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month