Descrivere le autorizzazioni per database e oggetti

Completato

Tutte le piattaforme di gestione di database relazionali dispongono di quattro autorizzazioni di base, che controllano le operazioni di Data Manipulation Language (DML). Queste autorizzazioni sono SELECT, INSERT, UPDATE e DELETE e si applicano a tutte le piattaforme SQL Server. Tutte queste autorizzazioni possono essere concesse, revocate o negate in tabelle e viste. Se un'autorizzazione viene concessa usando l'istruzione GRANT, viene assegnata all'utente o al ruolo a cui si fa riferimento nell'istruzione GRANT. È anche possibile negare le autorizzazioni agli utenti usando il comando DENY. Se a un utente viene concessa un'autorizzazione e negata la stessa autorizzazione, la DENY concessione viene sempre sostituita e all'utente verrà negato l'accesso all'oggetto specifico.

Nell'esempio, all'utente Demo vengono concesse SELECT e quindi negate SELECT le autorizzazioni sulla tabella dbo.Company. Quando l'utente tenta di eseguire una query che seleziona dalla tabella dbo.Company, visualizzerà un errore che lo informa che l'autorizzazione SELECT è stata negata.

Autorizzazioni per tabelle e viste

Le tabelle e le viste rappresentano gli oggetti per i quali è possibile concedere le autorizzazioni all'interno di un database. All'interno di tali tabelle e viste è inoltre possibile limitare le colonne a cui una determinata entità di sicurezza (utente o account di accesso) può accedere. SQL Server e il database SQL di Azure includono anche la sicurezza a livello di riga, che può essere usata per limitare ulteriormente l'accesso.

Autorizzazione	Definizione
SELECT	Consente all’utente di visualizzare i dati all’interno dell’oggetto (tabella o vista). Quando viene negato, all'utente viene impedito di visualizzare i dati all'interno dell'oggetto .
INSERT	Consente all’utente di inserire dati nell’oggetto. Quando viene negato, all'utente viene impedito di inserire dati nell'oggetto .
UPDATE	Consente all’utente di aggiornare i dati all’interno dell’oggetto. Quando viene negato, all'utente viene impedito di aggiornare i dati nell'oggetto .
DELETE	Consente all’utente di eliminare i dati all’interno dell’oggetto. Quando viene negato, all'utente viene impedito di eliminare dati dall'oggetto .

Il database SQL di Azure e Microsoft SQL Server dispongono di altre autorizzazioni, che possono essere concesse, revocate o negate in base alle esigenze.

Autorizzazione	Definizione
CONTROL	Concede tutti i diritti agli oggetti. Consente all'utente che dispone di questa autorizzazione di eseguire qualsiasi azione desiderata per l'oggetto, inclusa l'eliminazione.
REFERENCES	Concede all'utente la capacità di visualizzare le chiavi esterne nell'oggetto.
TAKE OWNERSHIP	Consente all’utente la capacità di assumere la proprietà dell'oggetto.
VIEW CHANGE TRACKING	Consente all'utente di visualizzare l'impostazione di rilevamento modifiche per l'oggetto.
VIEW DEFINITION	Consente all'utente di visualizzare la definizione dell'oggetto.

Autorizzazioni per funzioni e stored procedure

Come le tabelle e le viste, le funzioni e le stored procedure hanno diverse autorizzazioni, che possono essere concesse o negate.

Autorizzazione	Definizione
ALTER	Concede all'utente la capacità di modificare la definizione dell'oggetto.
CONTROL	Concede all'utente tutti i diritti per l'oggetto.
EXECUTE	Concede all'utente la capacità di eseguire l'oggetto.
VIEW CHANGE TRACKING	Consente all'utente di visualizzare l'impostazione di rilevamento modifiche per l'oggetto.
VIEW DEFINITION	Consente all'utente di visualizzare la definizione dell'oggetto.

EXECUTE AS

I comandi EXECUTE AS [user name] o EXECUTE AS [login name] (disponibili solo in SQL Server e Istanza gestita di SQL di Azure) consentono di modificare il contesto utente. Poiché i comandi e le istruzioni successivi vengono eseguiti usando il nuovo contesto con le autorizzazioni concesse a tale contesto.

