介绍数据库和对象权限

10 分钟

所有关系数据库管理平台都具有 4 种基本权限，用于控制数据操作语言 (DML) 操作。这些权限是 SELECT、INSERT、UPDATE 和 DELETE，它们适用于所有 SQL Server 平台。可针对表和视图授予、撤销或拒绝所有这些权限。如果使用 GRANT 语句授予权限，则会向 GRANT 语句中引用的用户或角色授予该权限。还可以使用 DENY 命令拒绝用户的权限。如果用户被授予权限并拒绝了相同的权限，则 DENY 始终取代该授予，并且用户将被拒绝访问特定对象。

DENY 取代 GRANT 的 T-SQL 示例

在示例中，向用户 Demo 授予了 SELECT 权限，随后拒绝了用户对“dbo.Company”表的 SELECT 权限。用户尝试执行从“dbo.Company”表中选择的查询时，将收到一条错误，指出已拒绝 SELECT 权限。

表和视图权限

表和视图表示数据库中可针对其授予权限的对象。在这些表和视图中，你还可以限制给定安全主体（用户或登录）可以访问的列。 SQL Server 和 Azure SQL 数据库还包括行级安全性，可用于进一步限制访问。

权限	定义
`SELECT`	允许用户查看对象（表或视图）中的数据。拒绝后，将阻止用户查看对象中的数据。
`INSERT`	允许用户向对象中插入数据。拒绝后，将阻止用户将数据插入对象中。
`UPDATE`	允许用户在对象中升级数据。拒绝后，将阻止用户更新对象中的数据。
`DELETE`	允许用户在对象中删除数据。拒绝后，将阻止用户从对象中删除数据。

Azure SQL 数据库和Microsoft SQL Server 具有其他权限，可以根据需要授予、撤销或拒绝这些权限。

权限	定义
`CONTROL`	授予对象所有权限。这允许拥有此权限的用户对对象执行任何他们希望执行的操作，包括删除对象。
`REFERENCES`	授予用户查看查看对象上外键的能力。
`TAKE OWNERSHIP`	允许用户获得对象的所有权。
`VIEW CHANGE TRACKING`	允许用户查看对象的更改跟踪设置。
`VIEW DEFINITION`	允许用户查看对象的定义。

函数和存储过程权限

像表和视图一样，函数和存储过程具有多种权限，可以授予或拒绝这些权限。

权限	定义
`ALTER`	授予用户更改对象定义的能力。
`CONTROL`	授予用户对该对象的所有权限。
`EXECUTE`	授予用户执行对象的能力。
`VIEW CHANGE TRACKING`	允许用户查看对象的更改跟踪设置。
`VIEW DEFINITION`	允许用户查看对象的定义。

EXECUTE AS

EXECUTE AS [user name] 或 EXECUTE AS [login name]（仅在 SQL Server 和 Azure SQL 托管实例中可用）命令允许更改用户上下文。随后，将使用新的上下文（具有授予给该上下文的权限）来执行命令和语句。

如果用户具有某个权限，而用户不再需要该权限，则可以使用 REVOKE 命令删除权限（授予或拒绝）。 revoke 命令将删除任何针对指定用户授予的 GRANT 或 DENY 权限。

所有权链

称为链接的概念适用于权限，允许用户从其他对象继承权限。链接最常见的示例是在执行期间访问表的函数或存储过程。如果该过程与表具有相同的所有者，则即使用户没有直接访问该表的权限，也可以执行存储过程并访问该表。该访问是可用的，因为用户继承了从存储过程访问表的权限，但仅限于存储过程执行期间，且仅在存储过程执行的上下文中。

在此示例中，以数据库所有者或服务器管理员身份运行，将创建一个新用户，并将其添加为新的 SalesReader 角色的成员，然后授予从任何对象中进行选择的权限，并在 Sales 架构中执行任何过程。然后，在 Sales 架构中创建一个存储过程，该存储过程访问 Production 架构中的表。

然后，该示例将内容更改为新用户，并尝试直接从 Production 架构的表中进行选择。

USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

查询导致用户 DP300User1 没有 SELECT 权限的错误，因为用户所属的角色在生产架构中没有任何权限。现在，我们可以尝试执行存储过程：

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

“DP300User1”用户对 Sales 架构中的存储过程具有 EXECUTE 权限，因为用户的角色对 Sales 架构具有 EXECUTE 权限。由于该表的所有者与过程相同，因此我们有一个未中断的所有权链，并且执行将成功并返回结果。

在存储过程中使用动态 SQL 时，不适用权限更改。动态 SQL 中断权限链的原因在于，动态 SQL 在调用存储过程的上下文之外执行。可以通过更改存储过程以使用动态 SQL 执行来查看此行为，如下所示。

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)

SET @sqlstring = '
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales, 
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'

EXECUTE sp_executesql @sqlstring
GO

--

EXECUTE AS USER = 'DP300User1'

EXECUTE Sales.DemoProc

DP300User1 用户收到一条错误信息，提示该用户对 SELECT 表没有权限，就像该用户尝试直接执行查询一样。权限链不适用，执行动态 SQL 的用户帐户必须具有对动态 SQL 中代码正在使用的表和视图的访问权限。

最低特权原则

最低权限的原则非常简单。此概念背后的基本思想是，仅应向用户和应用程序授予其完成任务所需的权限。应用程序应仅具有完成手头任务所需的权限。

例如，如果应用程序通过存储过程访问所有数据，则该应用程序应仅具有执行存储过程的权限，而不能访问表。

动态 SQL

动态 SQL 是一种以编程方式生成查询的概念。动态 SQL 允许在存储过程或查询本身中生成 T-SQL 语句。

SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases

该语句生成一个 T-SQL 语句列表，用于备份服务器上的所有数据库。通常，此生成的 T-SQL 是使用 sp_executesql 或传递给要执行的另一个程序执行的。

反馈

此页面是否有帮助？