保护 Azure Synapse 中的数据库Secure a database in Azure Synapse

本文详细介绍了保护 Synapse SQL 池的基本知识。This article will walk you through the basics of securing your Synapse SQL pool. 具体而言,本文介绍如何从资源着手,以便在使用 SQL 池预配的数据库中限制访问、保护数据和监视活动。In particular, this article gets you started with resources for limiting access, protecting data, and monitoring activities on a database provisioned using SQL pool.

连接安全性Connection security

连接安全性是指如何使用防火墙规则和连接加密来限制和保护数据库连接。Connection Security refers to how you restrict and secure connections to your database using firewall rules and connection encryption.

逻辑 SQL Server 及其数据库使用防火墙规则来拒绝源自未明确加入允许列表的 IP 地址的连接企图。Firewall rules are used by both the logical SQL server and its databases to reject connection attempts from IP addresses that haven't been explicitly whitelisted. 若要从应用程序或客户端计算机的公共 IP 地址进行连接,必须先使用 Azure 门户、REST API 或 PowerShell 创建服务器级防火墙规则。To allow connections from your application or client machine's public IP address, you must first create a server-level firewall rule using the Azure portal, REST API, or PowerShell.

最佳做法是尽量通过服务器级防火墙来限制允许的 IP 地址范围。As a best practice, you should restrict the IP address ranges allowed through your server-level firewall as much as possible. 要从本地计算机访问 SQL 池,请确保网络和本地计算机上的防火墙允许在 TCP 端口 1433 上的传出通信。To access SQL pool from your local computer, ensure the firewall on your network and local computer allows outgoing communication on TCP port 1433.

Azure Synapse Analytics 使用服务器级 IP 防火墙规则。Azure Synapse Analytics uses server-level IP firewall rules. 不支持数据库级 IP 防火墙规则。It doesn't support database-level IP firewall rules. 有关详细信息,请参阅 Azure SQL 数据库防火墙规则For more information, see Azure SQL Database firewall rules

默认加密到 SQL 池的连接。Connections to your SQL pool are encrypted by default. 通过修改连接设置来禁用加密的操作会被忽略。Modifying connection settings to disable encryption are ignored.

身份验证Authentication

身份验证是指连接到数据库时如何证明身份。Authentication refers to how you prove your identity when connecting to the database. SQL 池当前支持通过用户名和密码,以及 Azure Active Directory 进行 SQL Server 身份验证。SQL pool currently supports SQL Server Authentication with a username and password, and with Azure Active Directory.

在为数据库创建服务器时,已指定了一个包含用户名和密码的“服务器管理员”登录名。When you created the server for your database, you specified a "server admin" login with a username and password. 使用这些凭据,可以通过 SQL Server 身份验证以数据库所有者(或“dbo”)的身份在该服务器对任何数据库进行验证。Using these credentials, you can authenticate to any database on that server as the database owner, or "dbo" through SQL Server Authentication.

但是,组织的用户最好使用不同的帐户进行身份验证。However, as a best practice, your organization's users should use a different account to authenticate. 这样,便可以限制授予应用程序的权限,并在应用程序代码容易受到 SQL 注入攻击的情况下降低恶意活动的风险。This way you can limit the permissions granted to the application and reduce the risks of malicious activity in case your application code is vulnerable to a SQL injection attack.

若要创建 SQL Server 验证的用户,请使用服务器管理员登录名连接到服务器上的 master 数据库,并创建新的服务器登录名。To create a SQL Server Authenticated user, connect to the master database on your server with your server admin login and create a new server login. 最好也在 master 数据库中创建一个用户。It's a good idea to also create a user in the master database. 在 master 中创建用户以后,用户即可使用 SSMS 之类的工具登录,不需指定数据库名称。Creating a user in master allows a user to log in using tools like SSMS without specifying a database name. 此外,用户还可以使用对象资源管理器查看服务器上的所有数据库。It also allows them to use the object explorer to view all databases on a server.

-- Connect to master database and create a login
CREATE LOGIN ApplicationLogin WITH PASSWORD = 'Str0ng_password';
CREATE USER ApplicationUser FOR LOGIN ApplicationLogin;

然后,使用服务器管理员登录名连接到“SQL 池数据库”,并基于刚刚创建的服务器登录名创建数据库用户。Then, connect to your SQL pool database with your server admin login and create a database user based on the server login you created.

-- Connect to the database and create a database user
CREATE USER ApplicationUser FOR LOGIN ApplicationLogin;

要授予用户执行其他操作(例如创建登录名或新数据库)的权限,则还需在 master 数据库中为其分配 Loginmanagerdbmanager 角色。To give a user permission to perform additional operations such as creating logins or creating new databases, assign the user to the Loginmanager and dbmanager roles in the master database.

