STEPS TO Create ACL, add privilege and assign the ACL

 If a user for ex - SCOTT is not allowed to connect to a specific server due to ACL restrictions then perform the following steps:-

1. Create an ACL with the ‘connect’ privilege for user SCOTT:

begin

  dbms_network_acl_admin.create_acl ( 

    acl         => '/sys/acls/scott.xml', 

    description => 'HTTP Access', 

    principal   => 'SCOTT', 

    is_grant    => TRUE, 

    privilege   => 'connect', 

    start_date  => null, 

    end_date    => null

  );

  commit; 

end;

 

2. Now add resolve privilege as follows:

 

begin

  dbms_network_acl_admin.add_privilege ( 

    acl        => '/sys/acls/scott.xml', 

    principal  => ' SCOTT', 

    is_grant   => TRUE, 

    privilege  => 'resolve',  

    start_date => null, 

    end_date   => null

  ); 

  commit; 

end;

 
3. Now assign ACL created previously to IP address which SCOTT is allowed to connect:

begin

  dbms_network_acl_admin.assign_acl ( 

    acl        => '/sys/acls/scott.xml', 

    host       => '10.101.3.229', 

    lower_port => 25, 

    upper_port => NULL 

  ); 

  commit; 

end;

IF ACL is already created then do the following steps:

1. Add connect privilege:

begin

  dbms_network_acl_admin.add_privilege ( 

    acl        => '/sys/acls/scott.xml', 

    principal  => ' SCOTT', 

    is_grant   => TRUE, 

    privilege  => ' connect', 

    start_date => null, 

    end_date   => null

  ); 

  commit; 

end;

2. Add resolve privilege:

 

begin

  dbms_network_acl_admin.add_privilege ( 

    acl        => '/sys/acls/scott.xml', 

    principal  => ' SCOTT', 

    is_grant   => TRUE, 

    privilege  => 'resolve', 

    start_date => null, 

    end_date   => null

  ); 

  commit; 

end;

The DBA_NETWORK_ACLS view displays information about network and ACL assignments.

COLUMN host FORMAT A30

COLUMN acl FORMAT A30

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL

------------------------------ ---------- ---------- ------------------------------

10.1.10.*                                            /sys/acls/test_acl_file.xml

192.168.2.3                            80         80 /sys/acls/test_acl_file.xml

2 rows selected.

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.

COLUMN acl FORMAT A30

COLUMN principal FORMAT A30

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/test_acl_file.xml    TEST1       connect true  02-APR-2008

/sys/acls/test_acl_file.xml    TEST2       connect false

2 rows selected.