您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

SQL 身份验证

Azure Synapse Analytics 有两个可用来控制资源消耗的 SQL 组成要素。 本文介绍了这两个组成要素如何控制用户身份验证。

若要向 Synapse SQL 授权,可以使用两种授权类型:

  • Azure Active Directory 授权
  • SQL 授权

Azure Active Directory 使你能够在单一位置进行用户管理。 SQL 授权使旧版应用程序能够以一种熟悉的方式使用 Synapse SQL。

管理帐户

有两个充当管理员的管理帐户(服务器管理员Active Directory 管理员)。 若要在 SQL 服务器中识别这些管理员帐户,请打开 Azure 门户并导航到你的 Synapse SQL 的“属性”选项卡。

SQL 服务器管理员

  • 服务器管理员

    创建 Azure Synapse Analytics 时,必须指定“服务器管理员登录名”。 SQL 服务器创建该帐户作为 master 数据库中的登录名。 此帐户通过 SQL Server 身份验证(用户名和密码)进行连接。 此类帐户只能存在一个。

  • Azure Active Directory 管理员

    也可以将某个 Azure Active Directory 帐户(个人帐户或安全组帐户)配置为管理员。 配置 Azure AD 管理员的操作是可选操作,但如果需要使用 Azure AD 帐户连接到 Synapse SQL,则必须配置 Azure AD 管理员。

服务器管理员和 Azure AD 管理员帐户具有以下特征:

  • 只有这些帐户才能自动连接到服务器上的任何 SQL 数据库。 (其他帐户若要连接到用户数据库,它们必须是数据库的所有者,或者在用户数据库中具有相应的用户帐户。)
  • 这些帐户以 dbo 用户的身份进入用户数据库,在用户数据库中拥有所有权限。 (用户数据库的所有者也以 dbo 用户的身份进入数据库。)
  • 不以 dbo 用户的身份进入 master 数据库,并且在 master 数据库中的权限受限制。
  • 不是标准 SQL Server sysadmin 固定服务器角色的成员,SQL 数据库中未提供此角色。
  • 可以在 master 数据库和服务器级 IP 防火墙规则中创建、更改及删除数据库、登录名与用户。
  • 可以在 dbmanagerloginmanager 角色中添加和删除成员。
  • 可以查看 sys.sql_logins 系统表。

若要管理有权访问无服务器 SQL 池的用户,可以按以下说明操作。

若要创建用于无服务器 SQL 池的登录名,请使用以下语法:

CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
-- or
CREATE LOGIN [Mary@domainname.net] FROM EXTERNAL PROVIDER;

在登录名存在后,可以在无服务器 SQL 池终结点内的各个数据库中创建用户,并向这些用户授予所需的权限。 若要创建用户,可以使用以下语法:

CREATE USER Mary FROM LOGIN Mary;
-- or
CREATE USER Mary FROM LOGIN Mary@domainname.net;
-- or
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

在创建登录名和用户后,可以使用常规 SQL Server 语法来授予权限。

非管理员用户

非管理员帐户通常无需访问 master 数据库。 使用 CREATE USER (Transact-SQL) 语句在数据库级别创建包含数据库用户。

该用户可以是 Azure Active Directory 身份验证包含数据库用户(如果已针对 Azure AD 身份验证配置了环境),可以是 SQL Server 身份验证包含数据库用户,也可以是基于 SQL Server 身份验证登录名(在前一步骤中创建)的 SQL Server 身份验证用户。

要创建用户,请先连接到数据库,然后执行如下所示的语句:

CREATE USER Mary FROM LOGIN Mary;
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

开始时,仅其中一个管理员或数据库所有者可以创建用户。 若要授权其他用户来创建新用户,可通过如下所示语句向该选定用户授予 ALTER ANY USER 权限:

GRANT ALTER ANY USER TO Mary;

若要向其他用户授予对数据库的完全控制权限,可让这些用户成为 db_owner 固定数据库角色的成员。

在 Azure SQL 数据库或 synapse 无服务器中,使用 ALTER ROLE 语句。

ALTER ROLE db_owner ADD MEMBER Mary;

在专用 SQL 池中,请使用 EXEC sp_addrolemember

EXEC sp_addrolemember 'db_owner', 'Mary';

备注

创建基于服务器登录名的数据库用户的一个常见原因是用户需要访问多个数据库。 由于包含的数据库的用户都是单独的实体,因此每个数据库都维护其各自的用户及其密码。 这可能会导致开销,因为用户必须记住每个数据库的密码,当必须为许多数据库更改多个密码时,这通常难以做到。 但是,当使用 SQL Server 登录名和高可用性(活动异地复制和故障转移组)时,必须手动在每台服务器上设置 SQL Server 登录名。 否则,数据库用户在发生故障转移后将不再映射到该服务器登录名,并且在故障转移后将无法访问数据库。

有关为异地复制配置登录名的详细信息,请参阅针对异地还原或故障转移配置和管理 Azure SQL 数据库的安全性

配置数据库级防火墙

