使用SQL视图和存储过程限制和监视SQL Server数据访问

This article explains data security for accessing sensitive data and restricts access in application using SQL Views and stored procedures.

本文介绍了用于访问敏感数据的数据安全性，并限制了使用SQL视图和存储过程的应用程序中的访问。

We recently faced a leak of information for one of our employees that involved data which caused a conflict within our company, even if it was not personally identifiable information (PII data). When we investigated the issue, we uncovered that we need to organize data access for our teams and review who has access to what information. As of right now, all our users either have access to all tables directly or a subset of all our tables. What are some practices we can use in SQL Server to avoid giving direct table access to our users?

最近，我们遇到了一名员工泄漏的信息，其中涉及的数据会导致公司内部发生冲突，即使这些信息不是个人可识别信息（PII数据）也是如此。 当我们调查此问题时，我们发现我们需要为团队组织数据访问并检查谁有权访问哪些信息。 截至目前，我们所有的用户都可以直接访问所有表或所有表的子集。 我们可以在SQL Server中使用哪些实践来避免将直接表访问权限授予用户？

总览 (Overview)

In this tip, we’ll look at a few techniques that we can use to restrict permissions on data. Even though we are not looking at personally identifiable information, we can apply some of these techniques along with encryption when (or if) we have PII data stored. I will caution developers that data doesn’t have to belong to PII data to restrict access; for an example, in many jurisdictions around the world, salary and bonus information are not considered personal data, yet allowing everyone the company access to this data could turn into a legal disaster. With all data, always lean on the side of providing the least amount of data access to everyone.

在本文中，我们将介绍一些可用于限制数据权限的技术。 即使我们不查看个人身份信息，也可以在存储PII数据时（或如果存储）使用其中一些技术以及加密技术。 我将警告开发人员，数据不必属于PII数据即可限制访问； 例如，在世界上许多司法管辖区，薪水和奖金信息均不视为个人数据，但允许公司所有人访问此数据可能会导致法律灾难。 对于所有数据，始终倾向于为每个人提供最少的数据访问量。

使用SQL视图和过程 (Working with SQL views and procedures)

As a first step, we want to create our hierarchy to prevent some level of employees from accessing some data. For a contrived example throughout this tip, we’ll separate staff into three groups – group one of executive staff, who will have more permissions when viewing data and group two of other staff, who will have fewer permissions or no permissions when viewing data. The final group will be the contrived group of admins, who will have sole access to the audit information. Admin staff would be the CEO, CFO and a few others who may need to review audit data for access. The below image shows our contrived hierarchy:

第一步，我们要创建层次结构，以防止某些级别的员工访问某些数据。 对于整个技巧中的人为示例，我们将员工分为三类：第一类是执行人员，他们在查看数据时将具有更多权限；第二类是其他人员，在查看数据时将具有较少权限或没有权限。 最后一组将是人为设计的管理员组，他们将拥有唯一的访问审核信息的权限。 管理员人员将是CEO，CFO和其他一些人，他们可能需要查看审核数据以进行访问。 下图显示了我们人为设计的层次结构：

While this step involves organization, we’ll use this as a map for how we set permissions. I recommend that you always do this step prior to giving permissions.

尽管此步骤涉及组织，但我们将以此为基础来设置权限。 我建议您始终在授予权限之前执行此步骤。

We’ll also be using two service accounts in this example for executive and other staff, though we could use AD accounts as well and apply the same level of permissions used in this tip. We’ll start off by creating two service account users – one for the executive group and the other for the other group. The administrators in this example will have windows authentication access through their AD account and we’ll be using this windows authentication to create the objects (this will be your default logged in user already). These service accounts will also have a role each – other for other staff and executive for executive staff.

在本示例中，我们还将为执行人员和其他人员使用两个服务帐户，尽管我们也可以使用AD帐户并应用本技巧中使用的相同级别的权限。 我们首先创建两个服务帐户用户-一个用于执行组，另一个用于另一个组。 此示例中的管理员将通过其AD帐户具有Windows身份验证访问权限，我们将使用该Windows身份验证来创建对象（这已经是您的默认登录用户）。 这些服务帐户也将有每一个角色- 其他的其他工作人员和行政的行政人员。

USE master
GO
 
CREATE LOGIN [exampleExecutiveStaff] WITH PASSWORD = 'this1passwordisONLYanexample!'
CREATE USER [exampleExecutiveStaff] FROM LOGIN [exampleExecutiveStaff]
 
CREATE LOGIN [exampleOtherStaff] WITH PASSWORD = 'this2passwordisONLYanexample!'
CREATE USER [exampleOtherStaff] FROM LOGIN [exampleOtherStaff]
 
 
---- Example database
USE GenExaAll
GO
 
CREATE USER [exampleExecutiveStaff] FROM LOGIN [exampleExecutiveStaff]
CREATE USER [exampleOtherStaff] FROM LOGIN [exampleOtherStaff]
CREATE ROLE [executive]
CREATE ROLE [other]
ALTER ROLE [executive] ADD MEMBER [exampleExecutiveStaff]
ALTER ROLE [other] ADD MEMBER [exampleOtherStaff]

In our first example, we will create two SQL views: one SQL view for the executive staff and the other SQL view for the other staff. The other staff will only see part of the table, while the execute staff will be able to view the full table from the view (create in separate batches):

在第一个示例中，我们将创建两个SQL视图：一个用于执行人员SQL视图，另一个用于其他人员SQL视图。 其他人员将仅看到表的一部分，而执行人员将能够从视图中查看整个表（分批创建）：

--Create SQL View
 
  CREATE VIEW viSDRHoldingsExecutive
AS
  SELECT Members,SDRHoldings,SDRAllocations FROM tbSDRHoldings
 
CREATE VIEW viSDRHoldingsOther
AS
  SELECT Members,SDRAllocations FROM tbSDRHoldings
 
GRANT SELECT ON viSDRHoldingsExecutive TO [executive]
GRANT SELECT ON viSDRHoldingsOther TO [other]

What we’re using here is a SQL view on top of a table, granting an account access to a role, and granting the role read access to the view. Relative to what the SQL view allows, the user will have the permission. The user will not have further permissions on the table or the other view. As a test, we will log in as the exampleOtherStaff user and try to read from the tbSDRHoldings table and the viSDRHoldingsExecutive view directly and get an error, though we will be able to read from viSDRHoldingsOther without an error:

我们在这里使用的是表顶部SQL视图，向帐户授予对角色的访问权限，并向角色授予对该视图的读取访问权限。 相对于SQL视图所允许的，用户将具有权限。 用户将对该表或其他视图没有进一步的权限。 作为测试，我们将以exampleOtherStaff用户身份登录，并尝试直接从tbSDRHoldings表和viSDRHoldingsExecutive视图读取并得到一个错误，尽管我们将能够从viSDRHoldingsOther读取而不会出现错误：

---- logged in as exampleOtherStaff
  --Get data from SQL View
USE GenExaAll
GO
 
SELECT * FROM viSDRHoldingsExecutive
SELECT * FROM tbSDRHoldings
SELECT * FROM viSDRHoldingsOther

A view is one way we can restrict access. We can also restrict access using stored procedures. In our next example, we’ll create two stored procedures with the same select statements we see in the views and demarcate them by the group. We’ll then grant execute permissions to the stpSDRHoldingsOther to the other role:

视图是我们限制访问的一种方式。 我们还可以使用存储过程来限制访问。 在下一个示例中，我们将使用在视图中看到的相同的select语句创建两个存储过程，并按组对其进行划分。 然后，我们将对stpSDRHoldingsOther的执行权限授予其他角色：

CREATE PROCEDURE stpSDRHoldingsExecutive
AS
BEGIN
  SELECT Members,SDRHoldings,SDRAllocations FROM tbSDRHoldings
END
 
CREATE PROCEDURE stpSDRHoldingsOther
AS
BEGIN
  SELECT Members,SDRAllocations FROM tbSDRHoldings
END
 
 
GRANT EXECUTE ON stpSDRHoldingsOther TO [other]

If we try to execute both stored procedure when logged in as the exampleOtherStaff user, we only get the result for the stored procedure granted execute permission:

如果我们以exampleOtherStaff用户身份登录时尝试执行这两个存储过程，则只会得到被授予执行权限的存储过程的结果：

---- logged in as exampleOtherStaff
USE GenExaAll
GO
 
