BT

SQL Server 2016: Row-Level Security

by Jonathan Allen on Jun 17, 2015 |

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.

Rate this Article

Relevance
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss
General Feedback
Bugs
Advertising
Editorial
Marketing
InfoQ.com and all content copyright © 2006-2016 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT