Sicurezza a livello di riga

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Endpoint di analisi SQL in Microsoft Fabric Magazzino in Microsoft Fabric

La sicurezza a livello di riga (RLS) consente di usare l'appartenenza a gruppi o il contesto di esecuzione per controllare l'accesso alle righe in una tabella di database.

La sicurezza a livello di riga semplifica la progettazione e la codifica della sicurezza nell'applicazione. e facilita l'implementazione delle restrizioni di accesso alle righe di dati. Ad esempio, è possibile assicurarsi che i dipendenti possano accedere solo alle righe di dati pertinenti per il loro reparto. Un altro esempio può essere la limitazione dell'accesso ai dati dei clienti solo ai dati rilevanti per l'azienda.

La logica di restrizione dell'accesso si trova sul livello del database e non su un altro livello applicazione lontano dai dati. Il sistema del database applica le restrizioni di accesso a ogni tentativo di accesso ai dati da qualsiasi livello. Il sistema di sicurezza è così più affidabile e solido, grazie alla riduzione della superficie di attacco del sistema di sicurezza.

Implementare RLS usando l'istruzione Transact-SQL CREATE SECURITY POLICY e i predicati creati come funzioni con valori di tabella inline.

La sicurezza a livello di riga è stata introdotta per la prima volta in SQL Server 2016 (13.x).

Nota

Questo articolo riguarda in particolare le piattaforme SQL Server e SQL di Azure. Per Microsoft Fabric, vedere Sicurezza a livello di riga in Microsoft Fabric.

Descrizione

La sicurezza a livello di riga (RLS) supporta due tipi di predicati di sicurezza:

• I predicati del filtro filtrano automaticamente le righe disponibili per le operazioni di lettura (SELECT, UPDATE e DELETE).

• I predicati di blocco bloccano in modo esplicito le operazioni (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) che violano il predicato.

L'accesso ai dati a livello di riga in una tabella è limitato da un predicato di sicurezza definito come una funzione inline con valori di tabella. La funzione viene quindi richiamata e applicata dai criteri di sicurezza. Nel caso dei predicati del filtro, l'applicazione non rileva le righe filtrate dal set di risultati. Se vengono filtrate tutte le righe, viene restituito un set Null. Per i predicati di blocco, qualsiasi operazione che violi il predicato non verrà completata e genererà un errore.

I predicati di filtro vengono applicati durante la lettura dei dati dalla tabella di base Influiscono su tutte le operazioni Get:: SELECT, DELETE e UPDATE. Gli utenti non possono selezionare o eliminare le righe filtrate. L'utente non può aggiornare le righe filtrate. Tuttavia, è possibile aggiornare le righe in modo che vengano filtrate in un secondo momento. I predicati di blocco influiscono su tutte le operazioni di scrittura.

• I predicati AFTER INSERT e AFTER UPDATE possono impedire agli utenti di aggiornare le righe con valori che violano il predicato.

• I predicati BEFORE UPDATE possono impedire agli utenti di aggiornare le righe che attualmente violano il predicato.

• I predicati BEFORE DELETE possono bloccare le operazioni di eliminazione.

I predicati del filtro e di blocco e i criteri di sicurezza si comportano nel modo seguente:

• È possibile definire una funzione di predicato che si unisca a un'altra tabella e/o chiami una funzione. Se i criteri di sicurezza vengono creati con SCHEMABINDING = ON (valore predefinito), il join o la funzione è accessibile dalla query e funziona come previsto senza controlli aggiuntivi delle autorizzazioni. Se i criteri di sicurezza vengono creati con SCHEMABINDING = OFF, gli utenti dovranno disporre di autorizzazioni SELECT su queste tabelle e funzioni aggiuntive per eseguire query nella tabella di destinazione. Se la funzione di predicato richiama una funzione a valori scalari CLR, è necessaria anche l'autorizzazione EXECUTE.