EXEC stpSDRHoldingsExecutive
EXEC stpSDRHoldingsOther

Even if a user does not have direct access to a table, such as inserting, updating, deleting or even selecting from a table, we can still use grants on stored procedures to allow data operations. In our next example using the same stored procedure stpSDRHoldingsOther, we’ll add an ending insert to a logging table to the final part of the procedure so that we retain a record of when the user calls this stored procedure.

即使用户没有直接访问表的权限，例如插入，更新，删除或什至从表中进行选择，我们仍然可以在存储过程中使用授权来允许数据操作。 在下一个使用相同存储过程stpSDRHoldingsOther的示例中，我们将在日志记录表的末尾插入过程的最后一部分，以便保留用户调用此存储过程的记录。

---- Audit table:
CREATE TABLE tbAuditLog (
  LogUser VARCHAR(100), 
  LogDate DATETIME DEFAULT GETDATE()
)
 
---- Alter our procedure to add an insert to audit table:
ALTER PROCEDURE stpSDRHoldingsOther
AS
BEGIN
  
  SELECT Members,SDRAllocations FROM tbSDRHoldings
 
  INSERT INTO tbAuditLog (LogUser) VALUES (SUSER_NAME())
END

If we switch over to our window where the exampleOtherStaff is logged in and call the procedure now, we’ll notice in the messages we see that the insert took place:

如果我们切换到登录exampleOtherStaff的窗口并立即调用该过程，我们将在消息中注意到插入发生：

---- logged in as exampleOtherStaff
USE GenExaAll
GO
 
EXEC stpSDRHoldingsOther

If we try to read from the audit table as the exampleOtherStaff, we get an error, as this user does not have permission to this table outside of the insert within the stored procedure:

如果我们尝试从审计表中读取exampleOtherStaff，则会收到错误消息，因为该用户在存储过程中的插入之外没有对该表的权限：

---- logged in as exampleOtherStaff
USE GenExaAll
GO
 
SELECT * 
FROM tbAuditLog

When we use our admin user that we’re creating these objects with and we look at the table, we see relevant information about the user that executed the procedure and the time:

当我们使用与之创建这些对象的管理员用户并查看表时，我们会看到有关执行该过程的用户和时间的相关信息：

SELECT *
FROM tbAuditLog

While we have other ways of tracking who executed stored procedures, such as using traces and other audits, we can use stored procedures in this manner even without giving the user access to the logging tables. This means that we can design for allowing some lower level users to add or change data through restrictions in stored procedures without access to the underlying objects.

尽管我们还有其他方法可以跟踪谁执行了存储过程，例如使用跟踪和其他审核，但即使不授予用户访问日志表的权限，我们也可以以这种方式使用存储过程。 这意味着我们可以设计为允许一些较低级别的用户通过存储过程中的限制来添加或更改数据，而无需访问基础对象。

评论 (Review)

After we map out the levels of access, from the highest to the lowest level while restricting each level as much as possible, we can create an object such as views or procedures on top of other objects, such as tables. Using roles or even adding permissions directly to the user, we can grant the roles or users access to the higher-level objects, while keeping the underlying objects restricted. Finally, we can add logging to stored procedures, if we choose to go this route, as procedures can allow operations for roles or users without conceding permissions to the underlying objects.

在从最高级别到最低级别绘制访问级别并同时尽可能限制每个级别之后，我们可以在其他对象（例如表）之上创建一个对象（例如视图或过程）。 通过使用角色，甚至直接向用户添加权限，我们可以授予角色或用户对更高级别对象的访问权限，同时保持对基础对象的限制。 最后，如果我们选择采用这种方法，则可以向存储过程添加日志记录，因为过程可以允许角色或用户进行操作而无需放弃对底层对象的许可。

Remember that this only covers the database layer; on the application side, we would want to restrict user input as much as possible and eliminate all data or information that gives a user more insight into what exists underneath (like seeing a report name they don’t have access to).

请记住，这仅涵盖数据库层； 在应用程序方面，我们希望尽可能地限制用户输入，并消除所有数据或信息，这些数据或信息可以使用户更深入地了解其下的内容（例如查看他们无权访问的报告名称）。

翻译自: https://www.sqlshack.com/restricting-and-monitoring-data-access-with-views-and-procedures/