A common criticism for SQL Server’s security model is that it only understands tables and columns. If you want to apply security rules on a row-by-row basis, you have to simulate it using stored procedures or table value functions, and then find a way to make sure there is no way to bypass them. With SQL Server 2016, that is no longer a problem.
Implementation
Row-Level Security in SQL Server 2016 (and SQL Azure) is based on a specially crafted inline table valued function. This function returns either a single row with a 1 or no results depending on whether or not the user has access to the associate row. Consider this function:
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
This says that the current user must be either a manager or the sales rep associated with the record. The function doesn’t have access to the row itself, but you can pass in relevant columns (e..g SalesRep) using parameters. For example,
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) ON dbo.Sales
WITH (STATE = ON);
Practical Effects
When using row-level security, users simply don’t see the rows they don’t have access to. If it as if an additional security-related where clause was automatically applied whenever the table is accessed.
Because it acts as a where clause, there are some limitations. For example, you can leak data if you apply a Full Text Search index to the column. There is also the possibility of a side channel attack. Microsoft writes,
It is possible to cause information leakage through the use of carefully crafted queries. For example, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' would let a malicious user know that John Doe's salary is $100,000. Even though there is a security predicate in place to prevent a malicious user from directly querying other people's salary, the user can determine when the query returns a divide-by-zero exception.
You can also leak information via a statistics object. To reduce the risk, viewing the statistics on a secured column requires that “the user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role”.
Middle Tier Applications
So far we have been talking about scenarios where the user is logged in as themself. When working with a middle tier application such that everyone shares one database account, additional steps are needed.
The recommended design pattern is for the middle tier application to set the CONTEXT_INFO value to the user’s application-specific user id whenever a connection is opened. The CONTEXT_INFO value can then be referenced in the security function. For example,
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo') -- application context
AND CONVERT(int, CONVERT(VARBINARY(4), CONTEXT_INFO())) = @AppUserId; -- AppUserId (int) is 4 bytes
GO
CREATE SECURITY POLICY Security.SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId) ON dbo.Sales
WITH (STATE = ON);
This approach assumes that the user cannot execute arbitrary SQL, as that would allow them to change their CONTEXT_INFO at will.
