SQL Server 2005
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CertTest;
--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(200),pwd VARCHAR(200))
INSERT INTO t_A
SELECT 1,'aa','asd123'
UNION ALL
SELECT 2,'bb','123wer'
UNION ALL
SELECT 3,'cc','ryrty'
UNION ALL
SELECT 4,'dd','sdfsd'
CREATE TABLE t_B
(ID int,name VARCHAR(200),pwd VARCHAR(200))
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
--利用这个密钥加密数据并插入新建的表
insert t_B(
ID,
name,
pwd
)
select
ID,
name,
pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
from t_Aselect * from t_BOPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
select
ID,
name,
pwd = convert(varchar(200), DecryptByKey(pwd))
from t_B drop table t_A
drop table t_B每次运行的结果都不一样,结果为何会出现好多种。---------------------------------------------------------------------------------------------------------------------------------------------------------------------ps.具体可参照http://blog.csdn.net/yole_grise/article/details/14226773
sql server 2005加密对称密钥
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CertTest;
--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(200),pwd VARCHAR(200))
INSERT INTO t_A
SELECT 1,'aa','asd123'
UNION ALL
SELECT 2,'bb','123wer'
UNION ALL
SELECT 3,'cc','ryrty'
UNION ALL
SELECT 4,'dd','sdfsd'
CREATE TABLE t_B
(ID int,name VARCHAR(200),pwd VARCHAR(200))
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
--利用这个密钥加密数据并插入新建的表
insert t_B(
ID,
name,
pwd
)
select
ID,
name,
pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
from t_Aselect * from t_BOPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
select
ID,
name,
pwd = convert(varchar(200), DecryptByKey(pwd))
from t_B drop table t_A
drop table t_B每次运行的结果都不一样,结果为何会出现好多种。---------------------------------------------------------------------------------------------------------------------------------------------------------------------ps.具体可参照http://blog.csdn.net/yole_grise/article/details/14226773
sql server 2005加密对称密钥
你看是这样不,你把t_B表的字段pwd的字段类型改为varbinary(200),就可以了,这时效果:
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CertTest;
--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(200),pwd VARCHAR(200)) INSERT INTO t_A
SELECT 1,'aa','asd123'
UNION ALL
SELECT 2,'bb','123wer'
UNION ALL
SELECT 3,'cc','ryrty'
UNION ALL
SELECT 4,'dd','sdfsd' CREATE TABLE t_B
(
ID int,name VARCHAR(200),
pwd varbinary(200) --必须是varbinary类型才可以的,因为加密后是2进制的数据
)
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
--利用这个密钥加密数据并插入新建的表
insert t_B(
ID,
name,
pwd
)
select
ID,
name,
pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
from t_Aselect * from t_B
/*
ID name pwd
1 aa 0x002B0AE13562624CA394BF2AFE093EBC01000000B0E19D45B958E79FCEABFF76E195FB465590B68ACB8554852D1F39C2F91FBC25
2 bb 0x002B0AE13562624CA394BF2AFE093EBC0100000041D7A459EF5A335BB0F98F0D073FAF903F28C0BD80E3EC8443C87712E3D530FB
3 cc 0x002B0AE13562624CA394BF2AFE093EBC0100000088A2A0442E021C36FC3099F69C0013C0EDF5113472381436233B7B01D57316C1
4 dd 0x002B0AE13562624CA394BF2AFE093EBC01000000666BD66A4A090E6EFCD487BDF7DD7918B05A6D5206A02D4249C4E6F604259F3A
*/OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
select
ID,
name,
pwd = convert(varchar(200), DecryptByKey(pwd))
from t_B
/*
ID name pwd
1 aa asd123
2 bb 123wer
3 cc ryrty
4 dd sdfsd
*/drop table t_A
drop table t_B
这样可以~
要是我想实现t_B中pwd不用解密密钥查上去是空的,有什么好办法么?
你看是这样不,你把t_B表的字段pwd的字段类型改为varbinary(200),就可以了,这时效果:
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CertTest;
--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(200),pwd VARCHAR(200)) INSERT INTO t_A
SELECT 1,'aa','asd123'
UNION ALL
SELECT 2,'bb','123wer'
UNION ALL
SELECT 3,'cc','ryrty'
UNION ALL
SELECT 4,'dd','sdfsd' CREATE TABLE t_B
(
ID int,name VARCHAR(200),
pwd varbinary(200) --必须是varbinary类型才可以的,因为加密后是2进制的数据
)
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
--利用这个密钥加密数据并插入新建的表
insert t_B(
ID,
name,
pwd
)
select
ID,
name,
pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
from t_Aselect * from t_B
/*
ID name pwd
1 aa 0x002B0AE13562624CA394BF2AFE093EBC01000000B0E19D45B958E79FCEABFF76E195FB465590B68ACB8554852D1F39C2F91FBC25
2 bb 0x002B0AE13562624CA394BF2AFE093EBC0100000041D7A459EF5A335BB0F98F0D073FAF903F28C0BD80E3EC8443C87712E3D530FB
3 cc 0x002B0AE13562624CA394BF2AFE093EBC0100000088A2A0442E021C36FC3099F69C0013C0EDF5113472381436233B7B01D57316C1
4 dd 0x002B0AE13562624CA394BF2AFE093EBC01000000666BD66A4A090E6EFCD487BDF7DD7918B05A6D5206A02D4249C4E6F604259F3A
*/OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
select
ID,
name,
pwd = convert(varchar(200), DecryptByKey(pwd))
from t_B
/*
ID name pwd
1 aa asd123
2 bb 123wer
3 cc ryrty
4 dd sdfsd
*/drop table t_A
drop table t_B
这样可以~
要是我想实现t_B中pwd不用解密密钥查上去是空的,有什么好办法么?
你看是这样不,你把t_B表的字段pwd的字段类型改为varbinary(200),就可以了,这时效果:
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CertTest;
--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(200),pwd VARCHAR(200)) INSERT INTO t_A
SELECT 1,'aa','asd123'
UNION ALL
SELECT 2,'bb','123wer'
UNION ALL
SELECT 3,'cc','ryrty'
UNION ALL
SELECT 4,'dd','sdfsd' CREATE TABLE t_B
(
ID int,name VARCHAR(200),
pwd varbinary(200) --必须是varbinary类型才可以的,因为加密后是2进制的数据
)
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
--利用这个密钥加密数据并插入新建的表
insert t_B(
ID,
name,
pwd
)
select
ID,
name,
pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
from t_Aselect * from t_B
/*
ID name pwd
1 aa 0x002B0AE13562624CA394BF2AFE093EBC01000000B0E19D45B958E79FCEABFF76E195FB465590B68ACB8554852D1F39C2F91FBC25
2 bb 0x002B0AE13562624CA394BF2AFE093EBC0100000041D7A459EF5A335BB0F98F0D073FAF903F28C0BD80E3EC8443C87712E3D530FB
3 cc 0x002B0AE13562624CA394BF2AFE093EBC0100000088A2A0442E021C36FC3099F69C0013C0EDF5113472381436233B7B01D57316C1
4 dd 0x002B0AE13562624CA394BF2AFE093EBC01000000666BD66A4A090E6EFCD487BDF7DD7918B05A6D5206A02D4249C4E6F604259F3A
*/OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
select
ID,
name,
pwd = convert(varchar(200), DecryptByKey(pwd))
from t_B
/*
ID name pwd
1 aa asd123
2 bb 123wer
3 cc ryrty
4 dd sdfsd
*/drop table t_A
drop table t_B
这样可以~
要是我想实现t_B中pwd不用解密密钥查上去是空的,有什么好办法么?这个没什么好办法,你想,你把数据加密了,然后把密文,放到了列里面,到时候你需要从把这些列读取密文,然后解密。如果你的这个加密的字段里是一堆的NULL,或者空串,那么解密的时候,就没办法解密了。