SQL  Detailed explanation of server's row level security

catalogue1 Preface2 Description3 Permissions4 Security note: Side channel attack5 Cross functional compatibility6 Example1 PrefaceRow level security allows you to control access to rows in database tables using group membership or execution context.Row level security (RLS) simplifies security design and coding in applications

1 Preface

Row level security allows you to control access to rows in database tables using group membership or execution context.

Row level security (RLS) simplifies security design and coding in applications. RLS can help you enforce restrictions on data row access. For example, you can ensure that staff only access data rows related to their department. Another example is to restrict customer data access to only data related to their company.

The access restriction logic is located in the database layer, rather than away from data in another application layer. Every time you try to access data from any layer, the database system applies access restrictions. This makes your security system more reliable and powerful by reducing its surface area.

Implement RLS by using Transact-SQL statements that create security policies and predicates created as inline table valued functions.

Row level security was first introduced in SQL Server 2016 (13. x).

2 Description

RLS supports two types of security predicates.

• The filter predicate silently filters rows that can be used for read operations (select, update, and delete).
• Explicitly blocking write operations that violate the predicate (after insertion, after update, before update, before deletion).

Access to row level data in a table is limited by security predicates defined as inline table valued functions. Then, the security policy calls and enforces the function. For filter predicates, the application does not know which rows to filter from the result set. If all rows are filtered, an empty set will be returned. For block predicates, any operation that violates the predicate will fail with an error.

Apply filter predicates when reading data from the base table. They affect all fetch operations: select, delete, and update. Users cannot select or delete filtered rows. The user cannot update the filtered rows. However, rows can be updated for future filtering. Block predicates affect all write operations.

• “ After insertion; And&quo; After Update” Predicates can prevent users from updating rows with values that violate the predicate.
• The BEFOREUPDATE predicate can prevent users from updating rows that currently violate the predicate.
• Verbs can prevent deletion operations before deletion.

Filters, blocking predicates, and security policies all have the following behaviors:

• You can define a predicate function that joins and/or calls another table. If you use (default) to create a security policy, you can access joins or functions from a query and work as expected without any other permission checks. If creating a security policy is used, users will need SELECT permission on these additional tables and functions in order to query the target table. If the predicate function calls a CLR scalar valued function, then EXECUTE permission is also required.
• Queries can be issued against tables that have been defined but have security predicates disabled. Any rows filtered or blocked are not affected.
• If dbo users, db_ Members of the owner role or table owners who query tables with security policies defined and enabled will filter or block rows according to the definition of security policies.
• Attempting to change the schema of a table bound by a schema bound security policy will result in an error. However, columns that are not referenced by the predicate can be changed.
• Attempting to add a predicate on a table that has already defined a predicate for the specified operation will result in an error. This situation occurs regardless of whether the predicate is enabled or not.
• Attempting to modify a function (used as a predicate for a table in the schema binding security policy) will result in an error.
• Defining multiple active security policies that contain non overlapping predicates will succeed.

The filter predicate has the following behavior:

• Define security policies for filtering rows in a table. The application is not aware of any rows filtered for SELECT, UPDATE, and DELETE operations. Including filtering out all rows. Applications can insert rows even if they will be filtered during any other operation.

Block predicates have the following behavior:

• The block predicate of UPDATE is split into separate operations of 'Before' and 'After'. For example, users cannot be prevented from updating rows with values higher than the current value. If this logic is needed, the trigger must be used together with the DELETE and Insert intermediate tables to reference both old and new values.
• If the column used by the predicate function has not been changed, the optimizer will not check the AFTERUPDATE block predicate.
• No changes have been made to the bulk API, including bulk inserts. This means that the block predicate AFTERINSERT will be applied to batch insert operations, just as they will be applied to regular insert operations.

3 Permissions

Creating, changing, or deleting security policies requires&quo; Change any security policies; jurisdiction. Creating or deleting security policies requires Alter permission on the schema.

In addition, each predicate added requires the following permissions:

• SELECT and REFERENCE permissions on functions used as predicates.
• REFERENCES permission on the target table bound to the policy.
• The REFERENCES permission for each column in the target table used as a parameter.

The security policy applies to all users, including dbo users in the database. Dbo users can change or delete security policies, but they can audit their changes to security policies. If a highly privileged user (such as sysadmin or db_owner) needs to view all rows to troubleshoot or verify data, security policies must be written to allow this.

If creating a security policy is used, to query the target table, the user must have SELECT or EXECUTE permissions on the predicate function and any other tables, views, or functions used in the predicate function. If a security policy is created using (default), these permission checks will be bypassed when users query the target table.

4 Security note: Side channel attack

(1) Malicious Security Policy Manager.

It is important to note that the malicious security policy manager has sufficient permissions to create security policies on sensitive columns and has the authority to create or change inline table valued functions, but can collude with other users with selection permissions on the table to execute data leakage by maliciously creating inline table valued functions aimed at using side channel attacks to push data. This type of attack requires collusion (or granting excessive permissions to malicious users), and may require multiple iterations of modifying policies (requiring permission to remove predicates to break schema binding), modifying inline table valued functions, and repeatedly running select statements on the target table. We suggest that you restrict permissions as needed and monitor any suspicious activities. Activities should be monitored, such as constantly changing policies and inline table valued functions related to row level security.

(2) Carefully designed queries.

By using carefully designed queries that exploit errors, information leakage may occur.

5 Cross functional compatibility

