데이터 열 암호화

이 항목에서는 Transact-SQL을 사용하여 2014년 SQL Server 대칭 암호화를 사용하여 데이터 열을 암호화하는 방법을 설명합니다.

항목 내용

• 시작하기 전 주의 사항:

  보안

• Transact-SQL을 사용하여 데이터 열을 암호화하려면

시작하기 전에

보안

사용 권한

다음 권한은 아래 단계를 수행하는 데 필요합니다.

• 데이터베이스에 대한 CONTROL 권한.

• 데이터베이스에 대한 CREATE CERTIFICATE 권한. Windows 로그인, SQL Server 로그인 및 애플리케이션 역할만 인증서를 소유할 수 있습니다. 그룹 및 역할은 인증서를 소유할 수 없습니다.

• 테이블에 대한 ALTER 권한.

• 키에 대한 일부 사용 권한이며 VIEW DEFINITION 권한이 거부되지 않은 상태여야 합니다.

Transact-SQL 사용

간단한 대칭 암호화를 사용하여 데이터 열을 암호화하려면

1. 개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.

2. 표준 도구 모음에서 새 쿼리를 클릭합니다.

3. 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.

   USE AdventureWorks2012;  
   --If there is no master key, create one now.   
   IF NOT EXISTS   
       (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)  
       CREATE MASTER KEY ENCRYPTION BY   
       PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'  
   GO  
   
   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(128);   
   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('SHA1', 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('SHA1', CONVERT(varbinary, CreditCardID))))  
       AS 'Decrypted card number' FROM Sales.CreditCard;  
   GO  
   

인증자를 포함하는 대칭 암호화를 사용하여 데이터 열을 암호화하려면

1. 개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.

2. 표준 도구 모음에서 새 쿼리를 클릭합니다.

3. 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.

   USE AdventureWorks2012;  
   GO  
   
   --If there is no master key, create one now.   
   IF NOT EXISTS   
       (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)  
       CREATE MASTER KEY ENCRYPTION BY   
       PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'  
   GO  
   
   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 [AdventureWorks2012];  
   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  
   

자세한 내용은

• CREATE CERTIFICATE(Transact-SQL)

• CREATE SYMMETRIC KEY(Transact-SQL)

• ALTER TABLE(Transact-SQL)

• OPEN SYMMETRIC KEY(Transact-SQL)