SQL Server Ruoli applicazione e scenari di utilizzo

SQL Server consente di gestire le impostazioni di sicurezza per l’accesso al database oltre che tramite i ruoli server e i ruoli database anche tramite i ruoli applicazione che permettono ad un’applicazione di funzionare con proprie autorizzazioni consentendo l’accesso a dati specifici solo agli utenti che si collegano attraverso l’applicazione.

I ruoli applicazione funzionano con entrambe le modalità di autenticazione (autenticazione di Windows e autenticazione di SQL Server), ma differenza dei ruoli di database non contengono membri e sono inattivi per impostazione predefinita.

Dal momento che i ruoli applicazione sono di entità a livello di database possono accedere ad altri database solo tramite le autorizzazioni concesse in questi database all’account utente guest, quindi se su tali database “secondari” l’account utente guest è disabilitato non sarà quindi accessibile ai ruoli applicazione di altri database.

Inoltre i ruoli applicazione, non essendo associati a un’entità a livello di server, non possono accedere ai metadati a livello di server per impostazione predefinita, ma è possibile disabilitare questa limitazione e consentire quindi ruoli applicazione di accedere ai metadati a livello di server impostando il flag globale 4616, a riguardo si veda DBCC TRACEON – Flag di traccia (Transact-SQL).

Per quanto riguarda la connessione ad un database attraverso un ruolo applicazione il passaggio dal contesto di sicurezza a livello utente a quello a livello applicazione si svolge nel modo seguente:

  1. L’utente esegue l’applicazione client.
  2. L’applicazione si collega al database dell’istanza di SQL Server come utente tramite l’autenticazione di Windows o l’autenticazione di SQL Server, quindi l’utente dovrà almeno avere i privilegi che gli consentano di connettersi al database ovvero appartenere al ruolo di database public (a riguardo si veda Ruoli a livello di database).
  3. L’applicazione abilita il ruoli applicazione eseguendo la stored procedure sp_setapprole con una password nota solo all’applicazione, a riguardo si veda sp_setapprole (Transact-SQL).
  4. Se il nome del ruolo applicazione e la password sono validi, il ruolo applicazione viene abilitato.
  5. Dopo l’abilitazione del ruolo applicazione la connessione perde le autorizzazioni dell’utente e assume le autorizzazioni del ruolo applicazione.
  6. Le autorizzazioni acquisite attraverso il ruolo applicazione rimangono valide per tutta la durata della connessione.

Nelle versioni di SQL Server precedenti a SQL Server 2005 (9.x), l’unico modo per un utente di ritornare al contesto di sicurezza originale dopo l’avvio di un ruolo applicazione consisteva nel disconnettersi e riconnettersi a SQL Server. A partire da SQL Server 2005 (9.x) la stored procedure sp_setapprole dispone di un’opzione che crea un cookie contente le informazioni di contesto precedenti all’abilitazione del ruolo applicazione, tale cookie può essere usato dalla store procedure sp_unsetapprole per riportare la sessione al contesto originale.

L’esecuzione della stored procedure sp_setapprole richiede che l’utente che la esegue sia membro del ruolo di database public.

La stored procedure sp_setapprole non può essere eseguita all’interno di un’altra stored procedure o una transazione definita dall’utente.

La stored procedure sp_setapprole consente di inviare password offuscate tramite la funzione ODBC encrypt, ma la funzione ODBC encrypt non è supportata da SqlClient, ma solo da ODBC client o dall’OLE DB Provider for SQL Server. Si tenga comunque presente che è possibile trasmettere informazioni riservate su una rete,come la password del ruolo applicazione, utilizzando SSL (Secure Sockets Layer) o IPSec per crittografare il canale.

A riguardo si vedano:

La creazione di un ruolo applicazione può essere fatto tramite SQL Server Management Studio o tramite Transact-SQL, a riguardo si vedano Creazione di un ruolo applicazione e CREATE APPLICATION ROLE (Transact-SQL). Durante la creazione di un ruolo applicazione occorre tenere presente le seguenti:

  • Per poter creare un ruolo applicazione è richiesta l’autorizzazione ALTER ANY APPLICATION ROLE nel database.
  • In fase di impostazione delle password viene eseguito il controllo dei requisiti di complessità delle password.
  • Le applicazioni che richiamano i ruoli applicazione devono archiviare le relative password, quindi per creare falle di sicurezza le password dei ruoli applicazione devono essere sempre archiviate in forma crittografata. Si tenga conto che se è necessario mantenere le credenziali nell’applicazione è possibile crittografarle utilizzando le funzioni Crypto API.
  • In SQL Server 2005 (9.x) e versioni successive, il parametro password è archiviato come hash unidirezionale.
  • I ruoli applicazione sono visibili nella vista di sistema sys.database_principals.
  • Sebbene sia anche possibile creare un ruolo applicazione tramite la stored procedure sp_addapprole questo approccio è da evitare in quanto tale funzionalità è in maintenance mode e potrebbe essere rimossa nelle future versioni, inoltre per mantenere la compatibilità con le versioni precedenti di SQL Server, i criteri di complessità delle password non viene applicati dalla stored procedure sp_addapprole (al contrario di quanto avviene utilizzando il comando Transact-SQL CREATE APPLICATION ROLE o SQL Server Management Studio).

Fatte queste premesse tecniche sui ruoli applicazione un tipico scenario di utilizzo è quello che prevede che gli utenti si connettano al database su un’istanza SQL Server tramite le loro credenziali, preferibilmente utilizzando l’autenticazione Windows (a riguardo si veda Autenticazione in SQL Server), ma non dispongono di alcuna autorizzazione (ovvero appartengono solo al ruolo di database public). L’applicazione attiverà un ruolo applicazione per consentire l’esecuzione delle attività necessarie.

L’applicazione potrà attivare ruoli applicazione specificatamente creati per determinate attività, ad esempio se l’applicazione è un gestionale ed è stata avviata la form di gestione ordini di vendita sarà abilitato il ruolo applicativo che permette la lettura e modifica delle sole tabelle necessarie. Inoltre l’applicazione può anche abilitare ruoli applicazioni diversi non solo in base al contesto di utilizzo dell’applicazione, ma anche in base all’utente che aveva avviato la connessione utilizzando la funzione ORIGINAL_LOGIN() che restituisce l’identità dell’account di accesso originale in sessioni in cui si verificano cambi di contesto espliciti o impliciti.

Un software che utilizza l’approccio alla sicurezza basato su autenticazione di Windows e ruoli applicazione è Dynamics NAV, a riguardo si veda il post Extended NAV/SQL Database Hardening di Jörg A. Stryk (MVP Reconnect) in cui viene descritto come il modello di sicurezza di Dynamics NAV per l’accesso al database e come migliorare ulteriormente la sicurezza tramite attività di hardening:

In NAV the DB Security is somewhat complex. Assuming the NAV “Security Model” STANDARD (recommended). All a “normal” User (no admin or developer) needs to logon to the system is this:

  • SQL Server Login; Server-Role “public”
  • SQL Database User (SQL or Windows Login); Database-Role “public”
  • NAV Login (DB or Windows); NAV-Roles

When the NAV Client logs on to the SQL Server, it firstly reads some NAV system tables ($ndo$serverproperty, $ndo$dbproperty, etc.) to fetch some parameters, for example the C/SIDE Client learns from $ndo$dbproperty which “Secuirity Model” (e.g. Standard) is used. To accomplish this the role “public” is sufficient enough.
Then the Client is signing in using a NAV “Application Role” – with “Standard” security this is $ndo$shadow, with “Enhanced” security each Login has its own AR called $ndo$ar${GUID}. Once the AR has taken over, only this AR is in charge: $ndo$shadow” is using the “Extended Stored Procedure” xp_ndo_enumusersids to transform the NAV defined user-rights into SQL site permissions.

 

Hence, once the AR has taken over the roles and permissions as defined within NAV are in place. But this applies only to the NAV Client – the application!