How To Install Dbms_Network_Acl_Admin Package

NULL - When the SMTP server is down or unavailable, we don't have WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN WHEN utl_ansient_error OR utl_smtp.permanent_error THEN UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'If you receive this mail, you are able to connect external network.') Send_header('Subject', 'This is a test for network connection') UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF) Д¬ := UTL_SMTP.OPEN_CONNECTION('')

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS Here we use an anonymous PL/SQL block to test the function, which is an example of using UTL_SMTP provided by Oracle 11g Documentation, we just modified it for testing purpose. The above result is correct, so we can test the privileges. sys/acls/Connect_Access.xml SH connect More Tests on ACL SQL>В SELECT acl, principal, privilege FROM DBA_NETWORK_ACL_PRIVILEGES BEGINДЄfter creating the ACL, let's make sure the setting matched our expectation. Add PrivilegeДЄdd another user to this ACL: We give user SH a privilege of connect. If there is any other user asks for a privilege to connect, you can add a privilege to the ACL for this user. BEGINДBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'Connect_Access.xml', Assign ACLДЄssign the ACL to a specific network: We open the widest scope '*' to users. Note that, principal is the username who initially asked for the privilege, and it must be in upper case. Create ACLД¬reate an ACL: In this case, we create an ACL with a initial user HR, and the privilege is resolve. If this is the first time that a user ask for specific network function, DBA must creates an ACL first. There are two basic privileges in ACL to allow users to communicate with external network, one is resolve, which has the ability to resolve hostname, domain name and IP address the other is connect, which has the ability to act as a client to connect an external host through network protocols, i.e. ORA-24247 means that the user should have the right network privilege in Access Control List (ACL) to resolve hostname or connect to any external servers. Select UTL_INADDR.get_host_name() from dual Since 11g database introduces Access Control List (ACL) to control the limited network resource and prevent security leaks, there is no more open like 10g was. SQL> select UTL_INADDR.get_host_name() from dual SQL> SELECT owner, object_type, status FROM dba_objects WHERE object_name='UTL_INADDR' ДЄn user HR in 10g database can resolve the hostname as following. Let's see a case in 10g database, UTL_INADDR is a public synonym. But for 11g database users, there is a problem to use UTL_INADDR, the synonym is still public, but you may have no 'right' to communicate with outside world. There're two error patterns of ORA-24247 in this post.ДЇor 10g database users, there could be no issues to communicate with external network using public UTL_INADDR synonym to resolve hostname. ORA-24247: network access denied by access control list (ACL)

How To Install Dbms_Network_Acl_Admin Package