Row Level Security Deep Dive In SQL Server

Row Level Security Deep Dive In SQL Server

Row level security (RLS) provides us with a security tool that can limit data access on the row level of data. This security technique serves value in situations where we may require delineating access to data where a “whole” is stored, but only a “part” needs to be accessed by users — such as a product list from all companies where a company can only see their products.

While this post will do an example in SQL Server, row level security can be used in SQL Server 2016 and above, Azure SQL, Azure SQL Managed Instances, Azure Synapse and Warehouse in Fabric.

Questions

1. You’re in charge of data security at your company and have been told that you must implement row level security for your database. The requirements are that users should only be able to view their data and update their data. What predicates will you use with row level security?
2. True or false: you’ve assigned the user John Doe read access to a table called SummaryTable, but you have not assigned John Doe read access to a function that uses a filter predicate on SummaryTable to enforce row level security. Without having the minimum read access to this filter predicate function, John Doe will still be able to read his data from the SummaryTable.
3. You have the same challenge in question 1, except there’s an additional requirement where you have to ensure that users cannot violate any predicate with values that are returned with row level security. If applicable, how would your answer change?
4. We create row level security for our table we call OurTableOne that uses a filter predicate that only returns data based on the logged in user for OurTableOne. We have another table OurTableTwo, which has a foreign key reference to the logged in user from OurTableOne, but it has not filter predicate assigned to this table. All our users have read access to these two tables. Are the data in OurTableOne secure by preventing logged in users from accessing other logged in users’ data?
5. Think like a hacker for a minute. What is one way that you could go about compromising a company that uses row level security?

Terms

• Row level data security (also called row level security or RLS): complex data security technique that acts like a row filter on data (ie: WHERE Country = ‘Serbia’).
• Transparent data encryption (TDE): data security technique that encrypts data at rest through securing the data and log files on disk.
• Data masking: data security technique that hides sensitive data through a variety of techniques, such as randomizing the actual data values, obfuscating a fraction of the data values, or other techniques.
• Always encrypted: a data security technique that occurs on a level higher than the data (application layer), as the database will not have the encryption keys. This means that if the database is compromised, it’s meaningless because even a high level database administrator wouldn’t have access to the data.
• Predicate: an expression that is applied to a table that restricts the rows visible to users.
• Filter predicate: an expression that filters rows from a data set in operations that involve reading data (only INSERT would be excluded here, as the remaining CRUD operations must read data).
• Block predicate: an expression that blocks write operations, which falls into one of 4 categories — AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE. Since the wrong block predicate may cause issues for us in terms of securing our data, let’s look at each of these in more detail:
• AFTER INSERT: the predicate filter is applied to the data rows after the INSERT finishes, where the user will see the appropriate data rows even if the INSERT spanned more records. Example in practice: a sales person adds sales data for 3 sales people, but following the insert only see his records.
• BEFORE DELETE: this prevents delete operations.
• BEFORE UPDATE: prevents users from updating rows outside the predicate. In our below example, we’ll notice that our student can only update his rows because these return through the predicate, so if he attempts to update others’ rows, he can’t. However, he can update his rows to someone else.
• AFTER UPDATE: prevents users from updating rows outside the predicate and in violation of the predicate. In our below example, we’ll notice that our student can only update his rows because these return through the predicate, so if he attempts to update others’ rows, he can’t. We’ll also notice that unlike BEFORE UPDATE, our student can’t change the data row that returned from the predicate to someone else’s.

When Would We Use Row Level Security?

While RLS isn’t the only tool that will help us in these situations, it may be a tool that we find useful in the below situations that we could use it. We might consider RLS in the following circumstances:

• Complying with strict security requirements from our company or legal requirements where access to sensitive data must be filtered by user or role within the company.
• When the data we store on the row level relate directly to the responsibilities of the user or role who views the data. If the data relate to the user or role on a column level, we would consider dynamic data masking.

A visual way to think about RLS is a pie chart where each piece of the pie represents a user and each user should only be able to view their piece of the entire pie. Like with dynamic data masking as we’ll see, there are some security concerns with RLS, which is one reason why it’s not high on my recommendation list of security tools.

T-SQL Examples:

First, let’s look at a simple example that involves only returning data. In our simple example, we create a table with rankings along with various user labels that involve a director, instructor and students. The user label column will be our filter predicate, as we’ll want our director to see all rows, but the student to only see their own row. When we execute the code, we get that exact result — the instructor can see everything, but the student is only able to see his row (the updated date result is intentionally removed and is blank).

Some important notes about this simple code before you execute it:

• You must remove objects in the correct order. As we see, we drop the security policy if it exists because we cannot remove a table or function that is referenced by a security policy once it’s been created. Order matters.
• We’re filtering on the UserLabel column, so we create our function accordingly. In our `OR` statement, we specify the Director. This means that each user will see their own result, or the director will see the result too (along with all the results).
• Notice what happens if we try to remove the GRANT SELECT ON UserRankings TO StJames, then watch what happens when you execute the entire code — “The SELECT permission was denied on the object ‘UserRankings’, database ‘OurDatabase’, schema ‘dbo’”. StJames doesn’t have access, even though he has select permissions on the function. But he still needs access to the table, otherwise it won’t matter if he has access to the function. We can also play with this a bit more, let’s give him the db_datareader role and see what happens (replace GRANT SELECT ON UserRankings TO StJames with ALTER ROLE db_datareader ADD MEMBER StJames). He can see his own data again. What we see here is that we can create the function and security policy, but if the user has no access to the object, it won’t matter.

Simple example:

---- NOTE: order matters here because you cannot drop a table or function that is referenced by a security policy
IF OBJECT_ID('RankFilter') IS NOT NULL
BEGIN
 DROP SECURITY POLICY RankFilter
END

IF OBJECT_ID('UserRankings') IS NOT NULL
BEGIN
 DROP TABLE UserRankings
END

CREATE TABLE UserRankings (
 UserId INT IDENTITY(1,1),
 UserLabel VARCHAR(25),
 Person VARCHAR(25),
 Ranking TINYINT,
 UpdatedDate DATETIME DEFAULT GETDATE()
)

INSERT INTO UserRankings (UserLabel,Person,Ranking)
VALUES ('Director','Jane',7) ---director
 , ('Instructor','John',5) ---instructor
 , ('StJames','James',3) ---student
 , ('StSarah','Sarah',1) ---student
 , ('StAjay','Ajay',2) ---student
 , ('StHannah','Hannah',2) ---student
 , ('StMatthew','Matthew',2) ---student


IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'Director')
BEGIN
 DROP USER Director
END

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'StJames')
BEGIN
 DROP USER StJames
END

CREATE USER Director WITHOUT LOGIN
GRANT SELECT ON UserRankings TO Director
CREATE USER StJames WITHOUT LOGIN
GRANT SELECT ON UserRankings TO StJames


IF OBJECT_ID('tvfFilter') IS NOT NULL
BEGIN
 DROP FUNCTION tvfFilter
END

---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @secsql NVARCHAR(MAX)
SET @secsql = N'
CREATE FUNCTION tvfFilter (@userlabel AS VARCHAR(25))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS ReturnValue WHERE @userlabel = USER_NAME() OR USER_NAME() = ''Director''
'
----PRINT(@secsql) --check
EXEC sp_executesql @secsql


---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @spolsql NVARCHAR(MAX)
SET @spolsql = N'
CREATE SECURITY POLICY RankFilter
ADD FILTER PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings
WITH (STATE = ON)
'
EXEC sp_executesql @spolsql


GRANT SELECT ON tvfFilter TO Director ---note point below this
GRANT SELECT ON tvfFilter TO StJames ---note point below this


EXECUTE AS USER = 'Director'
SELECT * FROM UserRankings
REVERT