• È possibile inviare una query in una tabella con un predicato di sicurezza definito ma disabilitato. Le righe filtrate o bloccate non sono interessate.

• Se un utente dbo, un membro del ruolo db_owner o il proprietario della tabella esegue query in una tabella con criteri di sicurezza definiti e abilitati, le righe vengono filtrate o bloccate in base a quanto definito nei criteri di sicurezza.

• I tentativi di modificare lo schema di una tabella tramite un criterio di sicurezza associato allo schema generano un errore. Le colonne a cui non fa riferimento il predicato possono tuttavia essere modificate.

• I tentativi di aggiunta di un predicato in una tabella in cui è già presente un predicato definito per l'operazione specificata generano un errore. Ciò si verifica indipendentemente dal fatto che il predicato sia abilitato o meno.

• I tentativi di modifica di una funzione usata come predicato in una tabella inclusa in criteri di sicurezza associati a schema generano un errore.

• La definizione di più criteri di sicurezza attivi contenenti predicati non sovrapposti riesce correttamente.

I predicati del filtro si comportano nel modo seguente:

• Definire i criteri di sicurezza per filtrare le righe di una tabella. L'applicazione non rileva le righe filtrate per operazioni SELECT, UPDATE e DELETE. Sono incluse le situazioni in cui vengono filtrate tutte le righe. L'applicazione può eseguire INSERT per le righe, anche se verranno filtrate durante qualunque altra operazione.

I predicati di blocco si comportano nel modo seguente:

• I predicati di blocco per UPDATE vengono suddivisi in operazioni separate per BEFORE e AFTER. Di conseguenza non è possibile, ad esempio, impedire agli utenti di aggiornare una riga con un valore superiore a quello corrente. Se si deve applicare una logica di questo tipo, occorre usare i trigger con le tabelle intermedie DELETED e INSERTED per rimandare ai valori precedenti e nuovi insieme.

• L'ottimizzatore non controllerà il predicato di blocco AFTER UPDATE se le colonne usate dalla funzione del predicato non sono state modificate. Esempio: Alice non deve essere in grado di modificare uno stipendio in modo che sia maggiore di 100.000. Alice può modificare l'indirizzo di un dipendente il cui stipendio è già maggiore di 100.000, purché le colonne a cui fa riferimento il predicato non siano state modificate.

• Non sono state apportate modifiche in blocco alle API, incluso BULK INSERT. Ciò implica che i predicati di blocco AFTER INSERT si applicano alle operazioni di inserimento in blocco come se fossero operazioni di inserimento regolari.

Casi d'uso

Di seguito sono riportati alcuni esempi di progettazione relativi alle modalità di utilizzo della sicurezza a livello di riga (RLS):

• Un ospedale può creare criteri di sicurezza che consentono agli infermieri di visualizzare le righe di dati solo per i propri pazienti.

• Una banca può creare criteri per limitare l'accesso alle righe di dati finanziari in base alla divisione aziendale del dipendente o al suo ruolo nell'azienda.

• Un'applicazione multi-tenant può creare criteri per l’applicaizone di una separazione logica delle righe di dati di ogni tenant da qualunque altra riga del tenant. L'efficienza viene raggiunta archiviando i dati per diversi tenant in un'unica tabella. Ogni tenant può visualizzare solo le proprie righe di dati.

Le funzionalità dei predicati di filtro RLS riga sono equivalenti all'aggiunta di una clausola WHERE. Il predicato può essere sofisticato, se lo richiedono le procedure aziendali, oppure è possibile usare una clausola semplice, ad esempio WHERE TenantId = 42.

In termini più formali, la sicurezza a livello di riga introduce il controllo degli accessi basato su predicato. Tale controllo include una valutazione flessibile e centralizzata basata su predicato. Il predicato può essere basato su metadati o su qualsiasi altro criterio considerato appropriato dall'amministratore. Il predicato viene usato come criterio per determinare se l'utente dispone dell'accesso appropriato ai dati in base agli attributi utente. Il controllo di accesso basato su etichetta può essere implementato usando il controllo di accesso basato su predicato.

