在 Analytics Platform System (PDW) 中管理权限

本文介绍管理 SQL Server PDW 数据库权限的要求和选项。

数据库引擎权限基础知识

SQL Server PDW 上的数据库引擎权限通过登录名在服务器级别进行管理,在数据库级别通过数据库用户和用户定义的数据库角色进行管理。

登录 登录名是用于登录到 SQL Server PDW 的单个用户帐户。 SQL Server PDW 支持使用 Windows 身份验证和 SQL Server 身份验证登录。 Windows 身份验证的登录名可以是来自任何受 SQL Server PDW 信任的域的 Windows 用户或 Windows 组。 SQL Server 身份验证登录名由 SQL Server PDW 定义和身份验证,必须通过指定密码来创建。

sysadmin 固定服务器角色(如 sa 登录名)的成员可以连接到数据库,而无需映射到数据库用户。 它们映射到 dbo 用户。 数据库的所有者也映射为 dbo 用户。

服务器角色 有四个特殊的服务器角色,其中包含一组预配置的角色,可提供方便的服务器级权限组。 sysadmin、MediumRC、LargeRC 和 XLargeRC 固定服务器角色是当前在 SQL Server PDW 中实现的唯一服务器角色。 sa 登录名是 sysadmin 固定服务器角色的唯一成员,不能将其他登录名添加到 sysadmin 角色。 可以将登录名授予 CONTROL SERVER 权限,这与 sysadmin 固定服务器角色类似(但并不完全相同)。 使用 ALTER SERVER ROLE 将成员添加到其他服务器角色。 SQL Server PDW 不支持用户定义的服务器角色。

数据库用户 通过在数据库中创建数据库用户并将其映射到登录名,可以获得访问数据库的权限。 通常,数据库用户名与登录名相同,但不必相同。 每个数据库用户均会映射到单个登录名。 登录名只能映射到一个数据库中的用户,但可以在多个不同数据库中作为数据库用户映射。

固定数据库角色 固定数据库角色是一组预配置的角色,可提供方便的数据库级权限组。 可以使用 sp_addrolemember 过程将数据库用户和用户定义的数据库角色添加到固定数据库角色。 有关固定数据库角色的详细信息,请参阅 固定数据库角色。

用户定义的数据库角色 具有 CREATE ROLE 权限的用户可以创建新的用户定义的数据库角色来表示具有常见权限的用户组。 通常会对整个角色授予或拒绝权限,从而简化权限管理和监视。

使用 GRANT 语句将权限授予安全主体(登录名、用户和角色)。 使用 DENY 命令显式拒绝权限。 使用 REVOKE 语句删除了以前授予或拒绝的权限。 权限可累积,其中用户将获得授予用户、登录名和任何组成员身份的所有权限;但是,任何权限拒绝将替代所有授予。

下面的示例展示配置权限的常用方法和建议方法。

  1. 如果使用 Windows 身份验证,请为每个将连接到 SQL Server PDW 的 Windows 用户或 Windows 组创建登录名。 如果使用 SQL Server 身份验证,请为将连接到 SQL Server PDW 的每个人创建登录名。

  2. 在所有必要的数据库中为每个登录名创建一个数据库用户。

  3. 创建一个或多个用户定义的数据库角色,每个角色表示类似的函数。 例如财务分析师和销售分析师。

  4. 将数据库用户添加到一个或多个用户定义的数据库角色。

  5. 将权限授予用户定义的数据库角色。

登录名是服务器级对象,可以通过查看 sys.server_principals列出。 只能向服务器主体授予服务器级权限。

用户和数据库角色是数据库级对象,可以通过查看 sys.database_principals列出。 只能向数据库主体授予数据库级权限。

默认权限

以下列表描述了默认权限:

  • 使用语句CREATE LOGIN创建登录名时,登录名会收到 CONNECT SQL 权限,允许登录名连接到 SQL Server PDW。

  • 使用 CREATE USER 语句创建数据库用户时,用户会收到 CONNECT ON DATABASE::<database_name> 权限,允许登录名以用户身份连接到该数据库。

  • 默认情况下,所有主体(包括 PUBLIC 角色)均没有显式或隐式权限,因为隐式权限继承自显式权限。 因此,当不存在显式权限时,也不存在隐式权限。

  • 当登录名成为对象或数据库的所有者时,该登录名始终对对象或数据库拥有所有权限。 所有权权限不显示为显式权限。 和GRANTREVOKEDENY语句对所有权权限没有影响。 可以通过使用 ALTER AUTHORIZATION 语句来更改所有权。

  • sa 登录名对设备具有所有权限。 与所有权权限类似,sa 权限不能更改,并且不显示为显式权限。 GRANT、REVOKE 以及 DENY 语句对 sa 权限没有任何影响。

  • 默认情况下,PUBLIC 服务器角色不接收任何权限,并且不会从其他服务器角色继承权限。 可以使用 GRANT、REVOKE 和 DENY 语句向 PUBLIC 服务器角色授予显式权限。

  • 事务不需要权限。 所有主体都可以运行 BEGIN TRANSACTION、 COMMIT 和 ROLLBACK 事务命令。 但是,主体必须具有相应的权限才能在事务中运行每个语句。

  • USE 语句不需要权限。 所有主体都可以在任何数据库上运行 USE 语句,但若要访问某数据库,则必须在该数据库中拥有用户主体或者启用了来宾用户。

