(back)
 
Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 07 January 2007
Login triggers in ASE 12.5+
 
Summary
'Login triggers' are a new feature in ASE 12.5. This document explains how login triggers work, how to set these up, and addresses some practical issues.

Contents


Introduction
In ASE 12.5 GA, 'login triggers' have been introduced as a new feature. While these were originally designed as part of the 'row-level access' security feature, login triggers can also be used separately, as described in this document.
One of the reasons for writing this document is that the 'official' documentation for ASE 12.5 contains only limited information about login triggers; this document will hopefully answer some of the questions.

A login trigger is basically a regular ASE stored procedure which is automatically executed in the background when a user successfully logs in to ASE. As such, 'login trigger' is a slightly misleading name, because it does not involve any 'real' ASE triggers (i.e. those database objects created with the create trigger... T-SQL command). To confuse things further, the system stored procedures sp_modifylogin and sp_displaylogin (see below), use the name 'login script' instead. Still, 'login trigger' is the commonly used name for this feature.

Login-specific and global login triggers
The login triggers introduced in 12.5 GA are so-called 'login-specific' login triggers: these have to be configured for every login specifically. For details, go here.
In ASE 12.5.4, a new type of login trigger was added, the so-called 'global' login trigger. This has to be configured only once, and applies to all logins (including sa !). Otherwise, the global login trigger works exactly the same as a login-specific login trigger.
Using the global login trigger can be useful when you want all logins to use the same login trigger; with a global login trigger there is no need to keep track of new logins for which the login trigger must be configured.

Note that the global login trigger and a login-specific login trigger can co-exist. When both are defined, the global login trigger is executed first, and the login-specific login trigger is executed next.

Note that global login triggers are not yet available in ASE 15 -- they will be added in release 15.0.2 (expected later in 2007).

Configuring a login-specific login trigger
A login-specific login trigger is an attribute of an ASE login. By default, no login triggers exist; these must be set up through sp_modifylogin. The following example configures the stored procedure my_proc as a login trigger for ASE login my_login :
-- configure the login trigger
sp_modifylogin my_login, 'login script', my_proc

This command must be executed from within the login's default database, or an error will result. Also, the stored procedure in question (my_proc above) must exist in this database. Note that it is not checked if the login has execute permissions on the stored procedure; this permission check is performed only when the login trigger is executed because the user logs in to ASE.

One a stored procedure has been configured as a login trigger, it cannot be dropped anymore, but it must be unconfigured first. This can be done by dropping the login trigger altogether or changing the login trigger to a different stored procedure (as shown in the below examples):
-- drop the login trigger
sp_modifylogin my_login, 'login script', NULL

-- change the login trigger
sp_modifylogin my_login, 'login script', a_different_proc

The login trigger's object ID is stored in the column syslogins.procid. To set, change or drop a login trigger, sso_role is required. To display the current login trigger for a login, use sp_displaylogin:
1> sp_displaylogin my_login
2> go
(...)
Default Database: my_db
Default Language:
Auto Login Script: my_proc
Configured Authorization:
(...)


Configuring a global login trigger
The global login trigger is configured with the stored procedure sp_logintrigger:
-- configure the login trigger
sp_logintrigger 'my_db.dbo.my_globaltrigger'
Instead of sp_logintrigger, you can also use sp_modifylogin, but with NULL for the login name (otherwise both methods are exactly identical):
-- configure the login trigger
sp_modifylogin NULL, 'login script', 'my_db.dbo.my_globaltrigger'
Note that, unlike login-specific login triggers, you must specify the database in which the global login trigger resides.

To drop or modify the global login trigger follow the same procedure as above:
-- drop the global login trigger
sp_logintrigger 'drop'

-- change the global login trigger
sp_logintrigger 'your_db.dbo.a_different_proc'

(substitute 'sp_logintrigger' for 'sp_modifylogin NULL, "login script", ' 
as you like...)
To display the currently defined global login trigger, either run sp_logintrigger without parameters, or examine the server-wide global variable @@logintrigger:
-- display the current global login trigger setting
1> sp_logintrigger 
2> go
 Global login trigger       Status
 -------------------------- -------------
 my_db.dbo.my_globaltrigger Enabled

