sql 如何设置行级锁
With the release of SQL Server 2016 comes many great new features. One of these is the implementation of row level security in the database engine.
随着SQL Server 2016的发布,带来了许多强大的新功能。 其中之一是在数据库引擎中实现行级安全性。
This blogpost will cover the aspects of this new feature – including:
该博客文章将涵盖此新功能的各个方面–包括:
- Setup 建立
- Best practice 最佳实践
- Performance 性能
- Possible security leaks 可能的安全漏洞
介绍 (Introduction)
The row level security feature was released earlier this year to Azure – following Microsoft’s cloud-first release concept.
行级安全功能已于今年早些时候发布到Azure,这是Microsoft的云优先发布概念。
A past big issue with the SQL Server engine was that in only understands tables and columns. Then you had to simulate security using secured views, stored procedures or table value functions. The problem here was to make sure that there were no way to bypass them.
SQL Server引擎过去的一个大问题是只能理解表和列。 然后,您必须使用安全视图,存储过程或表值函数来模拟安全性。 这里的问题是确保没有办法绕过它们。
With SQL Server 2016, this is no longer an issue.
使用SQL Server 2016,这不再是问题。
Now the SQL Server engine handles the security policy in a central controlled area.
现在,SQL Server引擎在中央控制区域中处理安全策略。
设置和最佳实践 (Setup and best practice)
The Row-level security is based on a special inline table valued function. This function returns either a single row with a 1 or no rows based on the users rights to that specific row.
行级安全性基于特殊的内联表值函数。 此函数根据对特定行的用户权限返回具有1行或不具有行的单行。
Let us take an example:
让我们举个例子:
First of all, I’ll create a database and some users to test with:
首先,我将创建一个数据库和一些用户进行测试:
CREATE DATABASE RowFilter;
GO
USE RowFilter;
GO
CREATE USER userB