1 --示例一,使用证书加密数据.
2
3 --建立测试数据表
4 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
5 GO
6 --建立证书一,该证书使用数据库主密钥来加密
7 CREATE CERTIFICATE Cert_Demo1
8 WITH
9 SUBJECT=N'cert1 encryption by database master key',
10 START_DATE='2008-01-01',
11 EXPIRY_DATE='2008-12-31'
12 GO
13
14 --建立证书二,该证书使用密码来加密
15 CREATE CERTIFICATE Cert_Demo2
16 ENCRYPTION BY PASSWORD='liangCK.123'
17 WITH
18 SUBJECT=N'cert1 encrption by password',
19 START_DATE='2008-01-01',
20 EXPIRY_DATE='2008-12-31'
21 GO
22
23 --此时,两个证书已经建立完,现在可以用这两个证书来对数据加密
24 --在对表tb做INSERT时,使用ENCRYPTBYCERT加密
25
26 INSERT tb(data)
27 SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'这是证书1加密的内容-liangCK'); --使用证书1加密
28
29 INSERT tb(data)
30 SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'这是证书2加密的内容-liangCK'); --使用证书2加密
31
32
33 --ok.现在已经对数据加密保证了.现在我们SELECT看看
34
35 SELECT * FROM tb ;
36
37 --现在对内容进行解密显示.
38 --解密时,使用DECRYPTBYCERT
39
40 SELECT 证书1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),
41 --使用证书2解密时,要指定DECRYPTBYCERT的第三个参数,
42 --因为在创建时,指定了ENCRYPTION BY PASSWORD.
43 --所以这里要通过这个密码来解密.否则解密失败
44 证书2解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))
45 FROM tb ;
46
47 --我们可以看到,因为第2条记录是证书2加密的.所以使用证书1将无法解密.所以返回NULL
48
49 /*
50 证书1解密 证书2解密
51 -------------------------------------------------- --------------------------------------------------
52 这是证书1加密的内容-liangCK NULL
53 NULL 这是证书2加密的内容-liangCK
54
55 (2 行受影响)
56 */
57
58 GO
59
60 --删除测试证书与数据表
61 DROP CERTIFICATE Cert_Demo1;
62 DROP CERTIFICATE Cert_Demo2;
63 DROP TABLE tb;
64 GO
65
66
67 --示例二,使用对称密钥加密数据,
68 --对称密钥又使用证书来加密.
69
70 --创建测试数据表tb
71 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
72 GO
73
74 --建立证书,该证书用于加密对称密钥.
75
76 CREATE CERTIFICATE Cert_Demo
77 ENCRYPTION BY PASSWORD=N'liangCK.123'
78 WITH
79 SUBJECT=N'cert encryption by password',
80 START_DATE='2008-01-01',
81 EXPIRY_DATE='2008-12-31'
82 GO
83
84 --建立对称密钥
85
86 CREATE SYMMETRIC KEY Sym_Demo
87 WITH
88 ALGORITHM=DES --使用DES加密算法
89 ENCRYPTION BY CERTIFICATE Cert_Demo --使用Cert_Demo证书加密
90 GO
91
92 --要使用Sym_Demo对称密钥.必需使用OPEN SYMMETRIC KEY来打开它
93
94 OPEN SYMMETRIC KEY Sym_Demo
95 DECRYPTION BY CERTIFICATE Cert_Demo
96 WITH PASSWORD=N'liangCK.123'
97
98 --插入加密数据
99
100 INSERT tb(data)
101 SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'这是加密的数据,能显示出来吗?')
102
103 --关闭密钥
104 CLOSE SYMMETRIC KEY Sym_Demo
105
106 --插入完加密数据,现在使用SELECT来查询一下数据
107
108 SELECT * FROM tb
109
110 GO
111
112 --现在来解密此数据
113
114 --同样,还是要先打开对称密钥
115
116 OPEN SYMMETRIC KEY Sym_Demo
117 DECRYPTION BY CERTIFICATE Cert_Demo
118 WITH PASSWORD=N'liangCK.123'
119
120 SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --这里可见,数据已经解密出来了.
121 FROM tb
122
123 CLOSE SYMMETRIC KEY Sym_Demo
124 GO
125
126 --删除测试
127 DROP SYMMETRIC KEY Sym_Demo
128 DROP CERTIFICATE Cert_Demo
129 DROP TABLE tb
130
131
132
133 --示例三,还有一种方法加密数据更简单
134 --就是使用EncryptByPassPhrase
135
136 --建立测试数据表tb
137
138 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
139 GO
140
141 INSERT tb(data)
142 SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容');
143
144 --解密
145
146 SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data))
147 FROM tb
148
149 GO
150 DROP TABLE tb
151
2
3 --建立测试数据表
4 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
5 GO
6 --建立证书一,该证书使用数据库主密钥来加密
7 CREATE CERTIFICATE Cert_Demo1
8 WITH
9 SUBJECT=N'cert1 encryption by database master key',
10 START_DATE='2008-01-01',
11 EXPIRY_DATE='2008-12-31'
12 GO
13
14 --建立证书二,该证书使用密码来加密
15 CREATE CERTIFICATE Cert_Demo2
16 ENCRYPTION BY PASSWORD='liangCK.123'
17 WITH
18 SUBJECT=N'cert1 encrption by password',
19 START_DATE='2008-01-01',
20 EXPIRY_DATE='2008-12-31'
21 GO
22
23 --此时,两个证书已经建立完,现在可以用这两个证书来对数据加密
24 --在对表tb做INSERT时,使用ENCRYPTBYCERT加密
25
26 INSERT tb(data)
27 SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'这是证书1加密的内容-liangCK'); --使用证书1加密
28
29 INSERT tb(data)
30 SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'这是证书2加密的内容-liangCK'); --使用证书2加密
31
32
33 --ok.现在已经对数据加密保证了.现在我们SELECT看看
34
35 SELECT * FROM tb ;
36
37 --现在对内容进行解密显示.
38 --解密时,使用DECRYPTBYCERT
39
40 SELECT 证书1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),
41 --使用证书2解密时,要指定DECRYPTBYCERT的第三个参数,
42 --因为在创建时,指定了ENCRYPTION BY PASSWORD.
43 --所以这里要通过这个密码来解密.否则解密失败
44 证书2解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))
45 FROM tb ;
46
47 --我们可以看到,因为第2条记录是证书2加密的.所以使用证书1将无法解密.所以返回NULL
48
49 /*
50 证书1解密 证书2解密
51 -------------------------------------------------- --------------------------------------------------
52 这是证书1加密的内容-liangCK NULL
53 NULL 这是证书2加密的内容-liangCK
54
55 (2 行受影响)
56 */
57
58 GO
59
60 --删除测试证书与数据表
61 DROP CERTIFICATE Cert_Demo1;
62 DROP CERTIFICATE Cert_Demo2;
63 DROP TABLE tb;
64 GO
65
66
67 --示例二,使用对称密钥加密数据,
68 --对称密钥又使用证书来加密.
69
70 --创建测试数据表tb
71 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
72 GO
73
74 --建立证书,该证书用于加密对称密钥.
75
76 CREATE CERTIFICATE Cert_Demo
77 ENCRYPTION BY PASSWORD=N'liangCK.123'
78 WITH
79 SUBJECT=N'cert encryption by password',
80 START_DATE='2008-01-01',
81 EXPIRY_DATE='2008-12-31'
82 GO
83
84 --建立对称密钥
85
86 CREATE SYMMETRIC KEY Sym_Demo
87 WITH
88 ALGORITHM=DES --使用DES加密算法
89 ENCRYPTION BY CERTIFICATE Cert_Demo --使用Cert_Demo证书加密
90 GO
91
92 --要使用Sym_Demo对称密钥.必需使用OPEN SYMMETRIC KEY来打开它
93
94 OPEN SYMMETRIC KEY Sym_Demo
95 DECRYPTION BY CERTIFICATE Cert_Demo
96 WITH PASSWORD=N'liangCK.123'
97
98 --插入加密数据
99
100 INSERT tb(data)
101 SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'这是加密的数据,能显示出来吗?')
102
103 --关闭密钥
104 CLOSE SYMMETRIC KEY Sym_Demo
105
106 --插入完加密数据,现在使用SELECT来查询一下数据
107
108 SELECT * FROM tb
109
110 GO
111
112 --现在来解密此数据
113
114 --同样,还是要先打开对称密钥
115
116 OPEN SYMMETRIC KEY Sym_Demo
117 DECRYPTION BY CERTIFICATE Cert_Demo
118 WITH PASSWORD=N'liangCK.123'
119
120 SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --这里可见,数据已经解密出来了.
121 FROM tb
122
123 CLOSE SYMMETRIC KEY Sym_Demo
124 GO
125
126 --删除测试
127 DROP SYMMETRIC KEY Sym_Demo
128 DROP CERTIFICATE Cert_Demo
129 DROP TABLE tb
130
131
132
133 --示例三,还有一种方法加密数据更简单
134 --就是使用EncryptByPassPhrase
135
136 --建立测试数据表tb
137
138 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
139 GO
140
141 INSERT tb(data)
142 SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容');
143
144 --解密
145
146 SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data))
147 FROM tb
148
149 GO
150 DROP TABLE tb
151
