網路

對稱與非對稱加密TSQL

解決資料庫加密問題

加密方式有區分

1. 對稱式：加密跟解密的KEY是同一把，解密的效率很好，安全相較於非對稱式較差
2. 非對稱式：加密是public key、解密是Private Key，安全性高，但是解密會影響效能

成果展示

-- 查詢對稱式的加密金鑰
select database_id, name, is_master_key_encrypted_by_server
from sys.databases

--建立master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'qwer!@#$1234';

-- 備份master key
BACKUP MASTER KEY TO FILE = 'testkey'
ENCRYPTION BY PASSWORD = 'qwer!@#$1234'

-- 建立憑證
CREATE CERTIFICATE willis_test_cert
WITH SUBJECT = 'willis test',
EXPIRY_DATE = '20231230';
GO

-- 查詢憑證
select * from sys.certificates

-- 備份憑證
BACKUP CERTIFICATE willis_test_cert TO FILE = 'opt\certificate_name'
WITH PRIVATE KEY ( FILE = 'opt\pk_name' ,
ENCRYPTION BY PASSWORD = 'qwer!@#$1234');
GO

--  查詢目前資料庫中已建立的對稱式金鑰
select * from sys.symmetric_keys

-- 非對稱式加密
select * from sys.asymmetric_keys

-- 建立非對稱式金鑰
CREATE ASYMMETRIC KEY willis_test_asym_key
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'qwer!@#$1234'
GO

delete TestDB.dbo.Customer where sn = 1

select top 10 * from TestDB.dbo.Customer

insert into TestDB.dbo.Customer (sn,name,sid,e_sid) values (1,'willis',1,1)

UPDATE TestDB.dbo.Customer
SET e_sid
    = EncryptByAsymKey(AsymKey_ID('willis_test_asym_key'), sid)
GO

-- 使用非對稱式加密
SELECT sn, name, sid, e_sid,
CONVERT(varchar(max),
    DecryptByAsymKey(AsymKey_Id('willis_test_asym_key'),
    e_sid , N'qwer!@#$1234')) 'decrypt_e_sid'
FROM TestDB.dbo.Customer

tips

• 加密的KEY一定要備份，不然也無法解密