SQL Server, sicurezza spicciola

Un collega che sta studiando forsennatamente .Net, SQL, C# per poter costruire applicazioni gestionali professionali nel 21° secolo, vedendo un mio database mi ha fatto la seguente domanda:

Perché trovo l’utente MySqlUser nella Security del server, e poi lo trovo nel Database? E Perché mi dici di aver dato i permessi ad un ruolo creato sul database (db_MyRole) invece di darli all’utente?

Come funziona la sicurezza di SQL Server

Per poter accedere a SQL Server un utente o una applicazione necessita di conoscere uno UserName ed una Password oppure l’utente di windows con cui si logga deve essere mappato all’interno di SQL Server o deve appartenere ad un gruppo di windows mappato all’interno di SQl Server cosa che si può fare nella sezione Security del server usando SQL Management Studio, oppure lanciando uno di questi due comandi:

CREATE LOGIN [MyUser] WITH PASSWORD=N'MyPassword'
    , DEFAULT_DATABASE=[master]
    , DEFAULT_LANGUAGE=[us_english]
    , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [MyWindowsUserOrGroup] 
    FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    , DEFAULT_LANGUAGE=[us_english]

Ma non basta, infatti con questo login, SQL Server permetterà ad un utente di aprire una connessione, ma non darà loro modo di accedere ad alcun dato all’interno di un database.

Per accedere ad un database, i Login (SQL o Windows che siano) devono essere per prima cosa mappati sul database usando SQL Management Studio. Altrimenti con il seguente codice SQL.

CREATE USER [MyUser] 
    FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]

CREATE USER [MyWindowsUserOrGroup] 
    FOR LOGIN [MyWindowsUserOrGroup] WITH DEFAULT_SCHEMA=[dbo]

Adesso gli utenti possono accedere al server e possono accedere al database, però non abbiamo dato loro alcun permesso sugli oggetti contenuti nel database, pertanto non possono vedere nulla. Detto questo abbiamo due strade per farlo, assegnare i permessi direttamente all’utente oppure, con maggior furbizia possiamo costruire un ruolo a cui assegnare i permessi e poi assegnare al nostro utente il ruolo. Perché un ruolo? Perché il ruolo lo possiamo assegnare a più utenti quando necessario senza dover rifare tutta la mappatura dei permessi.

USE [MyDatabase]
GO
CREATE ROLE [MyRole] AUTHORIZATION [sa]
GO
use [MyDatabase]
GO
GRANT SELECT ON [dbo].[MyTable1] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT EXECUTE ON [dbo].[MySpSelect1] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT EXECUTE ON [dbo].[MySpSelect2] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT SELECT ON [dbo].[MyTable2] TO [MyRole]
GO

A questo punto diamo ai Login mappati il ruolo e  abbiamo finalmente raggiunto il traguardo. Lo possiamo sempre fare da SQL Management Studio oppure con il codice seguente:

USE [FyRepository]
GO
EXEC sp_addrolemember N'MyRole', N'Myuser'
GO

Conclusioni

Per poter accedere ai dati di un database un’utente o una applicazione deve avere modo di connettersi tramite un Login, il Login deve essere mappato su un Database e deve essere assegnato a Uno o più ruoli che gli forniscono i permessi sugli oggetti del database che poi utilizzerà per compiere il loro lavoro.