sqlserver 角色 详解Word文档格式.docx

sqlserver 角色 详解Word文档格式.docx

《sqlserver 角色 详解Word文档格式.docx》由会员分享，可在线阅读，更多相关《sqlserver 角色 详解Word文档格式.docx（25页珍藏版）》请在冰点文库上搜索。

管理SQLServer进程

dbcreator

创建和修改数据库

diskadmin

管理磁盘文件

下面两个系统过程用来添加或删除固定服务器角色成员：

sp_addsrvrolemember

sp_dropsrvrolemember

注意：

您不能添加、修改或删除固定服务器角色。

另外，只有固定服务器角色的成员才能执行上述两个系统过程来从角色中添加或删除登录账户。

sa登录

sa登录是系统管理员的登录。

在以前的SQLServer版本中不存在角色，sa登录具有所有可能的关于系统管理工作的权限。

在SQLServer2005中，sa登录保持了向后兼容性。

sa登录永远是固定服务器角色syadmin中的成员，并且不能从该角色中删除。

只有当没有其他方法登录到SQLServer系统中时，再使用sa登录。

12.4.2 

固定服务器角色及其权限

在某个SQLServer系统中，每个固定服务器角色都有其隐含的权限。

使用系统过程sp_srvrolepermission可以浏览每个固定服务器角色的权限。

该系统过程的语法形式为：

sp_srvrolepermission[[@srvrolename=]'

role'

]

如果没有指定role的值，那么所有的固定服务器角色的权限都将显示出来。

下面的部分将讨论每个固定服务器角色的权限。

1.sysadmin

固定服务器角色sysadmin的成员被赋予了SQLServer系统中所有可能的权限。

例如，只有这个角色中的成员（或一个被这个角色中的成员赋予了CREATEDATABASE权限的用户）才能够创建数据库。

固定服务器角色和sa登录之间有着特殊的关系。

sa登录一直都是固定服务器角色中的成员，并且不能从该角色中删除。

2.serveradmin

固定服务器角色serveradmin的成员可以执行如下的动作：

向该服务器角色中添加其他登录

运行dbccpintable命令（从而使表常驻于主内存中）

运行系统过程sp_configure（以显示或更改系统选项）

运行reconfigure选项（以更新系统过程sp_configure所做的所有改动）

使用shutdown命令关掉数据库服务器

运行系统过程sp_tableoption为用户自定义表设置选项的值

3.setupadmin

固定服务器角色setupadmin中的成员可以执行如下的动作：

添加、删除或配置链接的服务器

执行一些系统过程，如sp_serveroption

4.securityadmin

固定服务器角色securitypadmin中的成员可以执行关于服务器访问和安全的所有动作。

这些成员可以进行如下的系统动作：

读取SQLServer的错误日志

运行如下的系统过程：

如sp_addlinkedsrvlogin、sp_addlogin、sp_defaultdb、sp_defaultlanguage、sp_denylogin、sp_droplinkedsrvlogin、sp_droplogin、sp_grantlogin、sp_helplogins、sp_remoteoption和sp_revokelogin（所有这些系统过程都与系统安全相关。

）

5.processadmin

固定服务器角色processadmin中的成员用来管理SQLServer进程，如中止用户正在运行的查询。

这些成员可以进行如下的动作：

执行KILL命令（以取消用户进程）

6.dbcreator

固定服务器角色dbcreator中的成员用来管理与数据库创建和修改有关的所有动作。

运行CREATEDATABASE和ALTERDATABASE语句

使用系统过程sp_renamedb来修改数据库的名称

7.diskadmin

固定服务器角色diskadmin的成员可以进行如下与用来存储数据库对象的文件和文件组有关的动作：

运行如下系统过程：

sp_ddumpdevice和sp_dropdevice。

运行DISKINIT语句

12.4.3 

固定数据库角色

固定数据库角色在数据库层上进行定义，因此它们存在于属于数据库服务器的每个数据库中。

表12-2列出了所有的固定数据库角色。

表12-2 

固定数据库角色

db_owner

可以执行数据库中技术所有动作的用户

db_accessadmin

可以添加、删除用户的用户

db_datareader

可以查看所有数据库中用户表内数据的用户

db_datawriter

可以添加、修改或删除所有数据库中用户表内数据的用户

db_ddladmin

可以在数据库中执行所有DDL操作的用户

db_securityadmin

可以管理数据库中与安全权限有关所有动作的用户

db_backoperator

可以备份数据库的用户（并可以发布DBCC和CHECKPOINT语句，这两个语句一般在备份前都会被执行）

db_denydatareader

不能看到数据库中任何数据的用户

db_denydatawriter

