SQL Server 中的公共角色

数据库角色与 Windows 组类似 - 管理员不是单独撤销或授予每个用户访问权限，而是通过授予或撤销角色权限以及更改角色成员身份来管理访问权限。使用角色可以更轻松地准确地授予和撤销数据库用户的权限。由于多个用户可以是 SQL 数据库角色的成员，因此您可以轻松地同时管理整组用户的权限。

在这篇文章中，我们将解释 Microsoft SQL Server 中的公共角色以及与其相关的一些最佳实践。 （我们在这里故意使用小写字母，因为这是公共角色在 SQL Server 世界中的拼写方式，与 Oracle 世界不同。）

SQL Server 中的角色类型

Microsoft SQL Server 提供多种类型的预构建角色：

• 固定服务器角色——它们被称为“固定”服务器角色，因为除了公共角色之外，它们不能被修改或删除
• 固定数据库角色
• 应用程序角色 - 数据库主体，应用程序可以使用它以自己的一组权限运行（默认情况下禁用）
• 用户定义的角色（从 SQL Server 2012 开始）- 只有服务器级别的权限可以添加到用户定义的角色中

公共角色的定位

所有数据库平台都带有一个名为 public 的预定义角色，但该角色的实现因平台而异。在 SQL Server 中，公共角色是固定服务器角色的一部分，可以向 SQL Server 公共角色权限授予、拒绝或撤销权限。

创建 SQL Server 登录名后，公共角色将分配给该登录名并且无法撤销。如果服务器主体未授予或拒绝对安全对象的特定权限，则登录将自动继承授予公共角色的该对象的权限。

分配给公共角色的权限

为了维护 SQL Server 安全并遵守许多法规（包括 PCI DSS 和 HIPAA），您需要了解分配给每个用户的所有服务器和数据库级角色。让我们使用 Management Studio Transact-SQL 查询查看分配给公共角色的服务器级权限：

SELECT sp.state_desc as "Permission State", sp.permission_name as "Permission",
sl.name "Principal Name",sp.class_desc AS "Object Class", ep.name "End Point"
FROM sys.server_permissions AS sp
  JOIN sys.server_principals AS sl
  ON sp.grantee_principal_id = sl.principal_id
  LEFT JOIN sys.endpoints AS ep
  ON sp.major_id = ep.endpoint_id
WHERE sl.name = 'public';

如上表所示，public角色只分配了5个服务器级别的权限。请注意，VIEW ANY DATABASE 权限并不授予用户访问任何数据库对象的权限；它只允许他们列出 SQL Server 实例中的所有数据库。因此，如果您创建一个新的登录名，并且没有分配其他角色或权限，则该用户只能登录该实例，而无法执行任何其他操作。

为 SQL Server 登录分配默认数据库时的权限

现在，让我们通过为该用户分配默认数据库来创建使用 SQL 身份验证的 SQL Server 登录名。

USE [master]
GO
CREATE LOGIN [SQTest] WITH PASSWORD=N'nhggLboBn6SHolSWfipjzO/7GYw8M2RMbCt1LsCTK5M=', DEFAULT_DATABASE=[SBITS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

如果我们以该用户身份登录并列出该用户的默认数据库 SBITS 的所有数据库级别权限，我们就可以看到他们拥有哪些权限。如下所示，用户 Stealth 对 SBITS 数据库没有任何权限，即使这是用户的默认数据库。换句话说，仅仅因为将默认用户数据库分配给用户登录名，并不意味着该用户将能够看到数据库对象或数据。

为了验证这一点，我们可以使用以下脚本：

EXECUTE AS LOGIN= 'SQTest';
GO
USE SBITS
GO
SELECT dp.state_desc AS "Class Description", dp.permission_name AS "Permission Name",
SCHEMA_NAME(ao.schema_id) AS "Schema Name", ao.name AS "Object Name"
FROM sys.database_permissions dp
 LEFT JOIN sys.all_objects ao
  ON dp.major_id = ao.object_id
 JOIN sys.database_principals du
  ON dp.grantee_principal_id = du.principal_id
WHERE du.name = 'TestLoginPerms'
 AND ao.name IS NOT NULL
ORDER BY ao.name;
REVERT

主数据库上公共角色继承的权限

由于默认情况下，用户登录 Stealth 是 public 角色的一部分，因此让我们看看 public 角色对 master 数据库继承的权限。

USE master;
GO
 SELECT sp.state_desc AS "Permission State", sp.permission_name AS "Permission",
 SCHEMA_NAME(ao.schema_id) AS 'Schema', ao.name AS "Object Name"
 FROM sys.database_permissions sp
 LEFT JOIN sys.all_objects ao
  ON sp.major_id = ao.object_id
  JOIN sys.database_principals dp
  ON sp.grantee_principal_id = dp.principal_id
 WHERE dp.name = 'public'
 AND ao.name IS NOT NULL
 ORDER BY ao.name

在 SQL Server 2016 中，授予 public 角色的 master 数据库中存储了 2,089 个权限。虽然这看起来令人畏惧，但它们都是 SELECT 权限，并且不允许用户 Stealth 在 master 数据库中进行任何更改。但是，最好根据组织中的安全策略撤销某些权限。但是，撤销它们时要小心，因为有些是用户在某些情况下正常操作所必需的。

简化角色审核和管理

考虑使用 StealthAUDIT，而不是求助于复杂的自定义脚本来一次找出与一个实例中的公共角色相关的问题。该数据访问治理平台可以枚举所有 SQL Server 角色和权限，包括 SQL Server 公共角色和 SQL Server 公共数据库角色，并生成开箱即用的详细权限报告。它提供了企业中所有 SQL Server 公共角色的单一管理平台视图，并帮助您只需单击一个按钮即可修复任何问题。

SQL Server 中公共角色的最佳实践

当涉及 SQL Server 中的公共角色时，我们建议采用以下最佳实践：

• 在任何情况下，都不要向公共角色授予默认权限之外的任何其他权限。如有必要，请使用用户定义的角色。
• 不要修改public角色的服务器级权限，因为这样做可能会阻止用户连接到数据库。
• 每次升级 SQL Server 时都要检查公共权限，因为 Microsoft 经常对公共角色进行更改。

常问问题

公共数据库的作用是什么？

授予公共数据库角色的权限由每个数据库用户继承。

SQL Server 中的 public 角色有哪些权限？

每个 SQL Server 登录名都属于公共服务器角色。当服务器主体尚未被授予或拒绝对安全对象的特定权限时，用户会继承对该对象授予的权限给公共角色。