向数据库授予对 Azure SQL 数据库、SQL 托管实例和 Azure Synapse Analytics 的访问权限Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics

适用于: 是Azure SQL 数据库是Azure SQL 托管实例是Azure Synapse Analytics (SQL DW) APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance yes Azure Synapse Analytics (SQL DW)

本文介绍:In this article, you learn about:

  • 用于配置 Azure SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics(前称为 Azure SQL 数据仓库)的选项,目的是使用户能够执行管理任务和访问这些数据库中存储的数据。Options for configuring Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (formerly Azure SQL Data Warehouse) to enable users to perform administrative tasks and to access the data stored in these databases.
  • 最初新建服务器后的访问权限和授权配置。The access and authorization configuration after initially creating a new server.
  • 如何在 master 数据库和用户账户中添加登录名和用户帐户,然后为这些帐户授予管理权限。How to add logins and user accounts in the master database and user accounts and then grant these accounts administrative permissions.
  • 如何在用户数据库中添加用户帐户(与登录名关联或作为包含的用户帐户)。How to add user accounts in user databases, either associated with logins or as contained user accounts.
  • 使用数据库角色和显式权限配置在用户数据库中拥有权限的用户帐户。Configure user accounts with permissions in user databases by using database roles and explicit permissions.

重要

本文其余部分将 Azure SQL 数据库、Azure SQL 托管实例和 Azure Synapse 中的数据库统称为数据库,并且服务器指的是为 Azure SQL 数据库和 Azure Synapse 管理数据库的服务器Databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse are referred to collectively in the remainder of this article as databases, and the server is referring to the server that manages databases for Azure SQL Database and Azure Synapse.

身份验证和授权Authentication and authorization

身份验证是证明用户所声明身份的过程。Authentication is the process of proving the user is who they claim to be. 某个用户使用用户帐户连接到数据库。A user connects to a database using a user account. 当用户尝试连接到数据库时,需要提供用户帐户和身份验证信息。When a user attempts to connect to a database, they provide a user account and authentication information. 将使用以下两种身份验证方法之一对用户进行身份验证:The user is authenticated using one of the following two authentication methods:

  • SQL 身份验证SQL authentication.

    使用这种身份验证方法时,用户通过提交用户帐户名和关联的密码来建立连接。With this authentication method, the user submits a user account name and associated password to establish a connection. 对于与登录名关联的用户帐户,此密码存储在 master 数据库中;或对于没有与登录名关联的用户帐户,此密码存储在包含这一类用户帐户的数据库中。This password is stored in the master database for user accounts linked to a login or stored in the database containing the user accounts not linked to a login.

  • Azure Active Directory 身份验证Azure Active Directory Authentication

    使用此身份验证方法时,用户将提交用户帐户名,并请求服务使用 Azure Active Directory (Azure AD) 中存储的凭据信息。With this authentication method, the user submits a user account name and requests that the service use the credential information stored in Azure Active Directory (Azure AD).

登录名和用户:数据库中的用户帐户可以与 master 数据库中存储的登录名相关联,也可以是单个数据库中存储的用户名。Logins and users: A user account in a database can be associated with a login that is stored in the master database or can be a user name that is stored in an individual database.

  • 登录名是 master 数据库中的单个帐户,一个或多个数据库中的用户帐户可以关联到该帐户。A login is an individual account in the master database, to which a user account in one or more databases can be linked. 使用登录名时,用户帐户的凭据信息将与该登录名一起存储。With a login, the credential information for the user account is stored with the login.
  • 用户帐户是任何数据库中的单个帐户,该帐户可以但不一定要关联到登录名。A user account is an individual account in any database that may be, but does not have to be, linked to a login. 使用未关联到登录名的用户帐户时,凭据信息将与该用户帐户一起存储。With a user account that is not linked to a login, the credential information is stored with the user account.

访问数据和执行各种操作所需的授权是使用数据库角色和显式权限进行管理的。Authorization to access data and perform various actions are managed using database roles and explicit permissions. 授权是指为用户分配权限,它决定了该用户可以执行哪些操作。Authorization refers to the permissions assigned to a user, and determines what that user is allowed to do. 授权是由用户帐户的数据库角色成员身份对象级权限控制。Authorization is controlled by your user account's database role memberships and object-level permissions. 作为最佳实践,应向用户授予所需的最低权限。As a best practice, you should grant users the least privileges necessary.

创建新数据库后的现有登录名和用户帐户Existing logins and user accounts after creating a new database

首次部署 Azure SQL 时,需为该登录名指定管理员登录名和关联的密码。When you first deploy Azure SQL, you specify an admin login and an associated password for that login. 此管理帐户称为“服务器管理员”。在部署期间,将在 master 数据库和用户数据库中进行以下登录名和用户配置:This administrative account is called Server admin. The following configuration of logins and users in the master and user databases occurs during deployment:

  • 使用指定的登录名创建拥有管理特权的 SQL 登录名。A SQL login with administrative privileges is created using the login name you specified. 登录名是用于登录到 SQL 数据库、SQL 托管实例和 Azure Synapse 的单个用户帐户。A login is an individual user account for logging in to SQL Database, SQL Managed Instance, and Azure Synapse.
  • 此登录名对所有数据库拥有完全管理权限,充当服务器级主体This login is granted full administrative permissions on all databases as a server-level principal. 此登录名具有所有可用权限,且不受限制。The login has all available permissions and can't be limited. 在 SQL 托管实例中,会将此登录名添加到 sysadmin 固定服务器角色(Azure SQL 数据库中不存在此角色)。In a SQL Managed Instance, this login is added to the sysadmin fixed server role (this role does not exist in Azure SQL Database).
  • 将在每个用户数据库中为此登录名创建名为 dbo用户帐户A user account called dbo is created for this login in each user database. dbo 用户在数据库中拥有所有数据库权限,将映射到 db_owner 固定数据库角色。The dbo user has all database permissions in the database and is mapped to the db_owner fixed database role. 本文稍后将讨论其他固定数据库角色。Additional fixed database roles are discussed later in this article.

若要确定数据库的管理员帐户,请打开 Azure 门户,然后导航到服务器或托管实例的“属性”选项卡。To identify the administrator accounts for a database, open the Azure portal, and navigate to the Properties tab of your server or managed instance.

SQL Server 管理员

SQL Server 管理员

重要

创建管理员登录名后,无法对其进行更改。The admin login name can't be changed after it has been created. 若要重置服务器管理员的密码,请转到 Azure 门户,单击“SQL Server”,从列表中选择服务器,然后单击“重置密码” 。To reset the password for the server admin, go to the Azure portal, click SQL Servers, select the server from the list, and then click Reset Password. 若要重置 SQL 托管实例的密码,请转到 Azure 门户,单击该实例,然后单击“重置密码”。To reset the password for the SQL Managed Instance, go to the Azure portal, click the instance, and click Reset password. 也可以使用 PowerShell 或 Azure CLI。You can also use PowerShell or the Azure CLI.

创建拥有管理权限的其他登录名和用户Create additional logins and users having administrative permissions

此时,只是配置了使用单个 SQL 登录名和用户帐户来访问服务器或托管实例。At this point, your server or managed instance is only configured for access using a single SQL login and user account. 若要创建其他拥有完全管理权限或部分管理权限的登录名,可使用以下选项(取决于部署模式):To create additional logins with full or partial administrative permissions, you have the following options (depending on your deployment mode):

  • 创建拥有完全管理权限的 Azure Active Directory 管理员帐户Create an Azure Active Directory administrator account with full administrative permissions

    启用 Azure Active Directory 身份验证并创建 Azure AD 管理员登录名。Enable Azure Active Directory authentication and create an Azure AD administrator login. 可将一个 Azure Active Directory 帐户配置为拥有完全管理权限的 Azure SQL 部署管理员。One Azure Active Directory account can be configured as an administrator of the Azure SQL deployment with full administrative permissions. 此帐户可以是个人帐户或安全组帐户。This account can be either an individual or security group account. 若要使用 Azure AD 帐户连接到 SQL 数据库、SQL 托管实例或 Azure Synapse,“必须”配置 Azure AD 管理员。An Azure AD administrator must be configured if you want to use Azure AD accounts to connect to SQL Database, SQL Managed Instance, or Azure Synapse. 有关为所有 Azure SQL 部署类型启用 Azure AD 身份验证的详细信息,请参阅以下文章:For detailed information on enabling Azure AD authentication for all Azure SQL deployment types, see the following articles:

  • 在 SQL 托管实例中,创建拥有完全管理权限的 SQL 登录名In SQL Managed Instance, create SQL logins with full administrative permissions

  • 在 SQL 数据库中,创建具有有限管理权限的 SQL 登录名In SQL Database, create SQL logins with limited administrative permissions

    • 在 master 数据库中再创建一个 SQL 登录名。Create an additional SQL login in the master database.
    • 在 master 数据库中创建与此新登录名关联的用户帐户。Create a user account in the master database associated with this new login.
    • master 数据库中,使用 ALTER ROLE 语句(对于 Azure Synapse,请使用 sp_addrolemember 语句)将用户帐户添加到 dbmanager 和/或 loginmanager 角色。Add the user account to the dbmanager, the loginmanager role, or both in the master database using the ALTER ROLE statement (for Azure Synapse, use the sp_addrolemember statement).

    备注

    dbmanagerloginmanager 角色与 SQL 托管实例部署“不”相关。dbmanager and loginmanager roles do not pertain to SQL Managed Instance deployments.

    Azure SQL 数据库的这些特殊 master 数据库角色的成员有权创建和管理数据库或登录名。Members of these special master database roles for Azure SQL Database have authority to create and manage databases or to create and manage logins. 在属于 dbmanager 角色的成员的用户创建的数据库中,该成员将映射到 db_owner 固定数据库角色,并可以使用 dbo 用户帐户登录到该数据库以及对其进行管理。In databases created by a user that is a member of the dbmanager role, the member is mapped to the db_owner fixed database role and can log into and manage that database using the dbo user account. 这些角色在 master 数据库外部没有显式权限。These roles have no explicit permissions outside of the master database.

    重要

    无法在 SQL 数据库中创建拥有完全管理权限的其他 SQL 登录名。You can't create an additional SQL login with full administrative permissions in SQL Database.

