SQL Server 中的 Public Role

数据库角色类似于 Windows 组——与其针对每个用户单独撤销或授予访问权限，管理员通过从角色授予或撤销权限，并通过更改角色成员资格来管理访问。使用角色可以更轻松、准确地为数据库用户授予或撤销权限。而且由于多个用户都可以成为某个 SQL 数据库角色的成员，你可以一次性轻松管理整个用户组的权限。

在这篇文章中，我们将解释 Microsoft SQL Server 中的 public 角色以及与之相关的一些最佳实践。（我们刻意在这里使用小写字母，因为在 SQL Server 领域中 public 角色的拼写就是这样，而不是像 Oracle 那样。）

SQL Server 中的角色类型

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

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

Public 角色在其中的作用

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

当创建 SQL Server 登录时，会将 public 角色分配给该登录，并且无法撤销。如果服务器主体未对可保护对象获得或拒绝特定权限，则该登录会自动继承 public 角色已被授予的、针对该对象的权限。

授予给 Public 角色的权限

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

      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 角色。请注意，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，也并不在 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 中，授予公用角色的 master 数据库中存储了 2,089 个权限。虽然看起来可能令人畏惧，但这些权限全部都是 SELECT 权限，并且不允许用户 Stealth 对主数据库进行任何更改。不过，建议根据贵组织的安全策略撤销其中部分权限。由于在某些情况下，部分权限可能是用户进行正常操作所必需的，因此在撤销时请务必谨慎。

简化角色审查与管理

与其为找出与 public 角色相关的问题而在每次仅查看一个实例时使用复杂的自定义脚本，不如考虑使用 Netwrix Access Analyzer。该数据访问治理平台可以枚举所有 SQL Server 角色和权限，包括 SQL Server public 角色和 SQL Server public 数据库角色，并可直接生成详细的权限报告（无需额外配置）。它提供对整个企业中所有 SQL Server 里 public 角色的单一界面视图，并帮助你只需点击一下即可修复任何问题。

SQL Server 中 public 角色的最佳实践

在 SQL Server 的 public 角色方面，我们建议遵循以下最佳实践：

• 无论在任何情况下，都不要在默认权限（default privileges）之外向 public 角色授予任何额外权限。如有必要，请使用用户自定义角色。
• 不要修改分配给公用角色（public role）的服务器级权限，因为这样可能会导致用户无法连接到数据库。
• 每次升级 SQL Server 时都要检查公用权限，因为 Microsoft 经常会对 public 角色进行更改。

常见问题

什么是公用数据库角色（public database role）？

授予 public 数据库角色（public database role）的权限会被每个数据库用户继承。

在 SQL Server 中，public 角色拥有哪些权限？

每个 SQL Server 登录都属于 public 服务器角色。当服务器主体在可保护对象（securable object）上未被授予或拒绝特定权限时，用户将继承该对象上授予给 public 角色的权限。