Autorizzazioni

La creazione, la modifica o l'eliminazione di criteri di sicurezza richiede l'autorizzazione ALTER ANY SECURITY POLICY. La creazione o l'eliminazione di criteri di sicurezza richiede l'autorizzazione ALTER nello schema.

Inoltre, per ogni predicato che viene aggiunto sono richieste le autorizzazioni seguenti:

• Le autorizzazzioni SELECT REFERENCES per la funzione usata come predicato.

• L’autorizzazione REFERENCES per la tabella di destinazione associata ai criteri.

• L’autorizzazione REFERENCES per ogni colonna della tabella di destinazione usata come argomento.

I criteri di sicurezza sono applicati a tutti gli utenti, inclusi gli utenti dbo nel database. Gli utenti dbo possono modificare o eliminare i criteri di sicurezza, tuttavia tali modifiche ai criteri di sicurezza possono essere controllate. Se un utente con privilegi elevati, ad esempio sysadmin o db_owner, deve visualizzare tutte le righe per risolvere i problemi o convalidare i dati, i criteri di sicurezza devono essere scritti in modo da consentire tale operazione.

Se i criteri di sicurezza vengono creati con SCHEMABINDING = OFF, per eseguire query nella tabella di destinazione gli utenti devono disporre dell'autorizzazione SELECT o EXECUTE per la funzione di predicato e per qualunque tabella, vista o funzione aggiuntiva usata nella funzione di predicato. Se i criteri di sicurezza vengono creati con SCHEMABINDING = ON (impostazione predefinita), questi controlli delle autorizzazioni vengono ignorati quando gli utenti eseguono query sulla tabella di destinazione.

Procedure consigliate

• È consigliabile creare uno schema separato per gli oggetti con sicurezza a livello di riga, vale a dire funzioni di predicato e criteri di sicurezza. In questo modo si separano le autorizzazioni necessarie per questi oggetti speciali dalle tabelle di destinazione. La separazione aggiuntiva per diversi criteri e funzioni di predicato potrebbe essere necessaria nei database multi-tenant, ma non rappresenta uno standard per ogni caso.

• L’autorizzazione ALTER ANY SECURITY POLICY è destinata agli utenti con privilegi elevati (ad esempio il gestore dei criteri di sicurezza). Il gestore dei criteri di sicurezza non richiede l'autorizzazione SELECT per le tabelle che protegge.

• Non usare le conversioni del tipo nelle funzioni di predicato per evitare potenziali errori di run-time.

• Se possibile, evitare la ricorsione nelle funzioni di predicato per evitare un calo delle prestazioni. Query Optimizer proverà a rilevare le ricorsioni dirette, ma non è garantito che trovi quelle indirette. Una ricorsione indiretta si verifica quando una seconda funzione chiama la funzione di predicato.

• Evitare di usare un numero eccessivo di join di tabella nelle funzioni di predicato per ottimizzare le prestazioni.

Evitare una logica di predicato dipendente da opzioni SET specifiche della sessione: nonostante sia improbabile che vengano usate in applicazioni pratiche, le funzioni di predicato la cui logica dipende da determinate opzioni SET specifiche della sessione possono causare perdite di informazioni se gli utenti possono eseguire query arbitrarie. Ad esempio, una funzione di predicato che converte implicitamente una stringa in datetime potrebbe filtrare righe diverse in base all'opzione SET DATEFORMAT per la sessione corrente. In generale le funzioni di predicato devono rispettare le regole seguenti:

• Le funzioni di predicato non devono convertire implicitamente stringhe di caratteri in date, smalldatetime, datetime, datetime2 o datetimeoffset o viceversa, perché queste conversioni dipendono dalle opzioni SET DATEFORMAT (Transact-SQL) e SET LANGUAGE (Transact-SQL);. Usare, invece, la funzione CONVERT e specificare esplicitamente il parametro di stile.

