SQL Server: Ruoli definiti dall'utente

  • Articolo

Con l'avvento di SQL Server 2012 arriva anche la capacità di creare ruoli server definiti dall'utente, un passo in avanti enorme per la gestione SQL.

Denny Cherry

Benché siamo stati in grado di creare ruoli database definiti dall'utente per decenni per facilitare la procedura per concedere le autorizzazioni a livello di database, a livello di istanza ci sono sempre stati solo nove ruoli server fissi (otto se si utilizzano versioni precedenti a SQL Server 2000; il ruolo bulkadmin è stato introdotto con SQL Server 2005). Oggi, con SQL Server 2012, potremo finalmente creare ruoli server definiti dall'utente.

L'impossibilità di poter compiere questa operazione per anni ha comportato alla gestione di SQL non pochi grattacapi. Come comportarsi quando è necessario concedere diritti a più utenti o gruppi a livello di istanza e si desidera mantenere tali diritti sincronizzati? Supponiamo di voler concedere il diritto View System State a una notevole quantità di utenti per consentire a questi ultimi di visualizzare le informazioni di blocco all'interno dei server di sviluppo. Sarebbe necessario concederne uno a uno a ciascun utente o gruppo di dominio che ne avesse l'esigenza.

Se tutti gli accessi sono basati sugli accessi di dominio, l'unica soluzione utilizzabile consisterebbe nel creare un gruppo di dominio per tutti gli utenti che hanno bisogno di tale diritto (possibilmente che hanno bisogno di un gruppo di dominio per ciascun server se gli utenti non ottengono il diritto per tutti i server). È possibile inserire gli utenti in questo gruppo, creare un accesso associato al gruppo di dominio e concedere al gruppo il diritto View Server State nel server. Tuttavia, è necessario effettuare questa operazione con molta attenzione. In caso contrario, potrebbe accadere di concedere i diritti di accesso a utenti che non li possedevano. Potrebbe inoltre accadere di concedere a utenti diritti che non si desidera concedere.

Ruolo con T/SQL

È possibile creare un ruolo server definito dall'utente in diversi modi, tra cui tramite T/SQL, l'interfaccia utente di SQL Server Management Studio e Windows PowerShell. Per creare un ruolo server definito dall'utente mediante T/SQL, utilizzerei tre comandi diversi.. In primo luogo, utilizzerei l'istruzione Create Server Role per creare il ruolo server definito dall'utente; in secondo luogo, utilizzerei l'istruzione Alter Server Role per aggiungere un utente al ruolo server; infine, utilizzerei l'istruzione Grant per concedere i diritti necessari al ruolo.

Queste tre istruzioni sono riportate nel codice seguente, in cui viene delineata la creazione di un ruolo server definito dall'utente e denominato "ViewServerState". In questo modo si indica un utente aggiunto al ruolo denominato "SomeFakeLogin" a cui viene assegnato il diritto View Server State. Per concedere questo diritto ad altri utenti, è sufficiente aggiungerli al ruolo server fisso mediante l'istruzione Alter Server Role:

USE [master] GO CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa] GO ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin] GO GRANT VIEW SERVER STATE TO [ViewServerState] GO

Con l'istruzione Alter Server Role si rimuove un utente da un ruolo server definito dall'utente. Anziché utilizzare la sintassi Add Member, è sufficiente utilizzare la stringa Drop Member:

ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin] GO

Quando arriva il momento di dover distribuire uno o più ruoli server definiti dall'utente per più istanze di SQL Server, abbiamo a disposizione diverse possibilità. Probabilmente, non è auspicabile doversi connettere a ciascun server singolarmente per creare i ruoli server definiti dall'utente. Una possibilità consiste nell'utilizzare la funzionalità SQL Server Management Studio, che consente di eseguire script T/SQL per più istanze.

È possibile inoltre utilizzare i componenti Windows PowerShell di SQL Server per distribuire nuovi ruoli server definiti dall'utente per tutte le istanze di SQL Server presenti all'interno dell'organizzazione. A causa delle molteplici modalità di utilizzo di Windows PowerShell per la distribuzione di tali script, il processo esula dall'ambito di questo articolo.