如需详细了解这些额外的角色,以及如何在 SQL 数据库上进行身份验证,请参阅在 Azure SQL 数据库中管理数据库和登录名For more information on these additional roles and authenticating to a SQL Database, see Managing databases and logins in Azure SQL Database. 有关使用 Azure Active Directory 进行连接的详细信息,请参阅使用 Azure Active Directory 身份验证进行连接For more information on connecting using Azure Active Directory, see Connecting by using Azure Active Directory Authentication.

授权Authorization

授权是指在进行身份验证和连接后可以在数据库中执行的操作。Authorization refers to what you can do within a database once you are authenticated and connected. 授权权限由角色成员资格和权限决定。Authorization privileges are determined by role memberships and permissions. 作为最佳实践,应向用户授予所需的最低权限。As a best practice, you should grant users the least privileges necessary. 可使用下列存储过程来管理角色:To manage roles, you can use the following stored procedures:

EXEC sp_addrolemember 'db_datareader', 'ApplicationUser'; -- allows ApplicationUser to read data
EXEC sp_addrolemember 'db_datawriter', 'ApplicationUser'; -- allows ApplicationUser to write data

用于连接的服务器管理员帐户是 db_owner 所有者的成员,该帐户有权在数据库中执行任何操作。The server admin account you are connecting with is a member of db_owner, which has authority to do anything within the database. 请保存此帐户,以便部署架构升级并执行其他管理操作。Save this account for deploying schema upgrades and other management operations. 权限受到更多限制的“ApplicationUser”帐户可让用户使用应用程序所需的最低权限从应用程序连接到数据库。Use the "ApplicationUser" account with more limited permissions to connect from your application to the database with the least privileges needed by your application.

可通过多种方式进一步限制用户可在数据库中执行的操作:There are ways to further limit what a user can do within the database:

  • 通过细化权限,可控制能对数据库中单个列、表、架构、视图、过程和其他对象执行的操作。Granular Permissions let you control which operations you can do on individual columns, tables, views, schemas, procedures, and other objects in the database. 使用细化的权限可以进行最精细的控制,可以根据用户需要授予其最低权限。Use granular permissions to have the most control and grant the minimum permissions necessary.
  • 除 db_datareader 和 db_datawriter 以外的数据库角色可用于创建权限较大的应用程序用户帐户或权限较小的管理帐户。Database roles other than db_datareader and db_datawriter can be used to create more powerful application user accounts or less powerful management accounts. 内置的固定的数据库角色可以方便地用来授予权限,但可能会导致所授权限超出需要的情况。The built-in fixed database roles provide an easy way to grant permissions, but can result in granting more permissions than are necessary.
  • 存储过程 可用于限制可对数据库执行的操作。Stored procedures can be used to limit the actions that can be taken on the database.

下面的示例介绍如何对用户定义的构架授予读取访问权限。The following example grants read access to a user-defined schema.

--CREATE SCHEMA Test
GRANT SELECT ON SCHEMA::Test to ApplicationUser

通过 Azure 门户或 Azure 资源管理器 API 管理数据库和服务器的操作可根据门户用户帐户的角色分配进行控制。Managing databases and servers from the Azure portal or using the Azure Resource Manager API is controlled by your portal user account's role assignments. 有关详细信息,请参阅 Azure 门户中基于角色的访问控制For more information, see Role-based access control in Azure portal.

EncryptionEncryption

透明数据加密 (TDE) 可以对静态数据进行加密和解密,避免恶意活动造成的威胁。Transparent Data Encryption (TDE) helps protect against the threat of malicious activity by encrypting and decrypting your data at rest. 在加密数据库时,可以对关联的备份和事务日志文件加密,无需对应用程序进行任何更改。When you encrypt your database, associated backups and transaction log files are encrypted without requiring any changes to your applications. TDE 使用称为数据库加密密钥的对称密钥来加密整个数据库的存储。TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key.

在 SQL 数据库中,数据库加密密钥由内置服务器证书保护。In SQL Database, the database encryption key is protected by a built-in server certificate. 内置服务器证书是每个服务器特有的。The built-in server certificate is unique for each server. Azure 至少每隔 90 天自动轮换这些证书。Azure automatically rotates these certificates at least every 90 days. 使用的加密算法为 AES-256。The encryption algorithm used is AES-256. 有关 TDE 的一般描述,请参阅透明数据加密For a general description of TDE, see Transparent Data Encryption.

可以使用 Azure 门户T-SQL 加密数据库。You can encrypt your database using the Azure portal or T-SQL.

后续步骤Next steps

有关通过不同协议连接到仓库的详细信息和示例,请参阅连接到 SQL 池For details and examples on connecting to your warehouse with different protocols, see Connect to SQL pool.