????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