快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

SQL Server安全性简介-第3部分

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

SQL Server支持部分包含的数据库。这使您可以选择在数据库内部使用密码添加数据库用户。这使数据库更易于移动到另一个实例或参与Always On可用性组。在本文中,Robert Sheldon解释了如何与包含的数据库中的用户一起工作。

到目前为止的系列:

  1. SQL Server安全性简介-第1部分
  2. SQL Server安全性简介-第2部分
  3. SQL Server安全性简介-第3部分
  4. SQL Server安全性简介-第4部分
  5. SQL Server安全性简介-第5部分
  6. SQL Server安全性简介  -第6部分

Microsoft在SQL Server 2012中引入了包含的数据库。包含的数据库是与其他数据库以及托管该数据库的SQL Server实例隔离的数据库。数据库保留了许多自己的元数据,并支持数据库级别的身份验证,从而无需基于服务器的登录。结果,包含的数据库比传统的非包含的数据库具有更高的可移植性。它还可以简化数据库的开发和管理,并使其更易于支持Always On可用性组。

除了少数重要区别外,控制对包含数据库的访问与非包含数据库相似。在本系列的前两篇文章中,我在讨论SQL Server访问控制时简要地涉及了包含数据库的主题。在本文中,我将更深入地研究包含的数据库,并提供几个示例,这些示例说明如何创建包含的数据库用户,如何在多个包含的数据库中重复用户以及如何将数据库用户与服务器级登录名断开链接。

设置环境

要尝试本文中的示例,您需要一个包含所包含数据库的测试环境。在您的系统上,我可以使用SQL Server Management Studio(SSMS)创建一个简单的数据库,并使用WideWorldImporters数据库中的数据填充它,尽管您可以使用任何适合您需要的数据。

如果尚未启用此功能,则必须启用SQL Server实例以支持此功能,然后才能实现它。要使用T-SQL启用包含的数据库,请运行以下EXECUTE语句:

EXECUTE语句调用sp_configure存储过程以将contained database authentication设置设置为1(on)。然后,您应该运行该RECONFIGURE语句以实现更改。

对于本文中的示例ImportSales1,使用以下T-SQL脚本创建包含的数据库:

创建数据库时,可以通过在CONTAINMENT语句中包括该子句CREATE DATABASE并将其值设置为来指定应包含该数据库PARTIAL。默认值为NONE,这将禁用包含的数据库功能。使用该PARTIAL值是因为SQL Server仅支持部分包含的数据库,而不是完全包含的数据库。当前,SQL Server不支持完全包含的数据库。

部分包含的数据库使您可以实现跨越数据库边界的非独立功能。例如,您可以在部分包含的数据库中创建链接到SQL Server登录名的数据库用户。完全包含的数据库不允许使用不包含的功能。

创建ImportSales1数据库之后,可以添加表,然后填充它们,就像使用非包含数据库一样。要支持本文其余部分中的示例,请使用以下T-SQL脚本:

该脚本创建Sales架构,将Customers表添加到架构,然后使用WideWorldImporters数据库中的数据填充表。该SELECT语句的WHERE子句将结果限制为BuyingGroupName值为NOT NULL(我的系统上为402行)的那些行。如果您创建不同的结构或使用不同的数据,请确保根据需要修改其余示例。

创建数据库用户

在SQL Server中,您可以创建特定于所包含数据库且未链接到服务器级登录名的用户。包含的用户使维护包含的数据库和SQL Server实例之间的分隔成为可能,因此在实例之间移动数据库更加容易。

SQL Server支持两种类型的包含用户:带密码的SQL用户Windows用户。基于密码的用户是为数据库用户分配的密码,用于直接向数据库进行身份验证。该用户未与任何Windows帐户关联。

要创建基于密码的用户,必须WITH PASSWORDCREATE USER语句中包含一个子句。例如,以下CREATE USER语句定义一个名为的用户sqluser02,并将密码分配tempPW@56789给该用户:

当基于密码的用户尝试访问包含的数据库时,将在数据库级别(而不是服务器级别)对用户帐户进行身份验证。此外,通过分配的权限授予的所有授权都限于数据库。

包含的第二种数据库用户-Windows用户 -基于Windows帐户(本地或域)。Windows计算机或Active Directory服务对用户进行身份验证,然后将访问令牌传递到数据库。与基于密码的用户一样,数据库中也会根据授予或拒绝权限的方式进行授权。

创建Windows用户时,请确保Windows帐户尚未与登录名关联。如果尝试创建与登录名相同的Windows用户,则SQL Server会自动将该用户与登录名相关联,这意味着将不包含该用户。

在以下示例中,该CREATE USER语句基于winuser02我在win10b计算机上创建的本地帐户定义了一个用户:

每当以这种方式引用Windows帐户时,都必须使用以下格式,包括方括号(除非将帐户括在引号中):

创建包含的用户之后,可以像对任何数据库用户一样授予,拒绝或撤消权限。例如,以下GRANT语句SELECTSales架构许可授予两个用户:

您还可以将包含的用户添加到固定和用户定义的数据库角色中,并为用户定义的角色分配权限。有关创建数据库用户并授予他们权限的更多信息,请参考本系列的第二篇文章。

创建重复的数据库用户

使用包含的数据库时,您可能会发现某些用户需要能够访问多个数据库。对于基于密码的用户(带密码的SQL用户),您应该在每个数据库中创建相同的用户,并为每个用户实例分配相同的密码和安全标识符(SID)。

获取SID的一种方法是sys.database_principals在创建第一个用户后从系统视图中检索它,如以下示例所示:

SELECT语句返回数据库中用户的SID值。返回的值对于该用户将是唯一的,并且将采用类似于以下形式:sqluser02ImportSales1

每当在其他包含的数据库中复制用户时,都应使用此值。要查看其工作原理,可以创建一个与该数据库类似的ImportSales1数据库,但是将其命名为ImportSales2,如以下示例所示:

该脚本创建ImportSales2数据库,将Sales架构添加到数据库,将Customers表添加到架构,并使用来自WideWorldImporters数据库的261行数据填充表。在这种情况下,WHERE子句会过滤NULL值,而不是NOT NULL

接下来,仅这次sqluser02,在ImportSales2数据库中创建用户,包括一个SID子句,该子句从ImportSales1数据库中指定用户的SID ,如以下示例所示:

要创建重复的基于Windows的用户,CREATE USER请使用与ImportSales1数据库中相同的语句:

您还可以使用同GRANT一条语句为两个用户分配SELECTSales架构的权限:

这就是创建重复的基于密码和基于Windows的用户的全部。您可以使用相同的格式在其他包含的数据库中创建重复用户,具体取决于您的数据访问要求。

运行T-SQL查询

要测试在包含的数据库中创建的用户,可以使用EXECUTE ASSSMS中的语句在特定包含的用户的执行上下文中运行查询。例如,以下T-SQL将执行上下文设置给sqluser02用户,SELECTCustomers表运行一条语句,然后使用该REVERT语句返回到原始执行上下文:

在我的系统上,该SELECT语句返回261行,因为该语句在最后指定的数据库的上下文中运行ImportSales2。但是,该sqluser02用户存在于两个数据库中,并具有相同的名称,密码和SID,因此您应该能够Customers在两个数据库中查询该表,如以下示例所示:

不幸的是,如果您尝试运行该语句,将会收到类似于以下内容的错误:

问题不在于您如何设置用户帐户或查询,而在于如何TRUSTWORTHY配置数据库属性。该属性确定SQL Server实例是否信任数据库及其中的内容。尽管这似乎与所包含的数据库无关,但TRUSTWORTHY必须将数据库的属性设置ON为,ImportSales2因为您正在该数据库的上下文中运行查询,但尝试访问数据库中的ImportSales1数据。

默认情况下,该TRUSTWORTHY属性设置OFF为减少某些类型的威胁。您可以在SQL Server帮助主题TRUSTWORTHY数据库属性中找到有关该属性的更多信息。

在设置属性之前,必须确保您在正确的执行上下文中进行工作。如果您一直遵循示例,则会话可能仍在sqluser02用户上下文中进行。这是因为UNION ALL最后一个示例中的查询失败,这意味着该REVERT语句永远不会运行。因此,您当前的SQL Server会话仍在sqluser02用户的执行上下文中运行。要纠正这种情况,只需重新运行该REVERT语句即可。

