데이터 열 암호화

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

이 문서에서는 Transact-SQL을 사용하여 SQL Server에서 대칭 암호화를 사용하여 데이터 열을 암호화하는 방법을 설명합니다. 열 수준 암호화 또는 셀 수준 암호화라고 합니다.

이 문서에는 AdventureWorks2022 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  
    

다음 단계