ACCESS CONTROL LIST (ACL)

posted Feb 1, 2012, 6:11 PM by Sachchida Ojha   [ updated Feb 11, 2012, 7:03 PM ]
Fine-Grained Access Control (FGAC) on Network Services: Oracle supplied PL/SQL utility packages such as utl_tcp, utl_smtp, utl_mail, utl_http and utl_inaddr that provides access to network services now have (in 11G) enhanced security available. Rather than public being granted execute privileges on these packages, Oracle now has you create an ACCESS CONTROL LIST (ACL) in order to use these packages.
ACL Related Data Dictionary VIEWS: DBA_NETWORK_ACLS and [DBA/USER]_NETWORK_ACL_PRIVILEGES

1. To create ACL:

SQL> begin
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'www.dbaref.com-permissions.xml',
description=>'TEST ACL',
principal=>'DBAREF',                       
is_grant=>TRUE,
privilege=>'connect');
End;

PL/SQL procedure successfully completed.

Once the ACL is created , you can add additional user or privileges using the DBMS_NETWORK_ACL_ADMIN.add_privileges procedure.

SQL> BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl         =>  'www.dbaref.com-permissions.xml',
    principal   => 'SCOTT',
    is_grant    => FALSE,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);

  COMMIT;
END;
/ 

PL/SQL procedure successfully completed.


You can also use DBMS_NETWORK_ACL_ADMIN.delete_privileges package to drop privileges and DBMS_NETWORK_ACL_ADMIN.drop_acl to drop ACL.

ASSIGN ACL to NETWORK HOST:

SQL> begin
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'www.dbaref.com-permissions.xml',
host=>'usha');
End;
/
PL/SQL procedure successfully completed.

SQL>



Access control lists are assigned to networks using the ASSIGN_ACL procedure, whose parameters are listed below:
  • acl - The name of the access control list XML file.
  • host - The hostname, domain, IP address or subnet to be assigned. Hostnames are case sensitive, and wildcards are allowed for IP addresses and domains.
  • lower_port - Defaults to NULL. Specifies the lower port range for the 'connect' privilege.
  • upper_port - Defaults to NULL. If the lower_port is specified, and the upper_port is NULL, it is assumed the upper_port matches the lower_port.

The code below shows the ACL created previously being assigned to a specific IP address and a subnet.

SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'www.dbaref.com-permissions.xml',
host => '198.168.*.*',
lower_port => NULL,
upper_port => NULL);
COMMIT;
END;
/

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls;   

HOST                   LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------
usha                                                                   /sys/acls/www.dbaref.com-permissions.xml

198.168.*.*                                                          /sys/acls/www.dbaref.com-permissions.xml


SQL>
SQL> 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;

ACL                   PRINCIPAL              PRIVILE IS_GR START_DATE    END_DATE
------------------------------ ------------------------------ ------- ----- ----------- -----------
/sys/acls/www.dbaref.com-permi DBAREF                  connect true
ssions.xml

/sys/acls/www.dbaref.com-permi SCOTT                     connect false
ssions.xml


SQL>







Comments