ACL (Access Control List) in Oracle 12c

The following points should be in consideration while upgrading your Oracle database to 12c:

  • Starting with Oracle Database 12c, the access control of the UTL packages (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR) is implemented using Oracle Database Real Application Security and does not require Oracle XML DB.

 

  • existing ACL in XDB of your database will be migrated during the upgrade to 12c .

 

  • DBMS_NETWORK_ACL_ADMIN has been deprecated.

 

  • Existing ACL’s in your database will be migrated and “re-named” after the upgrade , the following query will list the old and the new name of the ACL:

           SQL> select * from DBA_ACL_NAME_MAP ;

  • take the backup of the following tables just in case ACL’s were not migrated successfully:

 SQL> create table TESTSCHEMA.DBA_NETWORK_ACLS as select * from DBA_NETWORK_ACLS;

SQL> create table TESTSCHEMA.DBA_NETWORK_ACL_PRIVILEGES as select * from DBA_NETWORK_ACL_PRIVILEGES;

Advertisement

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;

/