• Le funzioni di predicato non devono basarsi sul valore del primo giorno della settimana, perché questo valore dipende dall'opzione SET DATEFIRST (Transact-SQL).

• Le funzioni di predicato non devono basarsi su espressioni aritmetiche o di aggregazione che restituiscono NULL in caso di errore, ad esempio overflow o divisione per zero, perché questo comportamento dipende dalle opzioni SET ANSI_WARNINGS (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL) e SET ARITHABORT (Transact-SQL).

• Le funzioni di predicato non devono confrontare stringhe concatenate con NULL, perché questo comportamento dipende dall’opzione SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

Nota sulla sicurezza: attacchi side-channel

Gestore dei criteri di sicurezza malintenzionato

È importante osservare che un gestore dei criteri di sicurezza malintenzionato, con autorizzazioni sufficienti per creare criteri di sicurezza per una colonna sensibile e per creare o modificare le funzioni inline con valori di tabella, può agire in collusione con un altro utente con autorizzazioni Select su una tabella al fine di estrarre dolosamente i dati creando funzioni inline con valori di tabella progettate per usare attacchi al canale laterale per estrapolare i dati. Questi attacchi richiedono la collusione con altre persone (o autorizzazioni eccessive concesse a un utente malintenzionato) e richiedono probabilmente diversi tentativi di modifica dei criteri (che richiede autorizzazioni per rimuovere il predicato per interrompere l'associazione allo schema), la modifica delle funzioni con valori di tabella inline e diverse esecuzioni delle istruzioni Select nella tabella di destinazione. È consigliabile limitare le autorizzazioni in base alle esigenze e monitorare le attività sospette. Si dovrebbero monitorare le attività come modifiche costanti dei criteri e di funzioni inline con valori di tabella correlate alla sicurezza a livello di riga.

Query appositamente create

L’uso di query create con attenzione che usano errori per esfiltrare i dati può causare perdite di informazioni. Ad esempio, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe'; consentirebbe a un utente malintenzionato di sapere che lo stipendio di Mario Rossi ammonta esattamente a 100.000 dollari. Anche se è disponibile un predicato di sicurezza per impedire le query dirette di un utente malintenzionato relative allo stipendio degli altri dipendenti, l'utente può determinare quando la query restituisce un'eccezione di divisione per zero.

Compatibilità tra funzionalità

In generale la sicurezza a livello di riga funziona tra varie funzionalità nel modo previsto. Tuttavia, vi sono alcune eccezioni. Questa sezione contiene diverse note e avvertenze per l'uso della sicurezza a livello di riga con altre funzionalità specifiche di SQL Server.

• DBCC SHOW_STATISTICS genera statistiche su dati non filtrati e può causare perdite di informazioni altrimenti protette da criteri di sicurezza. Per questo motivo, l'accesso per visualizzare un oggetto statistiche per una tabella con criteri di sicurezza a livello di riga è limitato. L’utente deve essere il proprietario della tabella oppure un membro del ruolo predefinito del server sysadmin, del ruolo predefinito del database db_owner o del ruolo predefinito del database db_ddladmin.

• FileStream: RLS non è compatibile con FileStream.

• PolyBase: RLS è supportato con tabelle esterne in Azure Synapse e SQL Server 2019 CU7 o versioni successive.

• Tabelle ottimizzate per la memoria: la funzione con valori di tabella inline usata come predicato di sicurezza in una tabella ottimizzata per la memoria deve essere definita usando l'opzione WITH NATIVE_COMPILATION. Con questa opzione le funzionalità del linguaggio non supportate dalle tabelle ottimizzate per la memoria verranno escluse e verrà generato l'errore appropriato al momento della creazione. Per altre informazioni, vedere Sicurezza a livello di riga nelle tabelle ottimizzate per la memoria.

• Viste indicizzate: in generale, è possibile creare criteri di sicurezza nelle viste, che possono essere create in tabelle associate a criteri di sicurezza. Non è possibile tuttavia creare viste indicizzate in tabelle con un criterio di sicurezza, poiché le ricerche di righe tramite l'indice potrebbero ignorare il criterio.

• Change Data Capture: Change Data Capture (CDC) può causare la perdita di intere righe che devono essere filtrate per i membri di db_owner o gli utenti membri del ruolo di "controllo" specificato quando CDC viene abilitato per una tabella. È possibile impostare in modo esplicito questa funzione su NULL per consentire a tutti gli utenti di accedere ai dati delle modifiche. In effetti, i membri di questo ruolo di controllo e db_owner possono visualizzare tutte le modifiche dei dati in una tabella anche se per la tabella esistono criteri di sicurezza.

• Rilevamento modifiche: il rilevamento delle modifiche può causare la perdita della chiave primaria delle righe che devono essere filtrate per gli utenti che dispongono di entrambe le autorizzazioni SELECT e VIEW CHANGE TRACKING. I valori dei dati effettivi non vengono perduti, ma si perde solo il fatto che la colonna A è stata aggiornata/inserita/eliminata per la riga con la chiave primaria B. Questo rappresenta un problema se la chiave primaria contiene un elemento riservato, ad esempio un codice fiscale. In pratica, però, CHANGETABLE è quasi sempre unito alla tabella originaria per l’acquisizione dei dati più recenti.

• Ricerca full-text: è previsto un peggioramento delle prestazioni per le query che usano le funzioni seguenti di ricerca full-text e ricerca semantica, a causa di un join aggiuntivo introdotto per applicare la sicurezza a livello di riga ed evitare la perdita di chiavi primarie delle righe che devono essere filtrate: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

• Indici columnstore: RLS è compatibile con indici columnstore sia cluster che non cluster. Dato però che la sicurezza a livello di riga applica una funzione, l'ottimizzatore potrebbe modificare il piano di query in modo che non usi la modalità batch.

• Viste partizionate: non è possibile definire predicati di blocco nelle viste partizionate e non è possibile creare viste partizionate in tabelle che usano predicati di blocco. I predicati di filtro sono compatibili con le viste partizionate.

• Tabelle temporali: le tabelle temporali sono compatibili con RLS. I predicati di sicurezza nella tabella corrente non vengono tuttavia replicati automaticamente nella tabella di cronologia. Per applicare un criterio di sicurezza alla tabella della cronologia e alla tabella corrente, è necessario aggiungere singolarmente un predicato di sicurezza in ogni tabella.

Altre limitazioni:

• Microsoft Fabric e Azure Synapse Analytics supportano solo predicati di filtro. I predicati di blocco attualmente non sono supportati in Microsoft Fabric e Azure Synapse Analytics.

Esempi

R. Scenari per gli utenti che eseguono l'autenticazione nel database

In questo esempio vengono creati tre utenti, quindi viene creata e popolata una tabella con sei righe. Vengono quindi creati una funzione inline con valori di tabella e criteri di sicurezza per la tabella. L'esempio mostra poi in che modo le istruzioni Select vengono filtrate per i diversi utenti.

Creare tre account utente per mostrare le diverse capacità di accesso.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO

Creare una tabella per contenere i dati.

CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
    (
    OrderID int,
    SalesRep nvarchar(50),
    Product nvarchar(50),
    Quantity smallint
    );

Popolare la tabella con sei righe di dati che visualizzano tre ordini per ogni rappresentante.

INSERT INTO Sales.Orders  VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders  VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders  VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders  VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders  VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders  VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;

Concedere l'accesso in lettura alla tabella a ciascuno degli utenti.

GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO

Creare un nuovo schema e una funzione con valori di tabella inline. La funzione restituisce 1 quando una riga nella colonna SalesRep è uguale all’utente che esegue la query (@SalesRep = USER_NAME()) o se l’utente che esegue la query è l’utente gestore (USER_NAME() = 'Manager'). Questo esempio di funzione con valori di tabella definita dall'utente è utile come filtro per i criteri di sicurezza creati nel passaggio successivo.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

Creare i criteri di sicurezza aggiungendo la funzione come predicato di filtro. STATE deve essere impostato su ON per abilitare i criteri.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO

Concedere autorizzazioni SELECT per la funzione tvf_securitypredicate:

GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;

A questo punto, testare il predicato di filtro mediante la selezione dalla tabella Sales.Orders per ogni utente.

EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;

Il gestore dovrebbe visualizzare tutte e sei le righe. Gli utenti Sales1 e Sales2 devono visualizzare solo le loro vendite.

Modificare i criteri di sicurezza per disabilitarli.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

A questo punto, gli utenti Sales1 e Sales2 possono visualizzare tutte e sei le righe.

Collegarsi al Database SQL per pulire risorse da questo semplice esercizio:

DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;

B. Scenari per l'uso della sicurezza a livello di riga in una tabella esterna di Azure Synapse

Questo breve esempio crea tre utenti e una tabella esterna con sei righe. Vengono quindi creati una funzione inline con valori di tabella e criteri di sicurezza per la tabella esterna. L'esempio mostra in che modo le istruzioni Select vengono filtrate per i diversi utenti.

Prerequisiti

1. È necessario disporre di un pool SQL dedicato. Vedere Creare un pool SQL dedicato
2. Il server che ospita il pool SQL dedicato deve essere registrato con l'ID Microsoft Entra (in precedenza Azure Active Directory) ed è necessario disporre di un account di archiviazione di Azure con autorizzazioni Storage Blog Data Contributor. Attenersi alla procedura per Usare gli endpoint servizio di rete virtuale e le regole per i server nel Database SQL di Azure.
3. Creare un file system per l'account di archiviazione di Azure. Usare Azure Storage Explorer per visualizzare l'account di archiviazione. Fare clic con il pulsante destro del mouse contenitori e selezionare Crea file system.

Una volta soddisfatti i prerequisiti in-place, creare tre account utente che mostrano funzionalità di accesso differenti.

--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO

--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1  FOR LOGIN Sales1;
CREATE USER Sales2  FOR LOGIN Sales2 ;

Creare una tabella per contenere i dati.

CREATE TABLE Sales
    (
    OrderID int,
    SalesRep sysname,
    Product varchar(10),
    Qty int
    );

Popolare la tabella con sei righe di dati che visualizzano tre ordini per ogni rappresentante.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;

Creare una tabella esterna di Azure Synapse dalla tabella Sales creata.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);

CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Concedere SELECT per i tre utenti nella tabella esterna Sales_ext creata.

GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;

Creare un nuovo schema e una funzione con valori di tabella inline (è possibile che queste operazioni siano state completate nell'esempio A). La funzione restituisce 1 quando una riga nella colonna SalesRep è uguale all’utente che esegue la query (@SalesRep = USER_NAME()) o se l’utente che esegue la query è l’utente Manager (USER_NAME() = 'Manager').

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

Creare criteri di sicurezza per la tabella esterna usando la funzione inline con valori di tabella come predicato del filtro. STATE deve essere impostato su ON per abilitare i criteri.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);

A questo punto, testare il predicato di filtro mediante la selezione dalla tabella esterna Sales_ext. Accedere come utente Sales1, Sales2 e Manager. Eseguire il comando seguente per ogni utente.

SELECT * FROM Sales_ext;

Manager deve visualizzare tutte e sei le righe. Gli utenti Sales1 e Sales2 devono visualizzare solo le loro vendite.

Modificare i criteri di sicurezza per disabilitarli.

ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);

A questo punto, gli utenti Sales1 e Sales2 possono visualizzare tutte e sei le righe.

Collegarsi al Database SQL per pulire risorse da questo semplice esercizio:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;

Collegarsi al database master del server l.ogico per pulire risorse:

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C. Scenari per gli utenti che si connettono al database tramite un'applicazione di livello intermedio

