azure云数据库_在Azure SQL数据库中保护数据的五种方法

azure云数据库

When storing data in the cloud the main concern companies generally have is whether or not their data is safe. And what can be done to ensure that the following 4 scenarios are addressed:

当将数据存储在云中时，公司通常主要关心的是他们的数据是否安全。 以及如何确保确保解决以下4种情况：

• My database files must be protected in case the server is breached.
  我的数据库文件必须受到保护，以防服务器损坏。
• My data in the database must be stored in such a manner that no unauthorised user can view any data they do not explicitly have access to.
  我在数据库中的数据必须以这样一种方式存储：未经授权的用户不能查看他们没有明确访问权限的任何数据。
• Certain sensitive data should never be displayed entirely.
  某些敏感数据永远不能完整显示。
• My data should be protected while being transferred from my application to my database.
  从我的应用程序传输到数据库时，我的数据应受到保护。

In this article, I will discuss 5 features of the SQL Azure Database which will help you achieve the abovementioned criteria.

在本文中，我将讨论SQL Azure数据库的5个功能，这些功能将帮助您达到上述条件。

用防火墙保护服务器 (Protecting your server with a firewall)

The first step in protecting your data is to ensure that only authorized clients are in fact able to connect to your server and database. A firewall is used to allow only certain IP addresses or IP address ranges access to your database.

保护数据的第一步是确保只有经过授权的客户端才能连接到您的服务器和数据库。 防火墙用于仅允许某些IP地址或IP地址范围访问您的数据库。

In my previous article Configuring the Azure SQL Database Firewall I explained how to configure this firewall and as such I will not go into much depth here.

在我以前的文章《 配置Azure SQL数据库防火墙》中，我解释了如何配置此防火墙，因此，在这里我将不做深入介绍。

透明数据加密 (Transparent Data Encryption)

Transparent data encryption or TDE, as it is affectionately known, is not new to SQL Server. This was first introduced in SQL Server 2008 and is also included in SQL Database.

众所周知，透明数据加密或TDE对SQL Server来说并不是新事物。 它是在SQL Server 2008中首次引入的，并且也包含在SQL数据库中。

TDE encrypts the database files, such as the data, log files and backup files using a database encryption key. Basically, data gets encrypted when it is saved to disk, and it is automatically decrypted when the data is accessed.

TDE使用数据库加***加密数据库文件，例如数据，日志文件和备份文件。 基本上，将数据保存到磁盘后会对其进行加密，并且在访问数据时会对其进行自动解密。

TDE can be enabled without any changes to your application.

可以启用TDE，而无需对应用程序进行任何更改。

For SQL Server on premise, the first step involved is to create a master key, which is stored in the master database, using the following command:

对于前提条件SQL Server，涉及的第一步是使用以下命令创建存储在master数据库中的主**：

 
USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Your Password>';
 

In Azure SQL Database, however, you do not have to create your own master key. SQL Database will automatically do this for you when you enable TDE.

但是，在Azure SQL数据库中，不必创建自己的主**。 启用TDE时，SQL数据库将自动为您执行此操作。

为SQL数据库启用TDE (Enabling TDE for a SQL Database)

In the Azure portal select SQL Databases.

在Azure门户中选择“ SQL数据库”。

Next select the relevant SQL Database

接下来选择相关SQL数据库

Click on the Transparent data encryption option

单击透明数据加密选项

Set Data encryption on, and save.

设置“数据加密”并保存。

The following will be displayed while the files are being encrypted

加密文件时将显示以下内容

And once completed, this will be displayed:

并在完成后显示：

始终加密 (Always Encrypted )

Always Encrypted ensures that data is well, always encrypted, which means that the data is also encrypted while it is being transferred from the application to the database.

始终加密可确保数据完好，始终被加密，这意味着在将数据从应用程序传输到数据库时，数据也会被加密。

This ensures that the data cannot be stolen en-route using sniffers etc.

这确保了数据不会在使用嗅探器等的途中被盗。

See the below infographic on how it works:

有关其工作原理，请参见以下信息图：

The data is stored encrypted, which means that not even the database administrator will be able to view the data if he does not have the Encryption Key.

数据以加密方式存储，这意味着即使数据库管理员没有加***，也无法查看该数据。

The only change required to the application itself is to the connection string. Of course, the encryption key need to be available to the application.

对应用程序本身的唯一更改就是对连接字符串的更改。 当然，加***需要应用程序可用。

Driver	Required Version	Example
Ado.NET	4.6 or higher	“Data Source=min-sql-server; Initial Catalog=min-sql-database; Integrated Security=true; Column Encryption Setting=enabled”;
Microsoft JDBC	6.0 or higher	“jdbc:sqlserver://min-sql-server;user=minette;password=mypassword;databaseName=min-sql-database;columnEncryptionSetting=Enabled;”;
Windows ODBC	13.1 or higher	L”Driver={ODBC Driver 13 for SQL Server};Server=min-sql-server;Database=min-sql-database;Trusted_Connection=yes;ColumnEncryption=Enabled;”;

司机	所需版本	例
Ado.NET	4.6或更高	“数据源= min-sql-server； 初始目录= min-sql-database; 集成安全性= true; 列加密设置=已启用”；
Microsoft JDBC	6.0以上	“ jdbc：sqlserver：// min-sql-server; user = minette; password = mypassword; databaseName = min-sql-database; columnEncryptionSetting = Enabled;”；
Windows ODBC	13.1或更高	L” Driver = {用于SQL Server的ODBC驱动程序13};服务器= min-sql-server;数据库= min-sql-数据库; Trusted_Connection =是; ColumnEncryption = Enabled;”；

