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