Oracle ACL (Access Control List)

Document directory

•  
• Fine-grained access to network services
• Grant Permissions

In oralce 11g, if you want to obtain the Server IP address or hostname, run the following statement:

Select utl_inaddr.get_host_address from dual; // obtain the IP address

Select utl_inaddr.get_host_name from dual;
// Obtain the Host Name

If the execution can be normal in Oracle 9i, but you may get an inexplicable error message in 11g:

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 network services

To better control network permissions, Oracle 11g sets separate access control methods for these PL/SQL APIs (utl_tcp, utl_smtp, utl_mail, utl_http, and utl_inaddr.

Utl_smtp and utl_mail are email-related. For example, you can set in the trigger to send an email to a person in charge when inserting or deleting a table.

 

Grant Permissions

What if I want to assign users access to the functions mentioned above? Use the following PL/SQL statement

Begin

Dbms_network_acl_admin.create_acl(ACL
=>
'Abc. xml ',
-- The XML file name can be obtained at will, but different names may have the same name.

Description =>
'Acl list ',

Principal =>
'Arwen ',
-- Indicates the user to which the permission is granted.

Is_grant =>
True, -- true indicates that the permission is granted. If it is false, the permission is equivalent to canceling.

Privilege
=>
'Connect ');

 

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 name of a machine is specified here.

 

End;

 

 

If you want to delete the above control list

Begin

Dbms_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.

 

For more information about dbms_network_acl_admin, see the official Oracle documentation:

Http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm