[MSSQL] 암호화/복호화

<대칭키 생성>
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test'

CREATE CERTIFICATE certificate_user_pw with subject ='teset'

create symmetric key symmetricUserPw with algorithm = AES_256
encryption by certificate certificate_user_pw

 

<암호화>

OPEN SYMMETRIC KEY symmetricUserPw DECRYPTION BY CERTIFICATE  certificate_user_pw;

insert into users values(1,'test',ENCRYPTBYKEY(KEY_GUID('symmetricUserPw'),'1234'),'test') 

CLOSE SYMMETRIC KEY symmetricUserPw

 

<복호화>

open symmetric key symmetricUserPw Decryption by certificate certificate_user_pw

select user_id, convert(varchar(MAX),DECRYPTBYKEY(user_pw)) as user_pw from users

close symmetric key symmetricUserpw