Sybase ASE, reverse et duplication des logins

Introduction

Cet article s’adresse aux nouveaux DBA autour de Sybase Adaptive Server Enterprise pour dupliquer et synchroniser des logins, opérations au final très simples après quelques mois d’expérience autour de Sybase ASE en mettant à jour directement les tables systèmes, ce qui est officiellement non supporté…

Dans cet article, la base DALI_COMP est déplacée du serveur DCP_P1_ASE vers DTH_P1_ASE et pour cela les logins utilisés dans la base DALI_COMP (DCP_P1_ASE) sont dupliqués vers DTH_P1_ASE. Sans connaître les mots de passe en clair des logins, ces mots de passe peuvent être dupliqués très simplement vers le serveur cible selon les méthodes présentées ici.

Une des méthodes engendre potentiellement une désynchronisation des logins avec les users dans la base cible (Server user id is not valid a valid user in database), notamment dans le cas d’un dump/load : la correction des désynchronisations est décrite également.

Les logins n’ont aucun trigger login attaché et aucun rôle serveur attribué. La base DALI_COMP existe déjà dans la cible DTH_P1_ASE.

Dans tous les cas de figure présentés, le paramètre serveur "allow updates to system tables" doit être à 1 dans le serveur cible car les tables systèmes vont être directement mises à jour. Dans le cas contraire, l’erreur 10321 est levée lors des opérations :

Server Message: DTH_P1_ASE - Msg 10321, Level 14, State 1:
Ad-hoc updates to system catalogs not enabled.
A user with System Security Officer (SSO) role
must reconfigure system to allow this.

Pour passer ce paramètre à 1 si ce n’est pas le cas :

%> isql -Usa -SDTH_P1_ASE

exec sp_configure 'allow udpates to system tables',1
go

À l’issue des opérations, ce paramètre doit repasser à 0 par sécurité.

Cas simple #1 : iso version et aucun login applicatif n’existe encore dans le serveur cible - Utilisation de bcp

Dans le cas le plus simple, les serveurs sont iso-version, c’est à dire que la structure de syslogins dans la base master est identique entre la source et la cible et aucun login applicatif n’a encore été créé dans le serveur cible. Une opération "bcp out / bcp in" suffit simplement dans ce cas.

Une vue pointant sur syslogins est créée dans la source pour tous les logins existant dans les tables sysusers et sysalternates (alias) de la base à déplacer (DALI_COMP) sauf si il s’agit des logins sa et probe :

%> isql -Usa -SDCP_P1_ASE

use master
go
create view v_export_logins
as
   select * from syslogins
   where name not in ('sa','probe')
   and ( suid in (select suid from DALI_COMP..sysusers)
   or suid in (select suid from DALI_COMP..sysalternates))

Les résultats de la vue v_export_logins sont exportés de la source et injectés directement dans la table syslogins de la cible avec le binaire bcp en mode caractères ( -c ) :

%> bcp master..v_export_logins out v_export_logins.bcpc -Usa -SDCP_P1_ASE -c

%> bcp master..syslogins in v_export_logins.bcpc -Usa -SDTH_P1_ASE -c

Avec cette méthode, les valeurs suid (Server User ID) et les mots de passe des logins sont conservés.

Cas #2 : iso version et des logins applicatifs existent déjà dans le serveur cible - Utilisation de sp_addlogin avec mise à jour des mots de passe

Les serveurs sont toujours iso-version (même structure de syslogins dans la base master entre la source et la cible) mais des logins applicatifs existent déjà dans la cible.

Si les logins sont importés par bcp comme dans le cas #1, des conflits de valeurs pour suid (Server User Id), clé unique de syslogins, peuvent survenir si des logins déjà existants dans la cible ont le même suid que les logins importés. L’erreur "Attempt to insert duplicate key" est levée lorsqu’un conflit de suid survient à l’import avec bcp in.

