Security5:授予权限

SQL Server授予用户访问对象的权限,通常的模式是:Grants permissions on a securable to a principal(user or login),也就是说,授予权限的命令分为三部分:Permission,Securable 和 Principal,用一句话来解释这三个概念:授予 Principal 操作 Securable 的 Permission。Principal是被授予权限的主体,是被授与者(Grantee),是Login,User或Role。Securable是table,view,SP等对象,是Principal操作的对象;有时Principal也会作为Securable,被Principal操纵。在授予权限的子句中,没有主语,这是因为,只能授予已有的权限,而最原始的主体是在创建SQL Server实例时指定的,该账号拥有最高的权限,通过该账号把权限授予相应的安全主体。权限可以授予(GRANT)或禁止(DENY),也可以被回收(REVOKE),SQL Server通过命令GRANT,允许主体对安全对象做某些操作,通过命令DENY禁止主体对安全对象做某些操作,通过REVOKE命令收回对主体已授予(GRANT)或已禁止(DENY)的权限。

在SQL Server的安全模型中,安全主体主要分为两个级别:实例级别和数据库级别,每一个级别都需要一个与之对应的安全主体:Login是SQL Server实例级别的安全主体,用于登陆实例时的验证,验证方式分为Windows验证(通过Windows域账户验证登陆权限)和SQL Server验证(通过账号和密码验证登陆权限),而User是数据库级别的安全主体,用于访问数据库。Login和User可以通过SID(安全标识)关联起来,如果Login没有关联User,那么Login只能通过Public角色去访问数据库;如果User没有关联Login,那么该User是孤立用户,只能通过模拟权限被使用。一般情况下,一个User只能关联一个Login。

在SQL Server的安全模型中,sa和dbo 这两个安全主体拥有绝对控制权限,sa是login,属于实例级别的主体,dbo是user,属于数据库级别的主体。每一个属于sysadmin服务器角色的Login都会映射到数据库级别的dbo用户,也就是说,实例级别的login不同,数据库级别的user是相同的,都是dbo,但是,这些Login的SID都是相同的。

SQL Server 数据库引擎管理一个层次结构的实体集合,实体就是安全对象(Securable),最重要的安全对象是Server和Database,最重要的安全主体(Principal)是Login和User,如下图所示:

  • 左侧为安全主体:安全主体(Principal)分为三种:在Windows 级别上是Login,在SQL Server实例级别上是Login和实例级别的Role,在数据库级别是 User和数据库级别的Role。
  • 右侧为安全对象(Securable):对于安全对象(Securable)而言,SQL Server 实例是Database的集合,而Database是User,Role,Schema等的集合,Schema是数据库对象的集合,数据库对象是指Table,View,SP,Function等,操作不同的对象,需要的权限不同。

SQL Server的安全模型是有层次结构的,对安全对象的权限存在继承关系,对父安全对象上设置的权限,会自动继承到子安全对象上,例如,拥有架构(Schema)的CONTROL权限,这意味着,对该架构下的所有的数据库对象都用于CONTROL权限。

通常情况下,权限的管理都是针对数据库对象(Table,View,SP,Schema等)的,数据库对象的主要权限如下列表所示:

  • ALTER:用于修改数据库对象的定义,是DDL级别的权限;授予用户对特定Schema的ALTER权限,这意味着,用户可以alter, create, 或 drop 该Schema下的任何数据库对象。
  • VIEW DEFINITION:用于查看数据库对象的定义;
  • EXECUTE:用于执行SP,函数的权限;
  • DELETE,INSERT,UPDATE和SELECT:用于对数据表或视图执行增删改查操作,是DML级别的权限;
  • REFERENCES:授予引用的权限,用于在当前表中引用其他数据表主键的权限;
  • IMPERSONATE:模拟权限,模拟Login或User的权限
  • CONTROL:控制权,被授予权限的用户,实际上拥有操作对象的所有权限。The grantee effectively has all defined permissions on the securable.

