Access Control List

ACL in oracle


Creating ACL

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'https_permissions.xml',
        description  => 'ACL for users to send mail.',
        principal    => 'USER',
        is_grant     => TRUE,
        privilege    => 'connect',
        start_date   => null,
        end_date     => null
    );
END;
/

Add privilege to user
Use below to add a privilege to grant or deny the network access to the user. The access control entry (ACE) will be created if it does not exist.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'https_permissions.xml',
        principal   => 'USER',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/

Assing host to ACL
Use Below to assign an access control list (ACL) to a host computer, domain, or IP subnet, and if specified, the TCP port range.
BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'https_permissions.xml',
host => 'hostname.com',
lower_port => 1,
upper_port => 10000
);
END;
/
commit;

dont forget to give commit at end of the session to make these changes permanent.

Revoke priviledge from a user
The below procedure deletes a privilege in an access control list.

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





SELECT HOST, LOWER_PORT, UPPER_PORT, ACL,
   DECODE(
     DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,  'USER', 'connect'),
     1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE
FROM DBA_NETWORK_ACLS
WHERE host IN
  (SELECT * FROM      TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('hostname.com')))
 ORDER BY
  DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) DESC, LOWER_PORT, UPPER_PORT;

HOST                 LOWER_PORT  UPPER_PORT  ACL           PRIVILE
-------------------- ---------- ------------------------------
hostname.com     1         10000        /sys/acls/https_permissions.xml GRANTED



 Adding additional users to the ACL use below

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'SCOTT.xml',
        principal   => 'MN',
        is_grant    =>  TRUE,
        privilege   => 'connect');
END;
/


BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
     acl         => 'SCOTT.xml',
     host        => 'Mail Server name',
     lower_port => 25);
END;
/
commit;