1 2 Previous Next 25 Replies Latest reply: Feb 12, 2008 9:24 AM by 622513 RSS

    trigger for IP based restriction

    622296
      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
          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
            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
              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
                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
                  Billy~Verreynne
                  > 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
                    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
                      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
                        and restart the listener to see the changes..
                        • 9. Re: trigger for IP based restriction
                          Billy~Verreynne
                          > 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
                            Well.... you are not forcefully replying.
                            I said my problem, To restrict few IPs accessing my database.
                            • 11. Re: trigger for IP based restriction
                              Billy~Verreynne
                              > 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
                                "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
                                  cd_2
                                  Which part of "This is not a good idea!" didn't you understand?

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