Home >Database >Mysql Tutorial >SQL Server 2008中使用稀疏列和列集的方法(二)_MySQL

SQL Server 2008中使用稀疏列和列集的方法(二)_MySQL

WBOY
WBOYOriginal
2016-06-01 14:00:07994browse

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn