DBA Hub

📋Steps in this guide1/8

Fine-Grained Access to Network Services Enhancements in Oracle Database 12c Release 1

Take control of the external services accessed by the <code>UTL_TCP</code>, <code>UTL_INADDR</code>, <code>UTL_HTTP</code>, <code>UTL_SMTP</code>, and <code>UTL_MAIL</code> packages in Oracle 12c and above.

oracle 12cconfigurationintermediate
by OracleDba
16 views
1

Setup

In a multitenant environment, Access Control Entries (ACEs) can be created at the CDB or PDB level. For the examples in this article, all the host ACLs and host ACEs will be created at the PDB level. The following code creates two test users in a PDB.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
CONN sys@pdb1 AS SYSDBA

CREATE USER test1 IDENTIFIED BY test1;
GRANT CONNECT TO test1;

CREATE USER test2 IDENTIFIED BY test2;
GRANT CONNECT TO test2;
2

Append an Access Control Entry (ACE)

You will never create a host ACL directly. Instead, they are implicitly created when you append a host Access Control Entry (ACE) using the procedure. If you append a new ACE to a host that has no existing host ACL, a new host ACL is implicitly created. If the host already has an ACL, the new host ACE will be appended to the existing host ACL. Once the host ACE is appended, we can see the details are visible using the old and views, which are deprecated in 12c. We should really use the new and views. For the rest of the article, these general queries will be replaced with calls to the host_acls.sql and host_aces.sql scripts. We can append another host ACE to the same host ACL by referencing the same host. Notice how we get two entries in the view, but there is still only a single host ACL. Host ACEs are removed using the procedure. The parameter determines if unused host ACLs should be removed, as shown below.

Code/Command (click line numbers to comment):

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
CONN sys@pdb1 AS SYSDBA

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'oracle-base.com', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'test1',
                              principal_type => xs_acl.ptype_db)); 
END;
/

SET LINESIZE 150

COLUMN host FORMAT A40
COLUMN acl FORMAT A50

SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls
ORDER BY host;

HOST                                     LOWER_PORT UPPER_PORT ACL
---------------------------------------- ---------- ---------- --------------------------------------------------
oracle-base.com                                  80         80 NETWORK_ACL_02B9BC669CA5110CE0536638A8C05D8A

1 row selected.

SQL>


SET LINESIZE 150

COLUMN acl FORMAT A50
COLUMN principal FORMAT A20
COLUMN privilege FORMAT A10

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges
ORDER BY acl, principal, privilege;

ACL                                                PRINCIPAL            PRIVILEGE  IS_GR START_DATE           END_DATE
-------------------------------------------------- -------------------- ---------- ----- -------------------- --------------------
NETWORK_ACL_02B9BC669CA5110CE0536638A8C05D8A       TEST1                http       true

1 row selected.

SQL>

SET LINESIZE 150

COLUMN host FORMAT A20
COLUMN acl_owner FORMAT A10

SELECT HOST,
       LOWER_PORT,
       UPPER_PORT,
       ACL,
       ACLID,
       ACL_OWNER
FROM   dba_host_acls
ORDER BY host;

HOST                 LOWER_PORT UPPER_PORT ACL                                                ACLID            ACL_OWNER
-------------------- ---------- ---------- -------------------------------------------------- ---------------- ----------
oracle-base.com              80         80 NETWORK_ACL_02B9BC669CA5110CE0536638A8C05D8A       000000008000274C SYS

1 row selected.

SQL>


COLUMN host FORMAT A20
COLUMN start_date FORMAT A11
COLUMN end_date FORMAT A11

SELECT host,
       lower_port,
       upper_port,
       ace_order,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date,
       grant_type,
       inverted_principal,
       principal,
       principal_type,
       privilege
FROM   dba_host_aces
ORDER BY host, ace_order;

HOST                 LOWER_PORT UPPER_PORT  ACE_ORDER START_DATE  END_DATE    GRANT INV PRINCIPAL            PRINCIPAL_T PRIVILEGE
-------------------- ---------- ---------- ---------- ----------- ----------- ----- --- -------------------- ----------- ----------
oracle-base.com              80         80          1                         GRANT NO  TEST1                DATABASE    HTTP

1 row selected.

SQL>

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'oracle-base.com', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'test2',
                              principal_type => xs_acl.ptype_db)); 
END;
/


SQL> @host_acls

HOST                 LOWER_PORT UPPER_PORT ACL                                                ACLID            ACL_OWNER
-------------------- ---------- ---------- -------------------------------------------------- ---------------- ----------
oracle-base.com              80         80 NETWORK_ACL_02B9BC669CA5110CE0536638A8C05D8A       000000008000274C SYS

1 row selected.

SQL>


SQL> @host_aces

HOST                 LOWER_PORT UPPER_PORT  ACE_ORDER START_DATE  END_DATE    GRANT INV PRINCIPAL            PRINCIPAL_T PRIVILEGE
-------------------- ---------- ---------- ---------- ----------- ----------- ----- --- -------------------- ----------- ----------
oracle-base.com              80         80          1                         GRANT NO  TEST1                DATABASE    HTTP
oracle-base.com              80         80          2                         GRANT NO  TEST2                DATABASE    HTTP

2 rows selected.

SQL>

BEGIN
  DBMS_NETWORK_ACL_ADMIN.remove_host_ace (
    host             => 'oracle-base.com', 
    lower_port       => 80,
    upper_port       => 80,
    ace              => xs$ace_type(privilege_list => xs$name_list('http'),
                                    principal_name => 'test2',
                                    principal_type => xs_acl.ptype_db),
    remove_empty_acl => TRUE); 
END;
/


SQL> @host_aces

HOST                 LOWER_PORT UPPER_PORT  ACE_ORDER START_DATE  END_DATE    GRANT INV PRINCIPAL            PRINCIPAL_T PRIVILEGE
-------------------- ---------- ---------- ---------- ----------- ----------- ----- --- -------------------- ----------- ----------
oracle-base.com              80         80          1                         GRANT NO  TEST1                DATABASE    HTTP

1 row selected.

SQL>


BEGIN
  DBMS_NETWORK_ACL_ADMIN.remove_host_ace (
    host             => 'oracle-base.com', 
    lower_port       => 80,
    upper_port       => 80,
    ace              => xs$ace_type(privilege_list => xs$name_list('http'),
                                    principal_name => 'test1',
                                    principal_type => xs_acl.ptype_db),
    remove_empty_acl => TRUE); 
END;
/


SQL> @host_aces

no rows selected

SQL> @host_acls

no rows selected

SQL>
3

Create New ACL based on an Existing ACL

You can create a new ACL based on an existing one using the procedure. Notice we have two ACLs with similar ACEs associated with them.

Code/Command (click line numbers to comment):

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
CONN sys@pdb1 AS SYSDBA

-- Create an ACL by appending 2 ACEs.
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'oracle-base.com', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'test1',
                              principal_type => xs_acl.ptype_db)); 

  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'oracle-base.com', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'test2',
                              principal_type => xs_acl.ptype_db)); 

END;
/


SQL> @host_acls

HOST                 LOWER_PORT UPPER_PORT ACL                                                ACLID            ACL_OWNER
-------------------- ---------- ---------- -------------------------------------------------- ---------------- ----------
oracle-base.com              80         80 NETWORK_ACL_3BC62BD3BC2A0D82E0538638A8C026C7       0000000080002762 SYS

1 row selected.

SQL> @host_aces

HOST                 LOWER_PORT UPPER_PORT  ACE_ORDER START_DATE  END_DATE    GRANT INV PRINCIPAL                      PRINCIPAL_T PRIVILEGE
-------------------- ---------- ---------- ---------- ----------- ----------- ----- --- ------------------------------ ----------- ------------------------------
oracle-base.com              80         80          1                         GRANT NO  TEST1                          DATABASE    HTTP
oracle-base.com              80         80          2                         GRANT NO  TEST2                          DATABASE    HTTP

2 rows selected.

SQL>


-- Create a new ACL with ACEs based on those of the existing ACL we just created.
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACL (
    host       => 'www.oracle-base.com',
    lower_port => 80,
    upper_port => 80,
    acl        => 'NETWORK_ACL_3BC62BD3BC2A0D82E0538638A8C026C7');
END;
/


SQL> @host_acls

HOST                 LOWER_PORT UPPER_PORT ACL                                                ACLID            ACL_OWNER
-------------------- ---------- ---------- -------------------------------------------------- ---------------- ----------
oracle-base.com              80         80 NETWORK_ACL_3BC62BD3BC2A0D82E0538638A8C026C7       0000000080002762 SYS
www.oracle-base.com          80         80 NETWORK_ACL_3BC62BD3BC2B0D82E0538638A8C026C7       0000000080002765 SYS

2 rows selected.

SQL> @host_aces