Se un utente dispone di un'autorizzazione di cui non ha più bisogno, le autorizzazioni (grant o deny) possono essere rimosse usando il comando REVOKE. Il comando revoke rimuove qualsiasi GRANT o DENY autorizzazione relativa al diritto specificato per l'utente specificato.

Catene di proprietà

Un concetto denominato concatenamento si applica alle autorizzazioni, che consentono agli utenti di ereditare le autorizzazioni da altri oggetti. L'esempio più comune di concatenamento è una funzione o una stored procedure che accede a una tabella durante la sua esecuzione. Se la stored procedure ha lo stesso proprietario della tabella, può essere eseguita e può accedere alla tabella, anche se l'utente non dispone dei diritti per accedervi direttamente. Questo accesso è disponibile perché l'utente eredita dalla stored procedure i diritti per accedere alla tabella, ma solo durante l'esecuzione della stored procedure e solo nel contesto dell'esecuzione delle stored procedure.

Nell'esempio, viene eseguito come proprietario del database o amministratore del server, viene creato un nuovo utente e aggiunto come membro di un nuovo ruolo SalesReader , a cui viene quindi concessa l'autorizzazione per selezionare da qualsiasi oggetto ed eseguire qualsiasi procedura nello schema Sales. Nello schema Sales viene quindi creata una stored procedure che accede a una tabella nello schema Production.

Nell'esempio il contenuto viene quindi modificato in modo da essere il nuovo utente e viene effettuato un tentativo di selezionare direttamente dalla tabella nello schema Production.

USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

La query restituisce un errore che indica che l'utente DP300User1 non dispone SELECT dell'autorizzazione, perché il ruolo a cui appartiene l'utente non ha privilegi nello schema Production. A questo punto è possibile provare a eseguire la stored procedure:

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

L’utente DP300User1 dispone dell'autorizzazione EXECUTE per la stored procedure nello schema Sales perché il ruolo dell'utente dispone dell'autorizzazione EXECUTE per lo schema Sales. Poiché la tabella ha lo stesso proprietario della procedura, esiste una catena di proprietà ininterrotta; quindi, l'esecuzione avrà successo e i risultati verranno restituiti.

Le modifiche alle autorizzazioni non si applicano quando si usa SQL dinamico all'interno delle stored procedure. Il motivo per cui SQL dinamico interrompe la catena di autorizzazioni è che viene eseguito al di fuori del contesto della stored procedure chiamante. È possibile visualizzare questo comportamento modificando la stored procedure per l'esecuzione usando SQL dinamico come indicato di seguito.

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)

SET @sqlstring = '
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales, 
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'

EXECUTE sp_executesql @sqlstring
GO

--

EXECUTE AS USER = 'DP300User1'

EXECUTE Sales.DemoProc

L'utente DP300User1 riceve un errore che indica che l'utente non dispone SELECT dell'autorizzazione per la tabella Production.Product , proprio come l'utente ha tentato di eseguire direttamente la query. Le catene di autorizzazioni non si applicano e l'account utente che esegue SQL dinamico deve disporre dei diritti per le tabelle e le viste usate dal codice all'interno di SQL dinamico.

Principio dei privilegi minimi

Il principio del privilegio minimo è piuttosto semplice. L'idea di base del concetto è che a utenti e applicazioni devono essere concesse solo le autorizzazioni necessarie per completare l'attività. Le applicazioni devono disporre solo delle autorizzazioni necessarie per completare le attività specifiche.

Se ad esempio un'applicazione accede a tutti i dati tramite stored procedure, deve disporre solo dell'autorizzazione per eseguire le stored procedure, senza accesso alle tabelle.

SQL dinamico

SQL dinamico è un concetto in cui una query viene compilata a livello di codice. SQL dinamico consente la generazione di istruzioni T-SQL all'interno di una stored procedure o della query stessa.

SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases

L'istruzione genera un elenco di istruzioni T-SQL per eseguire il backup di tutto il database nel server. In genere, questo T-SQL generato viene eseguito usando sp_executesql o passato a un altro programma da eseguire.