Azure SQL数据库服务器中的固定服务器角色介绍

吴欣
243 阅读7分钟

本文用于讨论Azure SQL数据库服务器中的固定服务器角色。

简介

Azure SQL数据库在PAAS（平台即服务）中提供关系型的微软SQL服务器。该数据库的功能类似于企业内部的SQL数据库（功能有限），但管理员不能完全控制数据库的配置。在企业内部环境中，我们确实有SQL实例和该实例上存在的基础数据库。你可以从实例、数据库层面控制用户的权限。

例如，你在企业内部的实例中有几个角色，如Sysadmin、Serveradmin、Setupadmin、Securityadmin。

On-Prem SQL Server

一旦我们部署了Azure SQL DB，它就需要一个逻辑的Azure SQL服务器。逻辑服务器不允许数据库管理员管理类似于企业内部的SQL服务器的权限。用户不能对逻辑服务器进行实例级的配置。在Azure SQL Server中，你无法获得安全角色的访问权。尝试在SSMS中连接到Azure DB，它没有在实例级的安全标签中显示服务器角色。

如果在SSMS中连接到主数据库，安全显示现有的登录账户。

Security logins

但是，直接连接到具体的Azure SQL DB，会显示数据库内的安全控制。

Azure SQL Database controls

你可以在数据库层面上管理安全、登录。然而，为了简化权限，Azure提供了一些固定的服务器级角色。 这些服务器级别的角色可以帮助DBA在逻辑服务器上管理用户的权限。

要求

要跟上这篇文章，你应该部署一个Azure SQL数据库。你还需要SQL Server管理用户连接到主数据库或Azure数据库并执行特定的查询。如果你不了解Azure SQL，请参考SQL Azure类别。

让我们探讨一下Azure SQL数据库中的固定服务器角色。

内置服务器级角色 Azure SQL Server

Azure服务器内置的服务器角色有前缀##MS和后缀##，这样就可以把用户和这些角色区分开来。这些内置的服务器角色帮助你管理几个登录，他们的权限，而不提供服务器管理员或AD管理员的权限。它帮助管理员使用角色分离来遵守最小特权原则。

下表列出了这些角色和它们的描述。

  1. ##MS_DefinitionReader##

    该角色允许用户读取用户所在的Azure数据库的VIEW ANY DEFINITION、VIEW DEFINITION所涵盖的目录视图。

    • **服务器级别的权限。**查看任何数据库，查看任何定义，查看任何安全定义
    • **数据库级别的权限。**查看定义、查看安全定义

  2. ##MS_ServerStateReader##

    在这个固定的服务器角色中，用户可以执行所有的动态管理视图（DMV）和VIEW SERVER STATE和VIEW DATABASE STATE 所涵盖的功能，在Azure数据库中，该角色包含一个用户账户。

    • **服务器级别的权限。**查看服务器状态、查看服务器性能状态、查看服务器安全状态
    • **数据库级别的权限。**查看数据库状态，查看数据库性能状态，查看数据库安全状态

  3. ##MS_ServerStateManager##

    ##MS_ServerStateManager#角色包含#MS_ServerStateReader#的权限和ALTER SERVER STATE的额外权限。它允许用户执行DBCC命令 - ), DBCC SQLPERF(),DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF()。

    • **服务器级别的权限。**更改服务器状态，查看服务器状态，查看服务器性能状态，查看服务器安全状态
    • **数据库级别的权限。**查看数据库状态，查看数据库性能状态，查看数据库安全状态

***注意：*用户（管理员）不能修改分配给固定服务器角色的权限。

授予固定服务器角色的权限不能改变，而且这些角色不能有其他固定角色作为成员。Azure服务器角色的工作方式与内部SQL实例中的角色类似。用户服务器角色的权限可以传播到数据库的权限。但是，登录必须存在于相应的Azure SQL数据库中。

比方说，一个用户[Demo1]拥有服务器级别的角色**##MS_ServerStateReader##。角色#MS_ServerStateReader##**包含权限VIEW SERVER STATE。 用户[Demo1]在Azure DB [azuresqldemo]中有一个登录。因此，用户[Demo1]在主数据库和[azuresqldemo]数据库都有数据库级别的权限-VIEW DATABASE STATE权限。

注意。 你不能在SSMS GUI中处理服务器级别的角色。你可以使用T-SQL脚本创建、编辑或删除这些组中的用户。

