Using login triggers for other applications

Login triggers are 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.

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:

1. 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
   

2. As system security officer, configure this stored procedure as a login trigger for user “bob”:

   alter login bob modify login script 
   "limit_user_sessions"
   go
   

3. 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
   

4. This message appears in the Adaptive Server error log file:

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

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:

1. As system administrator, create this table:

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

2. 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
   

3. 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
   

4. As system security officer, configure the limit_access_time stored procedure as a login trigger for users “bob” and “mark”:

   alter login bob login script 
   "limit_access_time"
   go
   alter login mark login script 
   "limit_access_time"
   go
   

5. 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 Adaptive 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
   

6. The following message is logged in the error log:

   (...) 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,” use 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.