From Oracle 11g network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR which can be used to access external network resources, are more restricted and secured. Oracle 11g introduced Fine-Grained Access to these packages by creating an Access Control List to use any external network resource through these packages. Before this any user who had an execute privilege on these packages was able to do anything to any network resource like web and local mail servers etc. But now a user needs a little more then just an execute privilege on the network packages.
Lets talk about using ACL in oracle 11g:$ sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and grantee = 'PUBLIC';
GRANTEE TABLE_NAME PRIVILEGE
---------- -------------- -------------
PUBLIC UTL_HTTP EXECUTE
revoke execute on utl_http from public;
grant execute on utl_http to scott;
select grantee , table_name , privilege
from dba_tab_privs
where table_name = 'UTL_HTTP'
and grantee in ('PUBLIC','SCOTT')
GRANTEE TABLE_NAME PRIVILEGE
---------- -------------- -------------
SCOTT UTL_HTTP EXECUTE
SQL> conn scott/tiger
Connected.
create or replace procedure getTitle(pUrl VARCHAR2)
is
vResult CLOB;
begin
vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
dbms_output.put_line(vResult);
end;
/
SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
BEGIN getTitle('http://www.oracleflash.com'); END;
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SCOTT.GETTITLE", line 5
ORA-06512: at line 1
As you may see that even in the presence of EXECUTE privilege, SCOTT is not able to access the web page using UTL_HTTP and has encountered an error "network access denied by access control list (ACL)". This literally means that the user is being denied access by the Access Control List.
How to configure Access Control List
We need to configure an Access Control List (ACL) and grant "connect" privilege on that ACL to user SCOTT. Then we need to assign host "www.oracleflash.com" to this ACL and any other host to which user SCOTT needs access.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL()
creates a new Access Control List. Following are the parameters that it takes.
acl
=> Name of the Access Control List. This is a XML file which will be created in /sys/acls directory by default.
Description
=> Description of the ACL.
Principal
=> Name of the user or role (case sensitive) to whom the permissions are being granted or denied.
is_grant
=> TRUE or FALSE, whether to grant access or deny access.
privilege
=> connect or resolve (lowercase always). Will the user be able to connect to the network resource or just could resolve the network address.
start_date
=> Start date (optional) of the access to the user.
end_date
=> End date (optional) of the access to the user.
SQL> conn / as sysdba
Connected.
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'oracleflash.xml',
description => 'Permissions to access http://www.oracleflash.com',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Add a privilege to Access Control List
First access to the ACL to any user is granted when the ACL is created with the CREATE_ACL procedure. If any other user or role needs permission on the ACL you may user the procedure ADD_PRIVILEGE.
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE()
Add access for more users or roles in an already existing ACL. It takes similar parameters as CREATE_ACL procedure except there is no description parameter and a new parameter position which is used in ADD_PRIVILEGE but not in CREATE_ACL.
The position parameter decides the precedence of the rights for multiple users. For example we grant access to a role ORACLEFLASH at position 1, grant this role to user HR and deny access to user HR at position 2 in ACL. The user HR will still be able to use the network resource because he is granted access via role ORACLEFLASH which takes precedence in the ACL. When granting access to multiple roles and user set the precedence appropriately.
create role oracleflash;
.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'oracleflash.xml',
principal => 'ORACLEFLASH',
is_grant => TRUE,
privilege => 'connect',
position => null);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
So far we have created an ACL and have granted connect access to user SCOTT and role ORACLEFLASH on this ACL. Now is the time to assign network hosts that this ACL can be used to access. In our case the host is "www.oracleflash.com".
Assign a network host to Access Control List
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL()
assigns a network host local or remote to an ACL. It takes the following parameters:
acl
=> Name of the Access Control List.
host
=> Name of the host.
lower_port
=> Lower port (optional) from the range of ports allowed on this host.
upper_port
=> Upper port (optional) from the range of ports allowed on this host
Default for lower and upper port is null, which means all ports can be used on this host. And if you provide a port in lower_port and null in upper_port oracle assumes the upper_port=lower_port.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'oracleflash.xml',
host => '*.oracleflash.com');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'oracleflash.xml',
host => '*.oracle.com');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
NOTE: you may assign multiple hosts to one ACL, but you can't assign one host to multiple ACLs. If you do that then the previous assignment will be removed and new will become in effect.
Host Assignments:
Host assignment can be done in many ways. For example if you assign a host to an ACL like www.oracleflash.com, the users can only access www.oracleflash.com. But if you assign a host like *.oracleflash.com, the users can assign any sub-domain on the oracleflash.com. And *.com will grant access to the whole web using .com domains. You need to be careful with this as you may be granting access to more servers then you should.
DBMS_NETWORK_ACL_UTILITY.DOMAINS()
package contains functions to help determine possible matching domains. The DOMAINS table function returns all possible references against a host, that may be specified in ASSIGN_ACL procedure, in order of precedence.
SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.oracleflash.com'));
COLUMN_VALUE
------------------------------------
www.oracleflash.com
*.oracleflash.com
*.com
*
SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('192.168.0.132'));
COLUMN_VALUE
------------------------------------
192.168.0.132
192.168.0.*
192.168.*
192.*
*
The precedence here means that if you have assigned all these hosts to the ACLs then which host entry will take precedence on others. The above query returns results in order of precedence.
We can verify the ACL's host assignment and privileges via two dictionary views, DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES.
column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
/sys/acls/oracleflash.xml *.oracle.com
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
ACL PRINCIPAL PRIVILEGE IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml SCOTT connect true
/sys/acls/oracleflash.xml ORACLEFLASH connect true
Lets now see if the access is enabled or not.
SQL> conn scott/tiger
Connected.
SQL> set serveroutput on
SQL> execute getTitle('http://www.oracleflash.com');
OracleFlash.com: Oracle Articles, Tutorials, Step by Step Install Guides, Scripts.
PL/SQL procedure successfully completed.
SQL> execute getTitle('http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm');
Configuring Privilege and Role Authorization
PL/SQL procedure successfully completed.
The user SCOTT is able to access both the oracleflash.com and oracle.com hosts. Lets see how the ACL grant to a role works.
SQL> conn / as sysdba
Connected.
SQL> grant execute on utl_http to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual;
select substr(utl_http.request('http://www.oracleflash.com'),1,30) from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
Even after granting the EXECUTE privilege on UTL_HTTP to user HR, it is not able to access the host www.oracleflash.com. This is because the user HR has no access on the ACL we created for oracleflash.com. Now we will grant the role ORACLEFLASH to user HR, which has access on the ACL for oracleflash.com and see what happens.
SQL> conn / as sysdba
Connected.
SQL> grant oracleflash to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select substr(utl_http.request('http://www.oracleflash.com'),1,112) oracleflash
2 from dual;
ORACLEFLASH
-----------------------------------------------------------------------------------------
This time the HR can access the web page as it has the role ORACLEFLASH, which has access on the ACL.
Cleaning Up the Access Control List
Remove a host from Access Control List
Following procedure can be used to remove a host from the ACL.
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
/sys/acls/oracleflash.xml *.oracle.com
BEGIN
DBMS_NETWORK_ACL_ADMIN.unassign_acl (
acl => 'oracleflash.xml',
host => '*.oracle.com');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
Delete a privilege from Access Control List
Following procedure can be used to delete a privilege from the ACL.
SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
ACL PRINCIPAL PRIVILEGE IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml SCOTT connect true
/sys/acls/oracleflash.xml ORACLEFLASH connect true
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => 'oracleflash.xml',
principal => 'ORACLEFLASH',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
ACL PRINCIPAL PRIVILEGE IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/oracleflash.xml SCOTT connect true
Drop an Access Control List
Following procedure can be used to drop the ACL.
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL HOST LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/oracleflash.xml *.oracleflash.com
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
acl => 'oracleflash.xml');
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
no rows selected
Creating ACL for SMTP server:
BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
acl => 'mailserver_acl.xml',
host => 'CAINCCHX01.EG01.yahoo.net',
lower_port => 25,
upper_port => 25);
END;
/
COMMIT;
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl('mailserver_acl.xml');
END;
/
commit;
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'mailserver_acl.xml',
description => 'ACL that lets me talk to the my email server',
principal => 'PPM_USER',
is_grant => TRUE,
privilege => 'connect'
);
/
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'mailserver_acl.xml',
HOST => 'CAINCCHX01.EG01.Yahoo.net',
lower_port => 25,
upper_port => 25
);
COMMIT;
END;
/
References:http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html
http://oracledbajourney.blogspot.com/2012/09/ora-24247-network-access-denied-by.html
