适用于:SQL Server
Azure SQL 数据库
Azure SQL 托管实例
Azure Synapse Analytics
分析平台系统 (PDW)
Microsoft Fabric 预览版中的 SQL 数据库
本文回顾一些基本安全概念,然后介绍权限的典型实现。 数据库引擎中的权限通过登录名和服务器角色在 服务器级别 进行管理,在 数据库级别 通过数据库用户和数据库角色进行管理。
Microsoft Fabric 预览版中的 SQL 数据库和 SQL 数据库在每个数据库中提供相同的选项,但服务器级别权限不可用。
在 SQL 数据库中,请参阅 教程:保护 Azure SQL 数据库中的数据库。 建议采用 Microsoft Entra ID 身份验证。 有关更多信息,请参阅教程:使用 Microsoft Entra 创建 Microsoft Entra 用户。
在 Microsoft Fabric 预览版的 SQL 数据库中,数据库用户唯一支持的身份验证方法是Microsoft Entra ID。 未提供服务器级角色和权限。 有关详细信息,请参阅 Microsoft Fabric 中 SQL 数据库的授权。
Note
Microsoft Entra ID 以前称为 Azure Active Directory (Azure AD)。
Security principals
安全主体是 SQL Server 使用的标识,可以为其分配权限以执行操作。 安全主体通常是人员或人员组,但可以是假装是人的其他实体。 可以使用本文中所示的 Transact-SQL 示例或使用 SQL Server Management Studio 创建和管理安全主体。
Logins
登录帐号是用于登录到 SQL Server 数据库引擎的单个用户帐户。 SQL Server 和 SQL 数据库支持基于 Windows 身份验证的登录名,以及基于 SQL Server 身份验证的登录名。 有关这两种类型的登录的信息,请参阅 “选择身份验证模式”。
固定服务器角色
在 SQL Server 中, 固定服务器角色 是一组预配置的角色,可提供方便的服务器级权限组。 可以使用 ALTER SERVER ROLE ... ADD MEMBER
语句将登录名添加到角色。 有关详细信息,请参阅 ALTER SERVER ROLE。 SQL 数据库不支持固定服务器角色,但在数据库(master
和 loginmanager)中具有两个角色,这些角色类似于服务器角色。
用户定义的服务器角色
在 SQL Server 中,可以创建自己的 服务器角色 并为其分配服务器级权限。 可以使用 ALTER SERVER ROLE ... ADD MEMBER
语句将登录名添加到服务器角色。 有关详细信息,请参阅 ALTER SERVER ROLE。 SQL 数据库不支持用户定义的服务器角色。
Database users
若要授予对数据库登录名的访问权限,请在该数据库中创建 数据库用户 ,并将数据库用户映射到登录名。 数据库用户名通常与约定的登录名相同,但不必相同。 每个数据库用户均会映射到单个登录名。 只能将登录名映射到数据库中的一个用户,但可以将其映射为多个不同数据库中的数据库用户。
也可以创建不具有相应登录名的数据库用户。 这些数据库用户称为“包含的数据库用户”。 Microsoft鼓励使用包含的数据库用户,因为它可以更轻松地将数据库移动到其他服务器。 与登录名类似,包含的数据库用户可以使用 Windows 身份验证或 SQL Server 身份验证。 有关详细信息,请参阅使用包含的数据库将数据库设为可移植。
有 12 种类型的用户,这些用户类型在如何进行身份验证及其表示的人员方面略有差异。 若要查看用户列表,请参阅 CREATE USER。
固定数据库角色
固定数据库角色 是一组预配置的角色,可提供方便的数据库级权限组。 可以使用 ALTER ROLE ... ADD MEMBER
语句将数据库用户和用户定义的数据库角色添加到固定数据库角色。 有关详细信息,请参阅 ALTER ROLE。
用户定义的数据库角色
具有 CREATE ROLE
权限的用户可以创建新的 用户定义的数据库角色 来表示具有公共权限的用户组。 通常会对整个角色授予或拒绝权限,从而简化权限管理和监视。 可以使用 ALTER ROLE ... ADD MEMBER
语句将数据库用户添加到数据库角色。 有关详细信息,请参阅 ALTER ROLE。
Other principals
此处未讨论的其他安全主体包括 应用程序角色,以及基于证书或非对称密钥的登录名和用户。
有关显示 Windows 用户、Windows 组、登录名和数据库用户之间的关系的图形,请参阅 “创建数据库用户”。
Typical scenario
下面的示例展示配置权限的常用方法和建议方法。
在 Windows Active Directory 或 Microsoft Entra ID 中
- 为每个人员创建一个用户。
- 创建表示工作单位和工作职能部门的 Windows 组。
- 将 Windows 用户添加到 Windows 组。
如果用户将连接到多个数据库
创建 Windows 组的登录名。 (如果使用 SQL Server 身份验证,请跳过 Active Directory 步骤,并在此处创建 SQL Server 身份验证登录名。
在用户数据库中,为表示 Windows 组的登录名创建数据库用户。
在用户数据库中,创建一个或多个用户定义的数据库角色,其中每个角色均表示相似的职能部门。 例如,你可能具有财务分析师角色和销售分析师角色。
将数据库用户添加到一个或多个用户定义的数据库角色。
将权限授予用户定义的数据库角色。
如果用户将仅连接到一个数据库
在用户数据库中,为 Windows 组创建包含的数据库用户。 (如果使用 SQL Server 身份验证,请跳过 Active Directory 步骤,并在此处创建包含的数据库用户 SQL Server 身份验证。
在用户数据库中,创建一个或多个用户定义的数据库角色,其中每个角色均表示相似的职能部门。 例如,你可能具有财务分析师角色和销售分析师角色。
将数据库用户添加到一个或多个用户定义的数据库角色。
将权限授予用户定义的数据库角色。
此时的典型结果是,Windows 用户是 Windows 组的成员。 Windows 组在 SQL Server 或 SQL 数据库中具有登录名。 登录名将映射到用户数据库中的用户标识。 用户是数据库角色的成员。 现在,需要将权限添加到角色。
Assign permissions
大多数权限语句具有以下格式:
<authorization> <permission> ON <securable>::<name> TO <principal>;
<authorization>
必须是GRANT
、REVOKE
或DENY
。<permission>
规定您允许或禁止的行为。 SQL Server 和 Azure SQL 数据库之间的确切权限数不同。 有关权限的信息,请参阅 权限(数据库引擎),并参阅本文后面的图表。ON <securable>::<name>
是安全对象(服务器、服务器对象、数据库或数据库对象)的类型及其名称。 某些权限不需要<securable>::<name>
,因为它是明确的或在上下文中不适当。 例如,CREATE TABLE
权限不需要<securable>::<name>
子句(GRANT CREATE TABLE TO Mary;
允许 Mary 创建表)。<principal>
是获得或失去权限的安全主体(登录名、用户或角色)。 尽可能将权限授予角色。
以下示例语句授予名为UPDATE
的角色在Parts
架构中包含的Production
表或视图上的PartsTeam
权限。
GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;
下面的示例语句将授予对 UPDATE
架构的 Production
权限,并通过扩展该权限将此架构中包含的任何表或视图的权限授予名为 ProductionTeam
的角色,相较于在各个对象级别分配权限,这是一种更有效且更受欢迎的方法:
GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;
使用 GRANT
语句将权限授予安全主体(登录名、用户和角色)。 使用 DENY
命令显式拒绝权限。 使用 REVOKE
语句删除了以前授予或拒绝的权限。 权限可累积,其中用户将获得授予用户、登录名和任何组成员身份的所有权限;但是,任何权限拒绝将替代所有授予。
Caution
常见错误是尝试使用 GRANT
而不是 DENY
来删除 REVOKE
。 当用户从多个源接收权限(可能是一种常见方案)时,这可能会导致问题。 以下示例演示了原则。
“销售”组通过语句 SELECT
获得 OrderStatus
表上的 GRANT SELECT ON OBJECT::OrderStatus TO Sales;
权限。 用户 Jae
是 Sales
角色的成员。 还通过语句 SELECT
在 Jae 自己的用户名下授予 Jae 对 OrderStatus
表的 GRANT SELECT ON OBJECT::OrderStatus TO Jae;
权限。 假设管理员希望将GRANT
从Sales
角色中移除。
如果管理员正确执行
REVOKE SELECT ON OBJECT::OrderStatus TO Sales;
,则 Jae 会通过其单个SELECT
语句保留OrderStatus
对GRANT
表的访问权限。如果管理员错误地执行
DENY SELECT ON OBJECT::OrderStatus TO Sales;
,则 Jae 作为Sales
角色的成员,被拒绝SELECT
权限,因为DENY
到Sales
的设置会覆盖其个人GRANT
权限。
Note
可以使用 Management Studio 配置权限。 在对象资源管理器中查找安全对象,右键单击该安全对象,然后选择“属性”。 选择“权限”页面。 有关使用权限页面的帮助,请参阅“权限”或“安全对象”页。
Permission hierarchy
权限具有父/子层次结构。 也就是说,如果授予对数据库的 SELECT
权限,则该权限将包括对数据库中所有(子)架构的 SELECT
权限。 如果授予对架构的 SELECT
权限,则该权限将包括对架构中所有(子)表和视图的 SELECT
权限。 权限是 可传递的:如果授予 SELECT
对数据库的权限,则它包括 SELECT
对所有(子)架构以及所有(孙子)表和视图的权限。
权限还具有涵盖权限。 对象的 CONTROL
权限通常赋予你对该对象的其他所有权限。
由于父/子层次结构和涵盖层次结构可以作用于相同的权限,因此权限系统会变得很复杂。 例如,假设存在表 (Region
)、架构 (Customers
)、数据库 (SalesDB
)。
CONTROL
对表Region
的权限包括表Region
的其他所有权限,包括ALTER
、SELECT
、INSERT
UPDATE
、DELETE
和一些其他权限。拥有
SELECT
表的Customers
架构上的Region
包含对SELECT
表的Region
权限。
因此 SELECT
可以通过以下六个语句实现对 Region
表的权限:
GRANT SELECT ON OBJECT::Region TO Jae;
GRANT CONTROL ON OBJECT::Region TO Jae;
GRANT SELECT ON SCHEMA::Customers TO Jae;
GRANT CONTROL ON SCHEMA::Customers TO Jae;
GRANT SELECT ON DATABASE::SalesDB TO Jae;
GRANT CONTROL ON DATABASE::SalesDB TO Jae;
授予最少权限
前面列出的第一个权限(GRANT SELECT ON OBJECT::Region TO Jae;
)是最精细的权限。 该语句是授予 SELECT
语句的最低可能权限。 其中不附带对从属对象的权限。 始终尽可能 授予最少的权限是一个很好的原则,但应考虑在更高级别授予,以便简化授予系统。
因此,如果 Jae 需要对整个架构的权限,请在架构级别授予 SELECT
一次,而不是多次在表或视图级别授予 SELECT
。 数据库的设计可以极大地影响此策略的成功程度。 在设计数据库时,此策略最有效,以便将需要相同权限的对象包含在单个架构中。
Tip
设计数据库及其对象时,请从头开始规划应用程序和用户如何访问这些对象。 使用此信息控制对使用架构的表、视图、函数和存储过程的访问。 使用架构可以更轻松地对访问类型进行分组。
权限关系图
下图显示了权限以及它们彼此之间的关系。 一些较高级别的权限(如 CONTROL SERVER
)被多次列出。 在本文中,图表太小,无法阅读。 你可以下载 PDF 格式的完整数据库引擎权限文章。
有关显示数据库引擎主体与服务器和数据库对象之间关系的图形,请参阅权限层次结构(数据库引擎)。
权限与固定服务器和固定数据库角色
固定服务器角色和固定数据库角色的权限与粒度权限不同,但不完全相同。 例如, sysadmin 固定服务器角色的成员对 SQL Server 实例拥有所有权限,与具有 CONTROL SERVER
权限的登录名一样。
但是,授予 CONTROL SERVER
权限不会使登录成为 sysadmin 固定服务器角色的成员,并且向 sysadmin 固定服务器角色添加登录不会显式授予登录 CONTROL SERVER
的权限。 有时,存储过程会检查固定角色,而不是检查粒度权限,籍此检查权限。
例如,分离数据库需要具有 db_owner 固定数据库角色中的成员身份。 等效的 CONTROL DATABASE
权限并不够。 这两个系统并行运行,但彼此很少进行交互。 Microsoft建议尽可能使用较新的精细权限系统,而不是固定角色。
Monitor permissions
以下视图会返回安全信息。 有关所有与安全相关的视图,请参阅安全目录视图(Transact-SQL)。
View | Description |
---|---|
sys.server_principals
1 |
服务器上的登录名和用户定义的服务器角色 |
sys.database_principals |
数据库中的用户和用户定义的角色 |
sys.server_permissions
1 |
授予登录名和用户定义的固定服务器角色的权限 |
sys.database_permissions |
授予用户和用户定义的固定数据库角色权限 |
sys.database_role_members |
数据库角色成员关系 |
sys.server_role_members
1 |
服务器角色成员身份 |
1 此视图在 SQL 数据库中不可用。
Examples
以下语句会返回有关权限的有用信息。
A. 每个用户的数据库权限的列表
若要返回在数据库中授予或拒绝的显式权限(SQL Server 和 SQL 数据库),请在数据库中运行以下 Transact-SQL 语句。
SELECT perms.state_desc AS State,
permission_name AS [Permission],
obj.name AS [on Object],
dp.name AS [to User Name]
FROM sys.database_permissions AS perms
INNER JOIN sys.database_principals AS dp
ON perms.grantee_principal_id = dp.principal_id
INNER JOIN sys.objects AS obj
ON perms.major_id = obj.object_id;
B. 列出服务器角色成员
若要返回服务器角色(仅限 SQL Server)的成员,请运行以下语句。
SELECT roles.principal_id AS RolePrincipalID,
roles.name AS RolePrincipalName,
server_role_members.member_principal_id AS MemberPrincipalID,
members.name AS MemberPrincipalName
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
LEFT OUTER JOIN sys.server_principals AS members
ON server_role_members.member_principal_id = members.principal_id;
C. 列出作为数据库级别角色成员的所有数据库主体
若要返回数据库角色(SQL Server 和 SQL 数据库)的成员,请在数据库中运行以下语句。
SELECT dRole.name AS [Database Role Name],
dp.name AS [Members]
FROM sys.database_role_members AS dRo
INNER JOIN sys.database_principals AS dp
ON dRo.member_principal_id = dp.principal_id
INNER JOIN sys.database_principals AS dRole
ON dRo.role_principal_id = dRole.principal_id;