EXECUTE AS USER = 'StJames'
SELECT * FROM UserRankings
REVERT


DROP SECURITY POLICY RankFilter
DROP TABLE UserRankings
DROP FUNCTION tvfFilter

One extremely important point to make here is that the GRANT SELECT ON tvfFilter to the users are not required in order for these user to see their data. You can comment these lines out and you’ll still see results. If you were to experience the below error, it means you should investigate the read access of the user (however read access is assigned), but granting permission on the function won’t resolve the below error.

Msg 229, Level 14, State 5, Line N
The SELECT permission was denied on the object ‘UserRankings’, database ‘OurDatabase’, schema ‘dbo’.

For an example, if you were to GRANT SELECT ON tvfFilter TO StJames, but remove the line GRANT SELECT ON UserRankings TO StJames, you’ll get the above error. Users must have at minimum read permission on the table itself (or a role that has the table listed as having permissions).

Example: BEFORE UPDATE

In the below code snippet, we look at an example of using BEFORE UPDATE in our blocking predicate. The only difference with this example and the prior example, is that we’ve added a student (Sarah) and we’re now using a blocking predicate in addition to our filter predicate. Our blocking predicate specifies BEFORE UPDATE — which means that values can be changed provided that they meet the filter and blocking predicate, in this case UserLabel.

IF OBJECT_ID('RankFilter') IS NOT NULL
BEGIN
 DROP SECURITY POLICY RankFilter
END

IF OBJECT_ID('UserRankings') IS NOT NULL
BEGIN
 DROP TABLE UserRankings
END

CREATE TABLE UserRankings (
 UserId INT IDENTITY(1,1),
 UserLabel VARCHAR(25),
 Person VARCHAR(25),
 Ranking TINYINT,
 UpdatedDate DATETIME DEFAULT GETDATE()
)

INSERT INTO UserRankings (UserLabel,Person,Ranking)
VALUES ('Director','Jane',7) ---director
 , ('Instructor','John',6) ---instructor
 , ('StJames','James',2) ---student
 , ('StSarah','Sarah',1) ---student
 , ('StAjay','Ajay',2) ---student
 , ('StHannah','Hannah',2) ---student
 , ('StMatthew','Matthew',1) ---student


IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'Director')
BEGIN
 DROP USER Director
END

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'StJames')
BEGIN
 DROP USER StJames
END

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'StSarah')
BEGIN
 DROP USER StSarah
END

CREATE USER Director WITHOUT LOGIN
GRANT SELECT ON UserRankings TO Director
GRANT INSERT ON UserRankings TO Director
GRANT UPDATE ON UserRankings TO Director

CREATE USER StJames WITHOUT LOGIN
GRANT SELECT ON UserRankings TO StJames
GRANT UPDATE ON UserRankings TO StJames

CREATE USER StSarah WITHOUT LOGIN
GRANT SELECT ON UserRankings TO StSarah
GRANT UPDATE ON UserRankings TO StSarah


IF OBJECT_ID('tvfFilter') IS NOT NULL
BEGIN
 DROP FUNCTION tvfFilter
END

---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @secsql NVARCHAR(MAX)
SET @secsql = N'
CREATE FUNCTION tvfFilter (@userlabel AS VARCHAR(25))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS ReturnValue WHERE @userlabel = USER_NAME() OR USER_NAME() = ''Director''
'
EXEC sp_executesql @secsql


---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @spolsql NVARCHAR(MAX)
SET @spolsql = N'
CREATE SECURITY POLICY RankFilter
ADD FILTER PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings,
ADD BLOCK PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings BEFORE UPDATE
WITH (STATE = ON)
'
EXEC sp_executesql @spolsql


GRANT SELECT ON tvfFilter TO Director
GRANT SELECT ON tvfFilter TO StJames
GRANT SELECT ON tvfFilter TO StSarah



BEGIN TRAN
EXECUTE AS USER = 'StJames'

UPDATE UserRankings
SET UserLabel = 'StJames'

SELECT * FROM UserRankings

UPDATE UserRankings
SET UserLabel = 'StSarah'

SELECT * FROM UserRankings
REVERT
COMMIT TRAN

BEGIN TRAN
EXECUTE AS USER = 'StSarah'
SELECT * FROM UserRankings
REVERT
COMMIT TRAN

BEGIN TRAN
EXECUTE AS USER = 'Director'
SELECT * FROM UserRankings
REVERT
COMMIT TRAN




DROP SECURITY POLICY RankFilter
DROP TABLE UserRankings
DROP FUNCTION tvfFilter

As we see, James can’t change every student to himself on his first attempted update. This is because BEFORE UPDATE allows him to change his data rows, as these are allowed by the filter and blocking predicate. However, we’ll notice that James is able to update his row to Sarah’s name and now Sarah has two rows. We can see the possible issue here if we’re using row level security in a context where there could be coordination among users or in cases where the above could be abused. Let’s look at how this compares with AFTER UPDATE.

Example: AFTER UPDATE

In the below code, we’ve only made 1 change — BEFORE UPDATE was replaced with AFTER UPDATE in our security policy’s blocking predicate.

IF OBJECT_ID('RankFilter') IS NOT NULL
BEGIN
 DROP SECURITY POLICY RankFilter
END

IF OBJECT_ID('UserRankings') IS NOT NULL
BEGIN
 DROP TABLE UserRankings
END

CREATE TABLE UserRankings (
 UserId INT IDENTITY(1,1),
 UserLabel VARCHAR(25),
 Person VARCHAR(25),
 Ranking TINYINT,
 UpdatedDate DATETIME DEFAULT GETDATE()
)

INSERT INTO UserRankings (UserLabel,Person,Ranking)
VALUES ('Director','Jane',7) ---director
 , ('Instructor','John',6) ---instructor
 , ('StJames','James',2) ---student
 , ('StSarah','Sarah',1) ---student
 , ('StAjay','Ajay',2) ---student
 , ('StHannah','Hannah',2) ---student
 , ('StMatthew','Matthew',1) ---student


IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'Director')
BEGIN
 DROP USER Director
END

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'StJames')
BEGIN
 DROP USER StJames
END

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'StSarah')
BEGIN
 DROP USER StSarah
END

CREATE USER Director WITHOUT LOGIN
GRANT SELECT ON UserRankings TO Director
GRANT INSERT ON UserRankings TO Director
GRANT UPDATE ON UserRankings TO Director

CREATE USER StJames WITHOUT LOGIN
GRANT SELECT ON UserRankings TO StJames
GRANT UPDATE ON UserRankings TO StJames

CREATE USER StSarah WITHOUT LOGIN
GRANT SELECT ON UserRankings TO StSarah
GRANT UPDATE ON UserRankings TO StSarah


IF OBJECT_ID('tvfFilter') IS NOT NULL
BEGIN
 DROP FUNCTION tvfFilter
END

---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @secsql NVARCHAR(MAX)
SET @secsql = N'
CREATE FUNCTION tvfFilter (@userlabel AS VARCHAR(25))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS ReturnValue WHERE @userlabel = USER_NAME() OR USER_NAME() = ''Director''
'
EXEC sp_executesql @secsql


---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @spolsql NVARCHAR(MAX)
SET @spolsql = N'
CREATE SECURITY POLICY RankFilter
ADD FILTER PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings,
ADD BLOCK PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings BEFORE UPDATE
WITH (STATE = ON)
'
EXEC sp_executesql @spolsql


GRANT SELECT ON tvfFilter TO Director
GRANT SELECT ON tvfFilter TO StJames
GRANT SELECT ON tvfFilter TO StSarah



BEGIN TRAN
EXECUTE AS USER = 'StJames'

UPDATE UserRankings
SET UserLabel = 'StJames'

SELECT * FROM UserRankings

UPDATE UserRankings
SET UserLabel = 'StSarah'

SELECT * FROM UserRankings
REVERT
COMMIT TRAN

BEGIN TRAN
EXECUTE AS USER = 'StSarah'
SELECT * FROM UserRankings
REVERT
COMMIT TRAN

BEGIN TRAN
EXECUTE AS USER = 'Director'
SELECT * FROM UserRankings
REVERT
COMMIT TRAN




DROP SECURITY POLICY RankFilter
DROP TABLE UserRankings
DROP FUNCTION tvfFilter

Like our first example, James attempt to update all his students to himself fails. He then tries the same update where he sets his data row to Sarah. However, this fails with the error:

Msg 33504, Level 16, State 1 Line N
The attempted operation failed because the target object ‘OurDatabase.dbo.UserRankings’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

In other words, the specification of AFTER UPDATE prevented this change. This highlights the differences between BEFORE and AFTER UPDATE and where we might want to use either. In the case of BEFORE, users can update any rows that return provided that there are no limitations of the data returned. For instance, if a data row returned that was a foreign key reference and a user tried to update this value to an invalid value, he’d still get an error even if the filter and blocking predicate returned this value. However, outside of other restrictions users can update the data returned to any value. By contrast, this is not the same with AFTER UPDATE. A user cannot update data that conflict with the blocking and filter predicate, such as changing their return values based on the predicate to another predicate’s set of values (in our example, we can’t change the user in UserLabel). We can change data that’s returned provided that it does not alter any value that is returned by the filter or blocking predicate.

I use both of these examples here because depending on our use case for RLS, we could see big issues if we used BEFORE versus AFTER.

Example: AFTER INSERT

In our next example, we’ll look at a scenario where James will attempt to add 2 records — another student and a director with his name. He’ll then try to add another student record with him. Let’s look at what happens when we use AFTER INSERT and what occurs when James tries to add new records. As a quick note, the test user will need insert permissions, as we see this is added.

IF OBJECT_ID('RankFilter') IS NOT NULL
BEGIN
 DROP SECURITY POLICY RankFilter
END

IF OBJECT_ID('UserRankings') IS NOT NULL
BEGIN
 DROP TABLE UserRankings
END

CREATE TABLE UserRankings (
 UserId INT IDENTITY(1,1),
 UserLabel VARCHAR(25),
 Person VARCHAR(25),
 Ranking TINYINT,
 UpdatedDate DATETIME DEFAULT GETDATE()
)

INSERT INTO UserRankings (UserLabel,Person,Ranking)
VALUES ('Director','Jane',7) ---director
 , ('Instructor','John',5) ---instructor
 , ('StJames','James',3) ---student
 , ('StSarah','Sarah',1) ---student
 , ('StAjay','Ajay',2) ---student
 , ('StHannah','Hannah',2) ---student
 , ('StMatthew','Matthew',2) ---student


IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'Director')
BEGIN
 DROP USER Director
END

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'StJames')
BEGIN
 DROP USER StJames
END

CREATE USER Director WITHOUT LOGIN
GRANT SELECT ON UserRankings TO Director
CREATE USER StJames WITHOUT LOGIN
GRANT SELECT ON UserRankings TO StJames
GRANT INSERT ON UserRankings TO StJames


IF OBJECT_ID('tvfFilter') IS NOT NULL
BEGIN
 DROP FUNCTION tvfFilter
END

---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @secsql NVARCHAR(MAX)
SET @secsql = N'
CREATE FUNCTION tvfFilter (@userlabel AS VARCHAR(25))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS ReturnValue WHERE @userlabel = USER_NAME() OR USER_NAME() = ''Director''
'
EXEC sp_executesql @secsql


---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @spolsql NVARCHAR(MAX)
SET @spolsql = N'
CREATE SECURITY POLICY RankFilter
ADD FILTER PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings,
ADD BLOCK PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings AFTER INSERT
WITH (STATE = ON)
'
EXEC sp_executesql @spolsql

