mysql 非对称密钥_sqlser 对称加密 非对称加密笔记

一：对称加密

原始明文---密钥---加密数据---密钥---原始明文

速度快，通过算法将明文混淆，占用系统资源少

二：非对称加密

加密解密速度慢，较高的系统资源占用

三：混合数据加密

加密过程：随机生成对称密钥，使用公钥加密对称密钥。

解密过程：用私钥解开被加密的对称密钥，使用对称密钥解密数据

四：备份，还key原服务主密钥(sqlserver服务器主密钥)

备份密钥

Backup service master key to file =’c:\xx.bak’;

Encryption by password = ‘password’;

实例backupservicemasterkeytofile='D:\work path\dbFile\data.bak'

encryptionbypassword='test'

还原密钥

Restore service master key from file =’c:\xx.bak’

Decryption by password=’password’;

五：创建数据库主密钥(需要手动创建数据库主密钥)

Create master key encryption by password = ‘password’

createmasterkeyencryptionbypassword='databaseTest'

备份数据库密钥

backupmasterkeytofile='D:\work path\dbFile\database.bak'encryptionbypassword='databaseTest'

六：目录视图中查看数据库密钥信息

select*fromsys.symmetric_keys

select*fromsys.databases--is_master_key_encrypted_by_server 1 表示使用服务主密钥对数据库主密钥进行加密

七：创建证书

Create certificate cert_mycert;--创建证书

Encryption by password=’’--创建证书密码

withsubject标题

创建证书时效性，需要存储过程等方式手动验证

Start_data=’’expiry_date=’’

命令如下：

Createcertificatemyfirst_cert Encryptionbypassword='myfirst_cert'withsubject='myfirst_cert',start_date='1/1/',expiry_date='1/1/'

Createcertificatetest_cert Encryptionbypassword='test_cert'withsubject='test_cert',start_date='1/1/',expiry_date='1/1/'

查询证书

Select*fromsys.certificates

八：与证书相关的函数

Encyrptbycert(id,’cleartext’);

Cert_id(‘'myfirst_cert’)

九：创建非对称密钥

使用sn.exe工具也可以创建

Createasymmetrickeyasy_key1 Withalgorithm =rsa_2048Encryptionbypassword='asy_key1'

十：创建对称密钥

Createsymmetrickeysy_key1 Withalgorithm =aes_256Encryptionbypassword='sy_key1'

Createsymmetrickeysy_key1_test Withalgorithm =aes_256Encryptionbypassword='testPassword'

十一：查询密钥

1:打开密钥指令

opensymmetrickeysy_key1 decryptionbypassword='sy_key1'

查询密钥

select * from sys.openkeys

关闭密钥

closesymmetrickeysy_key1_test

十二：使用密钥加密数据例子 (加密列不要创建索引，无意义，并且考虑列长度会变长)

declare@oldContent varbinary(200);--定义原始变量

declare@newContent varbinary(200);--定义加密后的变量

set@oldContent =convert(varbinary(200),'这是测试数据');--给原始变量赋值

set@newContent =encryptbycert(cert_id('test_cert'),@oldContent)--通过证书加密数据test_cert证书名

select@newContent --加密查询

selectconvert(varchar(200),decryptbycert(cert_id('test_cert'),@newContent ,N'test_cert'))as[ts] --解密查询test_cert证书名N'test_cert'证书密码

十三：表示例

createtableusertest(id intprimarykeyidentity(20,1),username varbinary(20),usermoney int)

insertintousertest (username,usermoney)values('aaa',encryptbycert(cert_id('test_cert'),'200'))

insertintousertest (username,usermoney)values('bbb',encryptbycert(cert_id('test_cert'),'300'))

insertintousertest (username,usermoney)values('ccc',encryptbycert(cert_id('test_cert'),'400'))

insertintousertest (username,usermoney)values('ddd',encryptbycert(cert_id('test_cert'),'500'))

正确插入数据

insertintousertest (username,usermoney)values('aaa',encryptbykey(key_guid('sy_key1_test'),'200'))

insertintousertest (username,usermoney)values('bbb',encryptbykey(key_guid('sy_key1_test'),'300'))

insertintousertest (username,usermoney)values('ccc',encryptbykey(key_guid('sy_key1_test'),'400'))

insertintousertest (username,usermoney)values('ddd',encryptbykey(key_guid('sy_key1_test'),'500'))

selectid,username,cast(decryptbykey(usermoney)asvarchar(20))as'test'fromusertest

通过验证器加入数据

加数据方法Encryptbykey(key_guid(‘证书名字’),加密值,使用验证器,’验证器的值’)

insert into usertest (username,usermoney) values('aaa',encryptbykey(key_guid('sy_key1_test'),'600',1,'20') )

insert into usertest (username,usermoney) values('bbb',encryptbykey(key_guid('sy_key1_test'),'300',1,'21') )

insert into usertest (username,usermoney) values('ccc',encryptbykey(key_guid('sy_key1_test'),'400',1,'22') )

insert into usertest (username,usermoney) values('ddd',encryptbykey(key_guid('sy_key1_test'),'500',1,'23') )

解密方法

Cast(Decryptbykey(解密列1,1,cast(验证器值as varcahar(100))) as varchar(200))

select id,username,cast(decryptbykey(usermoney,1,cast(id as varchar(3))) as varchar(20)) as 'test' from usertest