Les comptes sont donc créés classiquement sur la cible avec sp_addlogin avec un mot de passe générique puis les mots de passe sont mis à jour à leur valeur réelle. Dans cette méthode, les valeurs de suid ne sont donc plus transférées directement et seront donc très probablement différentes entre la source et la cible pour les logins dupliqués. La base par défaut des logins recréés sur la cible avec sp_addlogin est la base cible DALI_COMP.

Une vue pointant sur les colonnes name et password de la table syslogins est créée dans la source pour tous les logins existant dans les tables sysusers et sysalternates (alias) de la base à déplacer (DALI_COMP) sauf si il s’agit des logins sa et probe :

%> isql -Usa -SDCP_P1_ASE

use master
go
create view v_export_logins
as
   select name, password from syslogins
   where name not in ('sa','probe')
   and ( suid in (select suid from DALI_COMP..sysusers)
   or suid in (select suid from DALI_COMP..sysalternates))

Les résultats de la vue v_export_logins sont exportés de la source :

%> bcp master..v_export_logins out v_export_logins.bcpc -Usa -SDCP_P1_ASE -c

v_export_logins.bcpc

dali_comp_dbo     0x1c....
dali_compusr      0x1c....
dali_ronly        0x1c....

Les résultats sont ensuite traités par le programme awk syb_revpwd.awk avec la variable DB=DALI_COMP, programme qui génère automatiquement les commandes sp_addlogin et les commandes update sur syslogins pour mettre à jour le mot de passe des logins avec leur valeur hexadécimale :

%> cat v_export_logins.bcpc | awk -v DB=DALI_COMP -f syb_revpwd.awk > revlogins.sql

revlogins.sql

exec sp_configure 'allow updates', 1
go

exec sp_addlogin 'dali_comp_dbo' , '123456!' , 'DALI_COMP'
update master..syslogins
set password=0x1c054de019234dcc3f26edff266dbf906176112645537005ced0f85e29cdc006ebf5ffceaf433abd08e2cde89713445c1e23eddb4b42fd568051202827bd87dff02a9dba34367490
where name='dali_comp_dbo'
go

 ...

exec sp_configure 'allow updates', 0
go

Le programme awk syb_revpwd.awk est le suivant :

syb_revpwd.awk

BEGIN {
        print "exec sp_configure 'allow updates', 1"
        print "go\n"
}
{
        PWD=$2
        printf "exec sp_addlogin '%s' , '%s' , %s\n", $1, "123456!", $3
        printf "update master..syslogins set password=0x%s where name='%s'\n", PWD,$1
        print "go\n"
}
END {
        print "exec sp_configure 'allow updates', 0"
        print "go\n"
}

Le résultat obtenu est ensuite exécuté avec isql sur la cible :

%> isql -Usa -SDTH_P1_ASE -irevlogins.sql

Cas #3 : Hétérogénéité des versions Sybase Adaptive Server Enterprise

Dans les cas où la version de Sybase ASE entre la source et la cible est différente, par exemple ASE 12.0 pour la source et ASE 15.0.2 pour la cible : la méthode du cas #2 est parfaitement applicable.

La méthode du cas #2 a le désavantage de ne pas garantir des valeurs identiques pour les suid.

Si la cible n’a aucun login applicatif déjà existant et que la conservation des suid dans la duplication des comptes est souhaitée et impérative, la méthode #1 avec bcp implique de rajouter dans le fichier exporté les colonnes nouvelles de syslogins entre la version 12.x et 15.x en prenant garde au caractère NULL ou NOT NULL de ces nouvelles colonnes.

Par exemple, entre la version 12.0 et 15.0.2 de Sybase Adaptive Server Enterprise, 6 nouvelles colonnes ont été ajoutées dans la table système syslogins :

CREATE TABLE syslogins (
 ...
   srvname        varchar(30)      null,
   logincount     smallint         null
)

