T-SQL????????????????????
???????????? ???????[ 2013/1/17 10:03:03 ] ????????
????3???????????
a) --?????????????????????????????????????????????????????????????
b) USE AdventureWorks
c) GO
d) CREATE ASYMMETRIC KEY AsymKey123 WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD='P@ssw0rd'
e) GO
f)
g) --??????д洢??????????
h) ALTER TABLE Person.Address ADD AddressLine3 nvarchar(MAX)
i) GO
j) --???м???
k) SELECT *??ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123')??CONVERT(VARCHAR(MAX )??AddressLine1))
l) FROM Person.Address
m) GO
n)
o) --????????μ????????
p) UPDATE Person.Address
q) SET AddressLine3=ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123')??CONVERT(VARCHAR(MAX )??AddressLine1))
r)
s)
t) SELECT *--addressline3
u) FROM Person.Address
v)
w) --???????????????????????????????????????????????????????????varchar??????????nvarchar?????????????
x) SELECT TOP 10 AddressID??CONVERT(VARCHAR(MAX)??CONVERT (VARCHAR(MAX )??DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey123')??AddressLine3??N'P@ssw0rd'))) AS Decryptedata
y) FROM Person.Address
????4?????????
a) --????????????????饗certificate??
b) CREATE CERTIFICATE certKey123--?????
c) ENCRYPTION BY PASSWORD='P@ssw0rd'--????
d) WITH SUBJECT='Address Certificate'??--???????
e) START_DATE='2012/06/18'??--?????Ч????
f) EXPIRY_DATE='2013/06/18' ;--??鵽????
g) GO
h) --??????????
i) SELECT *??ENCRYPTBYCERT(CERT_ID('certKey123')??CONVERT (VARCHAR(MAX )??AddressLine1)) cyberAddress
j) FROM Person.Address
k)
l) --??????д?????????
m) ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX )
n)
o) --????????????????
p) UPDATE Person.Address
q) SET AddressLine4=ENCRYPTBYCERT(CERT_ID('certKey123')??CONVERT (VARCHAR(MAX )??AddressLine1))
r)
s) --????
t) SELECT AddressID??CONVERT(VARCHAR(MAX )??CONVERT(VARCHAR(MAX )??DECRYPTBYCERT(CERT_ID('certKey123')??AddressLine4??N'P@ssw0rd'))) DecryAddress
u) FROM Person.Address
????5??????????
a) --?????????ù????????????????EncryptByPassPhrase???????????????????ο????????????????????????????
b) SELECT *??AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd'??CONVERT(varbinary??AddressLine1)??AddressID)
c) FROM Person.Address
d)
e) --??????д??????????ENCRYPTBYPASSPHRASE???????????VARBINARY????
f) ALTER TABLE Person.Address ADD AddressLine5 VARBINARY(256)
g)
h) --????????£??????????P@ssw0rd??AddressID?????е??????????
i)
j) UPDATE Person.Address
k) SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd'??CONVERT(varbinary??AddressLine1)??AddressID)
l)
m) SELECT * FROM Person.Address
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11