不能改变数据库中任何数据的用户

除了表12-2中列出的固定数据库角色之外，还有一种特殊的固定数据库角色，名为public，这里将首先介绍这一角色。

public角色

public角色是一种特殊的固定数据库角色，数据库的每个合法用户都属于该角色。

它为数据库中的用户提供了所有默认权限。

这样就提供了一种机制，即给予那些没有适当权限的所有用户以一定的（通常是有限的）权限。

public角色为数据库中的所有用户都保留了默认的权限，因此是不能被删除的。

（示例12.12给出了public角色的使用方法。

一般情况下，public角色允许用户进行如下的操作：

使用某些系统过程查看并显示master数据库中的信息

执行一些不需要一些权限的语句（例如PRINT）

12.4.4 

固定数据库角色及其权限

在数据库中，每个固定数据库角色都有其特定的权限。

这就意味着对于某个数据库来说，固定数据库角色的成员的权限是有限的。

使用系统过程sp_dbfixedrolepermission就可以查看每个固定数据库角色的权限。

该系统过程的语法为：

sp_db.xedrolepermission[[@rolename=]'

如果没有指定role的值，那么所有固定数据库角色的权限都可以显示出来。

下面的几节将讨论每个固定数据库角色的权限。

1.db_owner

固定数据库角色db_owner的成员可以在特定的数据库中进行如下的动作：

向其他固定数据库角色中添加成员，或从其中删除成员

运行所有的DDL语句

运行BACKUPDATABASE和BACKUPLOG语句

使用CHECKPOINT语句显式地启动检查点进程

运行下列dbcc命令：

dbcccheckalloc、dbcccheckcatalog、dbcccheckdb、dbccupdateusage

授予、取消或剥夺每一个数据库对象上的下列权限：

SELECT、INSERT、UPDATE、DELETE和REFERENCES

使用下列系统过程向数据库中添加用户或角色：

sp_addapprole、sp_addrole、sp_addrolemember、sp_approlepassword、sp_changeobjectowner、sp_dropapprole、sp_droprole、sp_droprolemember、sp_dropuser、sp_grantdbaccess

使用系统过程sp_rename为任何数据库对象重新命名

2.db_accessadmin

固定数据库角色db_accessadmin的成员可以执行与数据库访问有关的所有动作。

这些角色可以在具体的数据库中执行下列操作：

运行下列系统过程：

sp_addalias、sp_dropalias、sp_dropuser、sp_grantdbacess、sp_revokedbaccess

为Windows用户账户、Windows组和SQLServer登录添加或删除访问

3.dbdatareader

固定数据库角色dbdatareader的成员对数据库中的数据库对象（表或视图）具有SELECT权限。

然而，这些成员不能把这个权限授予其他任何用户或角色。

（这个限制对REVOKE语句来说同样成立。

4.dbdatawriter

固定数据库角色dbdatawriter的成员对数据库中的数据库对象（表或视图）具有INSERT、UPDATE和DELETE权限。

（这个限制对REVOKE语句来说也同样成立。

5.db_ddladmin

固定数据库角色db_ddladmin的成员可以进行如下的动作：

运行所有DDL语句

对任何表上授予REFERENCESE权限

使用系统过程sp_procoption和sp_recompile来修改任何存储过程的结构

使用系统过程sp_rename为任何数据库对象重命名

使用系统过程sp_tableoption和sp_changeobjectowner分别修改表的选项和任何数据库对象的拥有者

6.db_securityadmin

固定数据库角色db_securityadmin的成员可以管理数据库中的安全。

运行与安全有关的所有Transact-SQL语句（GRANT、DENY和REVOKE）

运行以下系统过程：

sp_addapprole、sp_addrole、sp_addrolemember、sp_approlepassword、sp_changeobjectowner、sp_dropapprole、sp_droprole、sp_droprolemember

7.db_backupoperator

固定数据库角色db_backupoperator的成员可以管理数据库备份的过程。

这些成员可以进行如下动作：

用CHECKPOINT语句显式地启动检查点进程

运行如下dbcc命令：

8.db_denydatareader和db_denydatawriter

顾名思义，固定数据库角色db_denydatareader的成员对数据库中的数据库对象（表或视图）没有SELECT权限。

如果数据库中含有敏感数据并且其他用户不能读取这些数据，那么就可以使用这个角色。

固定数据库角色db_denydatawriter的成员对数据库中的任何数据库对象（表或视图）没有INSERT、UPDATE和DELETE权限。

12.4.5 

应用程序角色

应用程序角色可以加强对某个特定的应用程序的安全。

换句话说，这些角色允许应用程序自己代替SQLServer接管用户身份验证的职责。

比如，如果公司中的员工只是使用某个特定的应用程序（而不是Transact-SQL语句或其他任何工具）来修改员工的数据信息，那么就可以为它创建一个应用程序角色。

应用程序角色与其他的角色类型有着显著不同。

首先，因为应用程序角色只使用应用程序，因而不需要把权限直接赋予用户，所以应用程序角色没有任何成员。

其次，您需要为应用程序设置一个口令来激活它。

当应用程序角色被应用程序的会话激活以后，在会话期间，会话就失去了适用于登录、用户账户或所有数据库中的角色的权限。

1.创建应用程序角色

使用下列方法可以创建应用程序角色：

CREATEAPPLICATIONROLE语句

系统过程sp_addapprole

CREATEAPPLICATIONROLE语句可以为当前数据库创建一个应用程序角色。

该语句有两个选项：

一个与口令的规范有关，另一个用于默认模式的定义，也就是，当服务器为该角色指定对象的名称时，服务器将要搜索的第一个模式。

示例12.10显示了一个应用程序角色的创建方法。

示例12.10

USEsample

CREATEAPPLICATIONROLEweekly_reportsWITHPASSWORD

='

x1y2z3w4'

DEFAULT_SCHEMA=my_schema

示例12.10向当前数据库中添加了一个名为weekly_reports的新的应用程序角色。

创建新的应用程序角色的第二种方法是使用系统过程sp_addapprole。

使用系统过程sp_addapprole，就可以创建应用程序角色并把权限授予它们。

该系统过程有如下的语法形式：

sp_addapprole[@rolename=]'

[@passwd_name=]'

password'

其中role指定了应用程序角色的名称，password是相应的口令。

（激活角需要用到password的值。

SQLServer将来的版本中将删除系统过程sp_addapprole这部分内容。

请避免使用这一功能。

您可以使用CREATEAPPLICATIONROLE语句来代替。

2.激活应用程序角色

当启动连接以后，必须执行sp_setapprole系统过程来激活与应用程序角色有关的权限。

该过程的语法形式如下所示：

sp_setapprole[@rolename=]'

[@password=]'

[,[@encrypt=]'

encrypt_style'

其中role是在当前数据库中定义的应用程序角色的名称。

password指定了相应的口令，而encrypt_style则定义了口令的加密样式。

在使用系统过程sp_setapprole激活应用程序角色时，必须知道如下的一些事项：

在激活了应用程序角色之后，在当前数据库中就不能使这个角色无效，而必须等到会话从SQLServer中断开连接之后才可以。

应用程序角色总是和数据库绑定在一起的。

也就是说，应用程序角色的作用域是当前数据库。

如果在会话中改变了当前数据库，那么根据那个数据库的权限，只能执行那个数据库中允许的（其他）动作。

通过提供应用程序角色的正确口令，任何用户都可以执行系统过程sp_setapprole。

3.修改应用程序角色

使用下列TransactSQL语句可以修改应用程序角色：

ALTERAPPLICATIONROLE

DROPAPPLICATIONROLE

ALTERAPPLICATIONROLE语句可以修改已有的应用程序角色的名称、口令或默认模式。

该语句的语法与CREATEAPPLICATIONROLE语句的语法形式类似。

要想执行ALTERAPPLICATIONROLE语句，需要取得对该对象的ALTER权限。

DROPAPPLICATIONROLE语句可以从当前数据库中删除应用程序角色。

如果应用程序角色拥有任何对象（可保护对象），就不能删除该角色。

您同样可以使用系统过程sp_dropapprole来删除某个应用程序角色，但是在SQLServer将来的版本将删除该系统过程，不再使用它。

12.4.6 

用户自定义的数据库角色

一般来说，在一组数据库用户需要在数据库中执行一套常用操作并且不存在可用的Windows用户组的情况下，才可能用到用户自定义的数据库角色。

这些角色通过Transact-SQL语句或SQLServer系统过程进行管理。

接下来我们将讨论Transact-SQL语句，然后介绍相应的系统过程。

1.角色和Transact-SQL

CREATEROLE语句可以在当前数据库中创建一个新的数据库角色。

该语句的语法格式为：

CREATEROLErole_name[AUTHORIZATIONowner_name]

其中role_name是创建的用户自定义的角色的名称。

Owner_name指定了即将拥有这个新角色的数据库用户或角色。

（如果没有指定用户，那么该角色将由执行CREATEROLE语句的用户所拥有。

CREATEROLE语句可以修改用户自定义的数据库角色的名称。

类似地，DROPROLE语句可以从数据库中删除角色。

拥有数据库对象（可保护对象）的角色不能从数据库中删除。

要想删除这类角色，必须首先转换那些对象的从属关系。

DROPROLE语句的语法形式如下所示：

DROPROLErole_name

2.角色和系统过程

创建或修改用户自定义角色的另一个方法是使用SQLServer系统过程。

下列系统过程可以用来创建和显示用户自定义的数据库角色：

sp_addrole

sp_addrolemember

sp_droprolemember

sp_droprole

sp_helprole

系统过程sp_addrole可以在当前数据库中创建一个新的角色。

只有数据库角色db_securityadmin或db_owner才能够执行这个系统过程。

SQLServer2005中包含的系统过程sp_addrole只是向后兼容的，在将来发布的SQLServer版本中可能不再支持该系统过程。

所以请使用CREATEROLE语句来代替。

向当前数据库中添加了一个角色之后，就可以使用系统过程sp_addrolemember来添加该角色的成员。

角色的成员可以是任何SQLServer中的合法用户、Windows用户组或用户，或另一个SQLServer角色。

只有数据库角色db_owner的成员才能执行该系统过程。

另外，角色拥有者也可以执行sp_addrolemember来向它所拥有的任何角色中添加成员。

系统过程sp_droprolemember可以用来从角色中删除现有的成员。

（但是不能使用这一系统过程来从某个Windows组中删除现有的Windows用户。

）只有数据库角色db_owner或db_securityadmin才能执行该系统过程。

在使用系统过程sp_droprolemember删除了角色中的所有成员之后，可以使用系统过程sp_droprole来从当前数据库中删除角色。

（含有现有成员的角色不能删除。

SQLServer2005中包含的系统过程sp_droprole只是向后兼容的，在将来发布的SQLServer版本中可能不再支持该系统过程。

所以请使用DROPROLE语句来代替。

系统过程sp_helprole可以用来显示当前数据库中某个特定的角色或所有角色（如果没有提供角色名称）的相关信息（角色名称和角色的ID号）。

只有数据库角色db_owner或db_securityadmin才能执行该系统过程。

12.5 

授权

只有被授权的用户才能执行语句或对可保护对象进行操作。

否则，将拒绝执行Transact-SQL语句或拒绝对数据库对象进行操作。

SQLServer支持3个与授权有关的Transact-SQL语句：

GRANT

DENY

REVOKE

在讨论这3个语句之前，我们将首先介绍SQLServer2005与安全有关的一个最重要的属性：

SQLServer2005引入了多个作用域和权限来帮助数据库管理员处理权限问题。

新的授权模型把该领域分为主体和可保护对象两个部分。

每个SQLServer可保护对象都含有可以授予主体的相关权限。

主体（例如个人、组或应用程序）可以访问可保护对象。

可保护对象是SQLServer授权系统规则可以访问的资源。

正如前面已经讨论过的，存在3个可保护对象的作用域：

服务器、数据库和模式（它含有其他可保护对象，如SQLServer登录、数据库用户、表和存储过程等）。

12.5.1 

GRANT语句

GRANT语句用来把权限赋予SQLServer可保护对象。

GRANT语句的语法格式为：

GRANTpermission_list[ONscope]

TOprincipal_list[WITHGRANTOPTION]

[AS{windows_group|sqlserver_login|db_user|db_role|appl_role}]

其中permission_list指定了被授予权限的语句或对象（通过逗号分开）。

scope指定了将被授予权限的可保护类或可保护名称，权限将赋予它。

principal_list列出了权限授予的所有账户（通过逗号分开）。

principal_list的组件可以是一个Windows用户账户、SQLServer登录、映射到证书的登录或用户账户、映射到非对称键的登录、数据库用户、数据库角色或应用程序角色。

SQLServer2000把所有权限分为两个权限组：

语句权限和对象权限，这些权限使用GRANT语句把权限授予Transact-SQL语句和数据库对象，其格式略有不同。

对于两类权限组，SQLServer2005中的GRANT语句具有统一的语法格式。

表12-3给出了所有的权限及其描述，以及可应用这些权限的相应可保护对象。

表12-3 

权限及相应的可获得资源

权 

限

适用范围

SELECT

表+列，同义词，视图+列，表值函数

提供选择（读取）行的能力。

可以通过列出列的名称来把权限约束到一列或多个列上。

（如果没有列出这些名称，那么就可以选择表中所有的列。

INSERT

表+列，同义词，视图+列

提供插入行的能力

UPDATE

提供修改列中数据的能力。

可以通过列出列的名称来把权限约束到列或多个列上。

（如果没有列出这些名称，那么就可以修改表中所有的列。

DELETE

提供删除行的能力

REFERENCES

用户自定义函数（SQL和