1> select @@logintrigger
2> go
 --------------------------
 my_db.dbo.my_globaltrigger
Note that, after defining or changing the global login trigger, the new value will not be displayed, or be displayed as 'Disabled', until the first subsequent successful login has happened.

Warning: before using global login triggers, make sure you've read the section Things that are not a good idea below!

Execution of a login trigger
Once a login trigger -either global or login-specific- has been configured, it will be executed automatically in the background when the corresponding login successfully logs in to the ASE server, but before any commands from the client application are executed on the ASE server.
When both the global login trigger and a login-specific login trigger are defined, the global login trigger is executed first, and the login-specific login trigger is executed next.
When multiple concurrent connections are made by the same login, the login trigger(s) is(are) executed independently for each session. Also, multiple logins can be configured to have the same stored procedure as a login trigger.

Even though a login trigger is basically a normal stored procedure, what makes them special is that the login trigger executes as a background task, i.e. without access to a user connection. This special execution mode has a special effect on any output generated by the login trigger stored procedure, as well as in any further stored procedures that are called from the login trigger stored procedure (see next section).
Also, some standard stored procedure features cannot be used in the context of a login trigger due to this background execution mode. For example, no parameters can be passed to/from the procedure (in fact, the login trigger will fail if the stored procedure has any parameters without default values), and no result status values can be passed back.

Of course, it is also possible to explicitly execute a login trigger stored procedure as a any normal stored procedure, for example from isql; in this case, the procedure will execute as a normal task and behave normally, showing all its output and error messages as usual.


Output from a login trigger
The main effect of executing as a background task is that any output from the login trigger stored procedure is not sent to the client application, but is written to the ASE errorlog file instead. For example, output from print or raiserror statements in the login trigger stored procedure and some error messages, will be written to the ASE errorlog.

Any output from print/raiserror will be prefixed with the words background task message or ...error in the ASE errorlog. For example, the statements print 'Hello!' and raiserror 123456 in a login trigger will show in the ASE errorlog as follows (assuming error message 123456 exists):
(...) background task message: Hello!
(...) background task error 123456: This is test message 123456
However, not all output goes to the ASE errorlog: any result sets from SELECT statements (which are normally sent to a client connection) will not be visible anywhere, not even in the ASE errorlog; instead, this information 'disappears' completely. INSERT...SELECT statement, SELECT...INTO statements or other regular DML statements (which do not send a result set to the client application), as well as DDL statements which are allowed in a stored procedure, are executed normally.
While some error messages are captured in the ASE errorlog, others are not; for example, a divide-by-zero error during execution of the login trigger will not be reported anywhere.
Updated 31-Jul-2003: Raymond Mardle reports that divide-by-zero will break the connection as long as you're not doing login/logout/security/adhoc auditing. When using these auditing options, divide-by-zero will not break the connection anymore, but produce some error message instead. Solution: use syb_quit().

Based on some of my own tests, it seems that errors which abort the statement but not the batch (like permission errors, rule violations, duplicate key errors, etc) are captured in the ASE errorlog, but errors which abort the batch are not. Please note that this has only been determined empirically, and is not documented anywhere. (note that this background execution is similar to the way thresholds are executed, which also run in the background without an active user connection, and where output also ends up in the ASE errorlog).


Possible applications
Login triggers were originally designed as a part of the new 'row-level access' feature in ASE 12.5. In this context, a login trigger is used in combination with the new 12.5 features of 'access rules' and 'application contexts' to set up row-level access controls once a session logs into ASE (BTW, note that using 'access rules' require licensing the ASE_ASM option; login triggers and application contexts do not require additional licensing).

However, login triggers can be used for other purposes as well. One example is detailed below, and addresses the need for a way of limiting the number of concurrent connections to ASE by a specific login (this has often been mentioned by DBAs and developers). Before ASE 12.5, there was no mechanism to achieve this in ASE, but this can now be implemented using login triggers.
Consider the following stored procedure which is configured to be a login trigger:
create procedure my_proc 
as
  declare @cnt int, @limit int, @loginname varchar(32)

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

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

  if @cnt > @limit -- check the limit
  begin
     select @loginname = suser_name()
     print 'Aborting login [%1!]: exceeds session limit [%2!]',
           @loginname, @limit
     select syb_quit() -- abort this session
  end