用户可以通过表值函数:sys.fn_builtin_permissions('object') 查看SQL Server支持的对object可授予权限的列表。

一,授予用户访问数据库对象的权限

在创建Login和User之后(可以参考《Security1:登录和用户》),把访问Object的权限授予数据库用户(User)或角色(Role),语法如下:

GRANT <permission> [ ,...n ] 
ON OBJECT :: schema_name.object_name[ ( column [ ,...n ] ) ] 
TO [Database_user | Database_role] [ ,...n ]

对于Table,View等数据库对象,可以把权限控制在column的粒度上,只允许用户访问特定的数据列;如果在GRANT子句中忽略column,那么用户可以访问整个table或view的所有列。

把访问Schema的权限授予数据库用户或角色,语法如下:

GRANT permission  [ ,...n ] 
ON SCHEMA :: schema_name  
TO database_principal [ ,...n ]  

1,授予对数据表的select权限

GRANT SELECT 
ON OBJECT::Person.Address 
TO RosaQdM; 

2,授予执行SP的权限

GRANT EXECUTE 
ON OBJECT::hr.usp_UpdateEmployeeHireInfo  
TO Recruiting11; 

3,授予引用数据列的权限
以下脚本授予用户Wanida引用数据库对象 HumanResources.vEmployee的数据列 BusinessEntityID作为外键。

GRANT REFERENCES (BusinessEntityID) 
ON OBJECT::hr.view_Employee   
TO Wanida WITH GRANT OPTION;

二,通过创建Role授予权限

授予权限的方法,共有两种,第一种方法是把权限直接授予用户,

  • 可以授予对单个数据库对象(单个数据表或视图等)的权限,
  • 也可以授予用户操作Schema的权限,由于Schema是objects的容器,授予用户操作Schema的权限,就等同于授予操作Schema下所有objects的权限;

第二种方法是把权限授予数据库角色Role,然后把角色Role的权限授予用户,那样,用户就拥有Role的所有权限,以下代码,通过创建Role,把权限授予特定的用户:

--create login
create login [domain\user] 
from windows
go

--create user
create user [domain\user]  
for login [domain\user] 
go

--create role
create role role_name
go

--create schema
create schema schema_name
go

--grant permission on schema to role
grant select,execute
on schema::schema_name
to role_name;

--grant permission on object to role
grant select ,insert
on object::schema_name.object_name
to role_name;

--add member
alter role role_name
add member [domain\user] ;
go
View Code

三,通过创建权限模拟来授予权限

在SQL Server的安全模型中,模拟(IMPERSONATE )权限的安全对象是User或Login,被授予者(Grantee )有权限模拟指定用户,在其安全上下文执行特定的操作。

例如,user1授予模拟user2的权限,当user2的安全上下文有足够的权限,而user1没有时,通过权限模拟,user1能够在user2的权限上下文中执行查询请求:

GRANT IMPERSONATE 
ON USER:: user2 
TO user1;

通过执行EXECUTE AS 命令模拟用户的权限,用户user1就运行在user2的安全上下文中,例如,user1在登陆数据库之后,模拟user2的权限:

EXECUTE AS USER = 'user2';

通过执行 REVERT 命令退出权限模拟,返回到用户原始的安全上下文中:

REVERT;

一般情况下,对于特殊的管理任务,需要创建孤立用户,给孤立用户授予特定的权限,并把模拟孤立用户的权限授予其他用户,这些人就有权限执行特定的管理任务。

 

参考文档:

Permissions (Database Engine)

GRANT Object Permissions (Transact-SQL)

Basic SQL Server Security concepts - logins, users, and principals

Basic SQL Server Security concepts - permissions and special principals: sa, dbo, guest

posted @ 2017-08-07 09:54  悦光阴  阅读(1662)  评论(0编辑  收藏  举报