Udostępnij za pośrednictwem

Szyfrowanie kolumny danych

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w usłudze Microsoft Fabric

Dotyczy:sql ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

W tym artykule opisano sposób szyfrowania kolumny danych przy użyciu szyfrowania symetrycznego w programie SQL Server przy użyciu języka Transact-SQL. Jest to czasami nazywane szyfrowaniem na poziomie kolumny lub szyfrowaniem na poziomie komórki.

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

Bezpieczeństwo

Uprawnienia

Następujące uprawnienia są niezbędne do wykonania poniższych kroków:

  • CONTROL uprawnienia do bazy danych.
  • CREATE CERTIFICATE uprawnienia do bazy danych. Tylko identyfikatory logowania systemu Windows, identyfikatory logowania programu SQL Server i role aplikacji mogą być właścicielami certyfikatów. Grupy i role nie mogą być właścicielami certyfikatów.
  • ALTER uprawnienia na tabeli.
  • Niektóre uprawnienia do klucza nie mogą być odrzucone, jeśli dotyczą uprawnień VIEW DEFINITION.

Tworzenie klucza głównego bazy danych

Aby użyć poniższych przykładów, musisz mieć klucz główny bazy danych. Jeśli baza danych nie ma jeszcze klucza głównego bazy danych, utwórz go. Aby go utworzyć, połącz się z bazą danych i uruchom następujący skrypt. Pamiętaj, aby użyć złożonego hasła.

Skopiuj poniższy przykład i wklej go w oknie zapytania połączonym z przykładową bazą danych AdventureWorks. Wybierz , wykonaj.

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

Zawsze należy utworzyć kopię zapasową klucza głównego bazy danych. Aby uzyskać więcej informacji na temat kluczy głównych bazy danych, zobacz CREATE MASTER KEY (Transact-SQL).

Przykład: Szyfrowanie przy użyciu szyfrowania symetrycznego i uwierzytelniacza

  1. W Eksploratorze obiektów , nawiąż połączenie z wystąpieniem silnika bazy danych.

  2. Na pasku Standardowa wybierz pozycję Nowe zapytanie.

  3. Skopiuj poniższy przykład i wklej go w oknie zapytania połączonym z przykładową bazą danych AdventureWorks. Wybierz pozycję , a następnie Wykonaj.

    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

Szyfrowanie przy użyciu prostego szyfrowania symetrycznego

  1. W Eksploratorze Obiektów nawiąż połączenie z wystąpieniem silnika bazy danych.

  2. Na pasku standardowym wybierz pozycję Nowe zapytanie.

  3. Skopiuj poniższy przykład i wklej go w oknie zapytania połączonym z przykładową bazą danych AdventureWorks. Wybierz pozycję Wykonaj.

     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

Następne kroki