The NETWORK_ACL_ADMIN package provides the interface to administer the network access control lists (ACL). ACLs are used to control access by users to external network services and resources from the database through PL/SQL network utility packages including UTL_TCP, UTL_HTTP, UTL_SMTP andUTL_INADDR.
starting with Oracle 12c oracle have deprecated subprograms such as: ADD_PRIVILEGE, CHECK_PRIVILEGE Function, CREATE ACL Procedure, DELETE PRIVILEGE Procedure. for further guide please visit:
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_networkacl_adm.htm#ARPLS148
The below is a guide for 11g Version:
Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. The CREATE_ACL procedure uses the following parameters to create a new ACL:
- acl – The name of the access control list XML file, generated relative to the “/sys/acls” directory in the XML DB Repository.
- description – A description of the ACL.
- principal – The first user account or role being granted or denied permissions. The text is case sensitive.
- is_grant – TRUE to grant, FALSE to deny the privilege.
- privilege – Use ‘connect’ for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access. Use ‘resolve’ for UTL_INADDR name/IP resolution. The text is case sensitive.
- start_date – Default value NULL. When specified, the ACL will only be active on or after the specified date.
- end_date – An optional end date for the ACL.
The following code creates two test users to act as principals, then creates a new ACL.
CONN sys/password@db11g AS SYSDBA
CREATE USER test1 IDENTIFIED BY test1;
GRANT CONNECT TO test1;
CREATE USER test2 IDENTIFIED BY test2;
GRANT CONNECT TO test2;
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => ‘test_acl_file.xml’,
description => ‘A test of the ACL functionality’,
principal => ‘TEST1’,
is_grant => TRUE,
privilege => ‘connect’,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
Once created, the ACL is visible in the “http://host:port/sys/acls/” directory.
Additional users or roles are added to the ACL using the ADD_PRIVILEGE procedure. Its parameter list is similar to the CREATE_ACL procedure, with the omission of the DESCRIPTION parameter and the addition of a POSITION parameter, which sets the order of precedence.
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => ‘test_acl_file.xml’,
principal => ‘TEST2’,
is_grant => FALSE,
privilege => ‘connect’,
position => NULL,
start_date => NULL,
end_date => NULL);
COMMIT;
END;
/
====================================================================
select grantee , table_name , privilege from dba_tab_privs where table_name = ‘UTL_HTTP’ and grantee = ‘PUBLIC’;
http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php
http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html
Querying current ACL’s configured in the database:
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, ‘DD-MON-YYYY’) AS start_date,
TO_CHAR(end_date, ‘DD-MON-YYYY’) AS end_date
FROM dba_network_acl_privileges;
Checking ACL Privileges:
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('test_acl_file.xml', 'TEST1', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual;
Dropping ACL:
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => ‘test_acl_file.xml’);
COMMIT;
END;
/
Deleting ACL privilege:
BEGIN
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => ‘test_acl_file.xml’,
principal => ‘TEST2’,
is_grant => FALSE,
privilege => ‘connect’);
COMMIT;
END;
/
Geodata Master 