Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: logon trigger

  1. #1
    Join Date
    Sep 2010
    Posts
    12

    Unanswered: logon trigger

    Hi, Can some one help me in writing this trigger.

    The requirement is to ensure that functional accounts can only connect from approved clients, based on their IP subnet. I have to create a logon trigger to compare the client IP with a predefined table of approved subnets, and reject the logon if it’s not in the list.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Lookup login-triggers in the Sybase-documentation, write a piece of code that verifies the ip and call function syb_quit() if it's not approved.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Sep 2010
    Posts
    12
    Hi Martijnvs,

    Thanks for your reply.I am actually oracle guy and i have created a trigger for logon with respect to oracle perspective.I want the same trigger formatted in Sybase.Your help is appreciated.

    CREATE OR REPLACE TRIGGER trig_after_logon
    --- Logon trigger which accepts connections from users with valid ip addresses
    --- Check if IP address belongs to a Valid subnet which can be found from the lookup table.

    AFTER LOGON ON DATABASE
    DECLARE
    vusername varchar2(30);
    vip varchar2(20);
    vos_user varchar2(20);
    BEGIN
    vusername:=SYS_CONTEXT('USERENV','SESSION_USER');
    vip:=SYS_CONTEXT('USERENV','IP_ADDRESS');
    vos_user:=SYS_CONTEXT('USERENV','OS_USER');


    IF vos_user <> 'oracle' THEN -- check if OS account is not oracle
    --IF vusername in ('TESTVRU, 'TEST') THEN
    IF check_user(vusername) THEN -- check username from a lookup table
    IF NOT check_subnet(vip) THEN -- check ip/subnet from a lookup table
    RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
    END IF;
    END IF;
    END IF;
    END;
    /

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Try this:
    Code:
    --create table to store approved clients
    use master
    go
    create table approved_clients (
    ipadress char(15)
    }
    go
    --insert approved ipadresses in table approved_clients
    <...>
    
    --create the logintrigger
    
    create procedure sp__checkip 
    as 
    begin 
    
    --declare variable
    declare @ipaddress char(15)
    --lookup ipaddress of current process
    select @ipaddress = ipaddr  
    from master..sysprocesses where spid = @@spid 
     
    --check if ipaddress is in the approved-list
    if not exists (select 1 from approved_clients where ipaddress = @ipaddress)
    begin
    --if not: terminate connection
        select syb_quit()
    end
    return 0
     
    end
    -- connect logintrigger to <loginname>
    sp_modifylogin <loginname>, 'login script', master..sp__checkip
    There is o custom error displayed, the connection is just dropped.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Sep 2010
    Posts
    12
    Martijnvs,

    Thanks for the script.I have tested this and works fine.

  6. #6
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Glad to hear that .
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Sep 2010
    Posts
    12
    Hi Martijnvs,

    I need to add something to this.

    Here instead of ipaddress i need to take into consideration the subnet which is the first 3 octets of ip address.How can i modify this procedure.
    and also have to capture the audit information of the users who will be logged out of the database by the use of the trigger.How can i do this?

    I am oracle guy recently shifted to Sybase. Your help is highly appreciated.

    please advise..

    Thanks
    Meda
    Last edited by medamadhu; 11-03-10 at 12:04.

  8. #8
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    So if I understand it correctly, you want to 1) verify both ip-adress AND subnet? Or just the subnet? Sybase does not register the subnet of a process, only the ip-adress.

    And 2) you want to log which user has been kicked out because he wasn't on the verified list?
    I'm not crazy, I'm an aeroplane!

  9. #9
    Join Date
    Sep 2010
    Posts
    12
    Hi Martijnvs,

    According to the requirement it requires only subnet.Since subnet is the first 3 octets of I.P,i think this below query gives me the desired out put

    --lookup ipaddress of current process

    select @ip=ipaddress from master..sysprocesses where spid=@@spid
    select @ip=substring(@ip,1,char_length(@ip)-charindex(".",reverse(@ip)))
    select @ip

    and now my script looks like this:

    create procedure sp__checkipcf
    @user_info varchar(20)
    as
    begin
    --declare variable
    declare @ip varchar(15)

    if exists(select * from user_info where username= @user_info)
    print 'user exists'
    else
    print 'doesnot exists'


    --lookup ipaddress of current process

    select @ip=ipaddress from master..sysprocesses where spid=@@spid
    select @ip=substring(@ip,1,char_length(@ip)-charindex(".",reverse(@ip)))
    select @ip


    --check if ipaddress is in the approved-list
    if not exists (select 1 from approved_clients where ipaddress = @ip)
    begin
    --if not: Login not on approved list - terminate
    select syb_quit()
    end
    return 0

    end
    go

    Let me know if i am wrong.

    And 2) you want to log which user has been kicked out because he wasn't on the verified list?
    yes..ur right,I want to know the info which user has been kicked out...

    Thanks
    Meda
    Last edited by medamadhu; 11-08-10 at 11:48.

  10. #10
    Join Date
    Sep 2010
    Posts
    12

    sumerp inserted

    Able to fix it..thanks

    Hi Martijnvs,

    Now Iam getting the below error when i tried to use the login i have created

    =>isql -Urobo -Probo123 -Shappy_uat_HR -w400

    Msg 1638, Level 16, State 1:
    Server 'happy_uat_HR':
    Execution of login script 'sp__checkipcf' failed with last error = 201. See server errorlog for details.
    CT-LIBRARY error:
    ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect


    I used the following script to execute this:


    create procedure sp__checkipcf
    @user_info varchar(20)
    as
    begin
    --declare variable
    declare @ip varchar(12)

    if exists(select * from user_info where username= @user_info)
    print 'user exists'
    else
    print 'doesnot exists'


    --lookup ipaddress of current process

    select @ip = ipaddr from master..sysprocesses where spid=@@spid
    select @ip= substring(@ip,1,char_length(@ip)-charindex(".",reverse(@ip)))
    select @ip

    --check if ipaddress is in the approved-list
    if not exists (select 1 from approved_clients where ipaddress = @ip)
    begin
    --if not: Login not on approved list - terminate
    select syb_quit()
    end
    return 0

    end
    go


    Thanks,
    Meda
    Last edited by medamadhu; 11-08-10 at 14:32.

  11. #11
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    To log the users that have been kicked, create an extra table with columns to match the information you want to log. Before select syb_quit(), do an insert of values into that table.
    I hope this is enough info, if not, let me know, and I'll try to explain in more detail.
    I'm not crazy, I'm an aeroplane!

  12. #12
    Join Date
    Sep 2010
    Posts
    12
    Hi Martijnvs,

    Sorry for my late reply.Actually we are building automated health checks to monitor for failures so we can quickly respond to any production issues.

    Do you have something similar for Sybase, where we can view the audit log?

    Thank,
    Meda
    Last edited by medamadhu; 11-12-10 at 12:27.

  13. #13
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    You can try to implement Sybase Auditing, but that's quite a big task. I've never done it, so I can't help you with that.
    To extend the logintrigger I previously posted with logging is not too hard. What do you want to log? ip-adress, date, time? Anything else?
    I'm not crazy, I'm an aeroplane!

  14. #14
    Join Date
    Sep 2010
    Posts
    12
    Hi Martijnvs,

    I realised sybase auditing is bit tedious process,i wouldn't go with it.I want to log the info of the loginname who has been kicked out and also dbname,ipadd,date,time etc.As per ur previous replies i understand that i need to create a table with above columns and insert into the stored procedure before syb_quit()...?Can you explain this in more detail

  15. #15
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    I've taken the first version I posted and added the extra logging:
    --create table to store approved clients
    use master
    go
    create table approved_clients (
    ipadress char(15)
    }
    go
    --create loggingtable
    create table client_logging(
    loginname varchar(25),
    source_ip char(15),
    kickeddate datetime
    }

    --insert approved ipadresses in table approved_clients
    <...>

    --create the logintrigger

    create procedure sp__checkip
    as
    begin

    --declare variable
    declare @ipaddress char(15)

    --lookup ipaddress of current process
    select @ipaddress = ipaddr
    from master..sysprocesses where spid = @@spid

    --check if ipaddress is in the approved-list
    if not exists (select 1 from approved_clients where ipaddress = @ipaddress)
    begin
    --if not:
    --log data into table
    insert into client_logging(loginname, source_ip, kickeddate)
    values (suser_name() , @ipaddress, getdate() )

    --and terminate connection
    select syb_quit()
    end
    return 0

    end
    -- connect logintrigger to <loginname>
    sp_modifylogin <loginname>, 'login script', master..sp__checkip
    I did not include the dbname in the logging since no other db than the logins default database has been used. Before any other db can be used, the trigger has already fired and killed the process.
    I hope you can use this .
    Last edited by Martijnvs; 11-16-10 at 05:13.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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