Sicurezza a livello di rigaRow-Level Security

SI APPLICA A: sìSQL Server sìDatabase SQL di Azure sìAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Immagine della sicurezza a livello di rigaRow level security graphic

La sicurezza a livello di riga consente di usare l'appartenenza a gruppi o il contesto di esecuzione per controllare l'accesso alle righe in una tabella di database.Row-Level Security enables you to use group membership or execution context to control access to rows in a database table.

La sicurezza a livello di riga semplifica la progettazione e la codifica della sicurezza nell'applicazioneRow-Level Security (RLS) simplifies the design and coding of security in your application. e facilita l'implementazione delle restrizioni di accesso alle righe di dati.RLS helps you implement restrictions on data row access. Ad esempio, è possibile assicurarsi che i dipendenti possano accedere solo alle righe di dati pertinenti per il loro reparto.For example, you can ensure that workers access only those data rows that are pertinent to their department. Un altro esempio può essere la limitazione dell'accesso ai dati dei clienti solo ai dati rilevanti per l'azienda.Another example is to restrict customers' data access to only the data relevant to their company.

La logica di restrizione dell'accesso si trova sul livello del database e non su un altro livello applicazione lontano dai dati.The access restriction logic is located in the database tier rather than away from the data in another application tier. Il sistema del database applica le restrizioni di accesso a ogni tentativo di accesso ai dati da qualsiasi livello.The database system applies the access restrictions every time that data access is attempted from any tier. La riduzione della superficie di attacco del sistema di sicurezza lo rende più affidabile e solido.This makes your security system more reliable and robust by reducing the surface area of your security system.

Implementare la sicurezza a livello di riga tramite l'istruzione CREATE SECURITY POLICYTransact-SQLTransact-SQL e i predicati creati come funzioni con valori di tabella inline.Implement RLS by using the CREATE SECURITY POLICYTransact-SQLTransact-SQL statement, and predicates created as inline table-valued functions.

Si applica a: SQL ServerSQL Server (da SQL Server 2016 (13.x)SQL Server 2016 (13.x) alla versione corrente), Database SQLSQL Database (fare clic qui per ottenerlo), SQL Data WarehouseSQL Data Warehouse.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Database SQLSQL Database (Get it), SQL Data WarehouseSQL Data Warehouse.

Nota

Azure SQL Data Warehouse supporta solo predicati di filtro.Azure SQL Data Warehouse supports filter predicates only. I predicati di blocco non sono attualmente supportati in Azure SQL Data Warehouse.Block predicates aren't currently supported in Azure SQL Data Warehouse.

DescrizioneDescription

La sicurezza a livello di riga supporta due tipi di predicati di sicurezza.RLS supports two types of security predicates.

  • I predicati del filtro filtrano automaticamente le righe disponibili per le operazioni di lettura (SELECT, UPDATE e DELETE).Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).

  • I predicati di blocco bloccano esplicitamente le operazioni di scrittura (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE e BEFORE DELETE) che violano il predicato.Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

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.Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. La funzione viene quindi richiamata e applicata dai criteri di sicurezza.The function is then invoked and enforced by a security policy. Nel caso dei predicati del filtro, l'applicazione non rileva le righe filtrate dal set di risultati.For filter predicates, the application is unaware of rows that are filtered from the result set. Se vengono filtrate tutte le righe, viene restituito un set Null.If all rows are filtered, then a null set will be returned. Per i predicati di blocco, qualsiasi operazione che violi il predicato non verrà completata e genererà un errore.For block predicates, any operations that violate the predicate will fail with an error.