下面的部分创建了一个新的SQL登录用户，并通过定义的固定服务器角色提供权限。它将使你更好地了解这种内置角色的工作方式。

创建Azure SQL数据库登录和用户

要在Azure SQL数据库中创建一个新的SQL数据库登录，请使用管理员用户凭证连接到Azure SQL服务器。下面的脚本在主数据库中创建了一个新的登录[DemoLogin1]和用户。

 CREATE LOGIN DemoLogin1 WITH PASSWORD = 'P@ssw0rd!23';
 CREATE USER DemoLogin1 FROM LOGIN DemoLogin1

现在，使用下面的ALTER SERVER ROLE将该用户添加为##MS_ServerStateReader##角色的成员。

ALTER SERVER ROLE ##MS_ServerStateReader##
  ADD MEMBER DemoLogin1;  
GO

我们可以使用sys.server_role_members和sys.sql_logins目录视图来返回具有任何固定服务器角色成员资格的SQL登录。

SELECT
  sql_logins.name                 AS MemberPrincipalName
  ,roles.name                     AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
  ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins 
  ON server_role_members.member_principal_id = sql_logins.principal_id;

如下所示，登录[DemoLogin1]是内置服务器角色##MS_ServerStateReader的一部分。

View built-in server roles

VIEW SERVER STATE允许用户执行服务器范围的动态管理视图和函数，如sys.dm_os_wait_stats。

使用SSMS连接到主数据库并运行以下查询。你会得到预期的结果。

Connect using SSMS

 SELECT * FROM sys.dm_os_wait_stats

OS waits

我们没有将这个用户添加到Azure SQL数据库[azuredemodatabase]中。因此，如果你试图访问该数据库，会出现以下错误。

Access error

要将该登录者添加为Azure SQL数据库用户，请在管理员安全上下文下运行以下CREATE USER语句。

  CREATE USER DemoLogin1 FROM LOGIN DemoLogin1

该用户自动获得分配给服务器级别角色##MS_ServerStateReader##的权限。因此，它可以执行需要VIEW DATABASE STATE权限的查询。例如，你可以使用数据库范围的数据库管理视图sys.dm_db_log_info来返回数据库中虚拟日志文件的数量，如下所示。

SELECT [name], COUNT(l.database_id) AS 'vlf_count' 
  FROM sys.databases s
  CROSS APPLY sys.dm_db_log_info(s.database_id) l
  GROUP BY [name]
  HAVING COUNT(l.database_id) > 1

Log status

用于检查角色成员资格的查询

IS_SRVROLEMEMBER()函数用于检查用户访问情况

Azure SQL数据库支持函数IS_SRVROLEMEMBER()来检查SQL Server登录者是否是指定服务器角色的成员。

  • 要检查登录用户的角色**##MS_ServerStateReader##**的服务器角色成员。
    • 值1：是，该用户是指定角色的成员
    • 值0：否，用户不是指定角色的成员
 SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##') AS LoginIsAMemberofGroup

check role memberships

注意：Azure SQL Server的主数据库不支持IS_SRVROLEMBER()函数。

sys.fn_my_permissions()

要返回服务器或数据库级别上的固定服务器角色权限，可以使用函数 - sys.fn_my_permissions()。

例如，在我的演示中，用户[demologin]是固定服务器角色##MS_ServerStateReader##的成员。因此，该查询返回以下输出。

 SELECT * FROM sys.fn_my_permissions(NULL, 'Server')

Memberships

SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')

Database permissions

服务器级角色的局限性

  • Azure可能需要5分钟才能使角色分配生效。
  • 如果你已经连接到Azure数据库，你需要重新打开连接，为用户分配服务器或数据库角色权限。
  • 为了减少等待时间（最多5分钟），Azure SQL管理员可以运行命令 -DBCC FLUSHAUTHCACHE。 它清空了SQL数据库认证缓存，其中包含了关于登录、当前用户数据库的防火墙规则的信息。但是，你仍然需要重新连接Azure DB。你可以参考微软的文档以了解更多细节。

注意：你不能执行dbcc flushauthcache因为主数据库有登录和防火墙规则的信息（物理存储）。

总结

本文探讨了逻辑Azure SQL Server的内置服务器角色。数据库管理员可以使用这些角色来管理用户的权限。一旦我们在所需的角色中添加一个登录/用户，它就会自动分配服务器和数据库级别的权限。但是，你应该在Azure SQL数据库中创建数据库用户，以访问具有指定权限的数据库。

avatar
文章
阅读
粉丝