SQL Server Management Studio

È possibile effettuare in modo semplice la stessa operazione con l'interfaccia utente di SQL Server Management Studio. Per creare un ruolo server definito dall'utente, occorre connettere l'istanza all'interno di Esplora oggetti. In Esplora oggetti selezionare NomeIstanza | Sicurezza | Ruoli server. Fare clic con il pulsante destro del mouse su Ruoli server e scegliere Nuovo ruolo server dal menu di scelta rapida. Quando viene visualizzata la finestra di dialogo Nuovo ruolo server, indicare il Nome ruolo server e il Proprietario, scegliere Entità a protezione diretta, quindi scegliere le Autorizzazioni da assegnare ai membri del ruolo (vedere la Figura 1).

Figura 1 Indicazione dei diritti da assegnare a un particolare utente.

Dopo aver completato la compilazione dei campi nella pagina Generale, selezionare la pagina Membri (vedere la Figura 2) e indicare gli account di accesso di SQL Server che diventeranno membri di questo ruolo server definito dall'utente.

Figura 2 Nella pagina Membri sarà possibile indicare gli utenti a cui consentire l'accesso a questo ruolo utente.

Dopo aver selezionato i Membri ruolo, selezionare la pagina Appartenenze, in cui sarà possibile indicare i ruoli server dei quali questo ruolo server definito dall'utente sarà membro. Se si indica un ruolo server in questa schermata, anche agli utenti all'interno del ruolo server definito dall'utente verranno assegnati i diritti concessi da tale ruolo server.

Se si dovesse creare un ruolo definito dall'utente e renderlo membro del ruolo server fisso serveradmin (vedere la Figura 3), tutti i membri del ruolo server definito dall'utente non diventerebbero in realtà membri del ruolo server fisso serveradmin. Come nel caso della nidificazione di gruppi di dominio o ruoli di database, è necessario esercitare particolare attenzione durante la nidificazione di ruoli, onde evitare di concedere agli utenti diritti che non dovrebbero ottenere dai gruppi di livello superiore.

Figura 3 È possibile applicare il proprio ruolo definito dall'utente ad altri ruoli server.

Per nidificare i ruoli server mediante T/SQL, sarà necessario comunque utilizzare l'istruzione Alter Server Role con la sintassi Add Member. Ad esempio, per rendere il ruolo server definito dall'utente ViewServerState membro del ruolo server fisso setupadmin, dovremmo modificare il ruolo server fisso setupadmin e aggiungere il ruolo server definito dall'utente ViewServerState come membro.

ALTER SERVER ROLE [setupadmin] ADD MEMBER [ViewServerState] GO

Ruoli di SQL Server 2012 definiti dall'utente Esistono decine di diritti a livello di istanza da poter concedere a un ruolo server definito dall'utente per semplificare la gestione dei diritti. È possibile inoltre creare un ruolo amministratore di database junior che possa concedere alcuni diritti, ma non diritti di amministratore completi. È possibile creare un gruppo AlwaysOnAdmin per concedere diritti di failover a un database AlwaysO (operazione da eseguire all'interno di SQL Server) senza dover disporre dei diritti di amministratore completi.

Sono disponibili una vasta gamma di casi di utilizzo per i ruoli server definiti dall'utente, i quali semplificheranno notevolmente i carichi di lavoro di amministrazione. Consentiranno inoltre di aumentare la sicurezza delle istanze di SQL Server, poiché questi nuovi ruoli server ridurranno le situazioni di concessione accidentale di diritti non appropriati.

DennyCherry

Denny Cherry* ha più di dieci anni di esperienza nella gestione di SQL Server. Tra i suoi ambiti di competenza, sono inclusi l'architettura, l'ottimizzazione delle prestazioni, la replica e la risoluzione dei problemi relativi ai sistemi. Ha conseguito diverse certificazioni relative a SQL Server, tra cui Microsoft Certified Master for SQL Server 2008. È stato Microsoft SQL Server MVP per diversi anni e ha scritto numerosi articoli e libri tecnici relative alla gestione di SQL Server e al modo in cui SQL Server si integra con diverse altre tecnologie.*

Contenuto correlato