sqlserver2008
对列集实施安全
对列集实施安全就像对其它字段实施安全一样，但是稀疏列的权限可能会影响从列集获取数据。让我们做些测试。
首先，让我们授予对所有稀疏列的select权限，并试图从列集获取数据。你需要有一个用于这个测试的单独账户。如果你没有额外的账户，那么创建一个登录和一个用户为user1。让我们使用user1权限来试着获取数据。
代码1：使用user1的帐户获取和更新数据。
--set the execution context to the user user1
execute as user = 'user1'
-- select statement 1
select gender, telephone, monthlyincome, comments from customers
-- select statement 2
select allsparsecolumns from customers
-- select statement 3
update dbo.customers
set gender = 1
where id = 3
-- select statement 4
update dbo.customers
set [allsparsecolumns] = '777225656test msg1'
where id = 3
revert
代码2：将稀疏列的select权限授予user1并执行代码1。-- grant select permission to all sparse columns
grant select (gender, telephone, monthlyincome, comments) on object::dbo.customers to user1
-- execute the code 1:
-- select statement 1 - will success
-- select statement 2 - will fail
-- select statement 3 - will fail
-- select statement 4 - will fail
-- remove select permission from user1
revoke select (id, gender, telephone, monthlyincome, comments) on object::dbo.customers to user1
尽管我们授予了对所有稀疏列的select权限，但是用户却不能从列集获取数据。它要求显式的select权限。但是如果我们授予稀疏列上的select和update权限，user1就将可以访问这个列集。但是user1不能更新这个列集。
代码3：授予稀疏列上的select和update权限给user1并执行代码1。
 -- grant select permission to all sparse columns
grant select, update (gender, telephone, monthlyincome, comments) on object::dbo.customers to user1
-- execute the code 1
-- select statement 1 - will success
-- select statement 2 - will success
-- update statement 3 - will success
-- update statement 4 - will fail
-- remove select, and update permissions from user1
revoke select, update (id, gender, telephone, monthlyincome, comments) on object::dbo.customers to user1
现在让我们授予对列集的select权限，并尝试访问稀疏列。
代码4授予列集上的select权限给user1并执行代码1。
-- grant select permission to the column set
grant select (allsparsecolumns) on object::dbo.customers to user1
-- execute the code 1
-- select statement 1 - will fail
-- select statement 2 - will success
-- update statement 3 - will fail
-- update statement 4 - will fail
-- remove select permission from user1
revoke select (allsparsecolumnss) on object::dbo.customers to user1
就像代码3中的代码一样，如果我们授予对列集的select和update权限给user1，那么select语句2将会成功。此外，user1将可以对列集执行update语句，但不能对稀疏列执行update语句。看下面的代码5。
代码5：授予对列集的select和update权限给user1并执行代码1。
 -- grant select and update permissions to the column set
grant select, update (allsparsecolumns) on object::dbo.customers to user1
-- execute the code 1
-- select statement 1 - will success
-- select statement 2 - will success
-- update statement 3 - will fail
-- update statement 4 - will success
-- remove select and update permission from user1
revoke select, update (allsparsecolumnss) on object::dbo.customers to user1
现在让我们测试deny权限是怎样传播的。让我们授予对稀疏列的select权限并拒绝对列集select的权限。正如你所预料的，user1将可以访问所有的稀疏列，但不能访问列集。拒绝对列集select的权限不会影响稀疏列。
代码6：授予对稀疏列select的权限并拒绝列集的select权限给user1并执行代码1。
 -- grant select permission on sparse columns
grant select (id, gender, telephone, monthlyincome, comments) on object::dbo.customers to user1
-- deny select permission on the column set
deny select (allsparsecolumns) on object::dbo.customers to user1
-- execute the code 1
-- select statement 1 - will success
-- select statement 2 - will fail
-- update statement 3 - will fail
-- update statement 4 - will fail
revoke all on object::dbo.customers to user1
go
但是当对稀疏列select的权限被拒绝时，它会传播到列集。看代码7。user1将不能访问到列集，即使我们授予了列集上的select权限。
代码7拒绝对稀疏列select的权限并授予对列集select的权限给user1并执行代码1。
-- deny select permission on sparse columns
deny select (id, gender, telephone, monthlyincome, comments) on object::dbo.customers to user1
-- grant select permission on the column set
grant select (allsparsecolumns) on object::dbo.customers to user1
-- execute the code 1
-- select statement 1 - will fail
-- select statement 2 - will fail
-- update statement 3 - will fail
-- update statement 4 - will fail
revoke all on object::dbo.customers to user1
go