Chapter 3 Protecting the Data（2）：分配列级权限，chapterprotecting

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39577861，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

未经作者同意，任何人不得以“原创”形式发布，也不得已用于商业用途，本人不负责任何法律责任。

        前一篇：http://blog.csdn.net/dba_huangzj/article/details/39548665

 

前言：

 

SQL Server的权限是有层次的，一个用户有架构级别的权限，就有了架构内部所有对象的权限，除非使用了DENY权限单独移除。但是对象并不是层次中的最低级，可以把权限设置到列级别。但是列级权限会覆盖掉表上被GRANT的权限。

 

实现：

 

要实现分配列级权限，可以使用GRANT SELECT ON <对象>语句，并且加上所需的列，如：

GRANT SELECT ON OBJECT::dbo.Employee (EmployeeId, LastName, Firstname, email) TO HumanResourceAssistant;

这个语句会仅供HumanResourceAssistant数据库角色成员能读取dbo.Employee 表上的EmployeeId, LastName, Firstname, email这三列，表上的其他列将不能被查询。（该表上还有Birthdate和Salary列），那么下面的查询中，第一个语句可以执行但是第二个语句不能执行，因为它需要使用到Salary列：

--能执行
SELECT FirstName + ' ' + LastName as Employee 
FROM dbo.Employee 
ORDER BY LastName, FirstName;
--不能执行 
SELECT FirstName + ' ' + LastName as Employee 
FROM dbo.Employee 
ORDER BY Salary DESC;

 

可以使用下面语句修改，使其可以查询全表的数据，但是仅能更新表上的三列：

GRANT SELECT ON OBJECT::dbo.Employee TO HumanResourceEmployee; 
GRANT UPDATE ON OBJECT::dbo.Employee (LastName, Firstname, email) TO HumanResourceEmployee;

原理：

 

当用户尝试查询未被授权的列是，会收到230错误：

The SELECT permission was denied on the column 'Salary' of the object 'Employee', database 'HumanResource', schema 'dbo'.

 

但是要注意权限的修改，特别是对DENY的使用，避免过多使用最底层的权限设置而导致权限策略太复杂而无法管理或者权限交叉。

 

更多：

 

列级权限会导致权限体系的不一致性，在后续版本可能被移除，建议使用视图来实现这种需求。

 

下一篇：

奥巴马在美国的总统生涯就此开始，似乎一个时代即将来临，全球众生瞩目，因为是凌晨一点多开始，我还要工作，早早的睡了，这个盛大的开幕式，我也就只能在想象一下了。今天找了奥巴马的英文版演讲稿，等回去好好看看，终究是一代黑人总统，我觉得似乎是一个重要的时刻。原文如下：

My fellow citizens:

I stand here today humbled by the task before us, grateful for the trust you have bestowed, mindful of the sacrifices borne by our ancestors. I thank President Bush for his service to our nation, as well as the generosity and cooperation he has shown throughout this transition.

Forty-four Americans have now taken the presidential oath. The words have been spoken during rising tides of prosperity and the still waters of peace. Yet, every so often the oath is taken amidst gathering clouds and raging storms. At these moments, America has carried on not simply because of the skill or vision of those in high office, but because we the people have remained faithful to the ideals of our forebears, and true to our founding documents.

So it has been. So it must be with this generation of Americans.

That we are in the midst of crisis is now well understood. Our nation is at war, against a far-reaching network of violence and hatred. Our economy is badly weakened, a consequence of greed and irresponsibility on the part of some, but also our collective failure to make hard choices and prepare the nation for a new age. Homes have been lost; jobs shed; businesses shuttered. Our health care is too costly; our schools fail too many; and each day brings further evidence that the ways we use energy strengthen our adversaries and threaten ou......余下全文>>