测试例子: 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'--切換登入賬號 goopen 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')) goclose 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'--到期日期 goopen symmetric key Salary_user_key decryption by password='roy666'--開啟對稱金鑰 go --密碼新增加密,然後由憑證移除加密alter symmetric key Salary_user_key add encryption by certificate SalaryCertgo 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 Salarygo /* 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
--
就是读\写数据有点不方便,
必须先解密后才能读写数据。。
没有解密读出来的数据为GUID格式
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'--切換登入賬號
goopen 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'))
goclose 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'--到期日期
goopen symmetric key Salary_user_key decryption by password='roy666'--開啟對稱金鑰
go
--密碼新增加密,然後由憑證移除加密alter symmetric key Salary_user_key add encryption by certificate SalaryCertgo
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 Salarygo
/*
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