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>
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>
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>
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>
SQL> SELECT EXTRACTVALUE(DBMS_XDB.cfg_get(), '//acl-evaluation-method') FROM DUAL;
EXTRACTVALUE(DBMS_XDB.CFG_GET(),'//ACL-EVALUATION-METHOD')
--------------------------------------------------------------------------------
ace-order
SQL>
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>
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
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
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;
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>
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
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>
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.
Comments (0)