在SQL Server和Azure SQL数据库中授予、与授予、撤销和拒绝语句

吴欣
73 阅读7分钟

本文将引导你了解企业内部和Azure SQL数据库的授予、有授予、撤销和拒绝语句。

原理、安全和权限

对于DBA来说，在实例、数据库或对象层面上管理SQL Server数据库权限是一项必不可少的关键任务。你必须保护敏感的、个人的数据，防止未经授权的访问。同样地，你需要为用户或角色分配相关的权限。有时，数据库专业人员会对Azure SQL数据库的数据库级别的安全性感到困惑。DBA的责任是确保只有相关的用户可以访问数据库对象，并授权执行插入、更新、删除、更改、选择等任务。

例如，某些用户可能只需要在特定的对象上有选择的权限。同样地，另一个用户组需要数据库备份、创建数据库、执行DML（插入、更新、删除）操作的权限。

在我们探讨SQL Server中的Grant、With Grant、Revoke和Deny语句之前，让我们了解一下企业内部和Azure SQL数据库的基本安全组件。

  • 原则：委托人是需要访问SQL Server实例、数据库或对象的个人用户、组或进程。它包括服务器级别的委托人以及数据库级别的委托人
  • 安全性：Securable是用于分配权限的服务器和数据库级别的组件。
    • 服务器级的安全。它包括数据库、端点、登录、服务器角色、可用性组。
    • 数据库级别的安全。它包括应用角色、证书、全文目录、数据库角色、模式、用户。
    • 模式级的安全。它包括表、视图、存储过程、函数
  • 权限：权限定义了访问的类型。例如，你可以分配查看记录的权限，或者对一个表执行插入、更新、删除操作的权限。如果你给高层分配权限，它将反映在低层组件中。例如，如果你给了实例级[sysadmin]权限，用户也会得到数据库级别的所有权限

让我们继续探索SQL Server和Azure SQL数据库中的授予、有授予、撤销和拒绝语句。

让我们来看看授予、有授予、撤销和拒绝语句的定义。

  • 授予： 授予语句为委托人提供指定安全区的权限。对于SQL Server和Azure SQL Server来说，授予的语法如下。

    授予<用户、登录者、组>在<可担保物>上的<权限>。

  • 撤销： Revoke 语句会删除之前授予或拒绝的权限。

  • 拒绝： Deny 语句拒绝委托人访问该安全区的许可。例如，如果我们不希望一个用户对一个表进行删除操作，DBA可以拒绝该对象的删除权限。

Azure SQL数据库中授予、拒绝和撤销的演示

让我们实际探讨一下授予、拒绝和撤销，了解它们的行为和区别。下面的脚本创建了一个SQL登录、用户和角色，为[Person]模式提供CONTROL权限。

Use Master
Go
CREATE LOGIN Demologin WITH PASSWORD = 'P@ssw0rd!23'
GO
USE [AdventureWorks2017];
GO
CREATE USER Demologin FROM LOGIN Demologin;
Go
GRANT CONTROL ON SCHEMA::Person TO Demologin;

要查看有效权限，我们可以使用函数fn_my_permissions()。如下图所示, 控制权限给了[Person]模式的选择, 插入, 更新, 删除, 执行, 更改权限.

Azure SQL Database security function

如果我们在一个不同的模式[sales]上检查用户权限, 你不会得到任何行的回报, 因为它在该模式上没有任何权限。

现在，假设你不希望[Demologin]用户在[Person].[Person]表中有任何权限。在这种情况下，我们可以使用下面的查询来拒绝权限。

DENY CONTROL ON [Person].[Person] to DemoLogin
Go
EXECUTE AS USER = 'Demologin'; 
SELECT * FROM fn_my_permissions ('Person.Person', 'OBJECT');

DENY permission statement

看看下面的T-SQL查询，它首先授予选择权，然后在[Person].[Person]表上拒绝它。

Grant Select ON [Person].[Person] to DemoLogin
DENY Select ON [Person].[Person] to DemoLogin
EXECUTE AS USER = 'Demologin'; 
select Top 2 * from Person.person
REVERT;  
GO

用户不能查看表的记录，并且得到一个错误，即对象的选择权限被拒绝。

Grant select permission with Deny statements

但是，如果我们把Azure SQL数据库的Grant和DENY语句的顺序颠倒过来，就可以正常工作。

DENY select ON [Person].[Person] to DemoLogin
Grant select ON [Person].[Person] to DemoLogin
EXECUTE AS USER = 'Demologin'; 
select top 2 *from Person.person
REVERT;  
GO

Check relevant access

假设你拒绝了[Person]模式的CONTROL权限。后来，你授予在[Person].[Person]表上选择记录的权限。用户能够访问该表吗？让我们执行下面的查询并查看结果。

DENY CONTROL ON SCHEMA::Person TO Demologin;
Go
GRANT select ON [Person].[Person] to DemoLogin
Go
EXECUTE AS USER = 'Demologin'; 
select top 2 *from Person.person
REVERT;  
GO

它不能检索记录，因为CONTROL权限在更高的范围（模式级别）被拒绝。在这种情况下，"拒绝 "优先，用户不能访问该表。

Denied Select permission error

同样，如果你在Azure SQL数据库中授予了较高范围的权限，但在较低层次上拒绝了，拒绝总是优先的。如下图所示，在[Person]模式下授予了控制访问权；但是，[Person].[Person]表的选择权限被拒绝。如果你尝试访问该表的记录，你会得到如下的错误。

Higher permission scope

撤销权限

撤销语句会删除Azure SQL数据库先前授予或拒绝的权限。例如，下面的SQL查询将销售模式的选择权限授予[Demologin]。后来，我们撤销了该权限。在这种情况下，Revoke会删除之前授予的权限。

GRANT SELECT ON SCHEMA :: Sales TO Demologin;
GO
REVOKE SELECT ON SCHEMA :: Sales TO Demologin;
GO
EXECUTE AS USER = 'Demologin'; 
select top 2 *from Person.person
REVERT;  
GO

Revoke permissions

让我们考虑另一个场景，你已经在[HumanResources]模式上为[DemoLogin]用户授予了控制权限。后来，你拒绝了一个特定对象[HumanResources].[Employee]的选择权限。正如前面所强调的，拒绝优先；因此，用户不能查看该表的记录。

Check DB access after revoke

我们可以使用REVOKE语句来删除grant或deny语句所给予的权限。

Grant CONTROL ON SCHEMA::[HumanResources] TO Demologin;
Go
DENY SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin;
GO
REVOKE SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin;
GO
EXECUTE AS USER = 'Demologin'; 
select top 2 *from [HumanResources].[Employee]
REVERT;  
GO

Grant or Deny statements

在下面的SQL语句中，我们撤销了对象[HumanResources].[Employee]的选择权限。但是，用户仍然可以查看表的记录，因为Revoke删除了之前授予的选择权限。然而，该用户在更高等级的模式级别上拥有控制权限。因此，由于模式级别的控制权限，该用户可以在SQL Server中运行选择语句。

Grant CONTROL ON SCHEMA::[HumanResources] TO Demologin;
Go
GRANT SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin;
GO
REVOKE SELECT ON OBJECT:: [HumanResources].[Employee] TO Demologin;
GO
EXECUTE AS USER = 'Demologin'; 
select top 2 *from [HumanResources].[Employee]
REVERT;  
GO

使用授权

如果你在SQL Server Management Studio中打开数据库用户属性，进入securable，你会得到一个额外的选项 -**With Grant，**如下图所示。

Check WITH GRANT permission in SSMS

With Grant选项类似于获得权限的安全负责人可以授予其他安全负责人同样的权限。你需要使用WITH GRANT OPTION关键字来达到这个目的。

下面的查询使用WITH GRANT OPTION关键字为指定的存储过程（[HumanResources].[uspUpdateEmployeeHireInfo]）分配执行权限。

Grant execute on [HumanResources].[uspUpdateEmployeeHireInfo] to [DemoLogin] WITH GRANT OPTION;

在SSMS中打开[DemoLogin]的用户属性，以及[HumanResources].[uspUpdateEmployeeHireInfo]的安全属性，它在Grant和With Grant复选框上都有一个勾。

Check database user properties in SSMS

现在，用户[DemoLogin]可以将相同的权限分配给另一个数据库用户。例如，它将指定SP的执行权限分配给用户[Testuser]。

EXECUTE AS USER = 'Demologin'; 
Grant execute on [HumanResources].[uspUpdateEmployeeHireInfo] to [Testuser] ;

该查询成功执行。你可以使用fn_my_permissions()函数来验证权限。

EXECUTE AS USER = 'Testuser'; 
SELECT * FROM fn_my_permissions ('HumanResources.uspUpdateEmployeeHireInfo', 'OBJECT'); 
REVERT;  
GO

Check permissions granted using WITH GRANT Statement

总结

这篇文章探讨了Grant、With Grant、Deny和Revoke语句在控制和管理服务器和数据库级对象的访问方面的区别。你应该有效地管理数据库的安全性，避免将更高层次的权限分配给非预期的人。你可以拒绝某些用户或组的访问，以主动控制访问。

avatar
文章
阅读
粉丝