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.
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.
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)
RETURN SELECT 1 AS accessResult
WHERE @ClientName = USER_NAME() OR USER_NAME() = 'Manager'
CREATE SECURITY POLICY dbo.customerAccessPolicy
ADD FILTER PREDICATE dbo.customerPredicate(ClientName) ON dbo.Customers,
ADD BLOCK PREDICATE dbo.customerPredicate(ClientName) ON dbo.Customers
— Now test the policy by impersonating Client001
EXECUTE AS USER = 'Client001'
— Only rows where ClientName = 'Client001' are returned (filter predicate)
SELECT * FROM dbo.Customers
— Error because the new ClientName <> 'Client001' (block predicate)
INSERT INTO dbo.Customers
(CustomerId, CustomerName, ClientName)
(1, 'New Customer', 'SalesRep99')
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.