GRANT SELECT ON tvfFilter TO Director
GRANT SELECT ON tvfFilter TO StJames


EXECUTE AS USER = 'StJames'
----Fails:
INSERT INTO UserRankings (UserLabel,Person,Ranking)
VALUES ('Director','James',7)
 , ('StJames','James',5)
----Passes:
INSERT INTO UserRankings (UserLabel,Person,Ranking)
VALUES ('StJames','James',5)
SELECT * FROM UserRankings
REVERT


EXECUTE AS USER = 'Director'
SELECT * FROM UserRankings
REVERT


DROP SECURITY POLICY RankFilter
DROP TABLE UserRankings
DROP FUNCTION tvfFilter

We see that on the first INSERT, James gets an error:

Msg 33504, Level 16, State 1, Line N
The attempted operation failed because the target object ‘OurDatabase.dbo.UserRankings’ has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.
The statement has been terminated.

James cannot add data that violate the predicate — he’s not a director.

RLS Considerations

Like with dynamic data masking, row level security (RLS) invites attacks because the data are present in the database. A hacker knows that if he compromises your database, he can get access to your data. In the case of RLS, the hacker knows that it’s only a matter of permissions. Hackers can use social engineering to determine if a company is using row level security and if this is the case, they know they only have to compromise the database. A more specific example of this (and a form of social engineering) would be to use social media to identify who works at a target company, whether they use RLS, and compromise the person’s access (spearphishing from social media analysis would be another route). Even if we assume the hacker only compromises one employee (one makes it easier to compromise a second and third), that is enough to get the data.

By contrast, in the case of a security approach like always encrypted where a different layer has the key to unencrypt the data, the hacker must compromise two layers — simply getting the data won’t be enough. In both the case of dynamic data masking and row level security, this is not the case.

If we plan to use RLS, we need to ensure that it functions through sufficient testing. As we’ve seen above this, AFTER and BEFORE UPDATE differ in what could happen with users. We would want to have detailed testing scenarios that ensure that row level security will function as intended. We should always remember that we’re designing for failure (or in the case of security, compromise). This means that we test with our architecture as if we’re trying to compromise the security. I highly recommend researching people who’ve used this feature with their architecture, as they will make note of some of the issues they’ve seen in their environment.

As a contrived example to this point, I’ve added the below example where I create architecture that doesn’t prevent our student from getting access to others’ students data. Our student uses the foreign key constraint to be able to query the others students’ data. While only a contrived example, this highlights the importance that our entire architecture must be identical in how we want our users to query and return data.

IF OBJECT_ID('RankFilter') IS NOT NULL
BEGIN
 DROP SECURITY POLICY RankFilter
END

IF OBJECT_ID('UserTests') IS NOT NULL
BEGIN
 DROP TABLE UserTests
END

IF OBJECT_ID('UserRankings') IS NOT NULL
BEGIN
 DROP TABLE UserRankings
END

CREATE TABLE UserRankings (
 UserId INT PRIMARY KEY, ---- changed for example
 UserLabel VARCHAR(25),
 Person VARCHAR(25),
 Ranking TINYINT,
 UpdatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE UserTests(
 UserId INT FOREIGN KEY REFERENCES UserRankings(UserID),
 TestId INT,
 Score INT
)

INSERT INTO UserRankings (UserId,UserLabel,Person,Ranking)
VALUES (1,'Director','Jane',7) ---director
 , (2,'Instructor','John',5) ---instructor
 , (3,'StJames','James',3) ---student
 , (4,'StSarah','Sarah',1) ---student

INSERT INTO UserTests
VALUES (3,1,80)
 , (4,1,100)
 , (4,2,100)


IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'Director')
BEGIN
 DROP USER Director
END

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'StJames')
BEGIN
 DROP USER StJames
END

CREATE USER Director WITHOUT LOGIN
GRANT SELECT ON UserRankings TO Director
CREATE USER StJames WITHOUT LOGIN
GRANT SELECT ON UserRankings TO StJames
GRANT SELECT ON UserTests TO StJames