CREATE TABLE syslogins (
 ...
   srvname        varchar(30)      null,
   logincount     smallint         null,
   procid         int              null,                      
   lastlogindate  datetime         null,                    
   crdate         datetime         null,                    
   locksuid       int              null,                      
   lockreason     int              null,                      
   lockdate       datetime         null                     
 )

Lors de l’application de la méthode #1, les colonnes exportées sont séparées par des tabulations par défaut, il est toutefois possible de modifier le caractère de séparation des colonnes avec l’option -t du binaire bcp. 17 colonnes sont exportées pour une version Sybase ASE 12.0.

Le séparateur de colonnes lors de l’export est dans cet exemple ";" et non la tabulation par défaut.

%> bcp master..v_export_logins out v_export_logins.bcpc -Usa -SDEC_T2_ASE -t";" -c

%> cat v_export_logins.bcpc | awk -F";" '{print NF}' | sort -u

17

En ajoutant 6 fois le caractère de séparation des colonnes utilisé lors de l’export avec bcp, le fichier obtenu est alors conforme à la version cible Sybase ASE pour l’import dans syslogins avec bcp :

%> cat v_export_logins.bcpc | awk -F";" '{print $0";;;;;;"}' > import.bcpc

%> bcp master..syslogins in import.bcpc -Usa -SDTH_P1_ASE -c

Cas des dump/load avec des désynchronisations des suid (Server user id is not a valid user in database)

À l’issue de la création des logins dans le serveur cible, la commande sp_changedbowner est utilisée pour changer le propriétaire dbo de la base DALI_COMP

%> isql -Usa -SDTH_P1_ASE

use DALI_COMP
go
sp_changedbowner 'dali_comp_dbo'
go

Lorsqu’un dump/load est utilisé pour charger la base DALI_COMP dans la cible et que la méthode #2 est utilisée (sp_addlogin + mise à jour des mots de passe), avec une forte probabilité, une désynchronisation se produit entre les valeurs suid contenues dans master..syslogins et les valeurs suid contenues dans la table sysusers de la base DALI_COMP chargée avec la commande LOAD DATABASE. Cette désynchronisation entraîne l’affichage du message d’erreur 916 :

Server user id %d is not a valid user in database '%.*s'

La resynchronisation des logins dans la cible entre master..syslogins et la table sysusers peut être simplement réalisée avec la requête ci-dessous :

%> isql -Usa -SDTH_P1_ASE

exec sp_configure 'allow updates',1
go
use DALI_COMP
go
update sysusers set a.suid=b.suid from sysusers a, 
master..syslogins b where a.name=b.name and a.suid != b.suid
go
exec sp_configure 'allow updates',0
go

Après avoir exécuté la resynchronisation des users avec la requête plus haut, si le message d’erreur 916 se produit encore, il s’agit non plus des users mais des éventuels alias stockés dans la table sysalternates (suid, altsuid). Pour la resynchronisation des alias, il est nécessaire de faire le reverse depuis la source :

%> isql -Usa -SDCP_P1_ASE

set nocount on
go

select suser_name(suid) as loginname,
       suser_name(altsuid) as aliasloginname 
into #aliases
from sysalternates
  where suser_name(suid) is not null
  and suser_name(altsuid) is not null
go

select 'sp_addalias ''' + loginname + ''', ''' + b.name + '''' + char(10) + 'go' + char(10)
from #aliases a, 
     sysusers b, 
     master..syslogins c
  where a.aliasloginname = c.name
  and c.suid = b.suid
go
          …
exec sp_addalias 'db2admin', 'dbo'     
go
…

La table sysalternates de la cible est alors tronquée (paramètre allow updates à 1) et les commandes sp_addalias générées depuis la source sont lancées sur la cible :

%> isql -Usa -SDTH_P1_ASE

exec sp_configure 'allow updates',1
go
use DALI_COMP
go
truncate table sysalternates
go
sp_addalias 'db2admin','dbo'
go
…
exec sp_configure 'allow updates',0
go