Sybase ASE 15, les déclencheurs à la connexion (login triggers), cas pratiques

Introduction

Les 'login triggers', autrement dit, déclencheurs à la connexion, ou encore scripts de login sont une nouveauté ASE 12.5 bien pratiques et implémentés sous forme de procédure stockée attachée à un login ou à l’instance (script dans ce cas déclenché pour toute connexion).

La mise en place est réalisée par appel de la procédure système sp_modifylogin. Aussi, la cinématique de mise en œuvre est des plus simples : création d’une procédure stockée puis association à un login ou à l’instance.

La procédure s’exécute APRÈS la connexion et AVANT toute autre opération. Les variables globales T-SQL d’environnement sont donc disponibles (@@spid, @@... )

Restriction : la procédure est exécutée 'en tâche de fond' et donc n’est rattachée à aucun terminal. Aussi, il n’est pas possible de retourner des résultats de commandes 'SELECT' à l’utilisateur. Au mieux, le résultat de commandes comme print ou raiserror apparaît dans le fichier de log de l’instance.

L’usage de cette fonction est multiple : statistiques de connexion, contrôle et limitation d’accès, définition d’options d’optimisation (set export_options on, implicite dans le cadre d’un script de login), préparation de contexte. Quelques exemples et cas pratiques sont donnés dans cette note avec notamment les paramètres d’optimisation de la version 15.0.2.

Syntaxe de sp_modifylogin

Création

Création d’un 'login trigger' pour un login précis :

exec sp_modifylogin login, 'login script', 'stored_proc'

Exemple : exécution de la procédure stockée ls_batch lors d’une connexion du login batch

exec sp_modifylogin batch, 'login script', 'ls_batch'

Pour définir un 'login trigger' au niveau de l’instance et qui sera déclenché pour toutes les connexions, la valeur NULL est donnée au paramètre login.

exec sp_modifylogin null, 'login script', 'stored_proc'

Exemple : exécution de la procédure stockée ls_instance pour toutes les connexions au serveur Sybase

exec sp_modifylogin null, 'login script', 'ls_instance'

Suppression

exec sp_modifylogin login, 'login script', NULL

Suppression d’un 'login trigger' pour le login batch :

exec sp_modifylogin batch, 'login script', NULL

Suppression d’un 'login trigger' au niveau de l’instance :

exec sp_modifylogin NULL , 'login script', NULL

Exemple pratique

Voici par l’exemple, la création d’un 'login trigger' qui va limiter, enregistrer la connexion et limiter à 10 la concurrence (bien pratique pour les applications "pirates" qui pallient aux problèmes de performance en ligne à ligne en appliquant des connexions en parallèle…).

La commande syb_quit( ) ferme la session si des pré-requis ne sont pas vérifiés.

-- optionnel : creation d’une table de log
create table lt_t_logtable ( logdate datetime , status tinyint)
go

-- supprime l’ancien trigger login déjà associé au login my_login
exec sp_modifylogin my_login, 'login script', null
go

-- Recreation de la procedure de login 
drop proc lt_my_login
go
create proc lt_my_login
as
    declare @nb_max smallint, @loginname varchar(32)
    declare @nb_cur smallint
    
    select @nb_max=10, @loginname=suser_name()
    select @nb_cur = count(1)  
       from master.dbo.sysprocesses    
       where suid = suser_id()

    if @nb_cur > @nb_max
    begin
      insert lt_t_logtable values ( getdate() , 1)  
         print 'Connection refused to login %1! // max connections defined to %2! // ', @loginname, @nb_max
         select syb_quit()
    end else

    begin
       insert lt_t_logtable values ( getdate() , 0)  
    end
go

-- droit d’exécution sur la procedure lt_my_login au compte my_login
grant exec on lt_my_login to my_login
go

-- Affectation de la procedure lt_my_login au login my_login
exec sp_modifylogin my_login, 'login script', 'lt_my_login'
go

Applications possibles

Paramètres d’optimisation (set export_options on implicite dans les scripts de login)

Avec les versions 12.5.4 et 15.0.2, la nouvelle commande set export_options on|off a été introduite. Cette commande a pour effet d’exporter les paramètres de session depuis une procédure stockée ou une commande execute immediate.

Exemple :

select * from my_table -- retourne 10 lignes
go

create procedure p
as
set rowcount 3
select * from my_table
set export_options on     
go

exec p -- retourne 3 lignes
go

select * from my_table -- retourne 3 lignes
go

Dans les 'login triggers', la commande set export_options est implicite (on). Elle peut être toutefois désactivée en incluant la commande set export_options off dans le script de login.

Ainsi à la connexion d’un login, quelques paramètres d’optimisation peuvent être appliqués pour toute la session comme par exemple la désactivation des jointures par fusion, etc.

Exemple :

create proc lt_my_login
as
  set plan optgoal allrows_mix
  set hash_join off
  set store_index off
  set merge_join off 
  -- set export_options on implicite dans un trigger login
go

Filtrages

Les 'login triggers' sont également bien pratiques pour améliorer la sécurité d’accès, filtrer et se prémunir des connexions "pirates". Il est possible de mettre en œuvre un filtrage par IP, par nom de programme, par nom de machines… grâce à la table sysprocesses et la variable @@spid, variable globale disponible dans le script de login.

Filtrage par IP

select @ip=ipaddr from sysprocesses where spid=@@spid
If @ip not in ( ) syb_quit()

Filtrage par noms de machines

select @hostname=hostname from sysprocesses where spid=@@spid
If @hostname not in ( ) syb_quit()

Filtrage par noms de programmes

select @appname=program_name from sysprocesses 
where spid=@@spid
If @appname not in ( 'Rapid SQL', 'Embarcadero', '...') syb_quit()

Il est envisageable de filtrer également sur les colonnes clientname, clienthostname et clientapplname de la table sysprocesses à condition que le client ait appliqué les paramètres de session correspondants :

set clientname client_name 
set clienthostname host_name
set clientapplname application_name