BT

Your opinion matters! Please fill in the InfoQ Survey!

SQL Server 2016: Row-Level Security

| by Jonathan Allen Follow 244 Followers on Jun 17, 2015. Estimated reading time: 2 minutes |

A note to our readers: As per your request we have developed a set of features that allow you to reduce the noise, while not losing sight of anything that is important. Get email and web notifications by choosing the topics you are interested in.

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

Adoption Stage
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

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT