8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Fine-Grained Access to Network Services Enhancements in Oracle Database 12c Release 1
Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP
, UTL_SMTP
, UTL_MAIL
, UTL_HTTP
and UTL_INADDR
), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduced fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants. Oracle provide the DBMS_NETWORK_ACL_ADMIN
and DBMS_NETWORK_ACL_UTILITY
packages to allow ACL management from PL/SQL.
Oracle Database 12c has deprecated many of the procedures and functions in the DBMS_NETWORK_ACL_ADMIN
package, replacing them with new procedures and functions. We still have the concept of Access Control Lists (ACLs), but these are often created implicitly when adding an Access Control Entry (ACE), which is similar to adding privileges using the previous API. The biggest change is an Access Control Entry can be limited to specific PL/SQL APIs (UTL_TCP
, UTL_INADDR
, UTL_HTTP
, UTL_SMTP
, and UTL_MAIL
). In the previous incarnation, once a port was opened for a user, it was accessible to all APIs. This gives a greater level of control.
Although deprecated, the old functionality is retained for backwards compatibility, but it should be avoided as it is inferior to the new functionality.
- Setup
- Append an Access Control Entry (ACE)
- Create New ACL based on an Existing ACL
- Checking Privileges
- Test the ACL
- Other Security Considerations
- Open ACE
- Parameter Definitions
Related articles.
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.
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;
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 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
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.
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; /
Once the host ACE is appended, we can see the details are visible using the old DBA_NETWORK_ACLS
and DBA_NETWORK_ACL_PRIVILEGES
views, which are deprecated in 12c.
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>
We should really use the new DBA_HOST_ACLS
and DBA_HOST_ACES
views.
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>
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 DBA_HOST_ACES
view, but there is still only a single host ACL.
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>
Host ACEs are removed using the DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE
procedure. The REMOVE_EMPTY_ACL
parameter determines if unused host ACLs should be removed, as shown below.
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>
Create New ACL based on an Existing ACL
You can create a new ACL based on an existing one using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACL
procedure.
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>
Notice we have two ACLs with similar ACEs associated with them.
Checking Privileges
In addition to the ACL and ACE views, privileges can be checked using the CHECK_PRIVILEGE
and CHECK_PRIVILEGE_ACLID
functions of the DBMS_NETWORK_ACL_ADMIN
package. The are deprecated, but are still useful.
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>
The DBMS_NETWORK_ACL_UTILITY
package contains functions to help determine possible matching domains. The DOMAINS
table function returns a collection of all possible references that may affect the specified host, domain, IP address or subnet, in order of precedence.
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>
The DOMAIN_LEVEL
function returns the level of the specified host, domain, IP address or subnet.
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>
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.
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>
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 UTL_HTTP
package to both users, then attempts to access a web page from each user.
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>
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.
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>
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.
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 UTL_TCP
, UTL_SMTP
, UTL_MAIL
and UTL_HTTP
. 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.
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.
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; /
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.
host
: Any valid host name or IP address. Wildcards are allowed.lower_port
: Specific port number, or lower part of a range of ports.upper_port
: Upper part of a range of ports. If NULL, it defaults to thelower_port
value.ace
: The access control entry, defined using theXS$ACE_TYPE
type.
The XS$ACE_TYPE
type has the following definition.
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
That looks a little complicated, but it most of the time you will only be using something like the following.
xs$ace_type(privilege_list => xs$name_list('privilege1', 'privilege2'), principal_name => 'user-or-role', principal_type => xs_acl.ptype_db)
privilege_list
: The list of privileges available to the ACE.principal_name
: The database user the ACE applies to.principal_type
: You will always useXS_ACL.PTYPE_DB
for these network ACEs as they apply to users and roles.
The privilege_list
specifies one or more privileges in a comma separated list. The available privileges are shown below.
http
: Access restricted to theUTL_HTTP
package and theHttpUriType
type.http_proxy
: Needed in conjunction withhttp
if HTTP access is via a proxy.smtp
: Access restricted to theUTL_SMTP
andUTL_MAIL
packages.resolve
: Access restricted to theUTL_INADDR
packages.connect
: Opens access to theUTL_TCP
,UTL_SMTP
,UTL_MAIL
,UTL_HTTP
, andDBMS_LDAP
packages and theHttpUriType
type.jdwp
: Enables Java Debug Wire Protocol debugging operations.
For more information see:
- Managing Fine-Grained Access in PL/SQL Packages and Types
- DBMS_NETWORK_ACL_ADMIN
- DBMS_NETWORK_ACL_UTILITY
- DBA_HOST_ACLS
- DBA_HOST_ACES
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
Hope this helps. Regards Tim...