Chapter3ProtectingtheData（3）：创建和使用数据库角色

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39639365，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349 未经作者同意，任何人不得以原创形式发布，也不得已用于商业用途，本人不负责任何法律责任。 前一篇：http://b

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

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

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

前言：

数据库层级的角色允许把数据库权限像服务器级别角色一样组合管理。你可以配置固定服务器角色，创建用户自定义角色等。 

实现：

1. 在SSMS中，进入某个数据库的节点，点击【安全性】，然后点击【数据库角色】节点：

 

固定角色	描述
db_accessadmin	db_accessadmin 固定数据库角色的成员可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限。
db_backupoperator	db_backupoperator 固定数据库角色的成员可以备份数据库。
db_datareader	db_datareader 固定数据库角色的成员可以从所有用户表中读取所有数据。
db_datawriter	db_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。
db_denydatareader	db_denydatareader 固定数据库角色的成员不能读取数据库内用户表中的任何数据。
db_denydatawriter	db_denydatawriter 固定数据库角色的成员不能添加、修改或删除数据库内用户表中的任何数据。
db_owner	db_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动，还可以删除数据库。
db_securityadmin	db_securityadmin 固定数据库角色的成员可以修改角色成员身份和管理权限。
db_ddladmin	db_ddladmin 固定数据库角色的成员可以在数据库中运行任何数据定义语言 (DDL) 命令。

2. 如果添加成员到某个角色，可以双击这个角色，在【属性】页的【此角色的成员】中添加：

 

3. 也可以使用T-SQL实现：

ALTER ROLE db_accessadmin ADD MEMBER test; 

4. 如果需要创建自定义角色，可以右键【数据库角色】节点然后选择【新建数据库角色】，输入角色名和拥有者，用于控制这个角色的全部权限，建议使用dbo或者留空。

5. 不要让GUI界面混淆了你，在【常规】页，有两个listboxes，第一个是【此角色拥有的架构】，列出了这个数据库的架构，在后面介绍。

6. 现在忘记第一个listbox，并到第二个listbox，【此角色的成员】，可以添加角色成员。

7. 然后到【安全对象】页，在这里可以选择安全对象，也可以用T-SQL实现：

USE marketing; 
CREATE ROLE ProspectManager; 
ALTER ROLE  ProspectManager ADD MEMBER Fred; 
GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.Prospect TO ProspectManager; 
GRANT INSERT, SELECT ON dbo.Contact TO ProspectManager;

原理： 

固定角色允许授权全局权限到数据库的所有对象中，大部分时间里面，不需要使用db_datareader/db_datawriter。因为权限限制太少，而db_denydatareader/db_denydatawriter却很有用，可以限制用户不允许访问表、视图。

所有用户都属于public数据库角色的成员，不能被移除，而db_owner角色的成员在数据库中以dbo用户的形式出现。

可以使用下面语句查看某个登录名是否属于某个数据库角色的成员：

SELECT IS_ROLEMEMBER ( 'ProspectManager', 'fred' ); 

返回1的话代表Fred是ProspectManager角色的成员。

更多： 

如果使用了WITH GRANT OPTION授权到Windows组中，那么这个角色的成员选哟在授权时使用AS关键字。 

USE marketing; 
CREATE USER fred WITHOUT LOGIN; 
CREATE USER mary WITHOUT LOGIN; 
GO 
CREATE ROLE contactReaders; 
ALTER ROLE contactReaders ADD MEMBER fred; 
GO 
GRANT SELECT ON dbo.contact TO contactReaders WITH GRANT OPTION; 
GO 
EXECUTE AS USER = 'fred'; 
-- this does not work 
GRANT SELECT ON dbo.contact TO mary; 
-- this works 
GRANT SELECT ON dbo.contact TO mary AS contactReaders; 
REVERT; 

当Fred使用GRANT命令，必须使用AS contactReaders。

MSDB角色：

MSDB由于其重要性，角色的数量和功能都和常规的库不一样：

角色	描述
db_ssisadmin	可以作为管理员管理服务器上的SSIS。
db_ssisoperator	可以查看所有SSIS包，但是不能导入或修改。
db_ssisltduser	可以查看和执行属于它自己的SSIS包。
dc_admin	可以管理data collector collection集和属性，又有所有dc_operator的权限。
dc_operator	可以读和更新DC collection set和属性。
dc_proxy	可以读取DC collection sets和对应属性。
PolicyAdministratorRole	可以执行基于策略的管理上所有配置和维护操作。
ServerGroupAdministratorRole	可以注册和使用中央管理服务器组。
ServerGroupReaderRole	可以连接中央管理服务器组。
dbm_monitor	允许监控数据库镜像。

关于SSIS角色和Data Collector 安全性，可以查看下面文章：

Integration Services Roles: http://msdn.microsoft.com/en-us/library/ms141053.aspx Data Collector Security: http://msdn.microsoft.com/en-us/library/bb630341.aspx