I predicati di filtro vengono applicati durante la lettura dei dati dalla tabella di baseFilter predicates are applied while reading data from the base table. e influiscono su tutte le operazioni Get: SELECT, DELETE e UPDATE.They affect all get operations: SELECT, DELETE and UPDATE. Gli utenti non possono selezionare o eliminare le righe filtrate.The users can't select or delete rows that are filtered. L'utente non può aggiornare le righe filtrate.The user can't update rows that are filtered. Tuttavia, è possibile aggiornare le righe in modo che vengano filtrate in un secondo momento.But, it's possible to update rows in such a way that they'll be filtered afterward. I predicati di blocco influiscono su tutte le operazioni di scrittura.Block predicates affect all write operations.

  • I predicati AFTER INSERT e AFTER UPDATE possono impedire agli utenti di aggiornare le righe con valori che violano il predicato.AFTER INSERT and AFTER UPDATE predicates can prevent users from updating rows to values that violate the predicate.

  • I predicati BEFORE UPDATE possono impedire agli utenti di aggiornare le righe che attualmente violano il predicato.BEFORE UPDATE predicates can prevent users from updating rows that currently violate the predicate.

  • I predicati BEFORE DELETE possono bloccare le operazioni di eliminazione.BEFORE DELETE predicates can block delete operations.

I predicati del filtro e di blocco e i criteri di sicurezza si comportano nel modo seguente:Both filter and block predicates and security policies have the following behavior:

  • È possibile definire una funzione di predicato che si unisca a un'altra tabella e/o chiami una funzione.You may define a predicate function that joins with another table and/or invokes a function. Se i criteri di sicurezza vengono creati con SCHEMABINDING = ON, il join o la funzione è accessibile dalla query e funziona come previsto senza controlli aggiuntivi delle autorizzazioni.If the security policy is created with SCHEMABINDING = ON, then the join or function is accessible from the query and works as expected without any additional permission checks. Se i criteri di sicurezza vengono creati con SCHEMABINDING = OFF, gli utenti dovranno avere autorizzazioni SELECT o EXECUTE su queste tabelle e funzioni aggiuntive per eseguire query sulla tabella di destinazione.If the security policy is created with SCHEMABINDING = OFF, then users will need SELECT or EXECUTE permissions on these additional tables and functions to query the target table.

  • È possibile inviare una query in una tabella con un predicato di sicurezza definito ma disabilitato.You may issue a query against a table that has a security predicate defined but disabled. Le righe filtrate o bloccate non sono interessate.Any rows that are filtered or blocked aren't affected.

  • Se un utente dbo, un membro del ruolo db_owner o il proprietario della tabella esegue query su una tabella con criteri di sicurezza definiti e abilitati, le righe vengono filtrate o bloccate in base a quanto definito nei criteri di sicurezza.If a dbo user, a member of the db_owner role, or the table owner queries a table that has a security policy defined and enabled, the rows are filtered or blocked as defined by the security policy.

  • I tentativi di modificare lo schema di una tabella tramite un criterio di sicurezza associato allo schema generano un errore.Attempts to alter the schema of a table bound by a schema bound security policy will result in an error. Le colonne a cui non fa riferimento il predicato possono tuttavia essere modificate.However, columns not referenced by the predicate can be altered.

  • I tentativi di aggiunta di un predicato in una tabella in cui è già presente un predicato definito per l'operazione specificata generano un errore.Attempts to add a predicate on a table that already has one defined for the specified operation results in an error. Ciò si verifica indipendentemente dal fatto che il predicato sia abilitato o meno.This will happen whether the predicate is enabled or not.

  • I tentativi di modifica di una funzione usata come predicato in una tabella inclusa in criteri di sicurezza associati a schema generano un errore.Attempts to modify a function, that is used as a predicate on a table within a schema bound security policy, will result in an error.

  • La definizione di più criteri di sicurezza attivi contenenti predicati non sovrapposti riesce correttamente.Defining multiple active security policies that contain non-overlapping predicates, succeeds.

I predicati del filtro si comportano nel modo seguente:Filter predicates have the following behavior:

  • Definire i criteri di sicurezza per filtrare le righe di una tabella.Define a security policy that filters the rows of a table. L'applicazione non rileva le righe filtrate per le operazioni SELECT, UPDATE e DELETE,The application is unaware of any rows that are filtered for SELECT, UPDATE, and DELETE operations. anche nelle situazioni in cui tutte le righe sono state escluse. L'applicazione può eseguire operazioni INSERT di righe, anche se verranno filtrate durante altre operazioni.Including situations where all the rows are filtered out. The application can INSERT rows, even if they will be filtered during any other operation.

