SQL Server 关于列的权限控制-白红宇

原文:

在SQL SERVER中列权限（Column Permissions）其实真没有什么好说的，但是好多人对这个都不甚了解，已经被人问了几次了，所以还是在这里介绍一下，很多人都会问，我能否单独对表的某列授权给某个用户？答案是可以，我们可以对表中的列授予SELECT、UPDATE权限，我们结合下面的简单案例来阐述一下可能效果更好。

案例1：在AdventureWorks2014中，登录名UserA 只能有权限查询[Person].[Person]里面的BusinessEntityID, NationalIDNumber, LoginID三个字段权限，不能查询其它字段

USE [master]
GO
CREATE LOGIN [UserA] WITH PASSWORD=N'UserA', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2014]
GO
CREATE USER [UserA] FOR LOGIN [UserA]
GO

给用户授予相关列的查询权限（SELECT）

GRANT SELECT(BusinessEntityID, NationalIDNumber, LoginID) ON  [HumanResources].[Employee] TO [UserA]

此时你可以用下面SQL查看授予UserA的权限：

SELECT  dp.grantee_principal_id ,
        P.name AS UName ,
        dp.permission_name ,
        C.name ,
        OBJECT_NAME(O.object_id) AS TabName
FROM    sys.database_permissions dp
        INNER JOIN sys.objects O ON dp.major_id = O.object_id
        INNER JOIN sys.columns C ON C.object_id = O.object_id
                                    AND C.column_id = dp.minor_id
        INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id;

以用户UserA登录，如下所示，如果查询语句使用BusinessEntityID, NationalIDNumber, LoginID字段之外的其它字段，就会出现类似下面错误，当然也不能使用SELECT *之类的查询语句。

Msg 230, Level 14, State 1, Line 8

The SELECT permission was denied on the column 'JobTitle' of the object 'Employee', database 'AdventureWorks2014', schema 'HumanResources'.

另外，也可以只授权用户更新某个列，例如对于登录名UserB，只允许其修改Person.Address的AddressLine1,AddressLine2两个字段，其它字段不许修改。

GRANT UPDATE(AddressLine1,AddressLine2) ON [Person].[Address] TO UserB;
 
SELECT  dp.grantee_principal_id ,
        P.name AS UName ,
        dp.permission_name ,
        C.name ,
        OBJECT_NAME(O.object_id) AS TabName
FROM    sys.database_permissions dp
        INNER JOIN sys.objects O ON dp.major_id = O.object_id
        INNER JOIN sys.columns C ON C.object_id = O.object_id
                                    AND C.column_id = dp.minor_id
        INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id
WHERE P.name='UserB'

另外，关于DELETE、INSERT权限，这个是没有所谓的列权限（Column Permissions）的，其实从逻辑上想想，你也能明白，这这两者对应的最小单位为一条记录，所以根本不能再细化到列级别了。

Msg 1020, Level 15, State 1, Line 36

Sub-entity lists (such as column or security expressions) cannot be specified for entity-level permissions.