时间戳

首页 > 解决方案 > SQL的列级加密在哪里使用？

sql - SQL的列级加密在哪里使用？

问题描述

我能够使用以下代码在 SQL 中进行列加密：

USE EncryptionDemonstration

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ThisIsMySampleStrongPassword'

CREATE CERTIFICATE MyServerCertificate WITH SUBJECT = 'This is my Demonstration Certificate'


CREATE SYMMETRIC KEY SSN_Keys  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE MyServerCertificate;  
GO  
CREATE SYMMETRIC KEY CreditCard_Keys  
    WITH ALGORITHM = AES_128  
    ENCRYPTION BY CERTIFICATE MyServerCertificate;  

ALTER TABLE Customer
    ADD SSN_Encrypted varbinary(128),
        CCN_Encrypted varbinary(128)

OPEN SYMMETRIC KEY CreditCard_Keys 
   DECRYPTION BY CERTIFICATE MyServerCertificate 
OPEN SYMMETRIC KEY SSN_Keys 
   DECRYPTION BY CERTIFICATE MyServerCertificate

UPDATE  Customer 
SET SSN_Encrypted = EncryptByKey(Key_GUID('SSN_Keys')  
    , SocialSecurityNumber)

UPDATE  Customer 
SET CCN_Encrypted = EncryptByKey(Key_GUID('CreditCard_Keys  ')  
    , CreditCardNumber)

GO  

OPEN SYMMETRIC KEY SSN_Keys
   DECRYPTION BY CERTIFICATE MyServerCertificate 

OPEN SYMMETRIC KEY CreditCard_Keys
   DECRYPTION BY CERTIFICATE MyServerCertificate 



SELECT SocialSecurityNumber, SSN_Encrypted   
    AS 'Encrypted SSN',  
    CONVERT(varchar, DecryptByKey(SSN_Encrypted))   
    AS 'Decrypted SSN'  
    FROM Customer  

SELECT CreditCardNumber, CCN_Encrypted 
    AS 'Encrypted Credit Card Number',
     CONVERT(varchar, DecryptByKey(CCN_Encrypted))   
     AS 'Decrypted Credit Card Number'
     FROM Customer

现在我的问题是，如果可以在此数据库上访问它的任何人都可以运行 DecryptByKey 函数来查看解密值，这对安全性有何帮助？我对数据库主密钥和证书的使用并不完全熟悉，所以请多多包涵。

标签： sqlsql-servertsqlencryptioncertificate

解决方案

推荐阅读