I predicati di blocco si comportano nel modo seguente:Block predicates have the following behavior:

  • I predicati di blocco di UPDATE vengono suddivisi in operazioni distinte BEFORE e AFTER.Block predicates for UPDATE are split into separate operations for BEFORE and AFTER. Di conseguenza non è possibile, ad esempio, impedire agli utenti di aggiornare una riga con un valore superiore a quello corrente.Consequently, you can't, for example, block users from updating a row to have a value higher than the current one. 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.If this kind of logic is required, you must use triggers with the DELETED and INSERTED intermediate tables to reference the old and new values together.

  • L'ottimizzatore non controllerà il predicato di blocco AFTER UPDATE se non è stata modificata alcuna delle colonne usate dalla funzione del predicato.The optimizer will not check an AFTER UPDATE block predicate if the columns used by the predicate function weren't changed. Esempio: Alice non deve essere in grado di modificare uno stipendio in modo che sia maggiore di 100.000.For example: Alice shouldn't be able to change a salary to be greater than 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.Alice can change the address of an employee whose salary is already greater than 100,000 as long as the columns referenced in the predicate weren't changed.

  • Non sono state modificate le API in blocco, compresa l'API BULK INSERT.No changes have been made to the bulk APIs, including BULK INSERT. Questo significa che i predicati di blocco AFTER INSERT verranno applicati alle operazioni di inserimento in blocco come se fossero operazioni di inserimento regolari.This means that block predicates AFTER INSERT will apply to bulk insert operations just as they would regular insert operations.

Modalità di utilizzo comuniUse Cases

Di seguito sono riportati degli esempi di progettazione relativi alle modalità di utilizzo della sicurezza a livello di riga:Here are design examples of how RLS can be used:

  • Un ospedale può creare criteri di sicurezza che consentono agli infermieri di visualizzare le righe di dati solo per i propri pazienti.A hospital can create a security policy that allows nurses to view data rows for their patients only.

  • 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.A bank can create a policy to restrict access to financial data rows based on an employee's business division or role in the company.

  • Un'applicazione multi-tenant può creare dei criteri per applicare una separazione logica delle righe di dati di ciascun tenant da qualsiasi altra riga del tenant.A multi-tenant application can create a policy to enforce a logical separation of each tenant's data rows from every other tenant's rows. L'efficienza viene raggiunta archiviando i dati per diversi tenant in un'unica tabella.Efficiencies are achieved by the storage of data for many tenants in a single table. Ogni tenant può visualizzare solo le proprie righe di dati.Each tenant can see only its data rows.

I predicati di filtro della sicurezza a livello di riga sono funzionalmente equivalenti all'aggiunta di una clausola WHERE .RLS filter predicates are functionally equivalent to appending a WHERE clause. Il predicato può essere sofisticato, se lo richiedono le procedure aziendali, oppure è possibile usare una clausola semplice, ad esempio WHERE TenantId = 42.The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42.

In termini più formali, la sicurezza a livello di riga introduce il controllo degli accessi basato su predicato.In more formal terms, RLS introduces predicate based access control. Tale controllo include una valutazione flessibile e centralizzata basata su predicato.It features a flexible, centralized, predicate-based evaluation. Il predicato può essere basato su metadati o su qualsiasi altro criterio considerato appropriato dall'amministratore.The predicate can be based on metadata or any other criteria the administrator determines as appropriate. Il predicato viene usato come criterio per determinare se l'utente dispone dell'accesso appropriato ai dati in base agli attributi utente.The predicate is used as a criterion to determine if the user has the appropriate access to the data based on user attributes. Il controllo degli accessi basato su etichetta può essere implementato usando un controllo degli accessi basato su predicato.Label-based access control can be implemented by using predicate-based access control.

AutorizzazioniPermissions

La creazione, la modifica o l'eliminazione dei criteri di sicurezza richiede l'autorizzazione ALTER ANY SECURITY POLICY .Creating, altering, or dropping security policies requires the ALTER ANY SECURITY POLICY permission. La creazione o l'eliminazione dei criteri di sicurezza richiede l'autorizzazione ALTER nello schema.Creating or dropping a security policy requires ALTER permission on the schema.

