Oracle ACL (Access Control List)

In Oracle 11g, if you want to obtain the ip address or hostname of the server, run the following statement:

SELECT utl_inaddr.get_host_address FROM dual; // obtain IPSELECT utl_inaddr.get_host_name FROM dual; // obtain the host Name

 

If it works fine in oracle 9i, but in 11G you may get an inexplicable error: ORA-24247: network access denied by access control list (ACL) you may need additional permissions immediately, but grant XXX to user_name permissions, but you cannot find the permissions corresponding to the ACL. in fact, extra permissions are required here, but the method of granting permissions is quite abnormal, which is too different from the method of grant. fine-grained access to the network service for more detailed control of network permissions, Oracle 11g for such PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR) the access is configured with a separate access control method. UTL_SMTP and UTL_MAIL are email-related. For example, you can set in the trigger to send a mail knowledge when you insert or delete a table. Responsibilities: grant permissions. What if we want to assign users access to the functions mentioned above? Use the following pl/SQL statement

BEGINDBMS_NETWORK_ACL_ADMIN.CREATE_ACL (acl => 'abc. xml', -- the name of this xml file can be obtained at will, but different situations with the same name may show description => 'acl list', principal => 'arwen ', -- indicates the user is_grant => true to which the permission is granted. -- true indicates that the permission is granted. If it is false, the permission privilege => 'connection' is canceled '); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (acl => 'abc. xml ', principal => 'arwen', is_grant => true, privilege => 'resolve'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'abc. xml ', host => 'oracle _ Host_name'); -- because the network operation permissions are for a server, the END name of a machine is specified here;

 

If you want to delete the above control list

BEGINDBMS_NETWORK_ACL_ADMIN.drop_acl ( acl => 'abc.xml');END;

 

To delete this list, the users who use the list to grant permissions are naturally revoked.