Oracle 11g (FGA) Access Control List

After got API for sending SMS and developed packages we tried to send URL, but we faced below known error.

SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual;  
 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  

Starting Oracle 11g Oracle introduce FGA (Fine Grained Access) for using UTTL_HTTP, UTL_MAIL etc packages.

After reading Oracle notes, we need to create ACL and then assgin it.

Let`s check our db registery.

SQL> set linesize 400  
 SQL> col comp_name format a40  
 SQL> select comp_name, status from dba_registry;  
 COMP_NAME   STATUS  
 ---------------------------------------- --------------------------------------------  
 OWB   VALID  
 Oracle Application Express  VALID  
 Oracle Enterprise Manager  VALID  
 OLAP Catalog   VALID  
 Spatial   VALID  
 Oracle Multimedia  VALID  
 Oracle XML Database  VALID  
 Oracle Text   VALID  
 Oracle Expression Filter  VALID  
 Oracle Rules Manager  VALID  
 Oracle Workspace Manager  VALID  
 COMP_NAME   STATUS  
 ---------------------------------------- --------------------------------------------  
 Oracle Database Catalog Views  VALID  
 Oracle Database Packages and Types VALID  
 JServer JAVA Virtual Machine  VALID  
 Oracle XDK   VALID  
 Oracle Database Java Packages  VALID  
 OLAP Analytic Workspace  VALID  
 Oracle OLAP API   VALID  
 18 rows selected.  

--Creating new ACL

SQL> BEGIN  
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (  
   acl     => 'smssend_nowsms.xml', --acl  
   description => 'Permissions to access http://10.10.9.15', --needed host  
   principal  => 'ALARMSENDER', --my user  
   is_grant   => TRUE,  
   privilege  => 'connect');  
   COMMIT;  
 EN 2  3  4  5  6  7  8  9 D;  
 / 10  
 PL/SQL procedure successfully completed.  
 SQL>

--Assign it

 SQL> BEGIN  
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (  
   acl     => 'smssend_nowsms.xml',         
   host     => '10.10.9.15',  
   lower_port  => 8084,  
   upper_port  => 8084);   
   COMMIT;  
 END;  
 / 

PL/SQL procedure successfully completed.

SQL>

--Checking using select statement

SQL> SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('10.10.9.15'));  
 COLUMN_VALUE  
 10.10.9.15  
 10.10.9.*  
 10.10.*  
 10.*  
 *  

SQL> select acl , host , lower_port , upper_port from dba_network_acls;  
 ACL,HOST,LOWER_PORT,UPPER_PORT  
 /sys/acls/smssend_nowsms.xml,10.10.9.15,8084,8084  

SQL> select acl , principal , privilege , is_grant from dba_network_acl_privileges;  
 ACL,PRINCIPAL,PRIVILEGE,IS_GRANT  
 /sys/acls/smssend_nowsms.xml,ALARMSENDER,connect,true  

--Now try again

SQL> select alarmsender.pkg_sms_sender.send_sms('Ulfet','99450???????','test') from dual;

works fine!