实例讲解SQL Server加密功能
16lz
2022-01-09
SQL Server中加密是层级的,每一个上层为下提供保护。如图:
实例:
/**
SMK(Service Master Key)在SQL Server安装时生成,由Windows DPAPI(Data Protection API)提供保护
**/
/**创建数据库级别DMK(Database Master Key),受SMK保护**/
create master key encryption by password=N'Passw0rd'
go
/**数据库内的加密对象受DMK保护
支持的对称加密算法:DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128| DESX | AES_128 | AES_192 | AES_256
非对称加密算法:RSA_512 | RSA_1024 | RSA_2048
注意避免使用RC,DESX类算法,2014之后会删除此功能
**/
--1.创建非对称密钥.
create asymmetric key asyc_key_enc with algorithm=RSA_1024 encryption by password=N'Pass@word' go
create symmetric key symc_key_enc with algorithm=Triple_DES encryption by password=N'Pass@word' go
create certificate cert_ENC with subject='certificate for ENC',expiry_date='20990101' go
--4.1 由非对称密钥加密
create symmetric key symc_key_enc_byAsyc with algorithm=AES_128 encryption by asymmetric key asyc_key_enc go
open symmetric key symc_key_enc decryption by password=N'Pass@word'; create symmetric key symc_key_enc_bySymc with algorithm = DES encryption by symmetric key symc_key_enc go
create symmetric key symc_key_enc_byCert with algorithm =AES_128 encryption by certificate cert_ENC go
--以ENCRYPTBYKEY为例,其它的大同小异
--对***号IDN进行加密和解密
create table tb(IDN int,Name varchar(20)); insert into tb values (123456789,'BigBrother'),(090807001,'SpiderMan'),(336655789,'SuperMan') go
alter table tb add Ency_IDN varbinary(128); go open symmetric key symc_key_enc_byAsyc decryption by asymmetric key asyc_key_enc with password=N'Pass@word'; update tb set Ency_IDN=ENCRYPTBYKEY(KEY_GUID('symc_key_enc_byAsyc'),CONVERT(Varbinary,IDN));--加密前要转成varbinary close symmetric key symc_key_enc_byAsyc --显式关闭对称密钥 go
open symmetric key symc_key_enc_byAsyc decryption by asymmetric key asyc_key_enc with password=N'Pass@word'; select IDN,Ency_IDN,convert(int,DECRYPTBYKEY(Ency_IDN))as Decr_IDN from tb; close symmetric key symc_key_enc_byAsyc --显式关闭对称密钥 go1<br>
更多相关文章
- 《Android和PHP最佳实践》官方站
- android用户界面之按钮(Button)教程实例汇
- [Android(安卓)NDK]Android(安卓)JNI开发例子 ---3 在JNI中实现o
- TabHost与RadioGroup结合完成的菜单【带效果图】5个Activity
- android 拨打紧急号码,通话时开启免提功能实现
- Android(安卓)下拉刷新,非常强大的下拉刷新功能
- Android中判断网络功能是否可用
- Android(安卓)UI开发第十七篇——Android(安卓)Fragment实例(Lis
- Android——Activity四种启动模式