I've created a trigger to restrict IP based access on database, but yet I want to restrict DBA access too.
Given below is the code for the same:
create or replace trigger ip_restrict
after logon on database
select sys_context('USERENV', 'ISDBA') into v_user from dual;
if v_user='TRUE' then
if sys_context('USERENV','IP_ADDRESS') not in ('192.168.15.18') then
raise_application_error (-20001,'Access restricted for this IP');
Is there anything wrong with this?? It's allowing any user, any IP to get access!!!
The meaning of your trigger is that only DBA user from 192.168.15.18 can not log on the database.
if you want 192.168.15.18 only DBA can logon the database please modify if v_user='TRUE' then to if v_user='FALSE' then
this may be the most easy way but may be not the most efficient way
> I said my problem, To restrict few IPs accessing my database
And I said that this is not a problem description, it is a solution description.
WHY RESTRICT IP ADDRESSES TO THE DATABASE? - the answer to that is the description of the problem you are trying to address.
And I need to emphasise again, that IP access restrictions in the Oracle db layer is a VERY POOR IDEA. In fact, so poor, that Oracle db layer security does NOT SUPPORT IT. Instead, it support internal authentication and external authentication. The external authentication includes support for o/s authentication and LDAP.
Take Unix/Linux/Windows for example. Does o/s authentication of a user include the IP address of the client used to telnet or ssh or NetBIOS into the server? No it does not. Why not? BECAUSE IT IS A VERY POOR IDEA TO USE IP ADDRESS AUTHENTICATION AT THIS LEVEL.
Where is IP addresses used then? At network level. Firewalls. NAT. Routers. Switches. An IP address is a network identifier, not a client application identifier.
It honestly makes no sense wanting to use that application level for authentication purposes. By all means use it for logging purposes. But for authentication? In today's corporate networks that deals with NAT, proxies, DHCP and so on? It is just plain silly.