22,207
社区成员
发帖
与我相关
我的任务
分享
use test
go
create login Login_roy with password='roy888'--創建登陸名
go
create user User_roy for login Login_roy --創建用戶名
create master key encryption by password='pass123456' --建立資料庫主要金鑰
go
create table Salary(
employee nvarchar(50),
salary varbinary(100)
)
go
grant select,insert to User_roy --授於roy用戶select\insert權限
go
create symmetric key Salary_user_key --建立對稱金鑰
authorization User_roy WITH algorithm= triple_des
encryption by password='roy666'
go
execute as login='Login_roy'--切換登入賬號
go
open symmetric key Salary_user_key decryption by password='roy666'--開啟對稱金鑰
go
select * from sys.openkeys--查詢結果,沒開啟時不會有結果
go
--以下操作表數據
--1新增
insert Salary values(N'程序員A',encryptbykey(Key_GUID('Salary_user_key'),'8000'))
insert Salary values(N'程序員B',encryptbykey(Key_GUID('Salary_user_key'),'10000'))
go
close all symmetric keys--關閉所有金鑰
go
select * from Salary
/*
employee salary
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
程序員A 0x0008BCED7496E247903671B6691E76BE01000000E2A581890D0CD44F17D9FD577F5B4F429538FFED339CE21F
程序員B 0x0008BCED7496E247903671B6691E76BE01000000F4F3204491C2114BDE26B09695B87A2CDC90D1002708FDD0
(2 個資料列受到影響)
*/
go
open symmetric key Salary_user_key decryption by password='roy666'--開啟對稱金鑰
go
SELECT
employee,
CONVERT(varchar,decryptByKey(salary))
FROM Salary
/*
employee
-------------------------------------------------- ------------------------------
程序員A 8000
程序員B 10000
(2 個資料列受到影響)
*/
go
insert Salary values(N'程序員C',encryptbykey(Key_GUID('Salary_user_key'),'12000'))
go
close all symmetric keys--關閉所有金鑰
go
SELECT
employee,
CONVERT(varchar,decryptByKey(salary))
FROM Salary
/*
employee
-------------------------------------------------- ------------------------------
程序員A NULL
程序員B NULL
程序員C NULL
(3 個資料列受到影響)
*/
revert; --切換回最後一個 EXECUTE AS 陳述式的登陸者
go
--用憑證方式....
create certificate SalaryCert --將憑證加入至資料庫中
authorization User_roy
with subject='Certificate person',
expiry_date = '12/31/3000'--到期日期
go
open symmetric key Salary_user_key decryption by password='roy666'--開啟對稱金鑰
go
--密碼新增加密,然後由憑證移除加密
alter symmetric key Salary_user_key add encryption by certificate SalaryCert
go
alter symmetric key Salary_user_key drop encryption by password= 'roy666'--移除加密
GO
close all symmetric keys
go
execute as login='Login_roy'--切換登入賬號
go
open symmetric key Salary_user_key decryption by certificate SalaryCert
go
SELECT
employee,
CONVERT(varchar,decryptByKey(salary))
FROM Salary
/*
employee
-------------------------------------------------- ------------------------------
程序員A 8000
程序員B 10000
程序員C 12000
(3 個資料列受到影響)
*/
go
close all symmetric keys --關閉
go
SELECT
employee,
CONVERT(varchar,decryptByKey(salary))
FROM Salary
go
/*
employee
-------------------------------------------------- ------------------------------
程序員A NULL
程序員B NULL
程序員C NULL
(3 個資料列受到影響)
*/
--drop symmetric key Salary_user_key--禁用
--drop certificate SalaryCert--禁用
go
SELECT
employee,
CONVERT(varchar,decryptByKey(salary))
FROM Salary
--truncate table Salary