Row Level Security in SQL Server

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

Then we need to create a function like the following. We will identify the person running the query with the where block of this function.
Finally, we complete the process by creating a security policy such as the following that will work with the function described above.

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *