Шифрование столбца данных

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics

В этой статье описывается, как шифровать столбец данных с помощью симметричного шифрования в SQL Server с помощью Transact-SQL. Иногда это называется шифрованием на уровне столбца или ячейки.

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

Безопасность

Разрешения

Для выполнения приведенных ниже шагов необходимы следующие разрешения.

  • Разрешение CONTROL в базе данных.
  • Разрешение CREATE CERTIFICATE в базе данных. Сертификаты могут принадлежать только именам входа Windows, именам входа SQL Server и ролям приложений. Сертификаты не могут принадлежать группам и ролям.
  • Разрешение ALTER в базе данных.
  • Некоторые разрешения применяются к ключу; также не должно быть запрета на разрешение VIEW DEFINITION.

Создание главного ключа базы данных

Для применения приведенных ниже примеров требуется главный ключ базы данных. Если в базе данных еще нет главного ключа базы данных, создайте его. Для этого подключитесь к базе данных и выполните следующий скрипт. Обязательно используйте сложный пароль.

Скопируйте и вставьте следующий пример в окно запроса, подключенное к примеру базы данных AdventureWorks. Выберите Выполнить.

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<complex password>';  

Всегда создавайте резервную копию главного ключа базы данных. Дополнительные сведения о главных ключах базы данных см. в статье CREATE MASTER KEY (Transact-SQL).

Пример. Шифрование с помощью симметричного шифрования и средства проверки подлинности

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте и вставьте следующий пример в окно запроса, подключенное к примеру базы данных AdventureWorks. Выберите Выполнить.

    CREATE CERTIFICATE Sales09  
       WITH SUBJECT = 'Customer Credit Card Numbers';  
    GO  
    
    CREATE SYMMETRIC KEY CreditCards_Key11  
        WITH ALGORITHM = AES_256  
        ENCRYPTION BY CERTIFICATE Sales09;  
    GO  
    
    -- Create a column in which to store the encrypted data.  
    ALTER TABLE Sales.CreditCard   
        ADD CardNumber_Encrypted varbinary(160);   
    GO  
    
    -- Open the symmetric key with which to encrypt the data.  
    OPEN SYMMETRIC KEY CreditCards_Key11  
       DECRYPTION BY CERTIFICATE Sales09;  
    
    -- Encrypt the value in column CardNumber using the  
    -- symmetric key CreditCards_Key11.  
    -- Save the result in column CardNumber_Encrypted.    
    UPDATE Sales.CreditCard  
    SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
        , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary  
        , CreditCardID)));  
    GO  
    
    -- Verify the encryption.  
    -- First, open the symmetric key with which to decrypt the data.  
    
    OPEN SYMMETRIC KEY CreditCards_Key11  
       DECRYPTION BY CERTIFICATE Sales09;  
    GO  
    
    -- Now list the original card number, the encrypted card number,  
    -- and the decrypted ciphertext. If the decryption worked,  
    -- the original number will match the decrypted number.  
    
    SELECT CardNumber, CardNumber_Encrypted   
        AS 'Encrypted card number', CONVERT(nvarchar,  
        DecryptByKey(CardNumber_Encrypted, 1 ,   
        HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID))))  
        AS 'Decrypted card number' FROM Sales.CreditCard;  
    GO  
    

Шифрование с помощью простого симметричного шифрования

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте и вставьте следующий пример в окно запроса, подключенное к примеру базы данных AdventureWorks. Выберите Выполнить.

     CREATE CERTIFICATE HumanResources037  
       WITH SUBJECT = 'Employee Social Security Numbers';  
    GO  
    
    CREATE SYMMETRIC KEY SSN_Key_01  
        WITH ALGORITHM = AES_256  
        ENCRYPTION BY CERTIFICATE HumanResources037;  
    GO  
    
    USE [AdventureWorks2022];  
    GO  
    
    -- Create a column in which to store the encrypted data.  
    ALTER TABLE HumanResources.Employee  
        ADD EncryptedNationalIDNumber varbinary(128);   
    GO  
    
    -- Open the symmetric key with which to encrypt the data.  
    OPEN SYMMETRIC KEY SSN_Key_01  
       DECRYPTION BY CERTIFICATE HumanResources037;  
    
    -- Encrypt the value in column NationalIDNumber with symmetric   
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
    UPDATE HumanResources.Employee  
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
    GO  
    
    -- Verify the encryption.  
    -- First, open the symmetric key with which to decrypt the data.  
    OPEN SYMMETRIC KEY SSN_Key_01  
       DECRYPTION BY CERTIFICATE HumanResources037;  
    GO  
    
    -- Now list the original ID, the encrypted ID, and the   
    -- decrypted ciphertext. If the decryption worked, the original  
    -- and the decrypted ID will match.  
    SELECT NationalIDNumber, EncryptedNationalIDNumber   
        AS 'Encrypted ID Number',  
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
        AS 'Decrypted ID Number'  
        FROM HumanResources.Employee;  
    GO  
    

Следующие шаги