How to use Access Control Lists in Oracle: Oracle, ORA-24247, ACL DBMS_NETWORK_ACL

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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all 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';

1:
2:
3:
4:

Select all 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!

How to use Access Control Lists in Oracle

Granting access

Removing access

Additional Resources

Known Issues

Conclusion

Oracle

ORA-24247

ACL DBMS_NETWORK_ACL_ADMIN

Oracle Database

About The Author

More Articles From sdstuber

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Oracle Database Experts