SQL Server Row Level Security (RLS) is one of the top new features shipped in SQL Server 2016. With RLS, you can control access to rows in a table based on the characteristics of the user executing a query.
The access restriction logic is located in the database tier and access restrictions are always applied, thus they cannot be skipped.
SQL Server Row Level Security by Example
Below I will showcase SQL Server Row-Level Security with the use of a simple scenario. This example features a fictitious school’s database and more specifically a table containing the school’s student records. Each student is assigned an advisor. By using Row-Level Security, it is possible for each advisor to see only the records of his students. In the same scenario, only the school principal can view all student records.
-- -- Row Level Security Example -- USE master; GO --Create sample database CREATE DATABASE RLSDemoDB; GO --Use the sample database USE RLSDemoDB; GO --Create four database users CREATE USER Advisor1 WITHOUT LOGIN; CREATE USER Advisor2 WITHOUT LOGIN; CREATE USER Advisor3 WITHOUT LOGIN; CREATE USER Principal WITHOUT LOGIN; --Create the students table CREATE TABLE tblStudents ( code VARCHAR(10) NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL, advisorCode sysname, classID INT, GPA FLOAT ); --Insert sample data INSERT tblStudents VALUES ( 'ST001', 'Student1','Advisor1', 1,3.2), ( 'ST002', 'Student2','Advisor1', 3,3.3), ( 'ST003', 'Student3','Advisor1', 5,2.8), ( 'ST004', 'Student4','Advisor2', 2,3.9), ( 'ST005', 'Student5','Advisor2', 4,3.1), ( 'ST006', 'Student6','Advisor2', 5,1.8), ( 'ST007', 'Student7','Advisor3', 6,2.2), ( 'ST008', 'Student8','Advisor3', 4,3.7), ( 'ST009', 'Student9','Advisor3', 3,3.6), ( 'ST010', 'Student10','Advisor4',2,3.5) GO
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Let’s check the table contents before enabling the security policy:
Now let’s grant SELECT access to all users which are the three advisors and the principal:
--Grant SELECT access to users GRANT SELECT ON tblStudents TO Advisor1; GRANT SELECT ON tblStudents TO Advisor2; GRANT SELECT ON tblStudents TO Advisor3; GRANT SELECT ON tblStudents TO Principal; GO
Now it’s time to create the Security policy. First we have to create the schema:
--Create security schema CREATE SCHEMA Security; GO
Then, we need to create the function that will be used for checking the executing user:
--Create function that checks the executing user CREATE FUNCTION Security.fn_securitypredicate(@advisorCode AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @advisorCode= USER_NAME() OR USER_NAME() = 'Principal'; GO
Finally we create and enable the security policy which uses the schema and the function created above:
--Create security policy CREATE SECURITY POLICY StudentFilter ADD FILTER PREDICATE Security.fn_securitypredicate(advisorCode) ON dbo.tblStudents WITH (STATE = ON); GO
Now let’s retrieve the records from the the tblStudents table, each time within the context of a different user (i.e. advisor1, advisor2, advisor3 and principal).
If we execute the above query as the user ‘Advisor1‘ we can only see the student records that have ‘Advisor1’ as their advisor:
If we execute the above query as the user ‘Advisor2‘ we can only see the student records that have ‘Advisor2’ as their advisor:
If we execute the above query as the user ‘Advisor3‘ we can only see the student records that have ‘Advisor3’ as their advisor:
If we execute the above query as the user ‘Principal‘ we can see all student records:
If we disable the policy, then we return to the original state where all the records are retrieved from the tblStudents table within the context of any user that was granted the SELECT permission on the table:
From the above example you can see that it’s quite easy to control access to your data at the most granular level in the database, that is the record level, by using Row-Level Security in SQL Server 2016 or later.
Secure your SQL Server Instances with DBA Security Advisor
DBA Security Advisor, is our SQL Server security tool, which can help you assess your SQL Server instances against a rich set of security checks. The assessment report, includes recommendations and remediation scripts that can help you better secure your SQL Server instances and databases (learn more…).Try DBA Security Advisor free for 30 days!
Featured Online Courses
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
Other SQL Server Security-Related Articles
- Why You Need to Secure Your SQL Server Instances
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- Frequent Password Expiration: Time to Revise it?
- Policy-Based Management in SQL Server
- The “Public” Database Role in SQL Server
- Encrypting SQL Server Databases
- Transparent Data Encryption (TDE) in SQL Server
- Encrypting a SQL Server Database Backup
- …check all
Check our latest software releases!
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.