Мы можем использовать Always Encrypted для защиты пользовательских данных в определенном столбце SQL Server. Я не буду рассказывать о Always Encrypted в этой статье, поэтому, пожалуйста, прочитайте официальную документацию.
Его очень легко настроить с помощью SSMS или PowerShell.
- Настройка Always Encrypted с помощью SQL Server Management Studio
- Настройка Always Encrypted с помощью PowerShell
Однако, как разработчик, я хочу настроить его через проект Visual Studio Database, так как мы можем управлять им через контроль исходных кодов.
Предварительные условия
Существует несколько комбинаций для поддержки этого сценария, и я использую Azure SQL в качестве механизма базы данных и Azure Key Vault для хранения главного ключа.
- Azure SQL
- хранилище ключей
- Visual Studio с шаблоном базы данных
Мы можем использовать PowerShell для автоматического предоставления этих служб, но я полагаю, что у большинства из нас уже есть ресурсы или IaC. В этой статье я использую имеющиеся у меня ресурсы.
Создание ключей
Чтобы использовать Always Encrypted, нам нужны два ключа.
- Главный ключ колонки (хранится в хранилище ключей)
- Ключ шифрования столбцов (хранится в базе данных SQL).
Создание главного ключа в хранилище ключей Azure
Существует множество способов создания ключа, и в этом примере я использую PowerShell. Я взял этот сценарий отсюда и просто использовал некоторые его части.
Поскольку у меня уже настроено хранилище ключей, я прокомментировал несколько команд. Важно отметить разрешение. Нам нужны разрешения get, create, delete, list, wrapKey, unwrapKey, sign, verify для использования Always Encrypted.
Import-Module Az
Connect-AzAccount
$SubscriptionId = "<Azure SubscriptionId>"
$resourceGroup = "devto"
$azureLocation = "eastus"
$akvName = "kenakamukv"
$akvKeyName = "CMK1"
$azureCtx = Set-AzConteXt -SubscriptionId $SubscriptionId # Sets the context for the below cmdlets to the specified subscription.
#New-AzResourceGroup -Name $resourceGroup -Location $azureLocation # Creates a new resource group - skip, if your desired group already exists.
#New-AzKeyVault -VaultName $akvName -ResourceGroupName $resourceGroup -Location $azureLocation # Creates a new key vault - skip if your vault already exists.
Set-AzKeyVaultAccessPolicy -VaultName $akvName -ResourceGroupName $resourceGroup -PermissionsToKeys get, create, delete, list, wrapKey,unwrapKey, sign, verify -UserPrincipalName $azureCtx.Account
$akvKey = Add-AzKeyVaultKey -VaultName $akvName -Name $akvKeyName -Destination "Software"
Подтвердите добавление ключа.
Создание ключа шифрования столбцов в проекте базы данных
Мы управляем ключами как частью проекта базы данных.
1. Создайте проект базы данных с помощью Visual Studio. Я создал проект базы данных «AlwaysEncrypted».
2. Добавьте новый элемент и выберите «Column Encryption Key».
3. Введите имена для обоих ключей и выберите «Azure Key Vault» для главного ключа. Нажмите «Создать».
4. Установите информацию Key Vault для главного ключа.
- KEY_STORE_PROVIDER_NAME: AZURE_KEY_VAULT
- Key_Path: Переход к созданному ключу
CREATE COLUMN MASTER KEY [CMK_Auto1]
WITH
(
KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
KEY_PATH = N'https://kenakamukv.vault.azure.net/keys/CMK1/4264df67d0154312ad86e3b82c29f898'
)
GO
5. Запустите следующий сценарий PowerShell для получения зашифрованного значения. Команда New-SqlColumnEncryptionKeyEncryptedValue вычисляет зашифрованное значение с помощью указанного ключа Azure Key Vault.
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl "https://kenakamukv.vault.azure.net/keys/CMK1/4264df67d0154312ad86e3b82c29f898"
$encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings
$encryptedValue | Set-Clipboard
6. Вставьте значение в ENCRYPTED_VALUE. В результате все должно выглядеть так, как показано ниже.
Создание таблицы с защищенным столбцом
В проекте базы данных добавьте таблицу для проверки ключа.
1. Добавьте таблицу. Я назвал ее User. Добавьте один столбец для тестирования и настройте определение столбца ENCRYPTED WITH. Более подробно см. раздел CREATE TABLE (Transact-SQL).
2. Опубликуйте из Visual Studio в любую базу данных.
Подтверждение результата
Мы можем выполнить запрос, чтобы подтвердить, как настроена база данных.
SELECT * FROM sys.column_master_keys
SELECT * FROM sys.column_master_key_definitions
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
SELECT encryption_type_desc FROM sys.all_columns WHERE name = 'SecretValue'
Мы также можем использовать SSMS для работы с данными, если вы правильно его настроите.
1. Подключитесь к базе данных, включив опцию «Enable Always Encrypted» в опциях.
2. После подключения убедитесь, что включена следующая опция.
3. Выполните следующий запрос для проверки.
DECLARE @secretValue NVARCHAR(50) = 'secret'
INSERT INTO [User] (Id, SecretValue) VALUES (1, @secretValue)
SELECT * FROM [User]
Резюме
Always Encrypted — отличная возможность разделить управление ключами между DBA и Key Admin. Однако есть еще несколько моментов, которые необходимо рассмотреть с точки зрения безопасности.
- Должны ли мы хранить зашифрованное значение в системе управления источниками?
- Как мы должны управлять ротацией ключей?
Это важные вопросы, особенно для производства.
Более подробно о ротации ключей см. в статье Ротация ключей Always Encrypted с помощью PowerShell.