rls数据预测
Of late, there’s been a lot of noise around the term, GDPR. Chances are, some of us even had to go through learning sessions targeted at IT professionals to learn about what this new standard of data protection means. GDPR is primarily a European privacy law which sets a new bar, globally, on privacy rights, compliance, and security. GDPR is mainly about protecting the rights of every individual, providing the individual with more control over his personal data. It dictates how data should be handled, managed and protected going forward, the individual’s choice being the prime focus.
最近，术语“ GDPR”周围有很多噪音。 我们中有些人甚至不得不参加针对IT专业人员的学习课程，以了解这种新的数据保护标准的含义。 GDPR主要是欧洲隐私法，在全球范围内为隐私权，合规性和安全性设定了新的标准。 GDPR主要是为了保护每个人的权利，使个人对其个人数据有更多的控制权。 它规定了今后应如何处理，管理和保护数据，个人的选择是首要重点。
Today, data is widespread; many corporations handle part of the data on the cloud and part of it on premises. Our focus being SQL Server, we shall talk about what capabilities Microsoft gives us in order to be compliant with these laws that come into effect on the 25th of May, 2018. We would have to modify our data handling procedures keeping the focus on the security of the data processing.
如今，数据非常普遍。 许多公司在云中处理部分数据，而在本地处理部分数据。 我们的重点是SQL Server，我们将讨论Microsoft提供的哪些功能，以便与这些法律相一致，这些法律于2018年5月25 日生效。我们将不得不修改数据处理程序，重点是数据处理的安全性。
There are several built-in security capabilities in SQL Server to help in reducing risk and an overall improvement in managing data at the database level or otherwise.
SQL Server中有几种内置的安全功能，可帮助降低风险以及在数据库级别或其他级别上管理数据的整体改进。
但是我们从哪里开始呢？ (But where do we start?)
Before we start discussing on the nitty-gritty of implementing GDPR principles in SQL Server, I would recommend reading the article Using production data for testing in a post GDPR world by Brian Lockwood, the CEO of ApexSQL.
在我们开始讨论在SQL Server中实现GDPR原则的本质之前，我建议阅读ApexSQL首席执行官Brian Lockwood的文章使用生产数据在GDPR后世界中进行测试 。
Now that we have a basic idea on GDPR, let’s now dive a little deeper. Here are a few points to keep in mind:
既然我们对GDPR有了基本的了解，那么现在让我们深入一点。 请记住以下几点：
- Discover: Identify which data is of personal nature, and technical details about it such as its location and the mode of storage. 发现：确定哪些数据属于个人性质，以及有关其的技术细节，例如其位置和存储方式。
- Manage: Classify the data access needs and decide the governance model accordingly. 管理：对数据访问需求进行分类，并相应地确定治理模型。
- Protect: set up security controls to prevent vulnerabilities and also detect and respond to data breaches. 保护：设置安全控制措施以防止漏洞，并检测和响应数据泄露。
- Report: Document and manage data requests, and provide notifications in case of breaches. 报告：记录和管理数据请求，并在发生违规时提供通知。
Following are the features in SQL Server that support GDPR compliance:
以下是SQL Server中支持GDPR合规性的功能：
- Row-Level Security (RLS) 行级安全性（RLS）
- Dynamic Data Masking (DDM) 动态数据屏蔽（DDM）
- Transparent Data Encryption (TDE) 透明数据加密（TDE）
- Transport Layer Encryption (TLS) 传输层加密（TLS）
- SQL Server Audit SQL Server审核
- Temporal Tables 时间表
- Always Encrypted (AE) 始终加密（AE）
- Authentication 认证方式
- Azure vault Azure库
- Azure Active Directory Azure活动目录
- SQL Threat detection SQL威胁检测
GDPR can be further classified into several categories as follow:
GDPR可以进一步分为以下几类：
-
- Configuring Always Encryption in SQL Server 在SQL Server中配置始终加密
- Dynamic Data Masking (DDM) 动态数据屏蔽（DDM）
-
- Row-Level Security (to be discussed in detail in this article) 行级安全性（将在本文中详细讨论）
- TDE TDE
- Azure Active Directory Azure活动目录
- Always Encrypted 始终加密
-
- SQL Server Audit SQL Server审核
- SQL Threat Detection SQL威胁检测
In this article we discuss the Row Level Security feature. This security feature is available from SQL Server 2016. With SQL Server 2016 has row-level security, fine-grained access control, completely transparent to client applications available in all editions. Row level security is the feature available to filter content based on a user’s specific need, thus reducing the database exposure to unauthorized disclosure of personal data.
在本文中，我们讨论行级安全性功能。 SQL Server 2016提供此安全功能。SQLServer 2016具有行级安全性，细粒度的访问控制，对于所有版本中可用的客户端应用程序完全透明。 行级安全性是可用于根据用户的特定需求过滤内容的功能，从而减少了数据库暴露于未经授权的个人数据泄露的风险。
Row-Level security defines the security policy to restrict access to objects based on specific entitlements. RLS defines the database user’s access restriction to specific rows by filtering the users using predicate clause.
行级安全性定义了安全策略，以基于特定的权限限制对对象的访问。 RLS通过使用谓词子句过滤用户来定义数据库用户对特定行的访问限制。
Row-level security is applied at the table level, without having to implement custom stored procedures to make it work for insert, update, and delete SQL statements. It greatly simplifies the design and coding required since it’s managing the data in the database tier. In previous designs, sensitive data used to be handled at the application tier. The process was very cumbersome and it required a lot of coding and design changes.
行级安全性应用于表级，而无需实现自定义存储过程即可使其适用于插入，更新和删除SQL语句。 由于它管理数据库层中的数据，因此极大地简化了所需的设计和编码。 在以前的设计中，敏感数据曾经在应用程序层进行处理。 该过程非常麻烦，并且需要进行大量编码和设计更改。
Let’s dive in to the concepts of RLS and see how the security policy can be built on the data table. It covers the filter and block predicate mechanism to show how the row-level security handles the input at the run time.
让我们深入了解RLS的概念，并了解如何在数据表上构建安全策略。 它涵盖了过滤器和块谓词机制，以显示行级安全性如何在运行时处理输入。
For the purpose of the demo, I’m going to refer Graph Database article to sample data for the “emp” table. I’m going to create a table called “emp” and query it based on the designation. The table has various designated employees such as ANALYST, MANAGER, CLERK, etc. The employee designated as manager is highest in the hierarchy and will be able to access the all of the rows of the table, however, clerk and analyst will be limited to viewing the related subset of the emp table.
出于演示目的，我将参考“ 图形数据库”文章来为“ emp”表采样数据。 我将创建一个名为“ emp”的表，并根据名称对其进行查询。 该表具有各种指定的员工，例如ANALYST，MANAGER，CLERK等。被指定为经理的员工在层次结构中最高，并且能够访问表的所有行，但是，业务员和分析师将限于查看emp表的相关子集。
We have an idea of a table-valued function, which will determine which rows should be visible to which users. And it’s entirely up to us to define the predicate, the filter that determines who sees what. You can also apply a block to insert, update, and delete. I’m going to show you the filter and block mechanism, which is row-level security, based on the input provided during the query execution. So starting at the top here, let’s look at our data for a moment. We have a table with a lot of locations in it.
我们有一个表值函数的概念，它将决定哪些行对哪些用户可见。 完全取决于我们来定义谓词，即确定谁看到什么的过滤器。 您还可以应用块来插入，更新和删除。 我将基于查询执行期间提供的输入向您展示过滤器和块机制，这是行级安全性。 因此，从顶部开始，让我们看一下数据。 我们有一张桌子，上面有很多位置。
Let’s query the emp table to retrieve the data subsets based on the designated job role.
让我们查询emp表，以根据指定的工作角色检索数据子集。
select ename,job,hiredate,deptno,e.* from emp e
where job in ('ANALYST','CLERK','MANAGER','PRESIDENT')
Now, the view vwEMPDetails is created, which will be used as test data.
现在，将创建视图vwEMPDetails ，它将用作测试数据。
CREATE VIEW vwEmpDetails
as
select ename,job,hiredate,deptno from dbo.emp
where job in ('ANALYST','CLERK','MANAGER')
The dbo is the current user under the current scope of the session context.
dbo是会话上下文当前范围内的当前用户。
SELECT USER_NAME(), CURRENT_USER
Now, we’re going to create three new users.
现在，我们将创建三个新用户。
- Manager, who has access to the all the rows of the emp table 管理员，有权访问emp表的所有行
- Clerk, who has access to clerk-related data, a subset of the emp table 可以访问与业务员相关的数据（emp表的子集）的业务员
- Analyst, who can see the related data of the analyst job of emp table 分析师，谁可以看到emp表的分析师工作的相关数据
CREATE USER ANALYST without login
CREATE USER CLERK without login
CREATE USER MANAGER without login
To define the security policies, first create a schema, the Row-Level Security (RLS) filter
要定义安全策略，首先创建一个模式，即行级安全性（RLS）过滤器
To define the security classifier function, one has to clearly define the filter clause since it determines how the filter works on the actual table.
要定义安全分类器功能，必须明确定义filter子句，因为它确定了过滤器如何在实际表上工作。
CREATE SCHEMA RLSSecurityFilter;
GO
Create the predicate to filter the data based on the current scope of the database users
创建谓词以根据数据库用户的当前范围过滤数据
CREATE FUNCTION RLSSecurityFilter.fn_getJob(@job AS varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE
USER_NAME()='MANAGER'
OR
@job= USER_NAME()
GO
Now, the function has been created. Let’s define the security policy using the classifier function. Under the Security tab, expand Security policies to view the newly-created security policy, JobFilter.
现在，该功能已创建。 让我们使用分类器功能定义安全策略。 在“ 安全性”选项卡下，展开“ 安全性策略”以查看新创建的安全性策略JobFilter 。
CREATE SECURITY POLICY JobFilter
ADD FILTER PREDICATE RLSSecurityFilter.fn_getJob(job)
ON dbo.emp
WITH (STATE = ON);
At first, the newly-created three users have no rights to access the table or the view. Let’s grant select permission on the table emp and view vwEmpDetails to all the three users.
首先，新创建的三个用户无权访问表或视图。 让我们向表emp授予选择权限，并向所有三个用户查看vwEmpDetails 。
GRANT SELECT ON emp TO ANALYST;
GRANT SELECT ON emp TO CLERK;
GRANT SELECT ON emp TO MANAGER;
GRANT SELECT ON emp TO [vwEmpDetails];
GRANT SELECT ON emp TO [vwEmpDetails];
GRANT SELECT ON emp TO [vwEmpDetails];
As we can see that the select statement as dbo will not yield any rows from the table. The filter is currently being applied to the dbo user. Since dbo doesn’t meet the criteria, the data is not displayed.
select USER_NAME()
select ename,job,hiredate,deptno from dbo.emp
Now, run the select statement as manager, clerk, and analyst to validate the output
现在，以经理，文员和分析师的身份运行select语句以验证输出
EXECUTE AS USER='CLERK'
select USER_NAME()
EXEC ('select ename,job,hiredate,deptno from dbo.emp')
revert;
go
EXECUTE AS USER='Analyst'
select USER_NAME()
EXEC ('select ename,job,hiredate,deptno from dbo.emp')
revert;
go
As we can see, the subset of emp table is projected as resultsets based on the current scope of the user
如我们所见，emp表的子集根据用户的当前范围投影为结果集
Now, it’s a time to validate the access the database user, Manager, has.
现在，是时候验证数据库用户Manager的访问权限了。
EXECUTE AS USER='Manager'
select USER_NAME()
EXEC ('select ename,job,hiredate,deptno from dbo.emp')
revert;
go
We can see that the database user Manager is able to view all the rows of emp table.
我们可以看到数据库用户管理器能够查看emp表的所有行。
Now, perform the DML operations on the emp table. Let’s grant the required permission to update or insert the data into the emp table.
现在，在emp表上执行DML操作。 让我们授予更新或将数据插入emp表所需的权限。
GRANT UPDATE, INSERT ON dbo.emp TO CLERK;
GRANT UPDATE, INSERT ON dbo.emp TO Manager;
EXECUTE AS USER='CLERK'
select USER_NAME()
INSERT INTO EMP VALUES
(7362, 'SQL', 'CLERK', 7902, '02-MAR-1983', 9000, NULL, 20)
REVERT
GO
EXECUTE AS USER='CLERK'
SELECT USER_NAME()
SELECT * FROM EMP
REVERT;
We can see in the following output that the 7,362 records were inserted into the table.
我们可以在下面的输出中看到将7,362条记录插入到表中。
In this section, we will discuss how the security policy can be defined to block the users from updating or inserting values into the table.
在本节中，我们将讨论如何定义安全策略以阻止用户更新值或将值插入表中。
Create a schema RLSSecurityblock using the create schema syntax.
使用创建模式语法创建模式RLSSecurityblock 。
CREATE SCHEMA RLSSecurityblock;
GO
Create the inline TVF (Table Valued Function) to create the Block predicate.
CREATE FUNCTION RLSSecurityblock.fn_getJob(@job AS varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
-- Predicate logic
WHERE USER_NAME()='MANAGER' --Only Manager should have DML access to all rows
GO
Modify the existing security policy by adding the block predicate portion to prevent any other users from inserting into the emp table
通过添加块谓词部分来修改现有的安全策略，以防止任何其他用户插入emp表
ALTER SECURITY POLICY jobfilter
ADD BLOCK PREDICATE RLSSecurityblock.fn_getJob(job)
ON dbo.emp AFTER INSERT;
Now, let’s validate if the block predicate functionality works as expected.
现在，让我们验证块谓词功能是否按预期工作。
EXECUTE AS USER='CLERK'
select USER_NAME()
INSERT INTO EMP VALUES
(7362, 'SQL', 'CLERK', 7902, '02-MAR-1983', 9000, NULL, 20)
On implementing the block predicate, we can see that the insert operation fails for the user clerk
在实现块谓词时，我们可以看到用户业务员的插入操作失败
Let’s test the block predicate by inserting a row into the emp table as Manager.
让我们通过在emp表中插入一行作为Manager来测试块谓词。
EXECUTE AS USER='MANAGER'
select USER_NAME()
INSERT INTO EMP VALUES
(7300, 'SQLShack', 'CLERK', 7900, '02-MAR-1984', 9000, NULL, 20)
REVERT;
EXECUTE AS USER='MANAGER'
SELECT USER_NAME()
SELECT * FROM EMP
REVERT;
We can see that the row 19 was inserted into the table.
我们可以看到第19行已插入到表中。
结语 (Wrapping up)
In this article, we walked through the filter and block predicates. We went step by step to provide the required access to users and also, isolate the data operations from various users. This feature greatly simplifies the data security design and helps go closer to implementing GDPR, by enabling us to manage the application access model effectively.
在本文中，我们遍历了过滤器和块谓词。 我们一步一步地为用户提供了所需的访问权限，并且还将数据操作与各种用户隔离开来。 通过使我们能够有效地管理应用程序访问模型，此功能极大地简化了数据安全性设计并有助于更接近于实施GDPR。
翻译自: https://www.sqlshack.com/sql-server-data-security-feature-rls-row-level-security-and-gdpr/
rls数据预测
2752
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
