lørdag den 28. januar 2012

Et loginsystem

Først vil jeg starte med at ønske mine læsere et godt nytår. Jeg håber alle kom godt ind i det. Dernæst skal jeg beklage den lange periode uden blogindlæg fra min side, men det er der flere grunde til. Dels, mellem jul og nytår, fik min computer et psysisk sammenbrud.

Jeg ville sætte en SSD disk i computeren som system disk, det gjorde computeren særdeles ustabil. Det viste sig efter lang tids søgen, at både grafikkort OG bund kort ikke var kompatibel med en SSD disk. Jeg endte med at købe en anden computer, fordi SSD disken ville jeg have.

Dernæst har jeg været ved at forsøge at flytte min blog over til sit eget domæne og dermed ud af Blogger.com - ikke fordi denne platform fejler noget, men nærmere fordi jeg ikke synes, jeg har ordenligt styr på min besøgsstatistik. Så indtil videre bliver bloggen her, men jeg vil stadig arbejde på at flytte den.

Ikke mere udenomssnak nu, nu til sagen.

I dette blog indlæg vil jeg fortælle om et login system baseret på en Microsoft SQL Server. Jeg har valgt SQLServer af flere grunde. Dels er det den jeg kender bedst både fra mit nuværende arbejde og tidligere jobs, og dels fordi den er gratis. Express udgaven af MS SQL er gratis og kan hentes på Microsofts hjemmeside her. Desværre må jeg skuffe de af mine læsere som ikke vil bruge MS SQL, fordi dette login system baserer sig meget på MS SQL, idet jeg bruger de interne system tabeller som rygraden.

Den første jeg vil gøre er at oprette en ny database og deri en tabel til mine brugere. Dette er kun nødvendigt i denne demo, men resten kan nemt flettets ind i en eksisterende applikation.

Jeg starter SQL Server Management Studio op og skriver det følgende:

CREATE DATABASE [LoginDEMO] 
GO 
USE [LoginDEMO] 
GO 
CREATE TABLE [dbo].[Users] 
  ( 
     [UserID]       [INT] IDENTITY(1, 1) NOT NULL, 
     [UserName]     [VARCHAR](50) NOT NULL, 
     [UserPassword] [VARCHAR](50) NULL, 
     CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserID] ASC )
      WITH (
        pad_index = OFF, 
        statistics_norecompute = OFF, 
        ignore_dup_key = OFF, 
        allow_row_locks = on, allow_page_locks = on) ON [PRIMARY] 
  ) 
ON [PRIMARY] 
GO 
INSERT INTO Users (UserName, UserPassword) 
VALUES      ('Jens1', 'pass1234') 

INSERT INTO Users (UserName, UserPassword) 
VALUES      ('Jens2', 'pass1234') 

Dette giver en ny database "LoginDEMO" og en tabel "Users" med to brugere i: "Jens1" og "Jens2".

Dernæst skal jeg bruge en tabel til at registrere, hvem der er logget ind i mit program og fra hvilken computer. Den har jeg valgt skal se således ud:


CREATE TABLE [dbo].[USERSESSIONS] 
  ( 
     [HOSTPROCESS]       [INT] NOT NULL, 
     [LOCALCOMPUTERNAME] [VARCHAR](50) NOT NULL, 
     [USERID]            [INT] NOT NULL, 
     [LOGINTIME]         [DATETIME] NOT NULL, 
     CONSTRAINT [PK_HOSTPROCESS] PRIMARY KEY CLUSTERED ( [HOSTPROCESS] ASC )WITH 
     (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
  ) 
ON [PRIMARY] 


Når en applikation logger ind i SQL Serveren første gang, får den tildelt et unikt Process ID - kaldet HostProcessID - på præcis samme måde som ProcessID i Windows. Når applikationen så opretter en eller flere connections til databasen, får hver af disse connections tildelt et "Server Process ID" - til daglig bare forkortet SPID. En given connection's SPID kan man iøvrigt få udleveret ved hjælp af SQL'en: SELECT @@SPID.  HOSTPROCESS ID skal gemmes i tabellen over brugere, fordi det blandt andet skal bruges til at finde ud af, hvilken computer brugeren er logget ind fra.

Det må være det næste naturlige skridt: At skrive en funktion der kan fortælle, hvilken computer man kalder funktionen fra:

CREATE FUNCTION [dbo].[GetLocalComputerName]() 
RETURNS VARCHAR(255) 
AS 
  BEGIN 
      RETURN 
        (SELECT s.hostname 
         FROM   MASTER..sysprocesses S 
         WHERE  s.spid = @@SPID) 
  END 


Som det ses, udnytter jeg system variablen @@SPID (som var ID'et på min connection) til at slå op i tabellen "sysprocesses" og finde ud af hvilken computer funktionen bliver kaldt fra.

Jeg vil lige vise her, at det rent faktisk virker :


Det ovenstående billede er fra min Management Studio. Som det ses hedder den computer jeg sidder ved JENSBORRISHOLT.

Når nu man kan finde computernavnet direkte via SQL, kan vi lige så godt lade SQL serveren selv udfylde det i USERSESSIONS tabellen, og det samme med LOGINTIME.

Jeg laver et par ændringer til tabellen:

ALTER TABLE [dbo].[USERSESSIONS] ADD CONSTRAINT 
[DF_USERSESSIONS_LOCALCOMPUTERNAME] DEFAULT ([dbo].[GetLocalComputerName]()) FOR [LOCALCOMPUTERNAME]
GO 
ALTER TABLE [dbo].[USERSESSIONS] ADD DEFAULT (getdate()) FOR [LOGINTIME] 


Nu har jeg en tabel hvori jeg kan gemme hvem der er logget ind, og en tabel med mine brugere i. Og jeg har fået SQL serveren til at udfylde nogle af felterne i USERSESSIONS tabellen. Nu vil det være naturligt at skrive noget til at logge ind med. Denne procedure har jeg valgt at splitte op i to: En der arbejder på UserID, og en der tager et brugernavn og password. Grunden til jeg har valgt en opsplitning er, at den procedure der arbejder på UserID ,er den der udfører arbejdet. Den rydder op i UserSessions - altså fjerner de linjer fra de brugere der ikke længere er logget ind, og den tjekker om det pågældende UserID rent faktisk findes. Hvis denne funktion bliver kaldt med NULL som parameter UserID, laver den bare en oprydning i UserSessions, dette er ganske nyttigt hvis du fx. vil lave et skærmbillede med hvem der er logget ind.

Lad mig vise koden til de to procedurer:

CREATE PROCEDURE [dbo].[sp_InternalLogOnUser](@UserId INT) 
AS 
  BEGIN 
      SET nocount ON 

      DECLARE @HostProcess INT 

      SELECT @HostProcess = S.HostProcess 
      FROM   MASTER..SysProcesses S 
      WHERE  S.spid = @@SPID 

      DELETE FROM UserSessions 
      WHERE  HostProcess = @HostProcess 

      DELETE FROM UserSessions 
      WHERE  HostProcess NOT IN (SELECT HostProcess 
                                 FROM   MASTER..SysProcesses S) 


      IF @UserId IS NULL 
        RETURN @@ERROR; 

      IF NOT EXISTS(SELECT 1 FROM   Users U WHERE  U.UserID = @UserId) 
        BEGIN 
            RAISERROR ('Brugeren findes ikke i databasen',16 /*kritisk ERROR*/,1) 
            RETURN @@ERROR; 
        END; 

      INSERT INTO UserSessions 
                  (HostProcess,USERID) 
      VALUES      ( @HostProcess,@UserId ) 

      RETURN @@ERROR; 
  END; 

CREATE PROCEDURE [dbo].[sp_LogOnUser](@UserName VARCHAR(50),@Password VARCHAR(50)) 
AS 
  BEGIN 
      DECLARE @UserId INT 

      SELECT @UserId = UserID 
      FROM   Users U 
      WHERE  U.UserName = @UserName 
             AND U.UserPassword = @Password; 

      IF ( @UserId IS NULL ) 
         AND ( @UserName IS NOT NULL ) 
        BEGIN 
            RAISERROR ('Brugeren findes ikke i databasen',16 /*kritisk ERROR*/,1) 
            RETURN @@ERROR; 
        END; 

      EXEC sp_InternalLogOnUser @UserId 

      RETURN @@ERROR; 
  END 


Som det fremgår af koden vil det være muligt at kalde EXEC sp_LogOnUser NULLNULL  for at rydde op i UserSessions. Dette er dog kun nødvendigt, hvis man ønsker et øjebliksbillede af hvem, der er logger ind og hvorfra. Normalt vil sp_InternalLogOnUser foretage oprydningen, når man logger ind.

Hvis man ønsker yderligere brugerstyring kan proceduren sp_InternalLogOnUser udvides således, at samme bruger kun kan logge på fra 1 computer ad gangen, eller at man samlet set kun kan have 5 brugere. Jeg har implementeret det således, at samme bruger kun kan logge ind 1 gang.

Inden jeg giver mig i gang med Delphi delen, vil jeg lige vise hvordan man så rent faktisk logger ind:



Som det ses kalder jeg bare sp_LogOnUser med brugernavn og password, og så klarer SQL Serveren resten. Hvis man angiver et forkert brugernavn og password, vil SQL serveren rejse en exception: 


Denne exception kan man så fange senere inde i sit Delphi program. Apropos nu til noget Delphi kode:

Det første jeg skal bruge for at teste mit login system er naturligvis en boks, hvor man kan indtaste brugernavn og password. Jeg har valgt en helt simpel model i mit eksempel - helt uden kode:


Så skal jeg bruge en funktion til - dels at logge brugeren ind i applikationen, og dels validere om brugeren har indtastet det rigtige brugernavn og password. Her havde jeg tidligere skrevet en stored procedure som klarede det job, så den skal bare kaldes. I min demo applikation har jeg valgt at benytte mig af dbGO (ADO) som databaselag - ikke fordi det er ret godt, men fordi det er indbygget i Delphi og det kræver ingen DLL el. lign. for at køre.

På designtime har jeg sat en connection komponent på min form og indsat en connectionstring i den. TAdoConnection'en kan også selv opbygge en connection string, hvis ikke lige du har en selv. Jeg regner med mine læsere et i stand til at oprette forbindelse til databasen gennem dbGO, så det vil jeg ikke komme yderligere ind på her. I stedet vil jeg gå direkte til sagen og vise min validerings-funktion:

function TMainForm.IsUserOK(const aUserName, aPassword: String): Boolean;
var
  Query: TADOQuery;
begin
  Result := False;
  Query := TADOQuery.Create(self);
  Query.Connection := ADOConnection1;
  try
    Query.SQL.Text := 'exec sp_LogOnUser '
AnsiQuotedStr(aUserName, #39) + ', ' + AnsiQuotedStr(aPassword, #39);
    try
      Query.ExecSQL;
      Result := True;
    except
      // vores SP retunerer en exception hvis man ikke kan logge ind
      // Her bør man i en rigtig applikation tjekke på error type etc.
      // Dette er udeladt her, da dette kun er en DEMO.
    end;
  finally

    FreeAndNil(Query);
  end;
end;



Som det ses er det ganske lige til: Kald proceduren sp_LogonUser med brugernavn og password og fang en eventuel exception. I et rigtigt program bær man nok lave det lidt mere intelligent end blot en simpel exception. Man kunne fx styre det vha. forskellige exception levels eller noget helt andet. Mulighederne er mange, og ude af scope for dette blogindlæg, Her vil jeg blot vise princippet.

Med denne funktion i hånden mangler vi bare to ting: Den ene er, at få funktionen ovenfor kaldt. Den anden er at liste brugere logget på systemet.

Det første først. Jeg har valgt at vise login skærmen i begyndelsen af FormCreate - og altså dermed lige når programmet starter. Når denne retunerer, er det bare at kalde IsUserOK funktionen.

procedure TMainForm.FormCreate(Sender: TObject);
begin
  with TLoginDialog.Create(self) do
    try
      if (ShowModal <> mrOk) or (not IsUserOK(LabeledEdit1.Text, LabeledEdit2.Text)  ) then
        Application.Terminate
      else
        ShowUserSessions;
    finally
      free;
    end;
end;


Og så en funktion til at vise, hvem der er logget ind:



procedure TMainForm.ShowUserSessions;
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'select * from UserSessions';
  ADOQuery1.Open;
end;
AdoQuery1 er forbundet med en ADOConnection på design time. Jeg bruger så en datasource til at vise resultatet i et DBGrid.

Til slut vil jeg vise min applikation med hhv. én og to brugere logget ind.

Det var det hele for denne gang. Sourcekoden kan sædvanen tro hentes her. I pakken findes både SQL scriptet og Delphi koden. Jeg håber vi ses til DAPUG erfamøde den 7. marts i Fredericia. Jeg har lovet at holde et oplæg. Emnet vil blive offentliggjort senere.

Jens Borrisholt

Ingen kommentarer:

Send en kommentar