Configure Access Control List

From Oracle 11g network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR which can be used to access external network resources, are more restricted and secured. Oracle 11g introduced Access Control List to use any external network resource through these packages.

Oracle provides the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages to allow ACL management from PL/SQL. 

First step is to create the actual ACL:-

Connect as SysDBA

BEGIN 
      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 
      acl => ’filename.xml’, 
      description => ’Description’, 
      principal => ’User or_Role’, 
      is_grant => TRUE | FALSE, 
      privilege => ’connect | resolve’, 
      start_date => null | timestamp_with_time_zone, 
      end_date => null | timestamp_with_time_zone);
END;
 
Parameters Description:-

acl: The name of the ACL. Relative path will be relative to "/sys/acls".
 
description: Description of the ACL created.
 
principal : The principal (database user or role) whom the privilege is granted to or denied from
 
is_grant : Is the privilege is granted or denied
 
privilege : The network privilege to be granted or denied
 
start_date: The start date of the access control entry (ACE). When specified, the ACE will be valid only on and after the specified date. The start_date will be ignored if the privilege is added to an existing ACE.
 
end_date: The end date of the access control entry (ACE). When specified, the ACE will expire after the specified date.The end_date must be greater than or equal to the start_date. The end_date will be ignored if the privilege is added to an existing ACE.

Example:

//

BEGIN
   DBMS_NETWORK_ACL_ADMIN.create_acl (
      acl => 'ExpertsUpdatesService.xml',
      description => 'ACL for getting access for ’Experts Updates Service’,
      principal => 'EXPERTSUPDATES',
      is_grant => TRUE,
      privilege => 'connect',
      start_date => NULL,
      end_date => NULL);
      COMMIT;
   END;
//

By using ADD _PRIVILEGE procedure we can add additional users or roles:-

 BEGIN 
   DBMS_NETWORK_ACL_ADMIN.add_privilege (
   acl => 'ExpertsUpdatesService.xml',
   principal => 'TEST',
   is_grant => TRUE,
   privilege => 'connect',
   position => NULL,
   start_date => NULL,
   end_date => NULL );
   COMMIT;
END;

By using DELETE_PRIVILEGE procedure we can remove privileges:-

BEGIN 
   DBMS_NETWORK_ACL_ADMIN.delete_privilege (
   acl => 'ExpertsUpdatesService.xml',
   principal => 'TEST',
   is_grant => TRUE,
   privilege => 'connect', );
   COMMIT;
END;

By using DROP_ACL procedure we can delete ACLs :-

BEGIN 
   DBMS_NETWORK_ACL_ADMIN.drop_acl (
   acl => 'ExpertsUpdatesService.xml', );
   COMMIT;
END;

Step to assign a network to ACL:-

Using ASSIGN_ACL procedure we can assign networks to Access control lists:-

BEGIN 
   DBMS_NETWORK_ACL_ADMIN.assign_acl (
   acl => 'ExpertsUpdatesService.xml',
   host => www.expertsupdates.com, -- Can use IP address also like 127.0.0.1 for e.g.
   lower_port => 80,
   upper_port => NULL );
   COMMIT;
END;

Using UNASSIGN_ACL procedure we can unassigned networks from Access control lists:-

BEGIN 
   DBMS_NETWORK_ACL_ADMIN.unassign_acl (
   acl => 'ExpertsUpdatesService.xml',
   host => www.expertsupdates.com, -- Can use IP address also like 127.0.0.1 for e.g.
   lower_port => 80,
   upper_port => NULL );
   COMMIT;
END;

Queries for verifying network ACLs and privileges :-

SELECT * FROM dba_network_acls;

SELECT
   host,
   lower_port,
   upper_port,
   acl
FROM
   dba_network_acls;


SELECT
   acl,
   principal,
   privilege,
   is_grant,
   start_date,
   end_date
FROM
   dba_network_acl_privileges;

About This Article

Copyrights 2017, www.expertsupdates.com