> Tech > T-SQL pour une application d’annuaire téléphonique

T-SQL pour une application d’annuaire téléphonique

Tech - Par iTPro - Publié le 24 juin 2010
email

Le code T-SQL de l’application d’annuaire téléphonique inclut un ensemble d’objets de base de données qui permettent aux utilisateurs de rechercher les numéros de téléphone de leurs collègues. Par ailleurs, en entrant leur ID d’employé et leur mot de passe, les employés peuvent se servir de cette application pour changer

leur numéro de téléphone ainsi que leurs prénom et nom, mais pas les informations d’autres personnes. Les utilisateurs sont aussi en mesure de changer leur mot de passe, mais pas ceux de leurs collègues. L’administrateur de l’application peut modifier tous les mots de passe.

Le script T-SQL téléchargeable commence par créer la base de données SSMWSEmpPhones. Vous devez mettre à jour l’emplacement du fichier de base de données dans le script si le dossier d’installation de SQL Server diffère de l’emplacement par défaut sur le lecteur C. Le script crée et remplit ensuite deux tables. La table EmpPhones comporte cinq colonnes pour le suivi des informations suivantes de chaque employé : ID d’employé (Employee ID), prénom, nom, numéro de téléphone et mot de passe. Le script CREATE TABLE pour EmpPhones inclut trois contraintes : une pour une clé primaire basée sur Employee ID, ainsi qu’une contrainte unique et une contrainte de contrôle de colonne pour la colonne Phone. Une deuxième table, dénommée AppVariables, contient le mot de passe de l’administrateur.

Le coeur du composant de base de données de l’application est constitué de scripts pour trois procédures stockées et une fonction UDF : la première procédure stockée (LikeLname), créée par le code suivant, permet à un utilisateur de rechercher un numéro de téléphone en saisissant une chaîne :

CREATE PROCEDURE LikeLname
@Start nvarchar(40)
AS
SELECT Fname, Lname, Phone
FROM EmpPhones
WHERE Lname LIKE @Start + ‘%’

LikeLname peut retourner les lignes de la table EmpPhones qui incluent un nom commençant par une chaîne de caractères saisie par l’utilisateur, telle qu’une lettre ou une combinaison de lettres. Le paramètre @Start de la procédure permet cette opération.

Le listing 1 présente le script T-SQL pour la deuxième procédure stockée, UpdateEmpPhonesRow, laquelle permet aux utilisateurs de mettre à jour les valeurs de colonne de la table EmpPhones pour le prénom, le nom et le numéro de téléphone. En appelant la procédure stockée, un utilisateur peut actualiser une, deux ou trois valeurs de colonne, bien que cette procédure ne génère pas d’erreur si aucune modification n’est spécifiée. Les paramètres @IDToEdit et @Password associés aux valeurs de la colonne Phone sont utilisés par la procédure stockée pour déterminer si une ligne de EmpPhones comporte une valeur EmployeeID correspondant à @IDToEdit et une valeur Password correspondant à @Password. L’argument SELECT de l’opération EXISTS en haut de la procédure identifie des lignes uniques car les valeurs de la colonne Phone ne peuvent pas être du type null et elles ont une contrainte unique.

Si la procédure UpdateEmpPhonesRow trouve une ligne qui correspond aux critères @IDToEdit et @Password, elle recherche sélectivement la présence de valeurs non définies par défaut pour @NewFname, @NewLname et @New- Phone. Dès qu’une valeur non définie par défaut est détectée, la procédure appelle l’instruction UPDATE afin d’assigner ladite valeur à la valeur de colonne Fname, Lname ou Phone appropriée.

L’application d’annuaire téléphonique permet aux utilisateurs de changer leur mot de passe en modifiant les valeurs Password appropriées dans la table EmpPhones. Le listing 2 illustre une fonction UDF et une procédure stockée destinées à valider un nouveau mot de passe et à mettre à jour la base de données avec ce dernier. En utilisant la fonction UDF ValidateNewPW() afin de renvoyer le nouveau mot de passe sous forme de valeur scalaire, l’application explique comment exposer une fonction UDF en tant que méthode Web, comment capturer la valeur scalaire renvoyée et comment se servir de cette valeur comme argument pour une procédure stockée (UpdatePassword).

Le listing 2 inclut le script T-SQL destiné à créer les deux objets de base de données car leur interaction est très étroite. Vous avez peut-être noté que l’application passe les informations d’ID d’utilisateur et de mot de passe sans les crypter. Je n’ai pas ajouté la fonction de cryptage, afin que les exemples de création d’un service Web de base restent simples, mais vous pouvez ajouter ce mécanisme à une application de production.

Examinons maintenant la fonction UDF ValidateNewPW() et la procédure stockée UpdatePassword afin de voir leur interfonctionnement. La fonction UDF renvoie un nouveau mot de passe valide si l’un de ses paramètres d’entrée correspond à une valeur de la colonne Password dans la table EmpPhones ou est un mot de passe d’administrateur. Une instruction IF…ELSE contenant des instructions IF…ELSE imbriquées met en oeuvre la logique pour l’instruction T-SQL précédente.
La fonction UDF retourne la valeur de son paramètre @NewPassword si un utilisateur a spécifié correctement son mot de passe avec le paramètre @OldPassword. La valeur courante du mot de passe d’administrateur réside dans la colonne SuperPassword de la table AppVariables. Si la valeur du paramètre @OldPassword ne correspond pas à une valeur de la colonne Password dans EmpPhones et si la valeur de @SuperPassword n’est pas un mot de passe d’administrateur, ValidateNewPW() renvoie la valeur de chaîne Failed.
La procédure stockée UpdatePassword, qui met à jour le mot de passe validé par la fonction UDF ValidateNewPW(), accepte deux paramètres. Le paramètre @IDToEdit pointe vers une ligne unique dans EmpPhones. La justification de l’utilisation ce paramètre au lieu du simple numéro de téléphone est simple : n’importe qui peut connaître les numéros de téléphone des autres personnes, mais vous ne souhaitez pas que toutes les personnes qui connaissent le numéro de téléphone de l’administrateur puissent modifier son mot de passe. Le paramètre @NewPassword spécifie le nouveau mot de passe pour la ligne dont la valeur de colonne EmployeeID correspond à @IDToEdit.

Le listing 3 montre la dernière séquence du code T-SQL dans le fichier .sql pour la base de données SSMWS EmpPhones. Ce code configure un login utilisable avec l’application. Ce login, intitulé SSMWSLogin, dispose de l’ensemble minimum d’autorisations nécessaire pour l’exécution du service Web car vous ne souhaitez pas que les utilisateurs de cette application puissent exécuter des fonctions non autorisées sur votre serveur de base de données. Cet ensemble minimum d’autorisations inclut l’accès à la base de données SSMWSEmpPhones et l’autorisation d’exécuter les procédures stockées et les fonctions UDF décrites.

Téléchargez gratuitement cette ressource

Sécurité Office 365 : 5 erreurs à ne pas commettre

Sécurité Office 365 : 5 erreurs à ne pas commettre

A l’heure où les données des solutions Microsoft de Digital Workplace sont devenues indispensables au bon fonctionnement de l’entreprise, êtes-vous certain de pouvoir compter sur votre plan de sécurité des données et de sauvegarde des identités Microsoft 365, Exchange et Teams ? Découvrez les 5 erreurs à ne pas commettre et les bonnes pratiques recommandées par les Experts DIB France.

Tech - Par iTPro - Publié le 24 juin 2010