This post describes the package DBMS_NETWORK_ACL_ADMIN (new to 11.x) with some examples on how to manually set and check privileges. With ACL’s, Oracle offers more fine-grained access control for users to access external network resources.
The packages UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc. allow communication beyond the database server to the outside world, but when granted access, all hosts can be accessed. This can be interpreted as a security flaw as no login is required when using UTL_TCP for example. DBA’s are advised to revoke the execute privileges from public on these kind of packages.
Since Oracle 11g, the Access Control List is introduced. You not only can control who has access to these packages by granting, but now you can also control which resources they can call.We can control accessibility based on host and port number.
In summary these are the steps:
1. Create an ACL – setting the privilege required for the user.
2. Assign the ACL to a network.
3. Test the UTL_ package.
Create an ACL
The ACL is an XML file which lists the permissions given to user(s). This XML is stored in Oracle XML DB. Ensure this is installed. Login as “SYS AS SYSDBA”. Start by creating a test user
CREATE USER [USER] IDENTIFIED BY [PWD]; GRANT CONNECT TO [USER];
Create an ACL – and give ‘connect’ privilege to [USER] (more about the privileges below). Notice the COMMIT is required to save the changes.
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'ACL_TEST.xml',
description => 'ACL FOR TEST PURPOSES',
principal => [USER],
is_grant => true,
privilege => 'connect',
start_date => null,
end_date => null );
COMMIT;
END;
/
Here,
acl – Name of the ACL file (which is stored as an XML file in “/sys/acls” which is defined in XDB). Important this is CASE SENSITIVE.
principal – database user or role you want to grant/deny privilege to.
is_grant – true(grant) or false(deny).
privilege – Either “connect” or “resolve” (this is CASE SENSITIVE). Connect is for external networks and required when using UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL. Resolve is for UTL_INADDR which resolves hostnames into IP addresses (or vice versa). There are other values, for example when using wallets, “use-passwords” to give the user permission to use passwords in the wallet. “use-client-certificates” to authenticate the user with a client certificate in the wallet.
start_date and end_date are optional.
Verify if ACL is created with correct permissions
Check that the ACL is created and connect permission is granted for your user using “CHECK_PRIVILEGE”.
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('ACL_TEST.xml', [USER], 'connect'),1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual
/
PRIVILE
-------
GRANTED
Adding additional privileges using ADD_PRIVILEGE (Optional)
ADD_PRIVILEGE can be used to add privileges for other users. It can also be used to add the same permission to the same user. For example:
To Add ‘connect’ privilege to our user [USER] but deny access. You would first add another ACE (Access Control Entity). Note the optional ‘position’ parameter allows you to define additional privileges to a user.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ACL_TEST.xml',
principal => [USER],
is_grant => false,
privilege => 'connect',
position => 1);
COMMIT;
END;
/
--use check_privilege as show above.
PRIVILE
-------
DENIED
This is correct as a second ACE was added. Position “Null” is the default and position 1 overrides the same privilege but with the value ‘false’. The privileges are chosen from the highest value (for example; 1 in this case).
– Be sure to delete that last ACE just added – to restore the initial privilege (true) that we had on ‘connect’.
BEGIN
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(acl => 'ACL_TEST.xml',
principal => ,
is_grant => false,
privilege => 'connect');
COMMIT;
END;
/
Optional: To continue this demo do not drop the ACL. This just shows the syntax should it be required
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => 'ACL_TEST.xml' );
COMMIT;
END;
/
Assign the ACL to a Network
The ACL needs to be assigned to a network host. The example below uses host www.abc.com. For UTL_SMTP this would be your mail server host.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'ACL_TEST.xml',
host => 'www.abc.com',
lower_port => null,
upper_port => null);
COMMIT;
END;
/
Here,
host – any host can only have one ACL assigned- but wildcards can be used for the domain or within IP Addresses. Attempting to assign another ACL to the same host will replace the existing ACL. There is a precedence if you define a host of “www.abc.com” the ACL used will take precedence over any that are assigned to “*.abc.com” which in turn takes precedence over “*” the same is true for IP Addresses. e.g. An ACL for “1.2.3.4” will be used before “1.*” or “*”
Optional – To continue this demo do not unassign the ACL. This just shows the syntax should it be required
BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
acl => 'ACL_TEST.xml',
host => 'www.abc.com',
lower_port => null,
upper_port => null);
COMMIT;
END;
/
– Check if the ACL has been assigned:
col host format a30 col acl format a30 set pagesize 999 SELECT host, acl, lower_port, upper_port FROM DBA_NETWORK_ACLS / HOST ACL LOWER_PORT ------------------------------ ------------------------------ ---------- UPPER_PORT ---------- www.abc.com /sys/acls/ACL_TEST.xml myproxy.abc.com /sys/acls/ACL_TEST.xml
– Additionally you can check privileges associated with an ACL using the view “DBA_NETWORK_ACL_PRIVILEGES” or for the user using “USER_NETWORK_ACL_PRIVILEGES”:
column acl format a30
column principal format a20
SELECT acl,
principal,
privilege,
is_grant,
to_char(start_date) ,
to_char(end_date)
FROM dba_network_acl_privileges
/
ACL PRINCIPAL PRIVILE IS_GRANT
------------------------------ -------------------- ------- --------------------
TO_CHAR(START_DATE)
----------------------------------------------------------------------
TO_CHAR(END_DATE)
----------------------------------------------------------------------
/sys/acls/ACL_TEST.xml USER connect true
Finally, this is an alternative select which checks your privilege via an ACLID:
col acl format a30
col host format a30
SELECT acl,
host,
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, , 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls
/
ACL HOST PRIVILE
------------------------------ -------------------- -------
/sys/acls/ACL_TEST.xml www.abc.com GRANTED
/sys/acls/ACL_TEST.xml myproxy.abc.com GRANTED
Testing the ACL
A simple test using UTL_HTTP. Note: change the proxy server to reflect the correct value.
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(32200);
BEGIN
UTL_HTTP.SET_PROXY('myproxy.abc.com','uk.abc.com');
req := UTL_HTTP.BEGIN_REQUEST('http://www.abc.com');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
--
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
begin
DBMS_OUTPUT.PUT_LINE('Finished');
UTL_HTTP.END_RESPONSE(resp);
end;
END;
/