Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
What is Row Level Security in SQL Server?
Row-Level Security is a feature introduced in SQL Server 2016 that allows each user to see only their own data. So users can only see their own rows. As I have done in almost every article, I will continue with an example. I think this way is more understandable.
Let’s create a table named Patient with the following script and insert some sample records. The column to be considered here is the NurseUser column. In the following sections of the article we will implement row level security through this column.
Example
1 2 3 4 5 6 7 | Create table dbo.Patient ( PatientID INT, NurseUser Varchar(100), RoomNo INT, Disease varchar(4000) ) |
1 2 3 4 5 6 7 8 9 10 | INSERT INTO dbo.Patient VALUES(1,'Nurullah',105,'Infection') INSERT INTO dbo.Patient VALUES(2,'Nurullah',106,'Rheumatism') INSERT INTO dbo.Patient VALUES(3,'Nurullah',107,'arm fracture') INSERT INTO dbo.Patient VALUES(4,'Thomas',108,'Varis') INSERT INTO dbo.Patient VALUES(5,'Thomas',109,'arm fracture') INSERT INTO dbo.Patient VALUES(6,'Thomas',110,'Backache') INSERT INTO dbo.Patient VALUES(7,'John',111,'Flu') INSERT INTO dbo.Patient VALUES(8,'John',112,'Infection') INSERT INTO dbo.Patient VALUES(9,'John',113,'Rheumatism') INSERT INTO dbo.Patient VALUES(10,'John',114,'Flu') |
1 2 3 4 5 6 7 8 | Create Function dbo.fn_securityPredicate (@NurseUser sysname) returns table with Schemabinding as return select 1 as [fn_securityPredicate] from dbo.Patient where @NurseUser = user_name() |
1 2 3 4 | Create security Policy fn_security add Filter Predicate dbo.fn_securityPredicate(NurseUser) on dbo.Patient |
Conclusion
Create a login named Nurullah and grant Select permission in the Patient table. Then connect to the instance with “Nurullah” and select the Patient table. It will return a result as follows.
You may want to read the article “How To Create a Login in SQL Server(Manage Logins)” about creating and authorizing login.
So Nurullah can only see the room and patient information he is responsible for.
I really like it. Microsoft has increased security in its latest versions. Another feature I like about security is Always Encrypted. With this feature, data is encrypted at the application level and no one can see the data in the database. (Yes even database administrators)
You can find detailed information about Always Encrypted in the article named “What is Always Encrypted in SQL Server”
We need ALTER ANY SECURITY POLICY to create, alter, or drop Security Policies. To grant this permission to a user, right-click the database, click properties, and then go to the permission tab.
