<

How to use Network Access Control Lists in Oracle

Published on
21,763 Points
11,863 Views
4 Endorsements
Last Modified:
sdstuber
Working with Network Access Control Lists in Oracle 11g (part 2)

Part 1: http://www.e-e.com/A_8429.html

Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many installations those instructions will be sufficient.  However there are some additional syntax and features to be aware of particularly when there are multiple or conflicting privileges.

Multiple privileges within a single ACL


In Part 1 I discussed a few ways in which ACL privileges differ from normal Oracle grants.  Here, I'll add another difference. Unlike a SELECT or INSERT privilege which you either have or do not have, with ACL's  you can both be granted and denied access within a single ACL.  I can think of no good reason to do this, but it is possible.  When this occurs, the first entry in the ACL determines the final privilege.

SQL> connect testuser/pa55w0rd
Connected.
SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      nice_drop('testacl1.xml');
 14      nice_drop('testacl2.xml');
 15
 16      DBMS_NETWORK_ACL_ADMIN.create_acl(
 17          acl           => 'testacl1.xml',
 18          description   => 'test resolve acl1',
 19          principal     => 'TESTUSER',
 20          is_grant      => TRUE,
 21          privilege     => 'resolve'
 22      );
 23
 24      DBMS_NETWORK_ACL_ADMIN.add_privilege(
 25          acl         => 'testacl1.xml',
 26          principal   => 'TESTUSER',
 27          is_grant    => FALSE,
 28          privilege   => 'resolve'
 29      );
 30
 31      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 32
 33      COMMIT;
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;

UTL_INADDR.GET_HOST_ADDRESS('GOOGLE.COM')
--------------------------------------------------------------------------------

74.125.225.81

SQL>

Open in new window


Now reverse it,  first deny access then allow it.

SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      nice_drop('testacl1.xml');
 14      nice_drop('testacl2.xml');
 15
 16      DBMS_NETWORK_ACL_ADMIN.create_acl(
 17          acl           => 'testacl1.xml',
 18          description   => 'test resolve acl1',
 19          principal     => 'TESTUSER',
 20          is_grant      => FALSE,
 21          privilege     => 'resolve'
 22      );
 23
 24      DBMS_NETWORK_ACL_ADMIN.add_privilege(
 25          acl         => 'testacl1.xml',
 26          principal   => 'TESTUSER',
 27          is_grant    => TRUE,
 28          privilege   => 'resolve'
 29      );
 30
 31      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 32
 33      COMMIT;
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;
SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL
       *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1


SQL>

Open in new window