Inoltre, per ogni predicato che viene aggiunto sono richieste le autorizzazioni seguenti:Additionally the following permissions are required for each predicate that is added:

  • Le autorizzazioniSELECT e REFERENCES per la funzione usata come predicato.SELECT and REFERENCES permissions on the function being used as a predicate.

  • L'autorizzazioneREFERENCES per la tabella di destinazione associata ai criteri.REFERENCES permission on the target table being bound to the policy.

  • L'autorizzazioneREFERENCES per ogni colonna della tabella di destinazione usata come argomento.REFERENCES permission on every column from the target table used as arguments.

I criteri di sicurezza sono applicati a tutti gli utenti, inclusi gli utenti dbo nel database.Security policies apply to all users, including dbo users in the database. Gli utenti dbo possono modificare o eliminare i criteri di sicurezza, tuttavia tali modifiche ai criteri di sicurezza possono essere controllate.Dbo users can alter or drop security policies however their changes to security policies can be audited. 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.If high privileged users, such as sysadmin or db_owner, need to see all rows to troubleshoot or validate data, the security policy must be written to allow that.

Se i criteri di sicurezza vengono creati con SCHEMABINDING = OFF, per eseguire query sulla tabella di destinazione gli utenti devono avere l'autorizzazione SELECT o EXECUTE sulla funzione di predicato e su qualsiasi tabella, vista o funzione aggiuntiva usata nella funzione di predicato.If a security policy is created with SCHEMABINDING = OFF, then to query the target table, users must have the SELECT or EXECUTE permission on the predicate function and any additional tables, views, or functions used within the predicate function. 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.If a security policy is created with SCHEMABINDING = ON (the default), then these permission checks are bypassed when users query the target table.

Procedure consigliateBest Practices

  • Si consiglia di creare uno schema separato per gli oggetti della sicurezza a livello di riga, la funzione di predicato e i criteri di sicurezza.It's highly recommended to create a separate schema for the RLS objects, predicate function, and security policy.

  • L'autorizzazione ALTER ANY SECURITY POLICY è destinata agli utenti con privilegi elevati (ad esempio il gestore dei criteri di sicurezza).The ALTER ANY SECURITY POLICY permission is intended for highly privileged users (such as a security policy manager). Il gestore dei criteri di sicurezza non richiede l'autorizzazione SELECT per le tabelle che protegge.The security policy manager doesn't require SELECT permission on the tables they protect.

  • Non usare le conversioni del tipo nelle funzioni di predicato per evitare potenziali errori di run-time.Avoid type conversions in predicate functions to avoid potential runtime errors.

  • Se possibile, evitare la ricorsione nelle funzioni di predicato per evitare un calo delle prestazioni.Avoid recursion in predicate functions wherever possible to avoid performance degradation. Query Optimizer proverà a rilevare le ricorsioni dirette, ma non è garantito che trovi quelle indirette.The query optimizer will try to detect direct recursions, but isn't guaranteed to find indirect recursions. Una ricorsione indiretta si verifica quando una seconda funzione chiama la funzione di predicato.An indirect recursion is where a second function calls the predicate function.

  • Evitare di usare un numero eccessivo di join di tabella nelle funzioni di predicato per ottimizzare le prestazioni.Avoid using excessive table joins in predicate functions to maximize performance.

Evitare una logica del predicato dipendente da opzioni SET specifiche della sessione: nonostante sia improbabile che vengano usate in applicazioni pratiche, le funzioni del predicato la cui logica dipende da determinate opzioni SET specifiche della sessione possono causare la perdita di informazioni se gli utenti possono eseguire query arbitrarie.Avoid predicate logic that depends on session-specific SET options: While unlikely to be used in practical applications, predicate functions whose logic depends on certain session-specific SET options can leak information if users are able to execute arbitrary queries. 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.For example, a predicate function that implicitly converts a string to datetime could filter different rows based on the SET DATEFORMAT option for the current session. In generale le funzioni di predicato devono rispettare le regole seguenti:In general, predicate functions should abide by the following rules:

Nota sulla sicurezza: Attacchi side-channelSecurity Note: Side-Channel Attacks

Gestore dei criteri di sicurezza malintenzionatoMalicious security policy manager

è 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.It is important to observe that a malicious security policy manager, with sufficient permissions to create a security policy on top of a sensitive column and having permission to create or alter inline table-valued functions, can collude with another user who has select permissions on a table to perform data exfiltration by maliciously creating inline table-valued functions designed to use side channel attacks to infer data. 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.Such attacks would require collusion (or excessive permissions granted to a malicious user) and would likely require several iterations of modifying the policy (requiring permission to remove the predicate in order to break the schema binding), modifying the inline table-valued functions, and repeatedly running select statements on the target table. È consigliabile limitare le autorizzazioni in base alle esigenze e monitorare le attività sospette.We recommend you limit permissions as necessary and monitor for any suspicious activity. 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.Activity such as constantly changing policies and inline table-valued functions related to row-level security should be monitored.

Query appositamente createCarefully crafted queries

È possibile causare perdite di informazioni usando query appositamente create.It is possible to cause information leakage through the use of carefully crafted queries. Ad esempio, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' consentirebbe a un utente malintenzionato di sapere che lo stipendio di John Doe ammonta a 100.000 dollari.For example, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' would let a malicious user know that John Doe's salary is $100,000. 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.Even though there is a security predicate in place to prevent a malicious user from directly querying other people's salary, the user can determine when the query returns a divide-by-zero exception.

Compatibilità tra funzionalitàCross-Feature Compatibility

