Using login triggers for other applications

Login triggers were designed as a part of the row level access control feature in Adaptive Server. In this context, you can use a login trigger in combination with the features for access rules and application contexts to set up row-level access controls, once a session logs in to Adaptive Server. However, you can use login triggers for other purposes as well.

Limiting the number of concurrent connections

The following example limits the number of concurrent connections to Adaptive Server that a specific login can make. Each of the commands described in steps 1 and 2 in the example are executed in the default database of the user for whom the access needs to be restricted:

As System Administrator, create the limit_user_sessions stored procedure:

create procedure limit_user_sessions
as
	declare @cnt int, 
   @limit int, 
   @loginname varchar(32)

	select @limit = 2 -- max nr. of concurrent logins

/* determine current #sessions */
	select @cnt = count(*)
from master.dbo.sysprocesses
	where suid = suser_id()

/* check the limit */
if @cnt > @limit
begin		select @loginname = suser_name()
		print "Aborting login [%1!]: exceeds session
			limit [%2!]",
			@loginname, @limit
    /* abort this session */
    select syb_quit()
end
go

grant exec on limit_user_sessions to public
go

As System Security Officer, configure this stored procedure as a login trigger for user “bob”:
```
sp_modifylogin "bob", "login script",
"limit_user_sessions"
go
```

Now, when user “bob” creates a third session for Adaptive Server, this session is terminated by the login trigger calling the syb_quit() function:

% isql -SASE125 -Ubob -Pbobpassword
1> select 1
2> goCT-LIBRARY error:
ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect

This message appears in the Adaptive Server error log file:

(...) background task message: Aborting login [ my_login]: exceeds session limit [2]

Enforcing timed-based restrictions

This example describes how System Administrators can create a login trigger to enforce time-based restrictions on user sessions. Each of the commands described in steps 1 – 4 are executed in the default database of the user for whom the access needs to be restricted:

As System Administraor, create this table:

create table access_times (
suid int not null,
dayofweek tinyint,
shiftstart time,
shiftend time)

As System Administrator, insert the following rows in table access_times. These rows indicate that user “bob” is allowed to log into Adaptive Server on Mondays between 9:00am and 5:00pm, and user “mark” is allowed to login to Adaptive Server on Tuesdays between 9:00Am and 5:00PM
```
insert into access_times
select suser_id(‘bob’), 1, ‘9:00’, ‘17:00’
go
insert into access_times
select suser_id(‘mark’), 2, ‘9:00’, ‘17:00’
go
```

As System Administrator, create the limit_access_time stored procedure, which references the access_time table to determine if login access should be granted:

create procedure limit_access_time as
declare @curdate date,
    @curdow tinyint,
    @curtime time,
    @cnt int,
    @loginname varchar(32)

-- setup variables for current day-of-week, time
select @curdate = current_date()
select @curdow = datepart(cdw,@curdate)
select @curtime = current_time()
select @cnt = 0

-- determine if current user is allowed access
select @cnt = count(*)
from access_times
where suid = suser_id()
and dayofweek = @curdow
and @curtime between shiftstart and shiftend

if @cnt = 0
begin
   select @loginname = suser_name()
   print "Aborting login [%1!]: login attempt past      normal working hours", @loginname

   -- abort this session
   return -4
end
go

grant exec on limit_access_time to public
go

As System Security Officer, configure the limit_access_time stored procedure as a login trigger for users “bob” and “mark”:
```
sp_modifylogin "bob", "login script",
"limit_access_time"
go
sp_modifylogin "mark", "login script",
"limit_access_time"
go
```

On Mondays, user “bob” can successfully create a session:

isql -Ubob -Ppassword
1> select 1
2> go
-----------
          1
(1 row affected)

However, user “mark” is denied access to Adpative Server:

isql -Umark -Ppassword
1> select 1
2> go
CT-LIBRARY error:
ct_results(): network packet layer: internal net library error: Net-Library operation terminated
due to disconnect

The following message is logged in the errorlog:

(...) server back-ground task message: Aborting login [mark]: login attempt past normal working hours

The above examples show how you can limit the number of concurrent connections for a specific login and restrict access to specific times of day for that login, but it has one disadvantage: the client application cannot easily detect the reason the session was terminated. To display a message to the user, such as “Too many users right now—please try later,” you need a different approach.Instead of calling the built-in function syb_quit(), which causes the server to simply terminate the current session, you can deliberately cause an error in the stored procedure to abort the login trigger stored procedure.

For example, dividing by zero aborts the login trigger stored procedure, terminates the session, and causes a message to appear.