您的位置:首页 > 数据库 > 数据库管理 > 正文

SQL Server中对列的权限设置

更多 时间:2016-6-15 类别:数据库 浏览量:312

SQL Server中对列的权限设置

SQL Server中对列的权限设置

一、方式一:使用视图

将需要限制用户只能看到特定的几个列、设置成一个视图,然后对这个视图进行权限控制

 

二、方式二:使用GRANT语句

 

1、授予相关列的查询权限(SELECT)

(1)、在数据库db1中,登录名UserA 只能有权限查询 Employee表 里面的BusinessEntityID, NationalIDNumber, LoginID三个字段权限,不能查询其它字段

 

  •  
  • SQL 代码   复制
  • 
    GRANT SELECT(BusinessEntityID, NationalIDNumber, LoginID) ON Employee TO UserA
    
    		
  •  

    (2)、可以用下面SQL查看授予UserA的权限

     

  •  
  • SQL 代码   复制
  • 
    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 and P.name='UserA'
    
    		
  •  

     

    (3)、如果查询语句使用BusinessEntityID, NationalIDNumber, LoginID字段之外的其它字段 

     

  • SQL 代码   复制
  • 
    SELECT BusinessEntityID, NationalIDNumber, LoginID,JobTitle FROM Employee 
    
    				
  •  

    报如下错误

    Msg 230, Level 14, State 1, Line 8

    The SELECT permission was denied on the column 'JobTitle' of the object 'Employee', database 'db1', schema 'dbo'

     

     

    2、授予相关列的修改权限(Update)

     

    (1)、对于登录名UserB,只允许其修改 Address表 的AddressLine1,AddressLine2两个字段,其它字段不许修改

     

  •  
  • SQL 代码   复制
  • 
    GRANT UPDATE(AddressLine1,AddressLine2) ON Address TO UserB
    
    		
  •  

    (2)、可以用下面SQL查看授予UserB的权限

     

  •  
  • SQL 代码   复制
  • 
    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 and P.name='UserB'
    
    		
  •  

     

    标签:SQL Server
    您可能感兴趣