Kick out on LOGON trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Kick out on LOGON trigger

  1. #1
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Why doesn't this trigger kick out the user B after log on:

    create or replace trigger logon_from_linux
    after logon on B.schema
    declare
    W_NAME varchar2(20);
    W_IP varchar2(20);
    begin
    select USERNAME into W_NAME from USER_USERS;
    select Sys_Context('USERENV','IP_ADDRESS') into W_IP from dual;
    insert into table values(W_NAME,W_IP);
    if W_NAME = 'B' and W_IP = 'XX.XX.XX' then
    EXECUTE IMMEDIATE 'DISCONNECT';
    -- dbms_utility.exec_ddl_statement('DISCONNECT');
    end if;
    end;
    /

    What's wrong with both:

    EXECUTE IMMEDIATE 'DISCONNECT';

    and

    dbms_utility.exec_ddl_statement('DISCONNECT');


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    is disconnect a ddl...?

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by pando
    is disconnect a ddl...?
    I guess not but what then is wrong with EXECUTE IMMEDIATE 'DISCONNECT'; ?



  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    AFAIK neither DISCONNECT nor CONNECT are even standard SQL commands! They are SQL*Plus (or maybe any other client tool's) commands.

    To restrict user access you simply need to raise application error. So inside your trigger, instead of

    ....
    EXECUTE IMMEDIATE 'DISCONNECT';
    ....

    use

    ....
    RAISE_APPLICATION_ERROR(-20001, 'Sorry, you are not allowed here!');
    ....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Thanks a lot Jurij! Works just fine.




  6. #6
    Join Date
    Dec 2011
    Posts
    1
    Yeah, I know... this is a really old thread, and everyone in the thread is happy with the solution.

    It's just that recently I had to solve a similar problem: only allow a user to login from a specific IP address. After Googling pages similar to this, I was able to figure it out and get it to work... but my understanding is that some of the information here is wrong.

    First: if the trigger is "after logon on B.schema", then it will only be triggered when you logon as "B". There really is no reason to test the username inside the trigger since it can only be "B".

    Second: There are several ways to get a "username", but the one used in the trigger above: "select USERNAME into W_NAME from USER_USERS;" will return the name of the user that executes the trigger. If the trigger is owned by "B", then "B" will execute the trigger, and it will always return "B" for the username. It makes more sense though for SYSTEM to own this trigger, so SYSTEM will execute the trigger and the USERNAME will always be "SYSTEM". The above IF will always fail.

    Third: If you want the USERNAME of the schema just logged into, use:

    SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') INTO W_NAME FROM DUAL;
    which will always return "B" for this trigger, regardless of who owns is.

    So it would look like this:

    create or replace trigger logon_from_linux
    after logon on B.schema
    DECLARE
    W_IP VARCHAR2 (20);

    BEGIN
    SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') INTO W_IP FROM DUAL;
    IF W_IP = 'xx.xx.xx.xx' THEN
    RAISE_APPLICATION_ERROR (-20001, 'Sorry, you are not allowed here!');
    END IF;

    END;
    Last edited by Solstice; 12-01-2011 at 11:31 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width