On the database side a couple more changes will be required which include:

在数据库方面，还需要进行一些其他更改，包括：

• Generating the Encryption keys
  生成加***
• Generating the meta data for the keys in the database
  为数据库中的键生成元数据
• Recreate the tables with the encrypted columns
  重新创建带有加密列的表
• Encrypt the data which already exists for the newly encrypted columns
  加密新加密的列已经存在的数据

行级安全 (Row-level security)

The purpose of row-level security (RLS) is to restrict user access to individual rows. For example, in a hospital a nurse only requires access to the patient in his/her care. Since all patients are stored in the same table RLS is the answer. No application changes are required, and the security is enforced at the database tier, which means that it will always be enforced regardless of which client is being used.

行级安全性（RLS）的目的是限制用户对单个行的访问。 例如，在医院中，护士仅需要接触他/她所护理的患者。 由于所有患者都存储在同一张表中，因此RLS是答案。 无需更改应用程序，并且在数据库层强制实施安全性，这意味着无论使用哪个客户端，都将始终实施安全性。

RLS can be implemented in 2 basic steps:

可以通过2个基本步骤来实现RLS：

1. Create a filter predicate, by creating an inline table valued function which applies the filter.
   通过创建一个应用过滤器的内联表值函数来创建过滤器谓词。
2. Create a security policy with the in-line function as the predicate WITH (STATE = ON)
   使用内联函数作为谓词WITH（STATE = ON）创建安全策略

动态数据屏蔽 (Dynamic Data Masking)

Dynamic data masking is used to obfuscate parts of certain sensitive fields, such as social security numbers, credit cards etc.

动态数据屏蔽用于混淆某些敏感字段的某些部分，例如社会保险号，信用卡等。

DDM has very little impact on the application since it is applied to the column in the database and access is controlled either a central masking policy

DDM对应用程序的影响很小，因为它已应用于数据库中的列，并且访问受*屏蔽策略控制

SQL Server 2016 has 4 predefined masks which can be applied.

SQL Server 2016具有4个可以应用的预定义掩码。

Mask	Description	Example
Default	A default mask will be applied depending on the datatype of the column.	“Hello World” will become “XXX” 07.26.1984 will become 01.01.1900 88.99 will become 0 01001 will become 0
Email	This mask is specifically created to obfuscate email addresses. Only the first letter and the domain will be displayed	[e-mail] will become [e-mail]
Random	This mask substitutes a random number for any numeric value. A range can be specified.	200 will become 388 or 023 or 777 or any other random value within the specified range.
Custom	This mask allows you to substitute the middle of a string with another string. Allowing you to choose how many characters of the original string should remain as the prefix and suffix.	2334-4566-6778-4444 will become XXXX-XXXX-XXXX-4444 depending on the setting for prefix and suffix. In this case the function would look like this: (FUNCTION = ‘partial(0, “XXXX-XXXX-XXXX-“,4)’)

面具	描述	例
默认	将根据列的数据类型应用默认掩码。	“ Hello World”将变为“ XXX” 1984年7月26日将变为01.01.1900 88.99将变为0 01001将变为0
电子邮件	该掩码是专门为混淆电子邮件地址而创建的。 仅显示第一个字母和域	[e-mail]将变为[e-mail]
随机	此掩码用随机数代替任何数值。 可以指定范围。	200将变为388或023或777或指定范围内的任何其他随机值。
自订	此掩码允许您将字符串的中间替换为另一个字符串。 允许您选择保留原始字符串的多少个字符作为前缀和后缀。	2334-4566-6778-4444将变为XXXX-XXXX-XXXX-4444，具体取决于前缀和后缀的设置。 在这种情况下，函数将如下所示：（FUNCTION ='partial（0，“ XXXX-XXXX-XXXX-”，4）'）

Here are some examples:

这里有些例子：

On my person table I have opted to apply a default mask to the [secret_answer] column, an email mask to the [email] column, and a random mask to the [salary] column, using the script below:

在我的人员表上，我选择使用以下脚本将默认掩码应用于[secret_answer]列，将电子邮件掩码应用于[email]列，将随机掩码应用于[salary]列：

 
ALTER TABLE person ALTER COLUMN secret_answer  ADD MASKED WITH (FUNCTION = 'default()')
ALTER TABLE person ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')
ALTER TABLE person ALTER COLUMN salary ADD MASKED WITH (FUNCTION = 'random(1000, 120000)')
 

I user without the UNMASK permission will see the following:

我的用户未经UNMASK许可将看到以下内容：

Whereas a user with the UNMASK permission, will see the real values:

拥有UNMASK权限的用户将看到实际值：

结论 (Conclusion)

Microsoft continues to invest greatly in SQL Database security to ensure that your data is protected against internal and external threats. Although some of these features require some changes to your application or database, designing with security in mind has become a lot easier than what it used to be.

Microsoft继续在SQL数据库安全性上进行大量投资，以确保您的数据受到内部和外部威胁的保护。 尽管其中一些功能需要对您的应用程序或数据库进行一些更改，但是考虑到安全性而进行的设计比以前要容易得多。

翻译自: https://www.sqlshack.com/five-ways-to-protect-your-data-in-azure-sql-database/

azure云数据库