没有证书的MS SQL列加密 [英] MS SQL column encryption without certificate
本文介绍了没有证书的MS SQL列加密的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试加密SQL Server表中的一个敏感列。我用这个脚本尝试了AES256加密,它完美无缺。但我不想在我的SQL Server上创建证书或对称密钥,因为它可能会在以后出现安全问题。如何在查询中使用单个密码或密钥加密数据?这种方法的另一个问题是,我只能使用一个主密码进行加密。
I am trying to encrypt one of my sensitive columns in a SQL Server table. I tried AES256 encryption with this script, and it works perfect. But I don't want to create Certificates, or Symmetric Keys on my SQL Server as it may be a security problem later. How can I encrypt the data with a single password or key in my query ? Another problem with this method is, I can use only a single master password for my encryption.
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '$Passw0rd'
GO
CREATE CERTIFICATE AESEncryptTestCert
WITH SUBJECT = 'AESEncrypt'
GO
CREATE SYMMETRIC KEY AESEncrypt
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE AESEncryptTestCert;
OPEN SYMMETRIC KEY AESEncrypt DECRYPTION
BY CERTIFICATE AESEncryptTestCert
SELECT ENCRYPTBYKEY(KEY_GUID('AESEncrypt'),'The text to be encrypted');
推荐答案
Passw0rd'
GO
创建 CERTIFICATE AESEncryptTestCert
WITH SUBJECT = < span class =code-string>' AESEncrypt'
GO
CREATE SYMMETRIC KEY AESEncrypt
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE AESEncryptTestCert;
OPEN SYMMETRIC KEY AESEncrypt DECRYPTION
< span class =code-keyword> BY CERTIFICATE AESEncryptTestCert
SELECT ENCRYPTBYKEY(KEY_GUID('' AESEncrypt'),' 加密');
Passw0rd' GO CREATE CERTIFICATE AESEncryptTestCert WITH SUBJECT = 'AESEncrypt' GO CREATE SYMMETRIC KEY AESEncrypt WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE AESEncryptTestCert; OPEN SYMMETRIC KEY AESEncrypt DECRYPTION BY CERTIFICATE AESEncryptTestCert SELECT ENCRYPTBYKEY(KEY_GUID('AESEncrypt'),'The text to be encrypted');
这篇关于没有证书的MS SQL列加密的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文