7 Replies Latest reply: May 5, 2008 4:40 AM by Girish Sharma RSS

    Restrict User Logon From Certain IP(s)

    Tomy3k_Bakr
      Hi All,
      Is there any way provided from oracle that i can use to restrict users to logon only from defined IP for each user. I know that i can do it using triggers but it is a requirement that the solution must not affect the performance ( triggers will slow down log on time becuase the logon request have to go to the DB then kicked out).

      Some of my friends told me about oracle connection manager. If it is how can i use it? can you send me simple and detail doc. to read.

      Regards;
        • 1. Re: Restrict User Logon From Certain IP(s)
          561093
          Read it here:

          http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/policies.htm#i1010197
          • 2. Re: Restrict User Logon From Certain IP(s)
            Tomy3k_Bakr
            Hi Citrus,
            I read the articale. But if you mean valid node checking it will be very slow and make problems if i want to change the login IP at any time. I have to restart the listener which is a very big problem.
            Any other suggestion?

            regards;
            • 3. Re: Restrict User Logon From Certain IP(s)
              Tomy3k_Bakr
              Hi all,
              till now i did not get any assistant, can anyone please help me.

              Regards;
              • 4. Re: Restrict User Logon From Certain IP(s)
                sgalaxy
                You may find the following useful:
                Limit table access for a specific ip
                Re: IP connect limitation

                Greetings...
                Sim
                • 5. Re: Restrict User Logon From Certain IP(s)
                  108476
                  Hi Tommy,
                  Is there any way provided from oracle that i can use to restrict users to logon only from defined IP for each user.
                  Use a logon trigger with this:

                  select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;

                  Then, test for the IP addresses you want to allow to continue.

                  To implement IP address checking at signon time, you can create an Oracle logon trigger which test for the IP address and compares it to an authorized user IP list.
                  Then, test for the IP addresses you want to allow to continue.
                  connect sys/manager;
                   
                  create table
                     stats$user_log
                  (
                     user_id           varchar2(30),
                     session_id           number(8),
                     host              varchar2(30),
                     ip_address        varchar2(30)
                  )


                  Once the table is designed, the next step is to create a system-level logon trigger that fills in as much information as possible at the time of the logon event.

                  create or replace trigger
                     logon_audit_trigger
                  AFTER LOGON ON DATABASE
                  BEGIN
                  insert into stats$user_log values(
                     user,
                     sys_context('USERENV','SESSIONID'),
                     sys_context('USERENV','HOST'),
                     sys_context('USERENV','IP_ADDRESS')
                  );
                  END;
                  /

                  http://www.dba-oracle.com/art_builder_sec_audit.htm

                  Message was edited by:
                  burleson
                  • 6. Re: Restrict User Logon From Certain IP(s)
                    Tomy3k_Bakr
                    Hi burleson,
                    I know that i can use the logon trigger to audit or to dis/allow a user to logon or not. But my primary goal is that i don't want the database to be the one who will decide to allow or deny logon. also, i want something more dynamic to allow me to change the IPs settings rather than using Valid Node Checking feature in the listener. I have an idea but i don't know if it works like this or not. I want the validation comes from another server for example (Active Directory) then the logon request will go to the listener which will direct it to the database. As far as my information states, we can merge our database security with active directory using advanced security or the other method is to use Oracle Connection manager. i searched in the second one becuase it is free to use without extra license but i did not reach any thing.another issue for auditing DML or DDL i just can enable auditing in oracle and it will do the same thing as your note says.
                    Another small hint i'm using oracle 9.2.0.6.

                    Please if any one has simple steps to do this and help me with it.

                    and please read my note carefully and do not tell me to use valid node checking (TCP.XXXX) or use the logon trigger.

                    Regards;

                    Message was edited by:
                    Tomy3k_Bakr
                    • 7. Re: Restrict User Logon From Certain IP(s)
                      Girish Sharma
                      trigger for IP based restriction may be helpful to you.

                      Regards
                      Girish