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

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.

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;
/

Try this:
There is o custom error displayed, the connection is just dropped.

Martijnvs,

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

Glad to hear that .

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

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?

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

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

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.

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

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?

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

I've taken the first version I posted and added the extra logging:
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 .