• Typically, row level security will work as expected across functions. However, there are also some exceptions. This section documents several considerations and considerations for combining row level security with certain other features of SQL Server.
• DBCCSHOW_ STATISTICS reports statistical information on unfiltered data and may leak information protected by security policies. Therefore, access to statistical information objects that view tables with row level security policies is restricted. The user must have the table, or the user must be a sysadmin fixed server role, db_ Owner fixed database role or db_ A member of the ddladmin fixed database role.
• File stream: RLS is not compatible with file stream.
• Memory optimization table: This option must be used to define inline table valued functions that serve as security predicates for memory optimization tables. Using this option will disable language features that are not supported by memory optimization tables and issue corresponding errors during creation.
• Index view: Typically, security policies can be created on top of the view, or views can be created on tables constrained by security policies. However, indexed views cannot be created on tables with security policies, as finding rows through indexes will bypass that policy.
• Change data capture: Change data capture may leak and should be filtered as db_ Owner member or specified when CDC is enabled for the table; Control; Users of role members (note: you can explicitly set this function to NULL to enable all users to access change data). Actually, db_ Owners and members of this control role can view all data changes on the table, even if there are security policies on the table.
• Change tracking: Change tracking may leak the primary key of the rows that should be filtered to both" Select” And&quo; View Change Tracking&# quo; Users with permissions. The actual data value will not be leaked; The fact that only column A has been updated/inserted/deleted with row B primary key. If the master key contains confidential elements (such as social security numbers), there may be problems. However, in practice, this CHANGETABLE is almost always connected to the original table to obtain the latest data.
• Full text search: Queries using the following full text search and semantic search functions are expected to reduce performance by introducing additional joins to apply row level security and avoid leaking the primary keys of rows that should be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasable, semanticsimilaritydetailable, semanticsimilaritytable, semanticsimilaritytable.
• Column storage index: RLS is compatible with clustered and non clustered column storage indexes. However, due to the application of row level security functions, the optimization program may modify the query plan so that it does not use batch mode.
• Partitioned view: Block predicates cannot be defined on partitioned views, nor can partitioned views be created on tables that use block predicates. Filter predicates are compatible with partitioned views.
• Tense table: Tense table is compatible with RLS. However, the security predicates on the current table will not be automatically copied to the history table. To apply security policies to the current table and history table, separate security predicates must be added to each table.

6 Example

The scheme for authenticating users to the database. Create three users and fill in a table with six rows. Then, it creates an inline table valued function and security policy for the table. Then, this example demonstrates how to filter select statements for various users.

(1) Create three user accounts that will demonstrate different access functions.

CREATEUSERManagerWITHOUTLOGIN; CREATEUSERSalesRep1WITHOUTLOGIN; CREATEUSERSalesRep2WITHOUTLOGIN; GO

(2) Create a table to save data.

CREATESCHEMASalesGOCREATETABLESales.Orders(OrderIDint,SalesRepnvarchar(50),Productnvarchar(50),Quantitysmallint);

(3) Fill the table with six rows of data, displaying three orders for each sales representative.

INSERTINTOSales.OrdersVALUES(1,'SalesRep1','Valve',5); INSERTINTOSales.OrdersVALUES(2,'SalesRep1','Wheel',2); INSERTINTOSales.OrdersVALUES(3,'SalesRep1','Valve',4); INSERTINTOSales.OrdersVALUES(4,'SalesRep2','Bracket',2); INSERTINTOSales.OrdersVALUES(5,'SalesRep2','Wheel',5); INSERTINTOSales.OrdersVALUES(6,'SalesRep2','Seat',5);-- Viewthe6rowsinthetableSELECT*FROMSales.Orders;

(4) Grant read access to the table to each user.

GRANTSELECTONSales.OrdersTOManager; GRANTSELECTONSales.OrdersTOSalesRep1; GRANTSELECTONSales.OrdersTOSalesRep2; GO

(5) Create a new schema and inline table valued functions. When the row in the column is the same as the user executing the query or when the user executing the query is a manager user, this function returns. This user-defined table-valued function example can be used as a filter for the security policy created in the next step.

CREATESCHEMASecurity; GOCREATEFUNCTIONSecurity.tvf_ securitypredicate(@SalesRepASnvarchar(50)) RETURNSTABLEWITHSCHEMABINDINGASRETURNSELECT1AStvf_securitypredicate_resultWHERE@SalesRep =USER_ NAME()ORUSER_ NAME()='Manager'; GO

(6) Create a security policy that adds functions as filter predicates. The state must be set to ON to enable policy.

CREATESECURITYPOLICYSalesFilterADDFILTERPREDICATESecurity.tvf_ securitypredicate(SalesRep)ONSales.OrdersWITH(STATE=ON); GO

(7) Allow SELECT permission on functions.

GRANTSELECTONSecurity.tvf_ securitypredicateTOManager; GRANTSELECTONSecurity.tvf_ securitypredicateTOSalesRep1; GRANTSELECTONSecurity.tvf_ securitypredicateTOSalesRep2;

(8) Change the security policy to disable it.

ALTERSECURITYPOLICYSalesFilterWITH(STATE=OFF);

(9) Connect to SQL database cleaning exercise resources.

DROPUSERSalesRep1; DROPUSERSalesRep2; DROPUSERManager; DROPSECURITYPOLICYSalesFilter; DROPTABLESales.Orders; DROPFUNCTIONSecurity.tvf_ securitypredicate; DROPSCHEMASecurity; DROPSCHEMASales;

That's all for this article on SQL Server row level security. For more related SQL Server row level security content, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!