In generale la sicurezza a livello di riga funziona tra varie funzionalità nel modo previsto.In general, row-level security will work as expected across features. Esistono tuttavia alcune eccezioni a questa regola.However, there are a few exceptions. Questa sezione contiene diverse note e avvertenze per l'uso della sicurezza a livello di riga con altre funzionalità di SQL ServerSQL Server.This section documents several notes and caveats for using row-level security with certain other features of SQL ServerSQL Server.

  • DBCC SHOW_STATISTICS genera statistiche sui dati non filtrati e può causare perdite di informazioni altrimenti protette da criteri di sicurezza.DBCC SHOW_STATISTICS reports statistics on unfiltered data, and can leak information otherwise protected by a security policy. Per questo motivo, l'accesso per visualizzare un oggetto statistiche per una tabella con criteri di sicurezza a livello di riga è limitato.For this reason, access to view a statistics object for a table with a row-level security policy is restricted. L'utente deve essere il proprietario della tabella oppure un membro del ruolo predefinito del server sysadmin o del ruolo predefinito del database db_owner o db_ddladmin.The user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

  • Filestream: la sicurezza a livello di riga non è compatibile con Filestream.Filestream: RLS is incompatible with Filestream.

  • PolyBase: la sicurezza a livello di riga è supportata solo con tabelle esterne Polybase per Azure SQL Data Warehouse.PolyBase: RLS is supported with Polybase external tables for Azure SQL Data Warehouse only.

  • Tabelle ottimizzate per la memoria: la funzione inline con valori di tabella usata come predicato di sicurezza in una tabella ottimizzata per la memoria deve essere definita con l'opzione WITH NATIVE_COMPILATION.Memory-Optimized Tables: The inline table-valued function used as a security predicate on a memory-optimized table must be defined using the WITH NATIVE_COMPILATION option. 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.With this option, language features not supported by memory-optimized tables will be banned and the appropriate error will be issued at creation time. Per altre informazioni, vedere la sezione relativa alla sicurezza a livello di riga nelle tabelle con ottimizzazione per la memoria in Introduzione alle tabelle con ottimizzazione per la memoria.For more information, see the Row-Level Security in Memory Optimized Tables section in Introduction to Memory-Optimized Tables.

  • Viste indicizzate: in generale è possibile creare criteri di sicurezza nelle viste ed è possibile creare viste su tabelle associate a criteri di sicurezza.Indexed views: In general, security policies can be created on top of views, and views can be created on top of tables that are bound by security policies. 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.However, indexed views cannot be created on top of tables that have a security policy, because row lookups via the index would bypass the policy.

  • Change Data Capture: Change Data Capture 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 Change Data Capture viene abilitato per una tabella (si noti che è possibile impostare esplicitamente questa funzione su NULL per consentire a tutti gli utenti l'accesso ai dati di modifica).Change Data Capture: Change Data Capture can leak entire rows that should be filtered to members of db_owner or users who are members of the "gating" role specified when CDC is enabled for a table (note: you can explicitly set this function to NULL to enable all users to access the change data). I membri di questo ruolo di controllo e db_owner possono infatti visualizzare tutte le modifiche dei dati in una tabella anche se per la tabella esistono criteri di sicurezza.In effect, db_owner and members of this gating role can see all data changes on a table, even if there is a security policy on the table.

  • Rilevamento delle modifiche: il rilevamento delle modifiche può provocare la perdita della chiave primaria delle righe che devono essere filtrate per gli utenti con autorizzazioni SELECT e VIEW CHANGE TRACKING.Change Tracking: Change Tracking can leak the primary key of rows that should be filtered to users with both SELECT and VIEW CHANGE TRACKING permissions. I valori dei dati effettivi non vengono perduti, ma va perduto solo il fatto che la colonna A è stata aggiornata/inserita/eliminata per la riga con la chiave primaria B.Actual data values are not leaked; only the fact that column A was updated/inserted/deleted for the row with B primary key. Questo rappresenta un problema se la chiave primaria contiene un elemento riservato, ad esempio un codice fiscale.This is problematic if the primary key contains a confidential element, such as a Social Security Number. In pratica però CHANGETABLE è quasi sempre unito alla tabella originale per ottenere i dati più recenti.However, in practice, this CHANGETABLE is almost always joined with the original table in order to get the latest data.

  • 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.Full-Text Search: A performance hit is expected for queries using the following Full-Text Search and Semantic Search functions, because of an extra join introduced to apply row-level security and avoid leaking the primary keys of rows that should be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • Indici columnstore: la sicurezza a livello di riga è compatibile con indici columnstore sia cluster che non cluster.Columnstore Indexes: RLS is compatible with both clustered and nonclustered columnstore indexes. Dato però che la sicurezza a livello di riga applica una funzione, l'ottimizzatore può modificare il piano di query in modo che non usi la modalità batch.However, because row-level security applies a function, it is possible that the optimizer may modify the query plan so that it doesn't use batch mode.

  • Viste partizionate: non è possibile definire predicati di blocco nelle viste partizionate e non è possibile creare viste partizionate in tabelle che usano predicati di blocco.Partitioned Views: Block predicates cannot be defined on partitioned views, and partitioned views cannot be created on top of tables that use block predicates. I predicati di filtro sono compatibili con le viste partizionate.Filter predicates are compatible with partitioned views.

  • Tabelle temporali: le tabelle temporali sono compatibili con la sicurezza a livello di riga.Temporal tables: Temporal tables are compatible with RLS. I predicati di sicurezza nella tabella corrente non vengono tuttavia replicati automaticamente nella tabella di cronologia.However, security predicates on the current table are not automatically replicated to the history table. Per applicare un criterio di sicurezza alla tabella della cronologia e alla tabella corrente, è necessario aggiungere singolarmente un predicato di sicurezza in ogni tabella.To apply a security policy to both the current and the history tables, you must individually add a security predicate on each table.

EsempiExamples

A.A. Scenari per gli utenti che eseguono l'autenticazione nel databaseScenario for users who authenticate to the database

In questo esempio vengono creati tre utenti, quindi viene creata e popolata una tabella con sei righe.This example creates three users and creates and populates a table with six rows. Vengono quindi creati una funzione inline con valori di tabella e criteri di sicurezza per la tabella.It then creates an inline table-valued function and a security policy for the table. L'esempio mostra poi in che modo le istruzioni Select vengono filtrate per i diversi utenti.The example then shows how select statements are filtered for the various users.

Creare tre account utente per mostrare le diverse capacità di accesso.Create three user accounts that will demonstrate different access capabilities.

Nota

Azure SQL Data Warehouse non supporta EXECUTE AS USER, pertanto è necessario eseguire CREATE LOGIN per ogni utente in anticipo.Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so you must CREATE LOGIN for each user beforehand. In un secondo momento si accede con le credenziali dell'utente appropriato per eseguire il test di questo comportamento.Later, you will log in as the appropriate user to test this behavior.

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

Creare una tabella per contenere i dati.Create a table to hold data.

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.Populate the table with six rows of data, showing three orders for each sales representative.

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;

Concedere l'accesso in lettura alla tabella a ciascuno degli utenti.Grant read access on the table to each of the users.

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

Creare un nuovo schema e una funzione con valori di tabella inline.Create a new schema, and an inline table-valued function. 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').The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (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 i criteri di sicurezza aggiungendo la funzione come predicato di filtro.Create a security policy adding the function as a filter predicate. Lo stato deve essere impostato su ON per abilitare i criteri.The state must be set to ON to enable the policy.

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

Consentire le autorizzazioni SELECT per la funzione fn_securitypredicateAllow SELECT permissions to the fn_securitypredicate function

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

Testare il predicato di filtro mediante la selezione dalla tabella Sales per ciascun utente.Now test the filtering predicate, by selected from the Sales table as each user.

EXECUTE AS USER = 'Sales1';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Sales2';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;
REVERT;  

Nota

Azure SQL Data Warehouse non supporta l'istruzione EXECUTE AS USER, pertanto accedere con le credenziali utente appropriate per eseguire il test del comportamento precedente.Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so log in as the appropriate user to test the above behavior.

Il gestore dovrebbe visualizzare tutte e sei le righe.The Manager should see all six rows. Gli utenti Sales1 e Sales2 dovrebbero visualizzare solo le proprie vendite.The Sales1 and Sales2 users should only see their own sales.

Modificare i criteri di sicurezza per disabilitarli.Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

Ora gli utenti Sales1 e Sales2 possono visualizzare tutte e sei le righe.Now Sales1 and Sales2 users can see all six rows.

Connettersi al database SQL per pulire le risorseConnect to the SQL database to clean up resources

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

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

B.B. Scenari per l'uso della sicurezza a livello di riga in una tabella esterna di Azure SQL Data WarehouseScenarios for using Row Level Security on an Azure SQL Data Warehouse external table

Questo breve esempio crea tre utenti e una tabella esterna con sei righe.This short example creates three users and an external table with six rows. Vengono quindi creati una funzione inline con valori di tabella e criteri di sicurezza per la tabella esterna.It then creates an inline table-valued function and a security policy for the external table. L'esempio mostra in che modo le istruzioni Select vengono filtrate per i diversi utenti.The example shows how select statements are filtered for the various users.

Creare tre account utente per mostrare le diverse capacità di accesso.Create three user accounts that will demonstrate different access capabilities.

CREATE LOGIN Manager WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales1 WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales2 WITH PASSWORD = 'somepassword'
GO

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 a table to hold data.

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.Populate the table with six rows of data, showing three orders for each sales representative.

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 SQL Data Warehouse dalla tabella Sales creata.Create an Azure SQL Data Warehouse external table from the Sales table created.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword';

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

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

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);
  
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='RLSExtTabletest.tbl', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

