How to use Network Access Control Lists in Oracle

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

Part 1: https://www.experts-exchange.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
17,103 Views
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT

Comments (0)

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.