WHITELIST USER IP ADDRESS USING ACL IN ORACLE DATABASE

What is ACL? ACL stands for access control list which filters the packets connected to a network providing security and limiting traffic to the users

Create ACL for the user

--Create acl
BEGIN
   DBMS_NETWORK_ACL_ADMIN.create_acl (
      acl          => 'kishan.xml',
      description  => 'HTTP Access',
      principal    => 'KISHAN',        -- user
      is_grant     => TRUE,
      privilege    => 'connect',
      start_date   => NULL,
      end_date     => NULL);
END;
/

Assign the ACL to IP address

--Assign ACL

BEGIN
   DBMS_NETWORK_ACL_ADMIN.assign_acl (acl         => 'kishan.xml',
                                      HOST        => '192.168.56.100',
                                      lower_port  => NULL,
                                      upper_port  => NULL);
END;
/

Add the privilege to the user with connect and resolve

--Add privilege

BEGIN
   
   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'kishan.xml',
                                         principal   => 'KISHAN',
                                         is_grant    => TRUE,
                                         privilege   => 'connect',
                                         start_date  => NULL,
                                         end_date    => NULL);

   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'kishan.xml',
                                         principal   => 'KISHAN',
                                         is_grant    => TRUE,
                                         privilege   => 'resolve',
                                         start_date  => NULL,
                                         end_date    => NULL);
END;
/

COMMIT;

Use the below query to view existing network ACL

SQL> select * from DBA_NETWORK_ACLS;

HOST                 LOWER_PORT UPPER_PORT ACL                  ACLID            ACL_OWNER
-------------------- ---------- ---------- -------------------- ---------------- --------------------
192.168.56.100                             /sys/acls/kishan.xml 0000000080002738 SYS
*                                          NETWORK_ACL_FD9ACFEC 0000000080002710 SYS
                                           5BCC48A9E043B6A9E80A
                                           FB6F

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s