为非管理员用户创建帐户Create accounts for non-administrator users

可使用以下两种方法之一为非管理用户创建帐户:You can create accounts for non-administrative users using one of two methods:

  • 创建登录名Create a login

    在 master 数据库中创建 SQL 登录名。Create a SQL login in the master database. 然后在用户需要访问的每个数据库中创建一个用户帐户,并将该用户帐户关联到该登录名。Then create a user account in each database to which that user needs access and associate the user account with that login. 如果用户必须访问多个数据库,而你希望密码保持同步,则此方法是首选方法。This approach is preferred when the user must access multiple databases and you wish to keep the passwords synchronized. 但是,在与异地复制结合使用时,此方法会很复杂,因为必须同时在主服务器和辅助服务器上创建登录名。However, this approach has complexities when used with geo-replication as the login must be created on both the primary server and the secondary server(s). 有关详细信息,请参阅针对异地还原或故障转移配置和管理 Azure SQL 数据库的安全性For more information, see Configure and manage Azure SQL Database security for geo-restore or failover.

  • 创建用户帐户Create a user account

    在用户需要访问的数据库中创建用户帐户(也称为包含的用户)。Create a user account in the database to which a user needs access (also called a contained user).

    • 对于 SQL 数据库,始终可以创建这种类型的用户帐户。With SQL Database, you can always create this type of user account.
    • 对于支持 Azure AD 服务器主体的 SQL 托管实例,可以创建用户帐户来对 SQL 托管实例进行身份验证,而无需创建用作包含的数据库用户的数据库用户。With SQL Managed Instance supporting Azure AD server principals, you can create user accounts to authenticate to the SQL Managed Instance without requiring database users to be created as a contained database user.

    使用此方法时,用户身份验证信息将存储在每个数据库中,并自动复制到异地复制的数据库。With this approach, the user authentication information is stored in each database, and replicated to geo-replicated databases automatically. 但是,如果同一帐户存在于多个数据库中,并且你使用的是 Azure SQL 身份验证,则必须手动保持密码同步。However, if the same account exists in multiple databases and you are using Azure SQL Authentication, you must keep the passwords synchronized manually. 此外,如果用户在不同的数据库中具有密码不同的帐户,则可能会很难记住这些密码。Additionally, if a user has an account in different databases with different passwords, remembering those passwords can become a problem.

重要

若要创建映射到 Azure AD 标识的包含的用户,必须使用充当 Azure SQL 数据库中数据库管理员的 Azure AD 帐户登录。To create contained users mapped to Azure AD identities, you must be logged in using an Azure AD account that is an administrator in the database in Azure SQL Database. 在 SQL 托管实例中,拥有 sysadmin 权限的 SQL 登录也可以创建 Azure AD 登录名或用户。In SQL Managed Instance, a SQL login with sysadmin permissions can also create an Azure AD login or user.

有关演示如何创建登录名和用户的示例,请参阅:For examples showing how to create logins and users, see:

提示

如需查看安全教程,包括如何在 Azure SQL 数据库中创建用户,请参阅教程:保护 Azure SQL 数据库For a security tutorial that includes creating users in Azure SQL Database, see Tutorial: Secure Azure SQL Database.