When the login connects to the ASE server, nothing special will happen as long as there are not already two other active sessions for this login, and the connections will be successful. However, when two other sessions are already active, the current session will be terminated by the login trigger calling the syb_quit() function (see below for details).
% isql -SASE125 -Umy_login -Pmypasswd
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 ASE errorlog file will contain this message:
(...) background task message: Aborting login [my_login]: 
exceeds session limit [2]

Note that this solution does allow you to limit the number of concurrent connections for a specific login in a rather strong-armed manner. A disadvantage of this approach is that the client application cannot (or at least, not easily) detect the reason why the session was terminated; to reliably display a message to the application user saying something like 'Too many users right now -- please try later', a different approach should probably be followed.

Instead of calling the built-in function syb_quit() , which makes the server simply terminate the current session, one could also get the same effect by deliberately causing an error in the stored procedure. For example, dividing by zero will cause the login trigger stored procedure to abort and the session to be terminated. Other errors might yield the same result. Still, calling syb_quit() seems the most reliable way of achieving this goal, as future enhancements or fixes could possibly change the way errors are dealt with. Note that syb_quit() , while formally undocumented, is safe to use. For example, it is used in Sybase's own T-SQL script which installs the HA stored procedures ($SYBASE/$SYBASE_ASE/scripts/insthasv).


In ASE 15, there is another way in which login triggers may turn out to be highly useful. The new query processing features in ASE 15 bring the important new concept of an 'optimization goal', which can be set server-wide as well as for individual sessions. A key to fully utilising the capabilities of ASE 15 is to pick the right optimization goal. In cases where a session needs a different optimization goal than the rest of the server, you could set goal this in the login trigger with the command set plan optgoal..... Using a login trigger has the advantage that this command can be executed while no existing SQL code has to be changed.
Please refer to chapter 15 of my Tips & Tricks book for more information about optimization goals, and tricks to set these in a login trigger.

Things that won't work (but sometimes do)
In principle, any type of application-specific processing can be done in a login trigger. However, some things will not work in practice, depending on your ASE version. For example, you cannot create #temp tables that can be used later in the session. The reason that this is not possible has nothing to do with login triggers as such, but this simply is a feature of stored procedures: creating #temp tables in a stored procedure only has an effect inside that stored procedure; once the procedure completes, the #temp tables are automatically dropped.