您随时可以通过调用以下CURRENT_USER函数来验证当前执行上下文:

一旦确定要在正确的用户上下文中使用,请运行以下ALTER DATABASE语句将TRUSTWORTHY属性设置为ON

现在,当您运行以下查询时,它应该从两个表中返回663行:

如果在win10b\winuser02用户的执行上下文下运行查询,您还将收到相同的结果,如以下示例所示:

我在SSMS中创建并运行了所有上述示例。如果自己尝试一下,也可能会使用SSMS或SQL Server数据工具(SSDT)。但是,在现实世界中,大多数连接将通过第三方脚本,实用程序或应用程序进行。在这种情况下,建立与所包含数据库的连接的连接字符串必须将该数据库指定为初始目录。否则,连接将失败。

断开数据库用户与其服务器登录名的链接

由于SQL Server包含的数据库仅部分包含,因此它们可以包含映射到服务器登录名的用户。用户可能在将数据库更改为包含状态之前就已经存在,或者可能在事实之后添加了这些用户。在任何一种情况下,由于数据库的登录连接,数据库的可移植性较差。

SQL Server提供了sp_migrate_user_to_contained系统存储过程,用于快速将数据库用户与其关联的SQL Server登录名断开链接。若要查看其工作原理,请首先在ImportSales1数据库中创建以下用户:

该脚本sqluser03基于sqluser01登录名创建用户,并向用户SELECT授予对Sales模式的权限。(如果sqluser01您的系统上不存在该登录名,那么您也可以使用其他登录名,或者参考本系列的第二篇文章中有关创建sqluser01登录名的信息。)

创建数据库用户之后,可以通过运行以下查询来测试它是否具有预期的访问权限:

查询应返回数据库Customers表中的所有行ImportSales1

如果在SSMS的Object Explorer中查看用户的属性,您会发现该General选项卡将相关的登录名显示为sqluser01,将用户类型显示为SQL user with login,如图1所示。

SQL Server安全性简介-第3部分

图1.基于SQL Server登录名的数据库用户

若要将此用户与SQL Server登录名断开链接,请运行sp_migrate_user_to_contained存储过程,并指定要迁移的数据库用户,如以下示例所示:

sp_migrate_user_to_contained系统存储过程采用以下三个参数:

  • @username参数是数据库用户。
  • @rename参数确定是使用数据库用户名还是服务器登录名。该keep_name值保留数据库用户名。在copy_login_name使用的登录名。
  • @disablelogin参数确定是否禁用登录。在这种情况下,登录将不会被禁用。要禁用登录,请指定disable_login值。

运行该EXECUTE语句后,重新打开该sqluser03用户的属性。您会发现登录不再与该用户相关联,并且用户类型已更改为SQL user with password,如图2所示。

SQL Server安全性简介-第3部分

图2.基于密码的包含数据库用户

当您从登录名取消数据库用户的链接时,SQL Server将登录名的密码分配给该用户,如图所示。作为安全性最佳实践,您应该在此时重置用户密码。如果现在要重新运行以下查询,则应该再次从ImportSales1数据库接收相同的行:

通过取消登录与数据库用户的链接,您可以充分利用所包含数据库固有的可移植性。但是请注意,sp_migrate_user_to_contained存储过程仅适用于SQL Server登录名,不适用于Windows登录名。

保护SQL Server包含的数据库

包含的数据库可以使将数据库从一个SQL Server实例移动到另一个实例变得更加容易,而不必担心在这些实例之间复制登录信息。但是,在实现包含数据库之前,您应该熟悉Microsoft的安全准则,如SQL Server帮助主题“ 包含数据库的安全最佳实践”中所述。本主题说明了控制对所包含数据库的访问的一些微妙方面,尤其是在角色和权限方面。

除了这些准则之外,您还会发现控制对包含数据库的访问的工作原理与非包含数据库非常相似。您可能需要在多个包含的数据库中复制用户或将数据库用户与服务器登录名断开链接,但这是相对简单的过程,就像通常控制访问一样。一旦了解了基础知识,就可以轻松支持更复杂的方案。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638