PUBLIC 角色

所有新的设备登录都会自动归属于 PUBLIC 角色。 PUBLIC 服务器角色具有以下特征:

  • 默认情况下,PUBLIC 服务器角色没有权限。

  • 所有主体都是 PUBLIC 服务器角色的成员,PUBLIC 服务器角色不是另一个服务器角色的成员。

  • PUBLIC 服务器角色无法继承隐式权限。 必须显式授予对 PUBLIC 角色的任何权限。

确定权限

登录名是否有权执行特定动作取决于授予或拒绝给登录名、用户和用户所属角色的权限。 服务器级权限(例如 CREATE LOGIN 和 VIEW SERVER STATE)可用于服务器级主体(登录名)。 数据库级权限(如从表 选择 或过程 上的 EXECUTE )可用于数据库级主体(用户和数据库角色)。

隐式和显式权限

显式权限是通过GRANT或DENY语句授予某个主体的GRANT或DENY权限。 数据库级权限列在 sys.database_permissions 视图中。 服务器级权限列在 sys.server_permissions 视图中。

隐式权限是主体(登录名或服务器角色)继承的GRANT或DENY权限。 可以通过以下方式继承权限。

  • 如果主体是角色的成员,即使主体没有显式 GRANT 或 DENY 权限,主体也可以继承角色的权限。

  • 如果主体对其中一个对象具有权限(例如表的架构或对整个数据库的权限),则主体可以继承对从属对象(如表)的权限。

  • 主体可以通过拥有包含从属权限的权限来继承权限。 例如,ALTER ANY USER 权限同时包括 CREATE USER 和 ALTER ON USER::<name> 权限。

执行操作时确定权限

确定分配给主体的权限的过程很复杂。 确定隐式权限时,复杂性会发生,因为主体可以是多个角色的成员,并且权限可以通过角色层次结构中的多个级别传递。

以下列表描述了用于确定权限的一般规则:

  • 所有权意味着权限。

    对象所有者对对象拥有所有权限。 同样,数据库所有者对数据库拥有所有权限,并且对数据库中的对象拥有所有权限。 无法更改这些权限。

  • 权限可以在服务器角色成员身份的层次结构中跨多个级别继承。

    例如,假设你有以下情况:

    • Login David 是数据库角色 PerfAnalysts 的成员。

    • PerfAnalysts 是数据库角色 Production 的成员。

    • David 和 PerfAnalysts 对 Customer 表没有 SELECT 权限。 权限已吊销或从未显式授予。

    • 生产对 Customer 表具有 SELECT 权限。

    在这种情况下,PerfAnalysts 将从 Production 继承 GRANT 对 Customer 表的权限,David 将从 Production 继承 GRANT 对 Customer 表的权限。

  • 当权限发生冲突时,DENY 优先于 GRANT。

    例如,假设 login David 对 Customer 表没有权限,并且是两个数据库角色 -dbgroup1 的成员,该角色具有 DENY Customer 表的权限,而 dbgroup2 具有 GRANT Customer 表的权限。 在这种情况下,David 将继承 Customer 表上的 DENY 权限。 这都是如此,无论角色是显式获取权限还是隐式获取权限。

审核权限

若要研究用户的权限,请检查以下内容。

  • 执行以下查询以确定哪些登录名是系统管理员。

    SELECT SPLogins.name, 'is a member of ', SPRoles.name 
    FROM sys.server_role_members AS SRM 
    JOIN sys.server_principals AS SPRoles 
        ON SRM.role_principal_id = SPRoles.principal_id 
    JOIN sys.server_principals AS SPLogins 
        ON SRM.member_principal_id = SPLogins.principal_id;
    
  • 执行以下查询以确定哪些登录名已被授予显式权限。

    SELECT name, 'has the ', state_desc , permission_name, ' permission'
    FROM sys.server_permissions AS SP
    JOIN sys.server_principals AS SPRoles 
        ON SP.grantee_principal_id = SPRoles.principal_id;
    
  • 在用户数据库中执行以下查询,以确定哪些数据库用户是数据库角色的成员。

    SELECT DPUsers.name, 'is a member of ', DPRoles.name  
    FROM sys.database_role_members AS DRM
    JOIN sys.database_principals AS DPRoles 
        ON DRM.role_principal_id = DPRoles.principal_id
    JOIN sys.database_principals AS DPUsers
        ON DRM.member_principal_id = DPUsers.principal_id;
    
  • 在用户数据库中执行以下查询,以确定已授予或拒绝特定权限的数据库用户和角色。 你将需要查询其他视图,例如 sys.objects 和 sys.schemas,以辨认由 major_id 标识的项。

    SELECT DPUsers.name, 'has the ', permission_name, 
    'permission on the item described as class = ', class, 'id = ', major_id
    FROM sys.database_permissions AS DP
    JOIN sys.database_principals AS DPUsers
        ON DP.grantee_principal_id = DPUsers.principal_id;
    

