sql 2005 学习（2.4 授予数据库权限)

 

一旦创建数据库用户，随之而来的便是管理这些用户权限。可以通过将用户加入一个数据库角色或者为其赋予更细的权限来管理用户。

2.4.1  创建数据库角色

数据库角色是数据库级的主体。我们可以使用数据库角色来为一组数据库用户指定数据库权限。SQL Server 2005为数据库创建了一套默认的数据库角色。表2.3列出了这些默认的角色。

表2.3  默认的数据库角色

数据库角色	描    述
db_accessadmin	可以管理对数据库的访问
db_backupoperator	可以备份数据库
db_datareader	可以读所有用户表中的所有数据
db_datawriter	可以在所有用户表中添加、删除和更新数据
db_ddladmin	可以执行任何DDL(数据定义语言)命令
db_denydatareader	不能读所有用户表中的所有数据
db_denydatawriter	不能在所有用户表中添加、删除和更新数据
db_owner	可以执行所有的配置和维护行为
db_securityadmin	可以修改数据库角色成员并管理权限
public	一个特别的数据角色。所有的数据库用户都属于public角色。不能将用户从public角色中移除

 

我们可以根据特定的权限需求在数据库中加入角色来对数据库用户进行分组。以下示例创建了一个名为Auditors的数据库角色，并在这个新角色中添加数据库用户Peter：

 

-- Change the connection context to the database AdventureWorks.

USE AdventureWorks;

GO

-- Create the role Auditors in the database AdventureWorks.

CREATE ROLE Auditors;

GO

-- Add the user Peter to the role Auditors

EXECUTE sp_addrolemember 'Auditors', 'Peter';

2.4.2  管理数据库角色

可以通过查询IS_MEMBER系统函数来判断当前数据库用户是否属于某个数据库角色。在下面的例子中，可以判断当前用户是否属于角色db_owner：

 

-- Change the connection context to the database AdventureWorks.

USE AdventureWorks;

GO

-- Checking if the current user belogs to the db_owner role

SELECT IS_MEMBER ('db_owner');

 提示     可以使用IS_MEMBER系统函数来判断当前数据库用户是否属于某个特定的Windows组，如下例所示：

        -- Change the connection context to the database AdventureWorks.

        USE AdventureWorks;

        GO

        -- Checking if the current user belogs to the Managers group

        -- in the ADVWORKS domain

        SELECT IS_MEMBER ('[ADVWORKS/Managers]');

 

通过使用sp_droprolemember系统存储过程，可以从一个数据库角色删除数据库用户。如果需要删除一个数据库角色，可以使用DROP ROLE语句。以下代码将数据库用户Peter从数据库角色Auditors中移除，然后删除了Auditors这个角色：

 

-- Change the connection context to the database AdventureWorks.

USE AdventureWorks;

GO

-- Drop the user Peter from the Auditors role

EXECUTE sp_droprolemember 'Auditors', 'Peter';

-- Drop the Auditors role from the current database

DROP ROLE Auditors;

 

 警告  SQL Server 2005不允许删除含有成员的角色。在删除一个数据库角色之前，必须移除这个角色下的所有用户。

2.4.3  授予小粒度的数据库权限

除了使用固定数据库角色，还有一种办法是为数据库角色和用户授予小粒度的数据库权限。可以通过GRANT，DENY和REVOKE语句来管理权限。在以下的示例中，要为数据库用户Peter授予BACKUP DATABASE(备份数据库)权限：

 

-- Change the connection context to the database AdventureWorks.

USE AdventureWorks;

GO

-- Grant permissions to the database user Peter

-- to backup the database AdventureWorks.

GRANT BACKUP DATABASE TO Peter;

 

用DENY语句删除一个数据库用户拥有的某个权限时，这个用户不能从它归属的拥有同样权限的数据库角色那里继承这个权限。

然而，如果使用REVOKE语句删除这个用户的某个权限时，它可以从它所归属并被赋予同样权限的数据库角色那里继承这个权限。使用REVOKE语句只能删除曾被授予过的权限。

最佳实践    为了减少维护权限结构的工作量，应该只为数据库角色授予权限，而不是单独的数据库用户。