This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Feb 12, 2008 7:24 AM by 622513 RSS

trigger for IP based restriction

622296 Newbie
Currently Being Moderated
Hi,
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
declare
v_user varchar2(10);
begin
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');
end if;
end if;
end;


Is there anything wrong with this?? It's allowing any user, any IP to get access!!!

Regards,
user959
  • 1. Re: trigger for IP based restriction
    542413 Newbie
    Currently Being Moderated
    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
  • 2. Re: trigger for IP based restriction
    622296 Newbie
    Currently Being Moderated
    Hi,
    Thanks for reply..
    But,
    The basic problem here is, I'm able to login through a DBA user through that IP too.
    Kindly guide me for the same.
  • 3. Re: trigger for IP based restriction
    571482 Oracle ACE
    Currently Being Moderated
    I strongly recommend doing IP based filtering at OS/firewall level and not on the DB.
    Use the right tool for the job, and the database is not the right tool for IP-based access lists.
  • 4. Re: trigger for IP based restriction
    451021 Newbie
    Currently Being Moderated
    Logon triggers will not work unless you exclude 'ADMINSTER DATABASE TRIGGER' from the dba user. Instead of that try this approach, set the parameters in protocol.ora,

    tcp.validnode_checking = YES
    tcp.excluded_nodes = {list of IP addresses}
    tcp.invited_nodes = {list of IP addresses}

    Hope this helps.
  • 5. Re: trigger for IP based restriction
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > Is there anything wrong with this??

    Yes. EVERYTHING is wrong with this.

    What you are trying to do in a database trigger makes absolutely no security sense.
  • 6. Re: trigger for IP based restriction
    622296 Newbie
    Currently Being Moderated
    Hi,
    Well... let me clear the idea.

    I need a DB restriction for few IPs.
    I tried using parameters inside sqlnet.ora, but yet it's not working.
    If you know, then suggest me something over this issue.
  • 7. Re: trigger for IP based restriction
    451021 Newbie
    Currently Being Moderated
    create a file named protocol.ora in the same directory where your listener.ora file is and set the ip's that you want to restrict in that file. It will work..
  • 8. Re: trigger for IP based restriction
    451021 Newbie
    Currently Being Moderated
    and restart the listener to see the changes..
  • 9. Re: trigger for IP based restriction
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > I need a DB restriction for few IPs.

    Why at IP level? It will take me less than 60 seconds to bring up a 2nd IP stack on my machine, to spoof an IP that you allow access to.

    IP address at application level is not a meaningful unique client identifier.

    > I tried using parameters inside sqlnet.ora, but yet it's not working.

    The IP allow and deny settings do work in sqlnet, but I would not consider using this as robust well managed and controlled IP access mechanism.

    > If you know, then suggest me something over this issue.

    I often say the following in the forums here. You are proposing a solution to some kind of problem or requirement.

    The solution you are proposing, IP level security in Oracle, is pretty much a flawed solution as far as security goes. You now seem to expect us to tell you how to make this flawed solution work.

    Instead, tell us what the actual problem or requirement is, in order for forum members to supply you with viable solutions to choose from.
  • 10. Re: trigger for IP based restriction
    622296 Newbie
    Currently Being Moderated
    Well.... you are not forcefully replying.
    I said my problem, To restrict few IPs accessing my database.
  • 11. Re: trigger for IP based restriction
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > 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.
  • 12. Re: trigger for IP based restriction
    622296 Newbie
    Currently Being Moderated
    "WHY??" is none of your business...
    I asked for the solution, if u knw... post it...
    If not, don't reply even.
  • 13. Re: trigger for IP based restriction
    60660 Journeyer
    Currently Being Moderated
    Which part of "This is not a good idea!" didn't you understand?

    C.
  • 14. Re: trigger for IP based restriction
    622296 Newbie
    Currently Being Moderated
    ??? Can you pls clarify what you want to say???
1 2 Previous Next