Sicurezza a livello di rigaRow-Level Security

Si applica a:Applies to: sìSQL ServerSQL Server (tutte le versioni supportate) yesSQL ServerSQL Server (all supported versions) Sìdatabase SQL di AzureAzure SQL DatabaseYesdatabase SQL di AzureAzure SQL Database SìIstanza gestita di SQL di AzureAzure SQL Managed InstanceYesIstanza gestita di SQL di AzureAzure SQL Managed Instance sìAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse AnalyticsSi applica a:Applies to: sìSQL ServerSQL Server (tutte le versioni supportate) yesSQL ServerSQL Server (all supported versions) Sìdatabase SQL di AzureAzure SQL DatabaseYesdatabase SQL di AzureAzure SQL Database SìIstanza gestita di SQL di AzureAzure SQL Managed InstanceYesIstanza gestita di SQL di AzureAzure SQL Managed Instance sìAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

Rappresentazione grafica 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. Il sistema di sicurezza è così più affidabile e solido, grazie alla riduzione della superficie di attacco del sistema di sicurezza.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:Applies to: sìSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) e versioni successiveSi applica a:Applies to: sìSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later, Database SQLSQL Database ( Getit), Azure Synapse AnalyticsAzure Synapse Analytics .Si applica a:Applies to: sìSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) e versioni successiveSi applica a:Applies to: sìSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later, Database SQLSQL Database (Get it), Azure Synapse AnalyticsAzure Synapse Analytics.

Nota

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

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 (valore predefinito), 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 (the default), 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 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 permissions on these additional tables and functions to query the target table. Se la funzione di predicato richiama una funzione CLR a valori scalari, è necessaria anche l'autorizzazione EXECUTE.If the predicate function invokes a CLR scalar-valued function, the EXECUTE permission is needed in addition.

  • È 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. Ad 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 di accesso basato su etichetta può essere implementato usando il controllo di accesso 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 autorizzazioni SELECT e REFERENCES per la funzione usata come predicato.SELECT and REFERENCES permissions on the function being used as a predicate.

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

  • L'autorizzazione REFERENCES 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

  • È consigliabile creare uno schema separato per gli oggetti con sicurezza a livello di riga, vale a dire funzioni di predicato e criteri di sicurezza.It's highly recommended to create a separate schema for the RLS objects: predicate functions, and security policies. In questo modo si separano le autorizzazioni necessarie per questi oggetti speciali dalle tabelle di destinazione.This helps to separate the permissions that are required on these special objects from the target tables. La separazione aggiuntiva per diversi criteri e funzioni di predicato può essere necessaria nei database multi-tenant, ma non rappresenta uno standard per ogni caso.Additional separation for different policies and predicate functions may be needed in multi-tenant-databases, but not as a standard for every case.

  • 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 mediante l'utilizzo di 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 funzionalità RLS è supportata con tabelle esterne in Azure Synapse e SQL Server 2019 CU7 o versione successiva.PolyBase: RLS is supported with external tables in Azure Synapse and SQL Server 2019 CU7 or higher.

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

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

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

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

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 read access on the table to each of the users.

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.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.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.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.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);  
GO

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

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

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 = '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.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 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.B. Scenari per l'uso della sicurezza a livello di riga in una tabella esterna di Azure SynapseScenarios for using Row Level Security on an Azure Synapse 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.

PrerequisitiPrerequisites

  1. È necessario disporre di un pool SQL dedicato.You must have a dedicated SQL pool. Vedere Creare un pool SQL dedicatoSee Create a dedicated SQL pool
  2. Il server che ospita il pool SQL dedicato deve essere registrato con AAD ed è necessario disporre di un account di archiviazione di Azure con autorizzazioni collaboratore ai dati del blog di archiviazione.The server hosting your dedicated SQL pool must be registered with AAD and you must have an Azure storage account with Storage Blog Data Contributor permissions. Seguire i passaggi qui.Follow the steps here.
  3. Creare un file system per l'account di archiviazione di Azure.Create a file system for your Azure Storage account. Usare Storage Explorer per visualizzare l'account di archiviazione.Use Storage Explorer to view your storage account. Fare clic con il pulsante destro del mouse su contenitori e scegliere Crea file system.Right click on containers and select Create file system.

Una volta soddisfatti i prerequisiti, creare tre account utente che dimostreranno le diverse funzionalità di accesso.Once you have the prerequisites in place, create three user accounts that will demonstrate different access capabilities.

--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 master and 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 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 Synapse dalla tabella Sales appena creata.Create an Azure Synapse external table from the Sales table you just created.

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 for the three users on the external table Sales_ext that you created.

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 inline con valori di tabella (è 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 gestore (USER_NAME() = 'Manager').Create a new schema, and an inline table-valued function, you may have completed this in example A. 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 criteri di sicurezza per la tabella esterna usando la funzione inline con valori di tabella come predicato del filtro.Create a security policy on your external table using the inline table-valued 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_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 Azure Synapse per pulire le risorseConnect to the Azure Synapse 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 poiché la funzionalità di predicati di blocco non è attualmente supportata per Azure Synapse, l'inserimento di righe per l'ID utente errato non viene bloccato con Azure Synapse.In this example block predicates functionality isn't currently supported for Azure Synapse, hence inserting rows for the wrong user ID isn't blocked with Azure Synapse.

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;

D.D. Scenario per l'uso di una tabella di ricerca per il predicato di sicurezzaScenario for using a lookup table for the security predicate

In questo esempio viene utilizzata una tabella di ricerca per il collegamento tra l'identificatore utente e il valore filtrato, anziché dover specificare l'identificatore utente nella tabella dei fatti.This example uses a lookup table for the link between the user identifier and the value being filtered, rather than having to specify the user identifier in the fact table. Crea tre utenti e crea e popola una tabella dei fatti con sei righe e una tabella di ricerca con due righe.It creates three users and creates and populates a fact table with six rows and a lookup table with two rows. Crea quindi una funzione inline con valori di tabella che unisce la tabella dei fatti alla ricerca per ottenere l'identificatore utente e criteri di sicurezza per la tabella.It then creates an inline table-valued function that joins the fact table to the lookup to get the user identifier, 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.

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

Creare uno schema di esempio e una tabella dei fatti per contenere i dati.Create a sample schema and a fact table to hold data.

CREATE SCHEMA Sample;

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

Popolare la tabella dei fatti con sei righe di dati.Populate the fact table with six rows of data.

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 a table to hold the lookup data – in this case a relationship between Salesrep and Product.

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

Popolare la tabella di ricerca con dati di esempio, collegando un prodotto a ogni rappresentante.Populate the lookup table with sample data, linking one Product to each sales representative.

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 nella tabella dei fatti a ognuno degli utenti.Grant read access on the fact table to each of the users.

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.Create a new schema, and an inline table-valued function. La funzione restituisce 1 quando un utente esegue una query sulla tabella dei fatti Sales e sulla colonna SalesRep della tabella Lk_Salesman_Product è uguale all'utente che esegue la query ( ) quando è unito alla tabella dei fatti nella colonna Product o se l'utente che esegue la query è l'utente @SalesRep = USER_NAME() Manager ( USER_NAME() = 'Manager' ).The function returns 1 when a user queries the fact table Sales and the SalesRep column of the table Lk_Salesman_Product is the same as the user executing the query (@SalesRep = USER_NAME()) when joined to the fact table on the Product column, or if the user executing the query is the Manager user (USER_NAME() = 'Manager').

CREATE SCHEMA Security ;

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.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(Product)
ON Sample.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 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;

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 FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security; 
DROP SCHEMA Sample;

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)