Sicurezza a livello di rigaRow-Level Security

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Immagine della sicurezza a livello di rigaRow level security graphic

La sicurezza a livello di consente ai clienti di controllare l'accesso alle righe in una tabella del database in base alle caratteristiche dell'utente che esegue una query (ad esempio, l'appartenenza al gruppo o il contesto di esecuzione).Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).

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 consente di implementare delle restrizioni di accesso alle righe di dati.RLS enables you to implement restrictions on data row access. Ad esempio, assicura che i dipendenti possano accedere solo alle righe di dati relative al proprio reparto o limita l'accesso ai dati di un cliente in modo che possa visualizzare solo i dati rilevanti per la propria azienda.For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer's 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 POLICY Transact-SQLTransact-SQL e i predicati creati come funzioni inline con valori di tabella.Implement RLS by using the CREATE SECURITY POLICY Transact-SQLTransact-SQL statement, and predicates created as inline table valued functions.

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

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 non sono presenti indicazioni per l'applicazione che le righe sono state filtrate dal set di risultati. Se vengono filtrate tutte le righe, viene restituito un set Null.For filter predicates, there is no indication to the application that rows have been filtered from the result set; 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 base. Questa azione influisce su tutte le operazioni Get: SELECT, DELETE (l'utente non può eliminare le righe filtrate) e UPDATE (l'utente non può aggiornare le righe filtrate, sebbene sia possibile aggiornare le righe in modo che vengano filtrate successivamente).Filter predicates are applied while reading data from the base table, and it affects all get operations: SELECT, DELETE (i.e. user cannot delete rows that are filtered), and UPDATE (i.e. user cannot update rows that are filtered, although it is possible to update rows in such way that they will be subsequently filtered). 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 in order 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 would have been filtered or blocked are not affected.

  • Se l'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 the dbo user, a member of the db_owner role, or the table owner queries against 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, indipendentemente dal fatto che sia abilitato o disabilitato, generano un errore.Attempts to add a predicate on a table that already has one defined for the specified operation (regardless of whether it is enabled or disabled) results in an error.

  • Per quanto riguarda i criteri di sicurezza associati allo schema, i tentativi di modifica di una funzione usata come predicato in una tabella inclusa nei risultati di un criterio di sicurezza generano un errore.For schema bound security policies, attempts to modify a function used as a predicate on a table within a security policy results 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 righe filtrate per le operazioni SELECT, UPDATEe DELETE , incluse le situazioni in cui sono state escluse tutte le righe. L'applicazione può eseguire INSERT su qualsiasi riga, a prescindere se sarà filtrata durante altre operazioni.The application is unaware that any rows have been filtered for SELECT, UPDATE, and DELETE operations, including situations where all the rows have been filtered out. The application can INSERT any rows, regardless of whether or not 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 cannot, 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 e UPDATE se non è stata modificata nessuna delle colonne usate dalla funzione del predicato.The optimizer will not check an AFTER UPDATE block predicate if none of the columns used by the predicate function were changed. Ad esempio, Alice non deve essere in grado di modificare uno stipendio in modo che superi 100.000, ma deve essere in grado di modificare l'indirizzo di un dipendente il cui stipendio è già maggiore di 100.000 e pertanto viola già il predicato.For example: Alice should not be able to change a salary to be greater than 100,000, but she should be able to change the address of an employee whose salary is already greater than 100,000 (and thus already violates the predicate).

  • 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 own patients only.

  • Una banca può creare dei 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 rows of financial data based on the employee's business division, or based on the employee's role within 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. Naturalmente, ogni tenant può visualizzar solo le proprie righe di dati.Of course, 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. Comprende una valutazione basata su predicato flessibile e centralizzata che può prendere in considerazione i metadati o altri criteri ritenuti appropriati dall'amministratore.It features a flexible, centralized, predicate-based evaluation that can take into consideration metadata or any other criteria the administrator determines as appropriate. Il predicato viene usato come criterio per determinare se l'utente dispone o meno dell'accesso appropriato ai dati in base agli attributi utente.The predicate is used as a criterion to determine whether or not 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, il criterio di sicurezza deve essere scritto 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 (funzione di predicato e criteri di sicurezza).It is 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 nelle tabella che protegge.The security policy manager does not 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 tenta di rilevare le ricorsioni dirette, ma non garantisce il rilevamento delle ricorsioni indirette (ossia, quando una seconda funzione chiama la funzione di predicato).The query optimizer will try to detect direct recursions, but is not guaranteed to find indirect recursions (i.e., 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 di predicato dipendente da opzioni SETspecifiche 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.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:

  • Le funzioni di predicato non devono convertire implicitamente stringhe di caratteri in date, smalldatetime, datetime, datetime2 o datetimeoffset né viceversa, perché queste conversioni sono influenzate dalle opzioni SET DATEFORMAT (Transact-SQL) e SET LANGUAGE (Transact-SQL).Predicate functions should not implicitly convert character strings to date, smalldatetime, datetime, datetime2, or datetimeoffset, or vice versa, because these conversions are affected by the SET DATEFORMAT (Transact-SQL) and SET LANGUAGE (Transact-SQL) options. Utilizzare invece la funzione CONVERT e specificare esplicitamente il parametro di stile.Instead, use the CONVERT function and explicitly specify the style parameter.

  • Le funzioni di predicato non devono basarsi sul valore del primo giorno della settimana, perché questo valore è influenzato dall'opzione SET DATEFIRST (Transact-SQL).Predicate functions should not rely on the value of the first day of the week, because this value is affected by the SET DATEFIRST (Transact-SQL) option.

  • Le funzioni di predicato non devono basarsi su espressioni aritmetiche o di aggregazione che restituiscono NULL in caso di errore, come overflow o divisione per zero, perché questo comportamento è influenzato dalle opzioni SET ANSI_WARNINGS (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL) e SET ARITHABORT (Transact-SQL).Predicate functions should not rely on arithmetic or aggregation expressions returning NULL in case of error (such as overflow or divide-by-zero), because this behavior is affected by the SET ANSI_WARNINGS (Transact-SQL), SET NUMERIC_ROUNDABORT (Transact-SQL), and SET ARITHABORT (Transact-SQL) options.

  • Le funzioni di predicato non devono confrontare stringhe concatenate con NULL, perché questo comportamento è influenzato dall'opzione SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).Predicate functions should not compare concatenated strings with NULL, because this behavior is affected by the SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) option.

Nota sulla sicurezza: attacchi al canale lateraleSecurity Note: Side-Channel Attacks

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.Malicious security policy manager: 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 that 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 inline con valori di tabella 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. Si consiglia di limitare le autorizzazioni concedendo solo quelle necessarie e di monitorare le attività sospette, ad esempio la modifica frequente dei criteri e delle funzioni inline con valori di tabella relativi alla sicurezza a livello di riga.It is strongly recommended to limit permissions as it is necessary and to monitor for any suspicious activity such as constantly changing policies and inline table valued functions related to row-level security.

Query create appositamente: è possibile causare perdite di informazioni mediante l'utilizzo di query create appositamente.Carefully crafted queries: 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 potrebbero quindi verificarsi perdite di informazioni altrimenti protette da criteri di sicurezza.DBCC SHOW_STATISTICS reports statistics on unfiltered data, and thus can leak information otherwise protected by a security policy. Per questo motivo, per visualizzare un oggetto statistiche per una tabella con criteri di sicurezza a livello di riga, 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.For this reason, in order to view a statistics object for a table with a row-level security policy, 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.

  • La sicurezza a livello di rigaFilestream non è compatibile con Filestream.Filestream RLS is incompatible with Filestream.

  • La sicurezza a livello di rigaPolybase non è compatibile con Polybase.Polybase RLS is incompatible with Polybase.

  • 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 TablesThe 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 nelle 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 NULL per consentire a tutti gli utenti di accedere 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 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 . 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, è previsto un calo di prestazioni per le query che usano le funzioni di ricerca semantica e ricerca full-text seguenti: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable e 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 non-clustered 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 such that it does not 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.

  • Le tabelle temporali sono compatibili con la sicurezza a livello di riga.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

Questo breve esempio crea tre utenti, crea e popola una tabella con sei righe, quindi crea una funzione inline con valori di tabella e i criteri di sicurezza per la tabella.This short example creates three users, creates and populates a table with 6 rows, then creates an inline table valued function and a security policy for the 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 USER Manager WITHOUT LOGIN;  
CREATE USER Sales1 WITHOUT LOGIN;  
CREATE USER Sales2 WITHOUT LOGIN;  

Creare una tabella semplice per conservare i dati.Create a simple 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 mostrano tre ordini per ciascun rappresentante.Populate the table with 6 rows of data, showing 3 orders for each sales representative.

INSERT Sales VALUES   
(1, 'Sales1', 'Valve', 5),   
(2, 'Sales1', 'Wheel', 2),   
(3, 'Sales1', 'Valve', 4),  
(4, 'Sales2', 'Bracket', 2),   
(5, 'Sales2', 'Wheel', 5),   
(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 inline con valori di tabella.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);  

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;  

Il gestore dovrebbe visualizzare tutte e sei le righe.The Manager should see all 6 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 the Sales1 and Sales2 users can see all 6 rows.

B.B. 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

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 semplice per conservare i dati.Create a simple 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 mostrano tre ordini per ciascun utente dell'applicazione.Populate the table with 6 rows of data, showing 3 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  

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)