使用固定数据库角色和自定义数据库角色Using fixed and custom database roles

在数据库中创建用户帐户后,可以授权该用户基于登录名或以包含的用户身份执行各种操作及访问特定数据库中的数据。After creating a user account in a database, either based on a login or as a contained user, you can authorize that user to perform various actions and to access data in a particular database. 可使用以下方法来授权访问:You can use the following methods to authorize access:

  • 固定数据库角色Fixed database roles

    将用户帐户添加到固定数据库角色Add the user account to a fixed database role. 有 9 个固定数据库角色,每个角色拥有一组定义的权限。There are 9 fixed database roles, each with a defined set of permissions. 最常用的固定数据库角色为:db_owner、db_ddladmin、db_datawriter、db_datareader、db_denydatawriter 和 db_denydatareader。 The most common fixed database roles are: db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, and db_denydatareader. db_owner 通常用于向部分用户授予完全权限。db_owner is commonly used to grant full permission to only a few users. 其他固定数据库角色可用于快速开发简单的数据库,但不建议用于大多数生产数据库。The other fixed database roles are useful for getting a simple database in development quickly, but are not recommended for most production databases. 例如,db_datareader 固定数据库角色授予用户对数据库中每个表的读取访问权限,这超出了必要的范畴。For example, the db_datareader fixed database role grants read access to every table in the database, which is more than is strictly necessary.

  • 自定义数据库角色Custom database role

    使用 CREATE ROLE 语句创建自定义数据库角色。Create a custom database role using the CREATE ROLE statement. 使用自定义角色可以创建自己的用户定义数据库角色,并根据业务需求慎重地向每个角色授予所需的最低权限。A custom role enables you to create your own user-defined database roles and carefully grant each role the least permissions necessary for the business need. 然后,可将用户添加到自定义角色。You can then add users to the custom role. 如果用户是多个角色的成员,则会聚合所有这些角色的权限。When a user is a member of multiple roles, they aggregate the permissions of them all.

  • 直接授予权限Grant permissions directly

    直接为用户帐户授予权限Grant the user account permissions directly. 可以在 SQL 数据库中单独授予或拒绝 100 多种权限。There are over 100 permissions that can be individually granted or denied in SQL Database. 这些权限中,许多都是嵌套式的。Many of these permissions are nested. 例如,针对架构的 UPDATE 权限包括针对该架构中每个表的 UPDATE 权限。For example, the UPDATE permission on a schema includes the UPDATE permission on each table within that schema. 与大多数权限系统中的情况一样,拒绝某个权限将覆盖对该权限的授予操作。As in most permission systems, the denial of a permission overrides a grant. 考虑到权限的嵌套性质和数目,可能需要进行仔细的研究才能设计出适当的权限系统,以便对数据库进行恰当的保护。Because of the nested nature and the number of permissions, it can take careful study to design an appropriate permission system to properly protect your database. 一开始可以了解权限(数据库引擎)中的权限列表,然后查看这些权限的海报大小的图Start with the list of permissions at Permissions (Database Engine) and review the poster size graphic of the permissions.

使用组Using groups

有效的访问管理使用分配给 Active Directory 安全组以及固定角色或自定义角色的权限,而不使用分配给个人用户的权限。Efficient access management uses permissions assigned to Active Directory security groups and fixed or custom roles instead of to individual users.

  • 使用 Azure Active Directory 身份验证时,请将 Azure Active Directory 用户放入 Azure Active Directory 安全组。When using Azure Active Directory authentication, put Azure Active Directory users into an Azure Active Directory security group. 为该组创建包含数据库用户。Create a contained database user for the group. 将一个或多个数据库用户添加为自定义或内置数据库角色的成员,这些角色具有对于该组用户而言合适的特定权限。Add one or more database users as a member to custom or builtin database roles with the specific permissions appropriate to that group of users.

  • 使用 SQL 身份验证时,请在数据库中创建包含的数据库用户。When using SQL authentication, create contained database users in the database. 将一个或多个数据库用户放入到对该用户组拥有特定适当权限的自定义数据库角色。Place one or more database users into a custom database role with specific permissions appropriate to that group of users.

    备注

    也可以对非包含数据库用户使用组。You can also use groups for non-contained database users.

应该自行熟悉一下下述能够用于限制或提升权限的功能:You should familiarize yourself with the following features that can be used to limit or elevate permissions:

后续步骤Next steps

有关所有 Azure SQL 数据库和 SQL 托管实例安全功能的概述,请参阅安全概述For an overview of all Azure SQL Database and SQL Managed Instance security features, see Security overview.