This discussion is archived
7 Replies Latest reply: May 5, 2008 2:40 AM by Girish Sharma RSS

Restrict User Logon From Certain IP(s)

629780 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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)
    629780 Newbie
    Currently Being Moderated
    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)
    629780 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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)
    629780 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    trigger for IP based restriction may be helpful to you.

    Regards
    Girish