数据库权限最佳做法

  • 在实际可行的最精细级别授予权限。 在表或视图级别授予权限可能会变得不可管理。 但是,在数据库级别授予权限可能过于宽松。 如果数据库设计为使用架构来定义工作边界,则对架构的授予权限可能是表级别与数据库级别之间的适当妥协。

  • 向角色授予权限,而不是授予用户或登录名的权限。 通过使用角色而不是用户来管理权限,可以轻松地通过将用户或登录帐户移入或移出角色来快速授予或撤销其权限集。 当函数从一个人传递到另一个人时,当角色成员身份发生更改时,权限可以在角色级别保持不变。

  • 根据作业功能授予角色权限,并根据由公司组执行操作时所需的作业功能角色合并而成的更高级别组角色授予权限。

  • 每个最终用户都应具有唯一的登录名。 不允许用户共享登录名。 为每个用户提供登录名可确保审核线索并简化权限管理。

固定数据库角色

SQL Server 提供预配置的(固定)数据库级角色,可帮助你管理服务器上的权限。 预先配置的角色是固定的,你无法更改分配给它们的权限。 还可以创建用户定义的数据库角色。 可以更改分配给用户定义的数据库角色的权限。

角色是将其他主体归为一组的安全主体。 数据库角色在权限范围上是针对整个数据库的。 可将数据库用户和其他数据库角色添加为数据库角色的成员。 固定的数据库角色不能相互添加。 (“角色”类似于 Windows 操作系统中的“组”。)

有 9 个固定数据库角色。

  • db_owner

  • db_securityadmin

  • db_accessadmin

  • db_backupoperator

  • db_ddladmin

  • db_datawriter

  • db_datareader

  • db_denydatawriter

  • db_denydatareader

固定数据库角色的权限

固定服务器角色和固定数据库角色的系统是起源于 20 世纪 80 年代的旧系统。 固定角色仍受支持,在用户很少且安全需求简单的环境中非常有用。 从 SQL Server 2005 开始,已创建更详细的授予权限系统。 此新系统更精细,为授予和拒绝权限提供更多选项。 更精细的系统的额外复杂性使得学习更加困难,但大多数企业系统应授予权限,而不是使用固定角色。 下图显示了与每个固定数据库角色关联的权限。 此 SQL Server 图形中的所有权限在 APS 中均不可用(或必需)。

APS 安全固定数据库角色

  • 若要创建用户定义的角色,请参阅 CREATE ROLE。

固定服务器角色

固定服务器角色由 SQL Server 自动创建。 SQL Server PDW 对 SQL Server 固定服务器角色的有限实现。 只有 sysadmin 和 public 具有成员用户登录权限。 setupadmin 和 dbcreator 角色由 SQL Server PDW 在内部使用。 无法从任何角色添加或删除其他成员。

“sysadmin” 固定服务器角色

sysadmin 固定服务器角色的成员可以在服务器中执行任何活动。 sa 登录名是 sysadmin 固定服务器角色的唯一成员。 无法将其他登录名添加到 sysadmin 固定服务器角色。 授予 CONTROL SERVER 权限近似于 sysadmin 固定服务器角色的成员身份。 以下示例向名为 Fay 的登录名授予 CONTROL SERVER 权限。

USE master;
GO
GRANT CONTROL SERVER TO Fay;

Important

CONTROL SERVER 权限提供对 SQL Server PDW 的几乎完全控制。 尽可能为登录提供更精细的权限。 例如,请考虑授予 VIEW SERVER STATE、ALTER ANY LOGIN、VIEW ANY DATABASE 或 CREATE ANY DATABASE 权限。

公共服务器角色

可以连接到 SQL Server PDW 的每个登录名都是 公共 服务器角色的成员。 所有登录名都继承授予对任何对象 公开 的权限。 仅当希望该对象可供所有用户使用时,才对对象分配 公共 权限。 不能更改 公共 角色的成员身份。

Note

public 的实现方式与其他角色不同。 由于所有服务器主体都是公共角色的成员,因此公共角色的成员身份未列在sys.server_role_members DMV 中。

固定服务器角色与授予权限

固定服务器角色和固定数据库角色的系统是起源于 20 世纪 80 年代的旧系统。 固定角色仍受支持,在用户很少且安全需求简单的环境中非常有用。 从 SQL Server 2005 开始,已创建更详细的授予权限系统。 此新系统更精细,为授予和拒绝权限提供更多选项。 更精细的系统的额外复杂性使得学习更加困难,但大多数企业系统应授予权限,而不是使用固定角色。