HOST                 LOWER_PORT UPPER_PORT  ACE_ORDER START_DATE  END_DATE    GRANT INV PRINCIPAL                      PRINCIPAL_T PRIVILEGE
-------------------- ---------- ---------- ---------- ----------- ----------- ----- --- ------------------------------ ----------- ------------------------------
oracle-base.com              80         80          1                         GRANT NO  TEST1                          DATABASE    HTTP
oracle-base.com              80         80          2                         GRANT NO  TEST2                          DATABASE    HTTP
www.oracle-base.com          80         80          1                         GRANT NO  TEST1                          DATABASE    HTTP
www.oracle-base.com          80         80          2                         GRANT NO  TEST2                          DATABASE    HTTP

4 rows selected.

SQL>
4

Checking Privileges

In addition to the ACL and ACE views, privileges can be checked using the and functions of the package. The are deprecated, but are still useful. The package contains functions to help determine possible matching domains. The table function returns a collection of all possible references that may affect the specified host, domain, IP address or subnet, in order of precedence. The function returns the level of the specified host, domain, IP address or subnet. These functions may be useful for when querying the ACL views for possible matches to a specific host, domain, IP address or subnet. The following examples use two different methods to determine if the user TEST1 has access to the "http" and "resolve" privileges.

Code/Command (click line numbers to comment):

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
CONN sys@pdb1 AS SYSDBA

SELECT DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege('NETWORK_ACL_02B9BC669CA7110CE0536638A8C05D8A', 'TEST1', 'http'),
         1, 'GRANTED', 0, 'DENIED', 'DENIED') privilege 
FROM dual;

PRIVILE
-------
GRANTED

1 row selected.

SQL> 


COLUMN acl FORMAT A50
COLUMN host FORMAT A30

SELECT acl,
       host,
       DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TEST2', 'http'),
         1, 'GRANTED', 0, 'DENIED', 'DENIED') privilege 
FROM   dba_network_acls;

ACL                                                HOST                           PRIVILEGE
-------------------------------------------------- ------------------------------ ----------
NETWORK_ACL_02B9BC669CA6110CE0536638A8C05D8A       www.oracle-base.com            DENIED
NETWORK_ACL_02B9BC669CA7110CE0536638A8C05D8A       oracle-base.com                DENIED

2 rows selected.

SQL>

SELECT *
FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('www.oracle-base.com'));
         
COLUMN_VALUE
--------------------------------------------------------------------------------
www.oracle-base.com
*.oracle-base.com
*.com
*

4 rows selected.

SQL>

SELECT *
FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('192.168.2.3'));

COLUMN_VALUE
-------------------------------
192.168.2.3
192.168.2.*
192.168.*
192.*
*

5 rows selected.

SQL>

SELECT DBMS_NETWORK_ACL_UTILITY.domain_level('www.oracle-base.com')
FROM   dual;

DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL('WWW.ORACLE-BASE.COM')
------------------------------------------------------------
                                                           3

1 row selected.

SQL> 

SELECT DBMS_NETWORK_ACL_UTILITY.domain_level('192.168.2.3')
FROM   dual;

DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL('192.168.2.3')
----------------------------------------------------
                                                   4

1 row selected.

SQL>

SET LINESIZE 150

SELECT host,
       lower_port,
       upper_port,
       acl,
       DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid,  'TEST1', 'http'),
         1, 'GRANTED', 0, 'DENIED', 'DENIED') PRIVILEGE
FROM   dba_network_acls
WHERE  host IN (SELECT *
                FROM   TABLE(DBMS_NETWORK_ACL_UTILITY.domains('www.oracle-base.com')))
ORDER BY 
       DBMS_NETWORK_ACL_UTILITY.domain_level(host) desc, lower_port, upper_port;
  
HOST                           LOWER_PORT UPPER_PORT ACL                                                PRIVILEGE
------------------------------ ---------- ---------- -------------------------------------------------- ----------
www.oracle-base.com                    80         80 NETWORK_ACL_02B9BC669CA6110CE0536638A8C05D8A       GRANTED

1 row selected.

SQL>


SELECT host,
       lower_port,
       upper_port,
       acl,
       DECODE(
         DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'TEST1', 'resolve'),
         1, 'GRANTED', 0, 'DENIED', 'DENIED') privilege
FROM  (SELECT host, acl, aclid, lower_port, upper_port,
              DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('oracle-base.com', host) AS precedence
       FROM   dba_network_acls)
WHERE precedence > 0
ORDER BY precedence DESC, lower_port nulls LAST;

HOST                           LOWER_PORT UPPER_PORT ACL                                                PRIVILEGE
------------------------------ ---------- ---------- -------------------------------------------------- ----------
oracle-base.com                        80         80 NETWORK_ACL_02B9BC669CA7110CE0536638A8C05D8A       DENIED

1 row selected.

SQL>
5

Test the ACL

