SQL Server 2016: Row-level security

SQL Server 2016: Row-level security

A common criticism for SQL Server is that its security model can only recognize tables and columns. If you want to apply security rules to behavior units, you need to use stored procedures or table value functions to simulate them, and then find a method to ensure that they are not bypassed. In SQL Server 2016, this is no longer a problem.

Implementation

Row-level security in SQL Server 2016 (and SQL Azure) is based on a specially designed inline Table value function. This function returns either a row containing only 1 or no result, depending on whether the row accessed by the user is related. See the following functions:

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 Code indicates that the current user must be a manager or a record-related salesman. This function does not access the row itself, but you can use parameters to pass in the corresponding column (for example, SalesRep ). For example:

Create security policy SalesFilter add filter predicate Security. fn_securitypredicate (SalesRep) ON dbo. Sales WITH (STATE = ON );

Actual Effect

When row-Level Security is used, users cannot see the rows they cannot access. This is like adding an additional security-related where clause when accessing the table.

Because it acts like a where clause, there are some limitations. For example, if you use full-text search indexes on that column, data may be leaked. In addition, databases may also suffer from bypass attacks. Microsoft wrote:

By using well-designed queries, information can be leaked. For example, SELECT 1/(SALARY-100000) from payroll where name = 'John Doe 'will let a malicious user know that John Doe's SALARY is $0.1 million. Even if there is an appropriate security predicate that prevents malicious users from directly querying others' salaries, he can determine the salary amount when the query returns an exception with a "division of 0.

In addition, information may also be leaked through statistical objects. To reduce risks, the user who views the statistics of protected columns must be the "Table owner, sysadmin, db_owner, or member of db_ddladmin, inherent role of the Database ".

Intermediate Application

So far, we have discussed the scenario where users log on as their own. In the middle-tier application, all users share the same database account, which requires additional steps to achieve row-level security.

For middle-tier applications, we recommend that you set the CONTEXT_INFO value to the user ID specific to the application when the connection is opened. Then, the security function can reference the value of CONTEXT_INFO. For example:

Create function Security. fn_securitypredicate (@ AppUserId int) returns table with schemabinding as return select 1 AS region WHERE DATABASE_PRINCIPAL_ID () = DATABASE_PRINCIPAL_ID ('dbo') -- application context and convert (int, CONVERT (VARBINARY (4), CONTEXT_INFO () = @ AppUserId; -- AppUserId (int) occupies 4 bytes of go create security policy Security. salesFilter add filter predicate Security. fn_securitypredicate (AppUserId) ON dbo. sales WITH (STATE = ON );

The premise of this method is that the user cannot execute any SQL, because it will allow them to change CONTEXT_INFO at will.

SQL Server 2016: Row-Level Security

This article permanently updates the link address: