<

Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Understand What is Row Level Security in SQL Server 2016

Published on
4,534 Points
1,334 Views
2 Endorsements
Last Modified:
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Security is one of the main concern in the arena of database. In all the SQL Server, there are some new security features or improvement in the existing feature. The similar thing comes up with the SQL Server 2016. There are various security features such as Always Encrypted, Dynamic Data Masking. Now, one more is Row-level security, which is being added in SQL Server 2016. In the following write-up, we will discuss about RLS feature.

About Row-Level Security


Row level security comes up with a fine-grained access control over a specific row in a database table. While choosing the desired data from table, all the rows are filtered that based on execution of each query. If you have ever executed the similar logic in the application then, there might have been through some previews on table that are having filter condition or stored process. The logic can be easy and limited within the database by utilizing row-level security feature. The easiest way for understanding it is that there is a hidden filter in addition, which has been implemented before data is viewed to the users. The filter can be planned in such a manner that users can see all the rows that are there. 

Permissions

There are some security permissions that are required for the creation, dropping, or altering the security policies. Moreover, some permissions that are needed for each predicate on schema is Select and References. The security policies are applied for all users that include dbo users in the database. If the security policy is made with SCHEMABINDING= OFF then, for targeting the query table users must choose as well as execute the permission on the predicate function. 

Usage of RLS

There are various places where row level security is used just as in hospitals you can make security policy by permitting nurses to open and preview data rows for their patients only. Moreover, in banks, MNCs, etc. can make a policy to make a security policy.


RLS filter predicates are mainly equivalent to adding a WHERE clause. The predicate can be business practice dictate or even the clause can be simple. The predicate is utilized as a criterion for simply determining whether the user has the proper right to access to the data based on user attributes or not. Label-based access control can be executed by utilizing predicate-based access control. 

Working

Row-level security is a way to perform predicate-based access control; it simply works by automatically relating a security predicate on all table queries. It determines that which user can utilize the specific row. There are mainly two types of security predicates, i.e.

  • Filter predicate filter the operations, i.e. SELECT, UPDATE, and DELETE for excluding rows, which do not satisfy predicate.
  • Block predicate clearly block the operation INSERT, UPDATE and DELETE, which are unable to satisfy predicate.
 
For adding security predicate on a table, firstly you need an inline table that has valued function and defines access criteria. After that, a security policy is created, which simply adds filter as well as block predicates on any table that is desired by utilizing this function. It will be cleared with the mentioned 

REATE FUNCTION dbo.customerPredicate(@ClientName AS sysname)
  RETURNS TABLE
  WITH SCHEMABINDING
AS
  RETURN SELECT 1 AS accessResult
  WHERE @ClientName = USER_NAME() OR USER_NAME() = 'Manager'
go
CREATE SECURITY POLICY dbo.customerAccessPolicy
  ADD FILTER PREDICATE dbo.customerPredicate(ClientName) ON dbo.Customers,
  ADD BLOCK PREDICATE dbo.customerPredicate(ClientName) ON dbo.Customers
go

— Now test the policy by impersonating Client001
EXECUTE AS USER = 'Client001'
go

— Only rows where ClientName = 'Client001' are returned (filter predicate)
SELECT * FROM dbo.Customers
go

— Error because the new ClientName <> 'Client001' (block predicate)
INSERT INTO dbo.Customers
  (CustomerId, CustomerName, ClientName)
VALUES
  (1, 'New Customer', 'SalesRep99')
go
REVERT
go

Open in new window

 

Conclusion


Row Level Security helps to take care of the custom filtering in a manner of user defined table-valued function and executing it on the object everyone. Moreover, we can easily add more predicate in security policy. The above-discussion will help you to understand deep about it easily. If you still have some queries or issues related to this RLS then, feel free to leave a comment below.
2
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Join & Write a Comment

Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month