The TEST1 user has a host ACE, but the TEST2 users does not. This means we can test the ACL functionality by comparing their responses to calls to external network services. The following code grants execute permission on the package to both users, then attempts to access a web page from each user. From this we can see that the TEST1 user was able to access the web page, while the TEST2 user was denied access by the ACL. The default action of the server is to deny access to external network service, as shown by the following test on a new user. This may cause some confusion when upgrading databases that access external network services from 10g to 12c. In these situations, it will be necessary to implement suitable access control lists before your original functionality is possible.

Code/Command (click line numbers to comment):

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
CONN sys@pdb1 AS SYSDBA
GRANT EXECUTE ON UTL_HTTP TO test1, test2;

CONN test1/test1@pdb1

DECLARE
  l_url            VARCHAR2(50) := 'http://oracle-base.com';
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
BEGIN
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(l_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);
  UTL_HTTP.end_response(l_http_response);
END;
/

PL/SQL procedure successfully completed.

SQL>

CONN test2/test2@pdb1

DECLARE
  l_url            VARCHAR2(50) := 'http://oracle-base.com';
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
BEGIN
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(l_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);
  UTL_HTTP.end_response(l_http_response);
END;
/
DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at line 7

SQL>

CONN sys@pdb1 AS SYSDBA

CREATE USER test3 IDENTIFIED BY test3;
GRANT CONNECT TO test3;
GRANT EXECUTE ON UTL_HTTP TO test3;

CONN test3/test3@pdb1

DECLARE
  l_url            VARCHAR2(50) := 'http://oracle-base.com';
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
BEGIN
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(l_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);
  UTL_HTTP.end_response(l_http_response);
END;
/
DECLARE
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at line 7

SQL>
6

Other Security Considerations

Oracle 12c has added a new level of granularity to the security of ACLs/ACEs, so the following comments are not directly relevant, but I think it's still worth going over them in case anyone is reading this without reading the 11g article first. Pete Finnigan commented on his blog and in his security presentations about the fact that the ACLs are not tied to a specific package. This means opening a port on a server with the 'connect' privilege makes it accessible by , , and . With this in mind there are some things to consider: - The use of fine-grained access to network services is not an excuse to ignore basic security measures, like revoking unnecessary privileges on network service related packages. - Control over the services you make available is possible by limiting access to the specific ports. If you only need HTTP access to port 80, specify the port rather than opening access to all ports on the server. - Wildcards can be dangerous as you may be granting access to more servers that you should. - You must protect your ACLs. If people can alter them, they become useless as a protection mechanism. Prevent direct access to the ACLs in the XML DB repository and make sure users don't have access to the management APIs. Thanks to Pete Finnigan for his input.
7

Open ACE

From a security standpoint, it's not a good idea to allow complete network access from the database, but for testing features I sometimes find it useful to create an open ACE for a user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
CONN sys@pdb1 AS SYSDBA

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '*', 
    lower_port => 1,
    upper_port => 9999,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'test1',
                              principal_type => xs_acl.ptype_db)); 
END;
/
8

Parameter Definitions

The parameters used in the procedures and functions above should be self explanatory, but we will cover them briefly here in case they are not obvious to you. - : Any valid host name or IP address. Wildcards are allowed. - : Specific port number, or lower part of a range of ports. - : Upper part of a range of ports. If NULL, it defaults to the value. - : The access control entry, defined using the type. The type has the following definition. That looks a little complicated, but it most of the time you will only be using something like the following. - : The list of privileges available to the ACE. - : The database user the ACE applies to. - : You will always use for these network ACEs as they apply to users and roles. The specifies one or more privileges in a comma separated list. The available privileges are shown below. - : Access restricted to the package and the type. - : Needed in conjunction with if HTTP access is via a proxy. - : Access restricted to the and packages. - : Access restricted to the packages. - : Opens access to the , , , , and packages and the type. - : Enables Java Debug Wire Protocol debugging operations. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
FINAL CONSTRUCTOR FUNCTION XS$ACE_TYPE RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PRIVILEGE_LIST                 XS$NAME_LIST            IN
 GRANTED                        BOOLEAN                 IN     DEFAULT
 INVERTED                       BOOLEAN                 IN     DEFAULT
 PRINCIPAL_NAME                 VARCHAR2                IN
 PRINCIPAL_TYPE                 PLS_INTEGER             IN     DEFAULT
 START_DATE                     TIMESTAMP WITH TIME ZONE IN     DEFAULT
 END_DATE                       TIMESTAMP WITH TIME ZONE IN     DEFAULT

xs$ace_type(privilege_list => xs$name_list('privilege1', 'privilege2'),
            principal_name => 'user-or-role',
            principal_type => xs_acl.ptype_db)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!