你的位置:首页 > 数据库

[数据库]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;


clipboard

 

以用户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'.

clipboard[1]

另外,也可以只授权用户更新某个列,例如对于登录名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'


clipboard[2]

 

另外,关于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.