IF OBJECT_ID('tvfFilter') IS NOT NULL
BEGIN
 DROP FUNCTION tvfFilter
END

---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @secsql NVARCHAR(MAX)
SET @secsql = N'
CREATE FUNCTION tvfFilter (@userlabel AS VARCHAR(25))
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS ReturnValue WHERE @userlabel = USER_NAME() OR USER_NAME() = ''Director''
'
EXEC sp_executesql @secsql


---- We must use dynamic SQL to execute all this code in 1 batch
DECLARE @spolsql NVARCHAR(MAX)
SET @spolsql = N'
CREATE SECURITY POLICY RankFilter
ADD FILTER PREDICATE dbo.tvfFilter(UserLabel) ON dbo.UserRankings
WITH (STATE = ON)
'
EXEC sp_executesql @spolsql




EXECUTE AS USER = 'StJames'

SELECT * FROM UserRankings

SELECT * 
FROM UserTests t1
 INNER JOIN UserRankings t2 ON t1.UserId = t2.UserId

SELECT UserTests.*
FROM UserTests
 LEFT JOIN (SELECT t1.UserId FROM UserTests t1 INNER JOIN UserRankings t2 ON t1.UserId = t2.UserId) filt ON filt.UserId = UserTests.UserId
WHERE filt.UserId IS NULL
REVERT


DROP SECURITY POLICY RankFilter
DROP TABLE UserTests
DROP TABLE UserRankings
DROP FUNCTION tvfFilter

Also similar to issues with dynamic data masking, Microsoft correctly observes that a user could carefully construct a query that would throw an error, but this would allow a user to deduce data because of the error. If we think about using RLS in a manufacturing context where users with different roles have different levels of access and we’re not protecting any intellectual property, this is less of a concern. By contrast a healthcare scenario might be a disaster here, as it would allow someone to deduce private information. We have to be extremely careful if we decide to use RLS, like dynamic data masking, because there are numerous business scenarios where neither make good solutions.

Performance

As we can imagine, anything that involves filtering data will have an impact on performance. With tables that use RLS, I would advise the following to enhance performance or reduce poor performance:

• Avoid clustered column store indexes. These are optimized for aggregate queries and would seldom be useful in situations where we would be using RLS. Exceptions exist, but for the most part, you’ll want to use B-tree indexes, as these are efficient for data filtering.
• Be aware of the impact on CPU and memory given that the searches on table(s) will be highly filtered. Cache frequently used queries when possible. In terms of design, don’t add this to a server that already has intense CPU/memory pressure.

Keep in mind that data size matters here too, as we can bypass row level security by partitioning our data in situations where we may have massive data volume, but only a small set of users or identities that we need to be filtering data. For instance, suppose that we’re storing products by company and we have 8 companies, but millions of products per company. In my view, breaking out all products by company would be better than throwing everything into a table and using RLS.

Answers

1. We would need a filter predicate along with either an AFTER or BEFORE UPDATE blocking predicate. This would ensure that users can only see their data rows and it would ensure that they can’t update values of other users.
2. True. Though we assign read access to the function in our simple example, we note that this is not required.
3. Depending on our answer to 1, the only change we might make would be that we would only use AFTER UPDATE as a blocking predicate (this wouldn’t apply if you answered this in the original question). For this question, BEFORE UPDATE wouldn’t work; see the code examples in this post.
4. No. As we see in the above example, we must apply our RLS architecture to all objects. OurTableTwo could still be accessed by users and they could derive what values potentially exist in OurTableOne based on the foreign key constraint.
5. There’s a variety of attacks against row level security, but two ways that are mentioned in this post is spearphishing or socially engineering a person through social media analysis.

Note: all images in the post are either created through actual code runs or from Pixabay. The written content of this post is copyright; all rights reserved. None of the written content in this post may be used in any artificial intelligence.