ROW LEVEL Security SQL SERVER 2016

To understand RLS (ROW LEVEL SECURITY) let’s understand the different problems first.

Problem 1 Suppose, you have a Multi-tenant e-commerce website and different companies registered on your website and you have centralized single database for all the client. Now as a product owner it is your responsibility that one tenant’s data should not be available to another tenant.  This is a very common problem.

2. Now, Suppose you have hospital database in which you have login user of different doctors & nurses. Now, your challenge is to show data to doctor or nurses to their relevant patient to whom they are giving treatment, not any other patient data should be available .

Here, limiting the user’s access to only certain rows of the data in database many have various reasons like compliance standards, regulatory need or security reasons.

Now, I know you were thinking that all the above problem can be resolved at code side easily by writing custom logic. I will say here yes you are right but this is not the 100% solution.  For example, if you have 4 different application like web, mobile, console, windows (Excel) and all has their own DAL then you have to implement this custom logic to every application and suppose  tomorrow if any time a new 3rd party came which want to integrate your data  or access database directly then in such cases it is tuff to apply same logic.

So, all the above problem can be easily handle using SQL SERVER 2016’s feature which is ROW Level Security (RLS). Security is one of the key areas which is handled in SQL SERVER 2016 very seriously.  As RLS (Row Level Security) is centralized security logic so you don’t need to repeat same security logic again and again.

As the name suggested Security implemented at Row Level in SQL SERVER 2016. In the Row Level, Security data is access according to user roles. It is a centralized data access Logic.

RLS has following properties

  • Fine-grained access role ( control both read & write  access to specific rows)
  • Application transparency  ( No application changes required)
  • Centralized the access within the database
  • Easy to implement & maintain

How RLS works?

RLS   is a predicate based function which runs seamlessly every time when a SQL is run on particular table on which RLS  predicate function implemented.

There are 2 predicates  which can be implemented in RLS

1) Filter Predicate: – By the name, it is clear that it will filter the row or we can say exclude the rows which do not satisfy the predicate and stop further option like select, Update & Delete.

for example: Suppose, you want to restrict doctor to see other doctor’s patient data then in such case you can apply filter predicate.

2) Block Predicate: –  This predicate helps in implementing policy by which insert, update and delete rows will prevent which violate the filter predicate. In other words, we can say it explicitly block write operation.

For example, you have multi-tenant application and you want to restrict one tenant user to insert or update other tenant’s data. Or suppose you have sales representative who belongs to specific region so they can not insert , update or delete other region’s data.

Demo:-

I know you will be super excited to see the demo of this feature so. Let’s do it right away.

There are 2 basic steps to create RLS

a) Create inline table function  or we can say predicate function  and write custom logic to control user access to every row

b) create the security policy and apply it.

In this demo ,I am creating a  new table called Patients which has following schema.

RLS_Demo_Indiandotnet_1

Here, I have inserted 2 rows for Nurse1 & 2 rows for Nurse2

RLS_Demo_Indiandotnet_2

The objective is to show only those rows to Nurse1, Nurse2 in which they are the in charge and a doctor user can see entire table’s data.

To achieve this let first create 3 users  in database

RLS_Demo_Indiandotnet_3

Once the users are created the next step is to grant permission of select to Nurse1 & Nurse2 user and full permission to doctor user.

RLS_Demo_Indiandotnet_4

Now, before creating function it is a standard to create a security schema in our case we are creating a schema with name sec as shown in below figure.

Now, create a function which will have security logic. The Logic is very simple if the user is doctor Or any in charge name then return 1 else 0.

RLS_Demo_Indiandotnet_5

Now create a security policy to proceed further

RLS_Demo_Indiandotnet_6

Till now we are good to go. Now, let’s test the security policy.

Firstly, running the select query with default user “dbo.”  and we have not given permission for this user if you see fn_RLSPredicate we have not mentioned it so obviously the result would show “0” records.

RLS_Demo_Indiandotnet_7

Now, running the same select statement but executing with “Nurse1” login then you will find 2 records which are relevant to Nurse1 is visible.

RLS_Demo_Indiandotnet_8

Similarly, I am running the same statement for Nurse2 user by running command “Execute as user” so, again I will get 2 records

RLS_Demo_Indiandotnet_9

Now, running the same statement with Doctor user and as per our expectation, it should show all 4 records.

RLS_Demo_Indiandotnet_10

So, as you can see we have achieved the goal using RLS (Row Level Security) feature. Now, next thing which might occur in your mind how to disable this policy if required then doesn’t worry it is very simple. Just alter the security policy and make state = off as shown in below figure.

RLS_Demo_Indiandotnet_11

I hope till now we are good to work on RLS. In next couple of post, we will dig deeper in RLS.

Please, share your thought for RLS.

Isn’t it easy to mask your data with Dynamic data Masking #5

Data security is always one of the important points which can not be ignored. Nowadays if you are working for any specific domain like Banking or Healthcare then there are a lot of compliance rules which you have to follow.

Data Masking is one of the best ways to help you to secure your sensitive data by a dynamic mask encryption.

This is one of the best features of SQL SERVER 2016 which I personally like most.

With the help of Dynamic Data Masking, you are just applying a mask to your sensitive data.  for example, if your system is storing SSN data then it should be visible to privileged or we can say authorized user only.

Dynamic Data Masking has following features:-

1) It masked the Sensitive data.

2) There will be no impact on functions & Stored Procedures and other SQL statement after applying this.

3) Applying the Data Masking is super easy.

4) You can allow any database user/role to see unmasked data by just simple Grant & Revoke Statement .

5) Data is not physically changed.

6) It is just on the fly obfuscation of data query result .

7) It is just  a T-SQL command with basic syntax.

Now , let us understand how to implement it.

Data masking implementation is very easy and below is the syntax for it.

Masksyntax1

Here, if you see the syntax is very simple the only new thing is MASKED and with (function=function name) only.

The function is nothing but the way to mask the data. SQL SERVER 2016 has following  different functions to mask the data

1) Default() function:- This is basic masking with the help of this function you can easily mask any field.

for example, your first name or last name field can be masked like XXXX etc.

2) Email() function :- If your column is email type or you we can say if you store Email in your column then you should use the Email() function for masking.

for example, your email can be mask like  RXXXX@XXXX.com

3) Partial () function:- With the help of this function you can mask specific data length and exclude some part of data from masking logic. for example, 123-4567-789 is your phone number then with partial masking feature you can mask like 12X-XXXX-7XX.

4) Random() function – By the name it is clear that you can mask the data with any random number range we will see more below in the hands on.

Remove Masking :- This is also possible that you applied a masking to a column and later on you don’t want that masking. So , don’t worry it very easy to remove masking from a column. below is the syntax for same.

DropMask

Now, let’s understand this by an example.

In the example we are using a new database “SecureDataMask” in this database we are creating a tblSecureEmployee as shown in below figure.

Create_Table_Secure_Employee

Now, in this table, we are inserting couple of data for testing as shown below

Indiandotnet_Insert_Default_Row

Now we are applying different masking on this table’s column

1) Default Masking : In the table, we are applying default masking on LastName

Indiandotnet_Default_Masking

2) Email Masking :- In the table, we are going to apply Email masking to email column below is the syntax for it.

Indiandotnet_Apply_Email_Masking

3) Partial Masking:- For SSN we are going to apply custom masking. below is the syntax for same. Here as we aware that SSN is 11 characters long in our database. we applied the partial masking to show first two & last two characters in original value and rest other in the mask.

Indiandotnet_Partial_Masking_SQL_SERVER_2016

4) Random Number Masking :-  In our table, we are going to apply Random number masking to Securepin column as shown below.

Indiandotnet_Random_Number_Masking_SQL_SERVER_2016

Here, so far we are done with all the masking now.  let me run the select statement to test it.

Indiandotnet_Select_statement

If you see the data is still in the original state because I logged in using  privilege account “SA”. now, to test the masking let me create a new user account.

Indiandotnet_Create_Login_User_SQL_SERVER_

After creating the account we are trying to log-in with a new account as shown in below screen.

Indiandotnet_Login_With_New_User

After our successful log in, we will run the select statement on same database’s table as we did earlier. If you see below snap you will find that we got masked data for LastName, Email, SSN, and securePin.

Indiandotnet_Masked_Data_With_Less_Preivileged_account

Now, it might be a rare case but suppose you want to remove the mask from any column on which you applied masking then don’t worry it is super easy.

Suppose, from the same table we don’t want mask on the LastName then below is the syntax for same.

Indiandotnet_Removing_Mask_from_column_Sqlserver2016

Now, let me run the same select statement seeMask_user. You will find the Last Name is unmasked now.

Indiandotnet_Last_Name_Visible_

From above few changes you can secure your data via Dynamic masking and as mentioned above there will be no impact on your existing function ,stored procedure because data is not physically changed.

I hope you may like this feature.   Please, share your input for same.

Enjoy !!

RJ