Concedere l'autorizzazione SELECT per la tabella esterna dei tre utenti.Grant SELECT for the three users external table.

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

Creare criteri di sicurezza sulla tabella esterna usando la funzione nella sessione A come predicato di filtro.Create a security policy on external table using the function in session A as a filter predicate. Lo stato deve essere impostato su ON per abilitare i criteri.The state must be set to ON to enable the policy.

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

Testare ora il predicato di filtro mediante la selezione dalla tabella esterna Sales_ext.Now test the filtering predicate, by selecting from the Sales_ext external table. Eseguire l'accesso con ogni utente, ovvero Sales1, Sales2 e manager.Sign in as each user, Sales1, Sales2, and manager. Eseguire il comando seguente per ogni utente.Run the following command as each user.

SELECT * FROM Sales_ext;

Il gestore dovrebbe visualizzare tutte e sei le righe.The Manager should see all six rows. Gli utenti Sales1 e Sales2 dovrebbero visualizzare solo le proprie vendite.The Sales1 and Sales2 users should only see their sales.

Modificare i criteri di sicurezza per disabilitarli.Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);  

Ora gli utenti Sales1 e Sales2 possono visualizzare tutte e sei le righe.Now the Sales1 and Sales2 users can see all six rows.

Connettersi al database SQL Data Warehouse per pulire le risorseConnect to the SQL Data Warehouse database to clean up resources

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;

