DBMS_NETWORK_ACL_ADMIN

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;

/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s