This manuel describes the solution ORA-24247: network access denied by access control list (ACL).
for the ACL’ss offers oracle more fine-grained access control for users who have access to external resources.
The packages UTL_MAIL , UTL_SMTP , UTL_HTTP , UTL_TCP etc. provide the ability to communicate to the outside world. When there are rights, data can be created from the database connection to all hosts. This can be seen as a security hole because there is no login is required . Since Oracle 11g , the Access Control List is introduced. You can not only control who has access to these packages, but you can also determine which resources they may comminceren .
For example, when a user is allowed to send emails through UTL_MAIL , you can also determine which server it should gebeuren.Ik UTL_MAIL will use the package as an example When a user does not have permission to email appears the error message:
ORA-24247: network access denied by access control list (ACL).
|
1 2 3 4 5 6 7 |
SQL> connect scott/tiger Connected. begin utl_mail.send( sender => 'scott@tiger.com' recipients => 'john@doe.org' message=> ' Hello World' ); |
This is because the user SCOTT has no rights to connect to the mail or SMTP servers. The user must be added to the ACL
Create ACL and privilege
The ACL is created as a file and manages the process of handing out rights and privileges. First we are going to create an ACL as SYS or another user with the right to execute DBMS_NETWORK_ACL_ADMIN . This file hold the rights to . You can add as many rights to this file.
|
1 2 3 4 5 6 7 8 9 10 |
begin dbms_network_acl_admin.create_acl ( acl => 'utl_mail.xml', description => 'Allow mail to be send', principal => 'SCOTT', is_grant => TRUE, privilege => 'connect' ); commit; end; |
Add Privilege
Now that the ACL is created you can add more as the privilege to ‘ resolve ‘ privilege .
|
1 2 3 4 5 6 7 8 9 |
begin dbms_network_acl_admin.add_privilege ( acl => 'utl_mail.xml', principal => 'SCOTT', is_grant => TRUE, privilege => 'resolve' ); commit; end; |
Grant ACL
The user SCOTT is granted connect and resolve , but there is not yet indicated to what resources he can connect.
|
1 2 3 4 5 6 7 |
begin dbms_network_acl_admin.assign_acl( acl => 'utl_mail.xml', host => 'smtp server host name or address' ); commit; end; |
Test
|
1 2 |
SQL> connect scott/tiger Connected. |
|
1 2 3 4 5 6 7 8 9 10 |
begin utl_mail.send( sender => 'scott@tiger.com', recipients => 'john@doe.org', message => 'Hello World' ); commit; end; PL/SQL procedure successfully completed. |
Connection to websites and TCP ports
With the ACL , you can set up TCP ranges start and end dates.
|
1 2 3 4 5 6 7 8 |
SQL> select utl_http.request('http://www.tiger.com') from dual; select utl_http.request('http://www.tiger.com') 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 |
Create ACL, add privileges and TCP ports
Excecute as SYS
|
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 |
begin dbms_network_acl_admin.create_acl ( acl => 'utl_http.xml', description => 'HTTP Access', principal => 'SCOTT', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); dbms_network_acl_admin.add_privilege ( acl => 'utl_http.xml', principal => 'SCOTT', is_grant => TRUE, privilege => 'resolve', start_date => null, end_date => null ); dbms_network_acl_admin.assign_acl ( acl => 'utl_http.xml', host => 'www.tiger.com', lower_port => 80, upper_port => 80 ); commit; end; |
In the hosts parameter dbms_network_acl_admin.assign_acl, can you can al so add wild cards ‘*.tiger.com’ or even ‘*’.
Execute as SCOTT:
|
1 |
SQL> select utl_http.request('http://www.tiger.com') from dual; |
|
1 2 3 |
UTL_HTTP.REQUEST('HTTP://WWW.TIGER.COM') ---------------------------------------- [result here] |
Now try to connect to the same URL but with a different port .
|
1 2 3 4 5 6 7 8 |
SQL> select utl_http.request('http://www.tiger.com:1234') from dual; select utl_http.request('http://www.tiger.com:1234') 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 |
dba_network_acls
You can by carrying out the following view the ACL and privileges by reacting a query loose on the dba_network_acls .
|
1 2 3 |
select host, lower_port, upper_port, acl from dba_network_acls where ACL='/sys/acls/'utl_http.xml'; |
Execute asSYS:
Unassign ACL
|
1 2 3 4 5 6 7 8 |
begin dbms_network_acl_admin.unassign_acl( acl => 'utl_http.xml', host => 'www.tiger.com', lower_port => 80, upper_port => 80 ); end; |
Remove Privilege
|
1 2 3 4 5 |
begin dbms_network_acl_admin.delete_privilege( 'utl_http.xml', 'SCOTT', NULL, 'connect' ); end; |
Drop ACL
|
1 2 3 4 5 |
begin dbms_network_acl_admin.drop_acl( 'utl_http.xml' ); end; |
Activate UTL_MAIL
|
1 2 3 4 5 |
SQL> @?/rdbms/admin/utlmail.sql SQL> @?/rdbms/admin/prvtmail.plb SQL> alter system set smtp_out_server = '<smtp host>' scope=spfile; SQL> shutdown immediate SQL> startup |
Please let me know if this manual ‘Oracle 11g Acces Control List and ORA-24247’ was usefull to you. If there are errors or you have suggestions regarding this manual, please let me know. No rights can be derived from this manual
Regards,
Maarten