Before ASE 12.5.4 (and 15.0.1) it was also not possible to use a login trigger to set session-specific options (such as set rowcount n, etc), for much the same reasons as why persistent #temp tables couldn't be created in a login trigger: the changed settings evaporated at the end of a stored procedure. However, as of ASE 12.5.4 (and 15.0.1), ASE allows persistent settings of session-level options in a login trigger, so that the settings still apply to the user's session after the login trigger has completed.
The new feature underlying this changed behaviour is the command set export_options on, which allows you to export changed settings (for most settings, though not for all) from a stored procedure. For login triggers, this setting is enabled by default (there's actually more to this; for more details, see section 13.11 of my Tips & Tricks book).

Note that the restrictions around #temp table creation have not changed as of ASE 12.5.4/15.0.1.

Things that are not a good idea
Technically, almost anything you can do from T-SQL is possible from a login trigger stored procedure. However, not all that is possible seems a good idea, and I would strongly recommend the following:
  • don't put any login triggers on the 'sa' login; you don't want to be locked out of the ASE server because the 'sa' login is effectively locked by a failing login trigger.
  • don't do extensive processing in a login trigger. Anything that may take longer than at most a few seconds, or has a risk of being blocked or deadlocked, should better be avoided.
    Executing waitfor statements doesn't look like a good idea either.
  • don't do tricky things with CIS or XP server in login triggers. There's some interesting ideas for creatively using these features elsewhere on this web site , but I'd rather not do this in login triggers.
  • try to avoid elementary programming mistakes, especially in a global login trigger. Imagine what happens when, for example, your global login trigger inadvertently *always* calls syb_quit()? Right: nobody, including you yourself, the DBA, will be able to log in anymore...
    Just in case this happens anyway, restart the ASE server with traceflag 4072, which will disable the global login trigger. (and when you kill the ASE server, please don't use kill -9, but use kill -15 instead -- see chapter 3 of my Tips & Tricks book for the full story).

Practical issues
Here are some things to keep in mind when using login triggers:
  • Once a stored procedure is configured to be a login trigger, that stored procedure cannot be dropped anymore. This makes developing and testing a login trigger stored procedure rather clumsy because the login trigger must be unconfigured every time before dropping and recreating the procedure. This may be even more inconvenient because configuring a login trigger requires sso_role.
    To avoid such problems, it's a good idea to use a dummy top-level procedure as the login trigger, which does calls a second stored procedure containing all functionality. This second procedure can be dropped and recreated easily:
  • -- this proc. contains the login trigger functionality
    create procedure proc_body
    as
       -- procedure code goes here...
    go
    
    -- this procedure is configured as the login trigger 
    create procedure proc_top 
    as
       exec proc_body
    go
    
    sp_modifylogin my_login, 'login script', proc_top
    go
    
    
  • It is possible to obtain session information (such as the loginname which has caused the login trigger to be executed) using the regular built-in functions and global variables. Additional information can be obtained by querying the sysprocesses table, as illustrated in the below example:
  • create procedure my_proc_2 
    as 
       declare @s varchar(12), @loginname varchar(32)
    
       select @s = status from master.dbo.sysprocesses 
       where spid = @@spid
    
       select @loginname = suser_name()
    
       print 'spid=[%1!]; login=[%2!]; status=[%3!]', 
             @@spid, @loginname, @s
    
    One interesting thing is that the above code can identify whether it is executing as a login trigger or as a regular stored procedure based on the value of the sysprocesses.status column. When executed as a login trigger, the output is as follows:
    (...) background task message: spid=[22]; 
    login=[my_login]; status=[background]
    
    When executed as a regular procedure, the output looks like this:
    spid=[13]; login=[my_login]; status=[running]
    

Common problems
This section lists some problems you may encounter when using login triggers.
  • When using login triggers, always check the ASE errorlog for error messages. Things may have gone wrong which did or did not cause problems for the session, but the only way to find out is to check the errorlog. One consequence is that you shouldn't use login triggers if you don't have access to the ASE errorlog, or if the errorlog is not routinely checked for error messages.

  • When logging in to ASE using a client application like isql , this client application is unaware of the existence execution of a login trigger. It will therefore present a command prompt immediately after the successful login. However, any commands submitted at the command isql prompt will not be executed by ASE until the login trigger has successfully completed execution. As shown in the next point, the prompt will still be displayed even if the login trigger has terminated the user connection.

  • The login trigger stored procedure should be executable for the user logging into ASE; if no execute permission has been granted, an error message will occur in the ASE errorlog and the user connection will be terminated immediately (though isql will still show a command prompt -- see the previous point).
  • % isql -SASE125 -Umy_login -Pmypasswd 
    1> -- no exec permission; connection will be closed,
    2> -- any command will fail
    3> select 1
    4> go
    CT-LIBRARY error:
    ct_results(): network packet layer: internal net 
    library error: Net-Library operation terminated due to
    disconnect
    
    When this happens, the ASE errorlog will contain a message like: EXECUTE permission denied on object my_proc, database my_db, owner dbo
  • The login trigger stored procedure should not have any parameters for which no default values are specified. If it does, the login trigger will fail and the connection will be closed as in the example in the previous point. The ASE errorlog will contain messages like Procedure my_proc expects parameter @param1, which was not supplied. .
  • Some errors that may occur during execution of the login trigger are not captured in the ASE errorlog and may go unnoticed ( see here ). It may therefore be difficult to debug a failing login trigger.




 This document is located at www.sypron.nl/logtrig.html

 Copyright © 1998-2014 Rob Verschoor/Sypron B.V. 
 All rights reserved. All trademarks are acknowledged.