--为用户XuWei创建证书
USE SQLHOL
GO
CREATE CERTIFICATE CERT_XuWei AUTHORIZATION XuWei
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Certificate For Database',
START_DATE = '01/01/2006',
EXPIRY_DATE = '12/31/2015'
GO
--为用户WangYu创建证书
USE SQLHOL
GO
CREATE CERTIFICATE CERT_WangYu AUTHORIZATION WangYu
ENCRYPTION BY PASSWORD = 'pGFD4bb92sdf23bd2439587y'
WITH SUBJECT = 'Certificate For Database',
START_DATE = '01/01/2006',
EXPIRY_DATE = '12/31/2015'
GO
--为用户创建对称密钥，并用其对应的证书对密钥进行加密
USE SQLHOL
GO
CREATE SYMMETRIC KEY Key_SYM_XuWei AUTHORIZATION XuWei
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CERT_XuWei
GO
CREATE SYMMETRIC KEY Key_SYM_WangYu AUTHORIZATION WangYu
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CERT_WangYu
GO
--创建实验用数据表，并授予用户对表的访问权限
USE SQLHOL
GO
CREATE TABLE dbo.Tab1
(
PT nchar(10),
ET varbinary(128),
)
GO
GRANT SELECT, INSERT ON Tab1 TO XuWei
GRANT SELECT, INSERT ON Tab1 TO WangYu
GO
--利用用户XuWei的身份写入一行加密数据
USE SQLHOL
GO
EXECUTE AS LOGIN = 'XuWei'
OPEN SYMMETRIC KEY Key_SYM_XuWei DECRYPTION BY CERTIFICATE CERT_XuWei WITH PASSWORD='pGFD4bb925DGvbd2439587y'
INSERT INTO Tab1
VALUES (N'XuWei',EncryptByKey(Key_GUID('Key_SYM_XuWei'), N'I am XuWei'))
CLOSE ALL SYMMETRIC KEYS
REVERT
GO
SELECT * FROM tab1
SELECT user
--利用用户WangYu的身份写入一行加密数据
USE SQLHOL
GO
EXECUTE AS LOGIN = 'WangYu'
OPEN SYMMETRIC KEY Key_SYM_WangYu DECRYPTION BY CERTIFICATE CERT_WangYu WITH PASSWORD = 'pGFD4bb92sdf23bd2439587y'
INSERT INTO Tab1
VALUES (N'WangYu',EncryptByKey(Key_GUID('Key_SYM_WangYu'), N'I am WangYu'))
CLOSE ALL SYMMETRIC KEYS
REVERT
GO
SELECT * FROM tab1
SELECT user
--检视加密后的数据表数据
USE SQLHOL
GO
SELECT * FROM Tab1
GO
OPEN SYMMETRIC KEY Key_SYM_XuWei DECRYPTION BY CERTIFICATE CERT_XuWei WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'
SELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM Tab1
CLOSE ALL SYMMETRIC KEYS
--利用XuWei的身份对加密数据进行解密
USE SQLHOL
GO
EXECUTE AS USER = 'XuWei'
OPEN SYMMETRIC KEY Key_SYM_XuWei DECRYPTION BY CERTIFICATE CERT_XuWei WITH PASSWORD = 'pGFD4bb925DGvbd2439587y'
SELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM Tab1
CLOSE ALL SYMMETRIC KEYS
GO
REVERT
GO
--利用WangYu的身份对加密数据进行解密
USE SQLHOL
GO
EXECUTE AS LOGIN = 'WangYu'
OPEN SYMMETRIC KEY Key_SYM_WangYu DECRYPTION BY CERTIFICATE CERT_WangYu WITH PASSWORD = 'pGFD4bb92sdf23bd2439587y'
SELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM Tab1
CLOSE ALL SYMMETRIC KEYS
GO
REVERT
GO
SELECT USER
select * from sys.certificates
select * from sys.asymmetric_keys
select * from sys.symmetric_keys
5259
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
