Sicuramente vi sarà capitato di dover gestire l'accesso ad aree protette da login e password all'interno di siti web. In questo articolo riprendiamo questo tema ma con alcune varianti.
Un cliente ci chiede di sviluppare un'area riservata contente informazioni e documenti destinati solamente a certi utenti web. L'accesso a quest'area deve essere vincolato non solo alle login valide formate da username+password, ma anche da:
- un flag di abilitazione (per attivare o disattivare l'account rapidamente)
- una data di scadenza dell'account (oltre a quella data non è più permesso l'accesso)
- il numero di visite fatte dall'utente (es. un account potrebbe avere una validità per 100 visite un altro per 50)
Vediamo in breve le specifiche funzionali di questa applicazione (in parole povere cosa fa):
- autenticazione standard tramite username e password (login)
- validazioni aggiuntive per numero di visite effettuate e data di scadenza
dell'account - registrazione delle statistiche degli accessi all'area per ogni singolo utente
L'applicazione è sviluppata in un ottica multi-strato:
- ASP 2.0 e Javascripts per la parte scripting e presentazione
- Transact-SQL per le regole logiche (incapsulate all'interno delle stored
procedure) - SQL Server 7.0 per il supporto ai dati
Le pagine ASP
common.asp - contiene le routines di accesso al db (creazioni degli oggetti connection, recordset e command) e per la chiamata alle stored procedures
esci.asp - contiene le routines per disconnettersi dal db e distruggere gli oggetti creati
entra.asp - è la form HTML di login
valida.asp - processa la validazione chiamando le opportune stored procedure e setta una variabile session per indicare la validità dell'account durante la navigazione nel sito
pagina.asp - pagina di benvenuto nell'area riservata del sito
messaggi.asp - contiene una routines per la gestione dei messaggi di errore sia dalle stored procedures sia dalle pagine asp
Le Stored Procedures
P_Login - Valida l'accesso al sito a seconda delle varie restrizioni
P_InserisciStats - Inserisce le statistiche
Il diagramma del database
Il database che ho sviluppato è molto semplice, è composto da due tabelle:
LOGIN - contiene i dati degli account dei vari utenti del sito
LOGIN_STATS - contiene i dati statistici di accesso al sito dei vari account
La chiave primaria nella seconda tabella è composta dai due campi loginID e data_login. Esiste una relazione tra le due tabelle, ovviamente nel campo loginID in LOGIN_STATS non possono esistere valori che non siano esistenti nel campo #id in LOGIN.
Come provare l'applicazione
Il file zip che potete scarica contiene:
- autenticazione_asp.zip è il files con le pagine asp
- sql_scripts.zip contiene gli script per la creazione delle tabelle e delle stored procedure
Per testare questa semplice applicazione ASP dovete fare alcuni passi.
Sul database
- Scaricare in una directory il contenuto del file zip sql_scripts.zip
- Aprire l'Enterprise Manager e creare un nuovo database, chiamatelo CorsoFreeASP, lo useremo anche in seguito per gli altri esempi
- Dopo aver creato il database aprite il Query Analyzer collegatevi al vostro database CorsoFreeASP e caricate gli scripts SQL per la creazione delle tabelle e delle stored procedured, in sequenza
- Apri il file crea_db.sql e premi F5 per eseguire i comandi contenuti nel file
- Apri il file sp.sql e premi F5 per eseguire i comandi contenuti nel file
Sul web server
- Creare una nuova directory virtuale nel vostro web server PWS o IIS che sia
- Scaricare nella nuova directory il contenuto del file zip autenticazione_asp.zip
- aprire il file common.asp con il vostro editor di testo preferito e cambiare i parametri della stringa di connessione con quelli del vostro database
Ora siete pronti, aprite il browser e collegatevi alla vostra directory.
Scarica il file "autenticazione_asp.zip"
Scarica il file "scripts_sql.zip"
Abbiamo visto come non sia difficile creare un'area protetta all'interno di un sito web, dove solo una ristretta serie di utenti può accedere dopo aver effettuato una login con username e password validi.
Il back-end del sito
In questa parte invece cominciamo a vedere come possiamo costruire il back-end di amministrazione dell'applicazione. Significa avere una pagina o più pagine web in grado di gestire l'account dell'utente. Devono essere possibili diverse operazioni:
- Inserire nuovi account
- Aggiornare gli account esistenti (visite massime, data di expire
dell'account, ecc...) - Cancellare gli account non più utilizzati
- Attivare o disattivare gli account
Ecco uno screenshot del back-end che ho creato per l'amministrazione del sito.
Come possiamo vedere nel menu a sinistra ho già incluso una funzione che però vedremo nel prossimo articolo che riguarda la visualizzazione delle statistiche (grafica e testuale).
Per la creazione del back end ho creato un set di pagine ASP (7 per la precisione), che per comodità ho organizzato in una sottocartella della root, che ho chiamato admin.
Nessuna modifica è stata fatta sulle tabelle del database, sono state aggiunte però alcune store procedures necessarie ad aggiornare, eliminare ed inserire gli account.
Le store procedure per l'amministrazione degli account.
| Nome procedura | Azione svolta |
| P_InserisciStats | Inserisce le statistiche |
| P_Login | Validazione della login |
| P_Del_Utenti | Elimina un utente |
| P_DisAtt_Utenti | Disattiva temporaneamente un utente |
| P_Ins_Utenti | Inserisce un nuovo utente |
| P_Sel_Utenti | Seleziona gli utenti |
| P_Upd_Utenti | Aggiorna i dati di un utente |
Ho segnato in corsivo le procedure che avevamo già scritto in precedenza.
Abbiamo esaminato due aspetti fondamentali di una login ad un'area privata:
- Il meccanismo di autenticazione
- L'area di backend per la gestione degli utenti
Ora prendiamo in cosiderazione la visualizzazione delle statistiche utente, necessarie a capire lo stato degli accessi al sito dei vari utenti.
Esaminiamo la query per i report
La query per ottenere le statistiche è molto articolata per ora mi limito ad accenare le sue principali componenti, ma la sua completa spiegazione sarà oggetto di un futuro articolo. Gli elementi principali della query sono:
- l'istruzione SELECT ovviamente
- INNER JOIN secondo la sintassi nuovo stile dell'SQL92
- Operatore UNION per effettuare il merge tra gli utenti che hanno le statistiche attive (cioè che almeno una volta hanno acceduto all'area privata) ed utenti con le statistiche disattiate (questi utenti non hanno ancora effettuato una login valida)
- Uso delle funzioni di aggregazione (COUNT(*), MAX e MIN) per avere un sommario di alcuni valori dal set di risultati della mia select (numero di login effettuate, data del primo accesso e data dell'ultimo accesso)
- Uso delle delle potentissime tabelle derivate
- Uso di funzioni Built-in di T-SQL come convert e cast per la conversione delle date
Nella progettazione della query non ho tenuto conto (per questo momento) dell'uso di indici per l'ottimizzazione del piano di esecuzione.
Ecco i dati che la query ci ritorna:
- #id - È l'ID univoco dell'utente
- username
- password
- attivo - Ci dice se l'utente ha l'account attivato o disattivato
- Visite consentite - Numero massimo di visite
- % accessi effettuati - Percentuale di accessi effettuati dall'account specifico nell'area protetta del sito fino ad oggi
- % accessi residui - Percentuale di accessi residui dell'account specifico
fino ad oggi - Scade il - Data di scadenza dell'account
- Primo accesso - Data del primo accesso all'area protetta dell'account
- Ultimo accesso - Data del primo accesso all'area protetta dell'account
- Accesso - Flag (yes/no) che ci dice se l'utente ha acceduto ad oggi all'area
protetta
Le informazioni che possiamo avere sull'account sono veramente tante e a dire il vero alcune non le ho nemmeno utilizzate ma sono comunque utili per capire bene le potenzialità del T-SQL / SQL.
Ecco la query in dettaglio:
SELECT A.#id,
A.username,
A.[password],
A.attivo,
A.maxvisite [Visite consentite],
cast(B.[Numero Accessi] as decimal(5,2))/A.maxvisite*100 [% accessi effettuati],
cast((A.maxvisite - B.[Numero Accessi])as decimal(5,2))/A.maxvisite*100 [% accessi
residui],
convert(varchar(12),A.date_expire,103) [Scade il],
B.[Primo accesso],
B.[Ultimo accesso],
'yes' Accesso
FROM LOGIN A
INNER JOIN
(select loginID,
count(loginID) [Numero Accessi],
convert(varchar(12),min(data_login),103)[Primo accesso] ,
convert(varchar(12),max(data_login),103)[Ultimo accesso]
from login_stats GROUP BY loginID) B
ON B.loginID = A.#id
UNION ALL
SELECT #id,
username,
[password],
attivo,
maxvisite [Visite consentite],
0,
100,
convert(varchar(12),(date_expire),103) ,
convert(varchar(12),(date_expire),103) ,
convert(varchar(12),(date_expire),103) ,
'no' Accesso
FROM LOGIN where #id not in(select loginID from login_stats)
Come provare l'applicazione
Il file zip che potete scaricare contiene:
- autenticazione_asp.zip è il files con le pagine asp (sostituiscono tutti i precedenti)
- stats_users.zip contiene lo script per la creazione della stored procedure necessaria per la visualizzazione delle statistiche
Per testare questa semplice applicazione ASP dovete fare alcuni passi:
Sul database
- Scaricare in una directory il contenuto del file zip stats_users.zip
- Dopo aver creato il database aprite il Query Analyzer collegatevi al vostro database CorsoFreeASP e caricate gli scripts SQL per la creazione della stored procedured, in sequenza
a. Apri il file stats_users.sql e premi F5 per eseguire i comandi contenuti nel file
Ricordate bene di:
- Aggiustare nel common.asp la stringa di connessone ad database, adattandola ai vostri parametri
- Far girare gli script sql allegati nel database di prova che avrete creato, appositamente per questo scopo.