You can view the ACEs for a given ACL with the following query  (I've formatted the results for ease of reading)

SQL> SELECT EXTRACT(object_value, '/').getclobval()
  2    FROM xdb.xdb$acl
  3   WHERE EXTRACTVALUE(
  4             object_value,
  5             '//a:security-class',
  6             'xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"'
  7         ) = '{http://xmlns.oracle.com/plsql}:plsql:network'
  8     AND object_id = (SELECT aclid
  9                        FROM dba_network_acls
 10                       WHERE acl = '/sys/acls/testacl1.xml');

EXTRACT(OBJECT_VALUE,'/').GETCLOBVAL()
--------------------------------------------------------------------------------
<a:acl xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd" description="test resolve acl1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
  <a:security-class xmlns:plsql="http://xmlns.oracle.com/plsql">plsql:network</a:security-class>
  <a:ace>
    <a:grant>true</a:grant>
    <a:principal>TESTUSER</a:principal>
    <a:privilege>
      <plsql:resolve xmlns:plsql="http://xmlns.oracle.com/plsql"/>
    </a:privilege>
  </a:ace>
  <a:ace>
    <a:grant>false</a:grant>
    <a:principal>TESTUSER</a:principal>
    <a:privilege>
      <plsql:resolve xmlns:plsql="http://xmlns.oracle.com/plsql"/>
    </a:privilege>
  </a:ace>
</a:acl>

SQL>

Open in new window



Explicitly assigning privilege order


In the examples above the ACE privileges were simply appended to the end of the ACL to create the ordering.  The DBMS_NETWORK_ACL_ADMIN package allows you to explicitly assign a position though. Any previous ACEs will be shifted down to accommodate the inserted entry.  In this example, we'll recreate the ACL with initial grant than insert the denial into position 1 pushing the grant down to position two. This will then cause the user to no longer have access.

SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      nice_drop('testacl1.xml');
 14      nice_drop('testacl2.xml');
 15
 16      DBMS_NETWORK_ACL_ADMIN.create_acl(
 17          acl           => 'testacl1.xml',
 18          description   => 'test resolve acl1',
 19          principal     => 'TESTUSER',
 20          is_grant      => TRUE,
 21          privilege     => 'resolve'
 22      );
 23
 24      DBMS_NETWORK_ACL_ADMIN.add_privilege(
 25          acl         => 'testacl1.xml',
 26          principal   => 'TESTUSER',
 27          is_grant    => FALSE,
 28          position    => 1,
 29          privilege   => 'resolve'
 30      );
 31
 32      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 33
 34      COMMIT;
 35  END;
 36  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;
SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL
       *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1


SQL>

Open in new window


Changing ACE evaluation method


The examples above show how the order of each ACE determines the established privileges.  This behavior is shared with other, non-network, ACL's that may be defined within the XDB system.  XDB supports multiple evaluation methods. Prior to the introduction of network acl's in 11g, the default method of evaluation was deny-trumps-grant.  That is, if you were specifically denied access with one ACE it didn't matter if you were previously or later granted access.  The deny always won.  In 11g the default evaluation method was changed to ace-order and produces the functionality shown above.  You can view the current ACE evaluation method with the following query.

SQL> SELECT EXTRACTVALUE(DBMS_XDB.cfg_get(), '//acl-evaluation-method') FROM DUAL;

EXTRACTVALUE(DBMS_XDB.CFG_GET(),'//ACL-EVALUATION-METHOD')
--------------------------------------------------------------------------------

ace-order

SQL>

Open in new window

 
The evaluation method can be changed with a simple pl/sql block to update the XDB configuration resource.

SQL> connect system/pa55w0rd
Connected.
SQL> DECLARE
  2      v_cfg XMLTYPE;
  3  BEGIN
  4      SELECT UPDATEXML(
  5                 DBMS_XDB.cfg_get(),
  6                 '/xdbconfig/sysconfig/acl-evaluation-method/text()',
  7                 'deny-trumps-grant'
  8             )
  9        INTO v_cfg
 10        FROM DUAL;
 11
 12      DBMS_XDB.cfg_update(v_cfg);
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL> SELECT EXTRACTVALUE(DBMS_XDB.cfg_get(), '//acl-evaluation-method') FROM DUAL;

EXTRACTVALUE(DBMS_XDB.CFG_GET(),'//ACL-EVALUATION-METHOD')
--------------------------------------------------------------------------------

deny-trumps-grant

SQL>

Open in new window


Now, rerun the tests above and you'll see the granted first ACE that worked previously no longer applies.

SQL> connect testuser/pa55w0rd
Connected.
SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      nice_drop('testacl1.xml');
 14      nice_drop('testacl2.xml');
 15
 16      DBMS_NETWORK_ACL_ADMIN.create_acl(
 17          acl           => 'testacl1.xml',
 18          description   => 'test resolve acl1',
 19          principal     => 'TESTUSER',
 20          is_grant      => TRUE,
 21          privilege     => 'resolve'
 22      );
 23
 24      DBMS_NETWORK_ACL_ADMIN.add_privilege(
 25          acl         => 'testacl1.xml',
 26          principal   => 'TESTUSER',
 27          is_grant    => FALSE,
 28          privilege   => 'resolve'
 29      );
 30
 31      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 32
 33      COMMIT;
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;
SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL
       *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Open in new window


Another interesting aspect of the evaluation method is that it is cached.  So, once it's been determined the same method will be used even if it's changed later on while your session is active.  In the next example the same ACL is created twice within the same session.  First with ace-order, then after testing the ACL. I logged in as SYSTEM and changed the method. TESTUSER's session can see the change but, when the ACL is dropped and recreated, the session is still using the ace-order method so the access is allowed until I login in again and force the evaluation method to be recached.

SQL> connect testuser/pa55w0rd
Connected.
SQL> SELECT EXTRACTVALUE(DBMS_XDB.cfg_get(), '//acl-evaluation-method') FROM DUAL;

EXTRACTVALUE(DBMS_XDB.CFG_GET(),'//ACL-EVALUATION-METHOD')
--------------------------------------------------------------------------------

ace-order

SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      nice_drop('testacl1.xml');
 14      nice_drop('testacl2.xml');
 15
 16      DBMS_NETWORK_ACL_ADMIN.create_acl(
 17          acl           => 'testacl1.xml',
 18          description   => 'test resolve acl1',
 19          principal     => 'TESTUSER',
 20          is_grant      => TRUE,
 21          privilege     => 'resolve'
 22      );
 23
 24      DBMS_NETWORK_ACL_ADMIN.add_privilege(
 25          acl         => 'testacl1.xml',
 26          principal   => 'TESTUSER',
 27          is_grant    => FALSE,
 28          privilege   => 'resolve'
 29      );
 30
 31      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 32
 33      COMMIT;
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;

UTL_INADDR.GET_HOST_ADDRESS('GOOGLE.COM')
--------------------------------------------------------------------------------

74.125.225.49

SQL> SELECT EXTRACTVALUE(DBMS_XDB.cfg_get(), '//acl-evaluation-method') FROM DUAL;

EXTRACTVALUE(DBMS_XDB.CFG_GET(),'//ACL-EVALUATION-METHOD')
--------------------------------------------------------------------------------

deny-trumps-grant

SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      nice_drop('testacl1.xml');
 14      nice_drop('testacl2.xml');
 15
 16      DBMS_NETWORK_ACL_ADMIN.create_acl(
 17          acl           => 'testacl1.xml',
 18          description   => 'test resolve acl1',
 19          principal     => 'TESTUSER',
 20          is_grant      => TRUE,
 21          privilege     => 'resolve'
 22      );
 23
 24      DBMS_NETWORK_ACL_ADMIN.add_privilege(
 25          acl         => 'testacl1.xml',
 26          principal   => 'TESTUSER',
 27          is_grant    => FALSE,
 28          privilege   => 'resolve'
 29      );
 30
 31      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 32
 33      COMMIT;
 34  END;
 35  /

PL/SQL procedure successfully completed.

SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;

UTL_INADDR.GET_HOST_ADDRESS('GOOGLE.COM')
--------------------------------------------------------------------------------

74.125.225.50

SQL> connect testuser/pa55w0rd
Connected.
SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;
SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL
       *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Open in new window


After testing, I have restored the evaluation method to the default ace-order with the following script and will leave it ace-order for the remainder of the tests below.

DECLARE
    v_cfg XMLTYPE;
BEGIN
    SELECT UPDATEXML(
               DBMS_XDB.cfg_get(),
               '/xdbconfig/sysconfig/acl-evaluation-method/text()',
               'ace-order'
           )
      INTO v_cfg
      FROM DUAL;

    DBMS_XDB.cfg_update(v_cfg);
END;

Open in new window



Multiple ACL evaluation


Finally, let's look at how privileges are handled between multiple ACL's.  Unfortunately the documentation on this is sparse at best.  The multi-ACL scenarios fall into two categories.  First, when the ACL's are assigned to targets of differing specificity (i.e. more or less wild carding in the hosts or ports.)  When this happens the functionality varies by version.  I could find no official documentation of this feature but saw it mentioned on Joel Kallman's blog ( http://joelkallman.blogspot.com ) and the claims were easy to test and reproduce.

In 11gR1, if you have 2 or more ACL's that are assigned to a target they are evaluated from least specific to most specific.
In 11gR2, if you have 2 or more ACL's that are assigned to a target they are evaluated from most specific to least specific.

For example, in the following example testacl1 ia assigned to HOST => '*', (i.e. "everything", not specific at all.) testacl2 is assigned to HOST=>'google.com', one specific address.  So, in 11gR1 we would expect TESTUSER would be allowed access; but in 11gR2, TESTUSER will be denied access.  The following demonstration was run on 11gR2 and confirms the expected behavior. 'google.com' is most specific and evaluated first granting access, but then '*' is evaluated next and denies acess.  In this example the denial is implicit because TESTUSER had no privileges within testacl2, the same behavior would have been observed with an explicit is_grant=>FALSE.

SQL> connect testuser/pa55w0rd
Connected.
SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      -- Create acl1 to revoke google.com , acl2 to  grant access to *
 14      nice_drop('testacl1.xml');
 15      nice_drop('testacl2.xml');
 16
 17      DBMS_NETWORK_ACL_ADMIN.create_acl(
 18          acl           => 'testacl1.xml',
 19          description   => 'test resolve acl1',
 20          principal     => 'TESTUSER',
 21          is_grant      => TRUE,
 22          privilege     => 'resolve'
 23      );
 24
 25      DBMS_NETWORK_ACL_ADMIN.create_acl(
 26          acl           => 'testacl2.xml',
 27          description   => 'test resolve acl2',
 28          principal     => 'SDS',
 29          is_grant      => TRUE,
 30          privilege     => 'resolve'
 31      );
 32
 33      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 34      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl2.xml', HOST => 'google.com');
 35
 36      COMMIT;
 37  END;
 38  /

PL/SQL procedure successfully completed.

SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;
SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL
       *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1


SQL> connect sds/pa55w0rd
Connected.
SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;

UTL_INADDR.GET_HOST_ADDRESS('GOOGLE.COM')
-------------------------------------------------------------------------------

74.125.225.51

SQL>

Open in new window



The other scenario is when the targets are identical, then which ACL wins?  I could find no documentation that determines the order but in my testing, the last ACL assigned (not necessarily created) wins.  This behavior can be shown in the examples below.  In both examples TESTUSER is granted resolve via testacl1 but is not granted anything in testacl2.  The ACL's are created in the same order in both examples, the only difference is the order in which they are assigned to the same host.

In the first example, testacl1 is assigned to * first, then testacl2 is assigned to the same host wildcard.  Since testacl2 was assigned last, it wins and TESTUSER is denied access.
SQL> connect testuser/pa55w0rd
Connected.
SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      -- Create acl1 to revoke google.com , acl2 to  grant access to *
 14      nice_drop('testacl1.xml');
 15      nice_drop('testacl2.xml');
 16
 17      DBMS_NETWORK_ACL_ADMIN.create_acl(
 18          acl           => 'testacl1.xml',
 19          description   => 'test resolve acl1',
 20          principal     => 'TESTUSER',
 21          is_grant      => TRUE,
 22          privilege     => 'resolve'
 23      );
 24
 25      DBMS_NETWORK_ACL_ADMIN.create_acl(
 26          acl           => 'testacl2.xml',
 27          description   => 'test resolve acl2',
 28          principal     => 'SDS',
 29          is_grant      => TRUE,
 30          privilege     => 'resolve'
 31      );
 32
 33      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 34      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl2.xml', HOST => '*');
 35
 36      COMMIT;
 37  END;
 38  /

PL/SQL procedure successfully completed.

SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;
SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL
       *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Open in new window


In the second example, the order of assignment is reversed. Since testacl1 was assigned last, it wins and TESTUSER is allowed access.
SQL> DECLARE
  2      PROCEDURE nice_drop(p_acl IN VARCHAR2)
  3      IS
  4      -- Drop ACL, if it was already gone, we don't care
  5      BEGIN
  6          DBMS_NETWORK_ACL_ADMIN.drop_acl(p_acl);
  7      EXCEPTION
  8          WHEN DBMS_NETWORK_ACL_ADMIN.acl_not_found
  9          THEN
 10              NULL;
 11      END;
 12  BEGIN
 13      -- Create acl1 to revoke google.com , acl2 to  grant access to *
 14      nice_drop('testacl1.xml');
 15      nice_drop('testacl2.xml');
 16
 17      DBMS_NETWORK_ACL_ADMIN.create_acl(
 18          acl           => 'testacl1.xml',
 19          description   => 'test resolve acl1',
 20          principal     => 'TESTUSER',
 21          is_grant      => TRUE,
 22          privilege     => 'resolve'
 23      );
 24
 25      DBMS_NETWORK_ACL_ADMIN.create_acl(
 26          acl           => 'testacl2.xml',
 27          description   => 'test resolve acl2',
 28          principal     => 'SDS',
 29          is_grant      => TRUE,
 30          privilege     => 'resolve'
 31      );
 32
 33      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl2.xml', HOST => '*');
 34      DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'testacl1.xml', HOST => '*');
 35      COMMIT;
 36  END;
 37  /

PL/SQL procedure successfully completed.

SQL> SELECT UTL_INADDR.get_host_address('google.com') FROM DUAL;

UTL_INADDR.GET_HOST_ADDRESS('GOOGLE.COM')
--------------------------------------------------------------------------------

74.125.225.80

SQL>

Open in new window


But, I must repeat, this behavior is not documented and, while it has been repeatable for me, there is no guarantee that it will continue to work this way.  As further "proof" that it's not reliable.  ACL/Host assignments are stored in the SYS.NET$_ACL table which doesn't have any sort of precedence indicator.  So, much like inserting 2 rows into any other table, it's common to see the second insert selected last even without an order by; but it's not reliable.  As such, I recommend trying to prevent identical host assignments between ACL's.


Conclusion


In Part 1 I gave all the basics needed to get started using Network ACL's, in this article I've extended that to show some of the more complicated scenarios and how multiple privileges and multiple lists will interact.  

I created the TESTUSER account with privileges convenient to conduct the tests above, not necessarily with privileges you'll want to mimic in production systems.

TESTUSER had the following privileges...

CREATE SESSION
SELECT ANY DICTIONARY
EXECUTE ON SYS.DBMS_NETWORK_ACL_ADMIN
SELECT ON XDB.XDB$ACL;


I hope you found both articles enjoyable and informative.
Questions, as always, are welcome.
Thanks for reading!
4
Comment
Author:sdstuber
0 Comments

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Join & Write a Comment

This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month