Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux
In today’s article, we will examine SQL Server Row Level Security.
Security is of great importance in the IT world. It is vital when it comes to database security. Because the database contains all the data and information of the organization in which it is located.
Think about it, you are working in an e-commerce company and there is a lot of information in the database such as customer information, orders, payments.
Monitoring and ensuring the security of this information is one of the most important duties of the DBA – the Database Administrator. In this article, I will be talking about the security and encryption solutions that we have gained with SQL Server 2016 and that are included in the next versions.
Remember, you will also need the features here at some point on the KVKK side. If you want to apply the examples in the article yourself, you must have at least SQL Server 2017 installed on your system.
You can choose the Developer edition as the free version. SQL Server 2019 Developer edition is installed on my system.
What is SQL Server Row Level Security?
Row-level security is a long-requested feature in SQL Server, and Microsoft introduced it with SQL Server 2016. You can use this feature in SQL Server 2016 and all later versions (Express, Standard and Enterprise).
Row-Level Security (RLS) meets many business needs for many customers. So we can generate many scenarios about row-level security.
First, it is an important need when all your customers are in the same table and Salespeople can report according to this table without seeing the data from other customers.
To give a different example, consider a nurse inside a hospital. It may be necessary to ensure that this nurse can only see the data of her own patients and not the data on different nurses.
You are probably producing scenarios by thinking about how to apply this to your own applications as I give examples. That’s why row-level security – Row Level Security – allows you to configure tables.
Thus, users can only see the rows that have been granted access. Under normal circumstances, to perform this operation, you would have to apply a limitation at the application level with the where condition to the person performing the operation.
However, when performing Insert, Update and Delete operations, you need to produce different business processes to ensure that it only operates within its own limitations. That’s where Row Level Security – RLS – that comes with SQL Server 2016 is just right for you.
Footnote for SQL Server Row Level Security: This feature will provide you with session-based security.
Let’s explain how we use this feature about a few of the examples we mentioned above.
Example 1: You are a store employee and you want to see how many sales you make at the end of the day.
For this article, I will use the database named “DMC_SQLSecurity” that we created and give an example. Don’t forget to change it while you implement it on your side.
To use it in the example, I need to create 1 Sales Manager and 2 Sales Representatives.
|
1 2 3 4 5 6 7 8 9 |
Use DMC_SQLSecurity go CREATE USER Salesmanager WITHOUT LOGIN; CREATE USER SalesRepresentative1 WITHOUT LOGIN; CREATE USER SalesRepresentative2 WITHOUT LOGIN; |
I created the users I will need. Now I need a Sales table next. Let’s create it with the code below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Sales ( Id int, Seller sysname, product varchar(10), piece int ); |
After creating the sales table, let’s add a record with the following code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO Sales VALUES (1, 'SalesRepresentative1', 'Notebook', 5), (2, 'SalesRepresentative1', 'PC', 2), (3, 'SalesRepresentative1', 'Monitor', 4), (4, 'SalesRepresentative2', 'Phone', 2), (5, 'SalesRepresentative2', 'HDMI cable', 5), (6, 'SalesRepresentative2', 'Chair', 5); |
Let’s see the records are added.
|
1 |
SELECT * FROM Sales; |
Let’s give “Select” authorization to our users that we created so that they can see the data on the Sales table.
|
1 2 3 4 5 6 |
GRANT SELECT ON Sales TO SalesManager; GRANT SELECT ON Sales TO SalesRepresentative1; GRANT SELECT ON Sales TO SalesRepresentative2; GO |
When we made a query on the sales table, we saw that both of our sales representatives received the data.
We will now use the SQL Server Row Level Security – RLS – feature to ensure that sales reps can only see their sales. For this, we need an Inline Table Value Function that returns a True value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR ALTER FUNCTION dbo.fn_RLS (@Seller AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_RLS_Result WHERE @SalesMaker = USER_NAME() OR USER_NAME() = 'SalesManager'; GO |
After this process, we need to create a policy that will filter the Inline function we created.
|
1 2 3 4 5 6 7 |
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE dbo.fn_RLS(Salesmaker) ON dbo.Sales WITH (STATE = ON); |
If we examine the function and policy codes we have created; As we can see, our function takes username as parameter and returns True and NULL values.
In our POLICY section, we specify that our function called fn_RLS, which we created in the PRADICATE section, will be used to automatically filter when the query is run on our table and on which table it will perform the operation.
Now, after these definitions, let’s query our Sales table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- We are querying the sales table with SalesRepresentative1 EXECUTE AS USER = 'SalesRepresentative1'; SELECT *,USER_NAME() USerName FROM Sales; REVERT; -- We are querying the sales table with SalesRepresentative2 EXECUTE AS USER = 'SalesRepresentative2'; SELECT *,USER_NAME() USerName FROM Sales; REVERT; -- We are querying the Sales table with the Sales Manager EXECUTE AS USER = 'SalesManager'; SELECT *,USER_NAME() USerName FROM Sales; REVERT; |
In fact, if you want to query the Sales table with the user you connect and perform the transactions with, you will not see a record.
|
1 |
SELECT * FROM Sales; |
So is the Sales table empty? Of course, the result of the select query returns when a user suitable for the Seller information comes in.
If you immediately think of how to implement this feature in versions before SQL Server 2016, let’s explain the answer with the example below.
How to Implement Row Level Security before SQL Server 2016?
We have made an example about the use of Row Level Security above, now let’s explain how we can use it before SQL Server 2016 through the same example topic. Let our Sales Representatives and Sales Managers be the same.
Before we start working for the example, let’s drop the Satis table we created in the RLS example. Before we drop, we need to turn off the policy we created.
|
1 2 3 4 5 |
DROP SECURITY POLICY [dbo].[SalesFilter] GO Drop Table Sales |
We’ve done the drop, now we can start working again for our example. For a similar example, let’s create the Sales table and add sample records into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE TABLE SALES ( id int, Seller sysname, Product varchar(10), Quantity int ); INSERT INTO Sales VALUES (1, 'SalesRepresentative1', 'Server', 5), (2, 'SalesRepresentative1', 'MousePad', 2), (3, 'SalesRepresentative1', 'Mouse', 4), (4, 'SalesRepresentative2', 'Mask', 2), (5, 'SalesRepresentative2', 'Wallet', 5) |
Let’s query the sales table.
|
1 |
SELECT * FROM Sales; |
We need a user mapping table as we cannot use the RLS feature.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE SQLUserMapping (CustomerID VARCHAR(5), SQLUserName sysname, Active Bit CONSTRAINT PK_SQLUserMapping PRIMARY KEY (CustomerID, SQLUserName)); GO |
Let’s add a record to the mapping table.
|
1 2 3 4 5 |
INSERT INTO SQLUserMapping (CustomerID, SQLUserName, Active) VALUES('DMC01', 'SalesRepresentative1', 1) GO |
Now let’s create a view where we will match the Sales table and the Mapping table and report the sales.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR ALTER VIEW v_Sales AS SELECT Sales* FROM Sales JOIN SQLUserMapping on Satis.SalesMaker = SQLUserMapping.SQLUserName AND SQLUserMapping.SQLUserName = USER_NAME() GO |
We give our users that we defined before, to be able to select a view named v_satis.
|
1 2 3 4 5 |
GRANT SELECT ON v_Satis TO SalesManager; GRANT SELECT ON v_Satis TO SalesRepresentative1; GRANT SELECT ON v_Satis TO SalesRepresentative2; |
After defining the authorization, let’s query both the Satis table and the view named v_satis with the user SatisRepresentative1.
|
1 2 3 4 5 |
EXECUTE AS USER = 'SalesRepresentative1'; SELECT *,USER_NAME() USerName FROM sales; REVERT; |
As we can see in the picture above, our user does not have permission to see the Sales table. Now let’s query for v_satis.
|
1 2 3 4 5 |
EXECUTE AS USER = 'SalesRepresentative1'; SELECT *,USER_NAME() USerName FROM v_Sales; REVERT; |
Let’s query v_satis with our Sales Representative2 and SalesManagement users.
|
1 2 3 4 5 |
EXECUTE AS USER = 'SalesRepresentative2'; SELECT *,USER_NAME() USerName FROM v_sales; REVERT; |
|
1 2 3 4 5 |
EXECUTE AS USER = 'SalesManager'; SELECT *,USER_NAME() USerName FROM v_sales; REVERT; |
As can be seen in Picture-7 and Picture-8, the query result was empty, the reason is the mapping definition we made in the view. If you change the view for the Sales Manager, the sales representative1 can see the transactions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR ALTER VIEW v_sales AS SELECT Sales* FROM Sales JOIN SQLUserMapping on sales.SalesMaker = SQLUserMapping.SQLUserName AND SQLUserMapping.SQLUserName = USER_NAME() OR USER_NAME() = 'SalesManager' GO |
We are questioning again with the Sales Manager.
|
1 2 3 4 5 |
EXECUTE AS USER = 'SalesManager'; SELECT *,USER_NAME() USerName FROM v_sales; REVERT; |
