Security has been always one the main concern by MS SQL Server. All the releases of SQL Server have some new security feature or enhancement of an existing feature. Similarly, the latest edition of SQL Server 2016 has many security features such as Always Encrypted, enhancement of Transparent Data Encryption, Dynamic Data Masking, and Row-Level Security are added.
SQL Server 2016 row level security offers fine-grained access control over particular row in table in the database. It simplifies the design and coding of data security in the application. It enables users to implement restrictions on data row access. The simplified way to understand is that an addition-hidden filter has been applied before showing the data to the user. This filter is designed in a way that users can see all the rows. In the following section, we will discuss the way to implement it, its usage, and exceptions.
Prerequisites Before Implementing Row Level Security SQL Server 2016
Before the SQL Server 2016 Row Level Security implementation, user requires some permission as mentioned below:
Users require Alter Any Security Policy permission before creating, altering, or dropping the security policies. For creating or dropping the security policy Alter permission is required. Along with this, the following permissions are required for each predicate, which is added:
- Select and Reference permission are required to use as predicate
- References permission on the target table bound to some policy.
- References permission on each column from target table is used as arguments.
Security Policies are applied for all users, including DBO users in the database. DBO users can drop or alter the security policies though their changes to the security policies can be audited. If some high privileged users want to see all the rows to troubleshoot the data, then the security policies should be written to allow them.
If any of the security policy is created with SCHEMABINDING=OFF, then to query the target table. Users must have Select or Execute permission on the predicate function and If the security policy is made with SCHEMABINDING = ON (by default), then all these permissions are passed when users query the target table.
How to Implement SQL Server 2016 Row-Level Security?
Users can implement the row-level security in SQL Server 2016 by following the steps given below.
- First, create a test table and add some sample values on it as mentioned:
- Now create the following prediction function according to the requirements
- User can define the security policy, which will use the predicate function as created above.
Create table dbo.Orders
(
Supplier_Code int,
[Supplier_code] varchar(10),
[OrderQuantity] int,
[ProcessedBy] Varchar(10)
)
Create Function fn_securitypredicateOrder (@processedby sysname)
returns table
with Schemabinding
as
return select 1 as [fn_securityPredicateOrder_result]
from
dbo.orders
where @processedby = user_name()
Create security Policy fn_security
Add Filter Predicate
Fn_securitypredicateOrder (processedby)
On dbo.orders
Now the security policy is added, which permits users to access the particular row that is accessed in the processedby column of table.
Usage of SQL Server Row- level Security
There are some examples, which help in understanding the use of RLS.
- Security policy can be created in hospitals so that the nurses are allowed to view the row of data for their patients only.
- A bank can create restrict access of data row of financial policy on the business division of employees.
- Multi-tenant application can make a policy that enforces the policy of logical separation of each tenant’s data rows from every other tenant’s rows.
In the following section, we will discuss the few exceptions of RLS with other features of SQL Server.
Compatibility with Cross-Features
There are some few exceptions with RLS in SQL Server, which are mentioned below. It will help to understand the usage of RLS with certain other features.
- Show_Statistics in DBCC
- Indexed View
- Full-text Search
- Columnstore Indexes
- Temporal Tables
It helps to report the statistics on unfiltered data and can leak the information that is protected by security policy. Due to this reason, in a way to view statistics object for a table with RLS, user must own the table with RLS or be a member of sysadmin fixed server role.
The security policies can be created on the top of views that are bounded by security policies. However, indexed views cannot be created at top of the tables, which have security policy.
A performance hit is expected for the queries by utilizing the Full-text search and semantic search functions due to extra join that is introduced to apply Row Level Security and avoid leaking the primary keys of rows.
RLS is well suited with both cluster and non-cluster columnstore indexes. However, it applies a function due to which it is possible for an optimizer to modify the query plan.
These tables are compatible with RLS as on the current table security predicates are not automatically replicated to history table. User must add security predicate individually on each table to apply security policy.
Conclusion
In the above discussion, Row Level Security feature of SQL Server is described. Along with this, a way to implement SQL Server 2016 Row Level Security and its usage is discussed. Its compatibility with other features is also described, which helps users in more understanding about RLS in SQL Server 2016.
DB Cloud TECH 