22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
-- 建立使用密码加密私钥的非对称密钥
CREATE ASYMMETRIC KEY ASYM_Test
WITH
ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = N'abc.124'
GO
-- 加密/解密数据
SELECT
Encrypt = ENCRYPTBYASYMKEY(ASYMKEY_ID(N'ASYM_Test'), N'加密'),
Decrypt = CONVERT(nvarchar(100),
DECRYPTBYASYMKEY(
ASYMKEY_ID(N'ASYM_Test'),
ENCRYPTBYASYMKEY(ASYMKEY_ID(N'ASYM_Test'), N'解密'),
N'abc.124'))
GO
-- 删除测试环境
DROP ASYMMETRIC KEY ASYM_Test
go
USE tempdb
GO
-- 创建使用密码加密的对称密钥
CREATE SYMMETRIC KEY SYM_Test
WITH
ALGORITHM = AES_128
ENCRYPTION BY
PASSWORD = N'abc.password_1'
-- 添加一个加密对称密钥的密码
-- 也可以在创建对称密钥时, 在 ENCRYPTION BY 子句中指定两个密码(指定两个 PASSWORD 项)
OPEN SYMMETRIC KEY SYM_Test
DECRYPTION BY PASSWORD = N'abc.password_1'
ALTER SYMMETRIC KEY SYM_Test
ADD
ENCRYPTION BY PASSWORD = N'abc.password_2'
CLOSE SYMMETRIC KEY SYM_Test
GO
-- 使用对称密钥加密数据
OPEN SYMMETRIC KEY SYM_Test
DECRYPTION BY PASSWORD = N'abc.password_1'
DECLARE
@ciphertext varbinary(1000)
SELECT
@ciphertext = ENCRYPTBYKEY(KEY_GUID(N'SYM_Test'), N'测试对称密钥加密')
CLOSE SYMMETRIC KEY SYM_Test
-- 使用另一个密码打开对称密钥进行数据解密
OPEN SYMMETRIC KEY SYM_Test
DECRYPTION BY PASSWORD = N'abc.password_2'
SELECT
CONVERT(nvarchar(10), DECRYPTBYKEY(@ciphertext))
CLOSE SYMMETRIC KEY SYM_Test
GO
-- 删除测试环境
DROP SYMMETRIC KEY SYM_Test
CREATE SYMMETRIC KEY key1 WITH ALGORITHM=DES
ENCRYPTION BY PASSWORD='abc$123'
GO
OPEN SYMMETRIC KEY key1
DECRYPTION BY PASSWORD='abc$123'
DECLARE @str NVARCHAR(100),@Encrypted VARBINARY(MAX)
SET @str=N'要加密的字串'
SELECT @Encrypted=ENCRYPTBYKEY(KEY_GUID('key1'),@str)
SELEcT '加密'=@Encrypted ,'解密'=CAST(DECRYPTBYKEY(@Encrypted) AS NVARCHAR(100))
CLOSE SYMMETRIC KEY key1
GO
DROP SYMMETRIC KEY key1
GO