Connettersi al database master logico per pulire le risorse.Connect to logical master to clean up resources.

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

C.C. Scenari per gli utenti che si connettono al database tramite un'applicazione di livello intermedioScenario for users who connect to the database through a middle-tier application

Nota

In questo esempio, la funzionalità di predicati di blocco non è attualmente supportata per Azure SQL Data Warehouse, quindi l'inserimento di righe per l'ID utente errato non viene bloccato con Azure SQL Data Warehouse.In this example block predicates functionality isn't currently supported for Azure SQL Data Warehouse, hence inserting rows for the wrong user ID isn't blocked with Azure SQL Data Warehouse.

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 SQL ServerSQL Server (l'applicazione).This example shows how a middle-tier application can implement connection filtering, where application users (or tenants) share the same SQL ServerSQL Server user (the application). L'applicazione imposta l'ID utente dell'applicazione corrente in SESSION_CONTEXT (Transact-SQL) dopo la connessione al database, quindi i criteri di sicurezza filtrano in modo trasparente le righe che non devono essere visibili a tale ID e impediscono all'utente di inserire righe per l'ID utente errato.The application sets the current application user ID in SESSION_CONTEXT (Transact-SQL) after connecting to the database, and then security policies transparently filter rows that shouldn't be visible to this ID, and also block the user from inserting rows for the wrong user ID. Non sono necessarie altre modifiche all'applicazione.No other app changes are necessary.

Creare una tabella per contenere i dati.Create a table to hold data.

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.Populate the table with six rows of data, showing three orders for each application user.

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.Create a low-privileged user that the application will use to connect.

-- 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 con cui usare l'ID utente dell'applicazione archiviato in SESSION_CONTEXT per filtrare le righe.Create a new schema and predicate function, which will use the application user ID stored in SESSION_CONTEXT to filter rows.

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.Create a security policy that adds this function as a filter predicate and a block predicate on Sales. Nel predicato di blocco è necessario 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 in precedenza.The block predicate only needs AFTER INSERT, because BEFORE UPDATE and BEFORE DELETE are already filtered, and AFTER UPDATE is unnecessary because the AppUserId column cannot be updated to other values, due to the column permission set earlier.

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);  

Ora è possibile simulare il filtro della connessione con la selezione dalla tabella Sales dopo l'impostazione di ID utente diversi in SESSION_CONTEXT.Now we can simulate the connection filtering by selecting from the Sales table after setting different user IDs in SESSION_CONTEXT. In pratica, l'applicazione è responsabile dell'impostazione dell'ID utente corrente in SESSION_CONTEXT dopo l'apertura di una connessione.In practice, the application is responsible for setting the current user ID in SESSION_CONTEXT after opening a connection.

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.Clean up database resources.

DROP USER AppUser;

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

Vedere ancheSee Also

CREATE SECURITY POLICY (Transact-SQL)CREATE SECURITY POLICY (Transact-SQL)
ALTER SECURITY POLICY (Transact-SQL)ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL)DROP SECURITY POLICY (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
SESSION_CONTEXT (Transact-SQL)SESSION_CONTEXT (Transact-SQL)
sp_set_session_context (Transact-SQL)sp_set_session_context (Transact-SQL)
sys.security_policies (Transact-SQL)sys.security_policies (Transact-SQL)
sys.security_predicates (Transact-SQL)sys.security_predicates (Transact-SQL)
Creare funzioni definite dall'utente (motore di database)Create User-defined Functions (Database Engine)