Nota

In questo esempio la funzionalità dei predicati di blocco attualmente non è supportata per Azure Synapse, per cui l'inserimento di righe per l'ID utente errato non viene bloccato.

Questo esempio mostra in che modo un'applicazione di livello intermedio può implementare il filtro della connessione, in cui gli utenti dell'applicazione (o i tenant) condividono lo stesso utente di SQL Server (l'applicazione). L'applicazione imposta l'ID utente dell'applicazione corrente in SESSION_CONTEXT dopo la connessione al database, quindi i criteri di sicurezza filtrano in modo trasparente le righe che non devono essere visibili a questo ID e impediscono all'utente di inserire righe per l'ID utente errato. Non sono necessarie altre modifiche all'applicazione.

Creare una tabella per contenere i dati.

CREATE TABLE Sales (
    OrderId int,
    AppUserId int,
    Product varchar(10),
    Qty int
);

Popolare la tabella con sei righe di dati che visualizzano tre ordini per ogni utente dell'applicazione.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);

Creare un utente con privilegi limitati che l'applicazione userà per connettersi.

-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;

-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;

Creare un nuovo schema e una nuova funzione di predicato che userà l'ID utente dell'applicazione archiviato in SESSION_CONTEXT() per filtrare le righe.

CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO

Creare un criterio di sicurezza che aggiunga questa funzione come predicato di filtro e predicato di blocco in Sales. Il predicato di blocco richiede solo AFTER INSERT, perché BEFORE UPDATE e BEFORE DELETE sono già filtrati e AFTER UPDATE non è necessario perché la colonna AppUserId non può essere aggiornata con altri valori a causa dell'autorizzazione per la colonna impostata precedentemente.

CREATE SECURITY POLICY Security.SalesFilter
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);