最好是规定非管理员用户只能通过防火墙来访问所使用的数据库。 可以使用 sp_set_database_firewall_rule 语句来配置数据库级防火墙,而不必通过服务器级防火墙来授权其 IP 地址访问所有数据库。 不能通过门户来配置数据库级防火墙。

非管理员访问路径

对数据库级防火墙进行适当配置以后,数据库用户即可使用 SQL Server Management Studio 或 SQL Server Data Tools 这样的客户端工具进行连接。 仅最新工具提供所有的特性和功能。 下图显示了典型的非管理员访问路径。

非管理员访问路径

组和角色

有效的访问管理需要将权限分配到组和角色,而不是分配到单个用户。

  • 使用 Azure Active Directory 身份验证时,请将 Azure Active Directory 用户放入 Azure Active Directory 组。 为该组创建包含数据库用户。 将一个或多个数据库用户添加到数据库角色,然后向数据库角色分配权限

  • 使用 SQL Server 身份验证时,请在数据库中创建包含的数据库用户。 将一个或多个数据库用户添加到数据库角色,然后向数据库角色分配权限

数据库角色可以是内置的角色,例如 db_ownerdb_ddladmindb_datawriterdb_datareaderdb_denydatawriterdb_denydatareaderdb_owner 通常用于向部分用户授予完全权限。 其他固定数据库角色可用于快速开发简单的数据库,但不建议用于大多数生产数据库。

例如,db_datareader 固定数据库角色授予用户对数据库中每个表的读取访问权限,这通常超出了必要的范畴。

最好使用 CREATE ROLE 语句创建自己的用户定义数据库角色,并谨慎地为每个角色授予满足业务需要所需的最低权限。 如果用户是多个角色的成员,则会聚合所有这些角色的权限。

权限

可以在 SQL 数据库中单独授予或拒绝 100 多种权限。 这些权限中,许多都是嵌套式的。 例如,针对架构的 UPDATE 权限包括针对该架构中每个表的 UPDATE 权限。 与大多数权限系统中的情况一样,拒绝某个权限将覆盖对该权限的授予操作。

考虑到权限的嵌套性质和数目,可能需要进行仔细的研究才能设计出适当的权限系统,以便对数据库进行恰当的保护。

一开始可以了解权限(数据库引擎)中的权限列表,并查看这些权限的海报大小的图

注意事项和限制

管理 SQL 数据库中的登录名和用户时,请注意以下要点:

  • 执行 CREATE/ALTER/DROP DATABASE 语句时,必须连接到 master 数据库。

  • 不能更改或删除对应于 服务器管理员 登录名的数据库用户。

  • 美国英语是 服务器管理员 登录名的默认语言。

  • 只有管理员(服务器管理员 登录名或 Azure AD 管理员)和 master 数据库中 dbmanager 数据库角色的成员才有权执行 CREATE DATABASEDROP DATABASE 语句。

  • 执行 CREATE/ALTER/DROP LOGIN 语句时,必须连接到 master 数据库。 但不建议使用登录名。 改用包含的数据库用户。

  • 若要连接到用户数据库,必须在连接字符串中提供数据库的名称。

  • 只有服务器级别主体登录名和 master 数据库中 loginmanager 数据库角色的成员才有权执行 CREATE LOGINALTER LOGINDROP LOGIN 语句。

  • 在 ADO.NET 应用程序中执行 CREATE/ALTER/DROP LOGINCREATE/ALTER/DROP DATABASE 语句时,不允许使用参数化命令。 有关详细信息,请参阅命令和参数

  • 在执行 CREATE/ALTER/DROP DATABASECREATE/ALTER/DROP LOGIN 语句时,上述每个语句都必须是 Transact-SQL 批处理中的唯一语句。 否则,将会出错。 例如,以下 Transact-SQL 会检查该数据库是否存在。 如果该数据库存在,则调用 DROP DATABASE 语句删除该数据库。 因为 DROP DATABASE 语句不是该批处理中的唯一语句,所以执行以下 Transact-SQL 将导致错误。

    IF EXISTS (SELECT [name]
             FROM   [sys].[databases]
             WHERE  [name] = N'database_name')
    DROP DATABASE [database_name];
    GO
    

    请改用以下 Transact-SQL 语句:

    DROP DATABASE IF EXISTS [database_name]
    
  • 在使用 FOR/FROM LOGIN 选项执行 CREATE USER 语句时,该语句必须是 Transact-SQL 批处理中的唯一语句。

  • 在使用 WITH LOGIN 选项执行 ALTER USER 语句时,该语句必须是 Transact-SQL 批处理中的唯一语句。

  • 若要执行 CREATE/ALTER/DROP 操作,用户需要对数据库拥有 ALTER ANY USER 权限。

  • 在数据库角色的所有者尝试在该数据库角色中添加或删除其他数据库用户时,可能会发生以下错误:“此数据库中不存在用户或角色‘Name’”。 发生此错误的原因是用户对所有者不可见。 若要解决此问题,请向角色所有者授予对该用户的 VIEW DEFINITION 权限。

后续步骤

有关详细信息,请参阅 包含的数据库用户 - 使你的数据库可移植