A questo punto, è possibile simulare il filtro della connessione con la selezione dalla tabella Sales dopo l'impostazione di ID utente differenti diversi in SESSION_CONTEXT(). In pratica, l'applicazione è responsabile dell'impostazione dell'ID utente corrente in SESSION_CONTEXT() dopo l'apertura di una connessione. L'impostazione del parametro @read_only su 1 impedisce una nuova modifica del valore fino alla chiusura della connessione (restituita al pool di connessioni).

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO

/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;

SELECT * FROM Sales;
GO

INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO

REVERT;
GO

Pulire le risorse del database.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

D. Scenario per l'uso di una tabella di ricerca per il predicato di sicurezza

In questo esempio viene usata una tabella di ricerca per il collegamento tra l'ID utente e il valore filtrato, anziché la specifica dell’ID utente nella tabella dei fatti. Crea tre utenti e crea e popola una tabella dei fatti, Sample.Sales, con sei righe e una tabella di ricerca con due righe. Crea, quindi, una funzione con valori di tabella inline che unisce la tabella dei fatti alla ricerca per ottenere l'ID utente e un criterio di sicurezza per la tabella. L'esempio mostra poi in che modo le istruzioni Select vengono filtrate per i diversi utenti.

Creare tre account utente per mostrare le diverse capacità di accesso.

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

Creare uno schema Sample e una tabella dei fatti, Sample.Sales, per includere i dati.

CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
    (
    OrderID int,
    Product varchar(10),
    Qty int
    );

Popolare Sample.Sales con sei righe di dati.

INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;

Creare una tabella per contenere i dati di ricerca, in questo caso una relazione tra Salesrep e Product.

CREATE TABLE Sample.Lk_Salesman_Product
  ( Salesrep sysname,
    Product varchar(10)
  ) ;

Popolare la tabella di ricerca con dati di esempio, collegando un solo Product a ogni rappresentante di vendita.

INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;

Concedere l'accesso in lettura alla tabella a ognuno degli utenti.

GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;

Creare un nuovo schema e una funzione con valori di tabella inline. La funzione restituisce 1 quando un utente esegue una query nella tabella dei fatti Sample.Sales e la colonna SalesRep della tabella Lk_Salesman_Product corrisponde all'utente che esegue la query (@SalesRep = USER_NAME()) quando viene unita alla tabella dei fatti nella colonna Product oppure se l'utente che esegue la query è l'utente Manager (USER_NAME() = 'Manager').

CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
         (@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
           RETURN ( SELECT 1 as Result
                     FROM Sample.Sales f
            INNER JOIN Sample.Lk_Salesman_Product s
                     ON s.Product = f.Product
            WHERE ( f.product = @Product
                    AND s.SalesRep = USER_NAME() )
                 OR USER_NAME() = 'Manager'
                   ) ;

Creare i criteri di sicurezza aggiungendo la funzione come predicato di filtro. STATE deve essere impostato su ON per abilitare i criteri.

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;

Concedere autorizzazioni SELECT per la funzione fn_securitypredicate:

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;

A questo punto, testare il predicato di filtro mediante la selezione dalla tabella Sample.Sales per ogni utente.

EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;

EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;

Manager deve visualizzare tutte e sei le righe. Gli utenti Sales1 e Sales2 devono visualizzare solo le loro vendite.

Modificare i criteri di sicurezza per disabilitarli.

ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

A questo punto, gli utenti Sales1 e Sales2 possono visualizzare tutte e sei le righe.

Collegarsi al Database SQL per pulire risorse da questo semplice esercizio:

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;

E. Scenario di sicurezza a livello di riga in Microsoft Fabric

È possibile dimostrare la sicurezza a livello di riga Warehouse e l'endpoint di analisi SQL in Microsoft Fabric.

Nell'esempio seguente vengono create tabelle di esempio che funzioneranno con Warehouse in Microsoft Fabric, ma nell'endpoint di analisi SQL vengono usate tabelle esistenti. Nell'endpoint di analisi SQL non è possibile usare CREATE TABLE, ma è possibile usare CREATE SCHEMA, CREATE FUNCTION e CREATE SECURITY POLICY.

In questo esempio, creare prima uno schema sales, una tabella sales.Orders.

CREATE SCHEMA sales;
GO

-- Create a table to store sales data
CREATE TABLE sales.Orders (
    SaleID INT,
    SalesRep VARCHAR(100),
    ProductName VARCHAR(50),
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);

-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
    (1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
    (2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
    (3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
    (4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
    (5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
    (6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
    (7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
    (8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
    (9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
    (10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');

Creare uno schema Security, una funzione Security.tvf_securitypredicate e criteri di sicurezza SalesFilter.

-- Creating schema for Security
CREATE SCHEMA Security;
GO

-- Creating a function for the SalesRep evaluation
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'manager@contoso.com';
GO

-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO

Dopo l’applicazione dei criteri di sicurezza e la creazione della funzione, gli utenti Sales1@contoso.com e Sales2@contoso.com saranno in grado di visualizzare i loro dati nella tabella sales.Orders, dove la colonna SalesRep è uguale al loro nome utente restituito dalla funzione predefinita USER_NAME. L'utente di Fabric manager@contoso.com è in grado di visualizzare tutti i dati nella tabella sales.Orders .

Contenuto correlato

• CREATE SECURITY POLICY (Transact-SQL)
• ALTER SECURITY POLICY (Transact-SQL)
• DROP SECURITY POLICY (Transact-SQL)
• CREATE FUNCTION (Transact-SQL)
• SESSION_CONTEXT (Transact-SQL)
• sp_set_session_context (Transact-SQL)
• sys.security_policies (Transact-SQL)
• sys.security_predicates (Transact-SQL)
• Creare funzioni definite dall'utente (motore di database)
• GRANT - autorizzazioni per oggetti (Transact-SQL)