D盘

6.4  服务器和数据库角色

在7.0版之前，SQL Server有过组的概念——这是用户权限的分组，你只需简单地把用户分配到组中，就能一次指派所有这些权限。这里的组与Windows中的组起作用的方式有很大不同，用户能够属于多个Windows组，因此，可以根据需要混合搭配它们。在SQL Server 6.5（和更早的版本）中，每一个数据库里，一个用户只允许属于一个组。

SQL Server 7.0之前版本的这种方式产生的后遗症是，SQL Server组属于以下3类之一：

l    经常根据用户级别的许可权限对它们进行修改；

l    它们只是主要的组的微小变形；

l    它们拥有多于所需的访问权限（以便使DBA的工作更为轻松）。

基本上，它们虽然很有必要，但同时也是一个很大的麻烦。

伴随7.0版的出现，在这方面发生了一些很大的变化。现在，用户属于一个角色，而非一个组。在最一般的意义上，角色与组是相同的事物。

在这里，相似之处逐渐消失。使用角色时，用户能够一次属于多个角色。由于能够把访问权限组织到更小的和更合理的组中，然后把它们混合搭配为最适合用户的规则，这简直令人难以置信的便利。

角色分为两类：

l    服务器角色；

l    数据库角色。

很快，我们还将看到第三种称为角色的事物——应用程序角色，尽管我希望微软选用另外的名字。这是一种特殊的方式，用来把用户化名到不同的许可权限组中。应用程序角色不是分配用户的，它是一种让应用程序拥有的权限集不同于来自用户的权限集的方法。由于这个原因，我通常不认为应用程序角色是真正意义上的“角色”。

服务器角色限制在那些当发布SQL Server时就已经建立于其中的角色，并且，它在这里主要是为了进行系统的维护以及授予完成非数据库特有的事情的能力，如创建登录账户和创建链接服务器。

与服务器角色很类似，这里有一定数目的内置（或“固定”）数据库角色，不过，你也可以定义自己的数据库角色，以满足你独特的需求。数据库角色用来进行设置，以及在一个给定的数据库中分组特定的用户权限。

接下来，我们分别来看这两种类型的角色。

6.4.1  服务器角色

所有的服务器角色都是“固定的”角色，并且，从一开始就存在于那里——自安装完SQL Server的那一刻起，你将拥有的所有服务器角色就已经存在了。

对于在服务器上承担管理角色任务的单个用户，你可以对其混合搭配这些角色。一般来说，我怀疑只有最大型的数据库才会使用比sysadmin和securityadmin更多的角色，然而，有它们在旁边还是很便利的。

在本章的前面，我曾就全能用户会带来的麻烦进行过抨击。当新的sysadmin角色添加到7.0版时，我完全是欣喜若狂的，或许，得知此事你不会感到惊奇。sysadmin角色的存在表明，在不断发展的基础上，不再需要让所有人都有sa登录账户——只要让需要拥有那种访问级别的用户成为sysadmin角色的成员，这样他们就不再需要以sa登录。

6.4.2  数据库角色

数据库角色限制在单个数据库的范围之内——用户属于一个数据库中的db_datareader角色并不意味着他属于另一个数据库中的那个角色。数据库角色分为两个子类：固定数据库角色和用户定义数据库角色。

1．固定数据库角色

就如同存在若干个固定服务器角色一样，这里也有许多的固定数据库角色。他们中的一些有预先定义好的专门的用途，这是不能使用常规的语句复制出来的（即是说，你无法创建拥有同样功能的用户定义数据库角色）。然而，大多数角色的存在是为了处理更一般的情形，并让你做起事情来更加容易。

与使用固定服务器角色很类似，除非是在最大型的数据库中，否则，你可能不会使用到所有这些角色。在这些固定数据库角色中，一些是无法用你自己的数据库角色来替换的，而另一些，只不过在处理那些似乎经常出现的简单粗糙的情形时非常便利而已。

2．用户定义数据库角色

实际上，可供使用的固定角色只是为了帮助你开始入手。安全性真正的中流砥柱是用户定义数据库角色的创建和分配。对于这些角色来说，由你来决定它们将包含什么许可权限。

使用用户定义角色时，可以像针对单独的用户那样，用完全相同的方式进行GRANT、DENY和REVOKE。关于使用角色，好的事情是，用户往往归入访问需要的范畴——通过使用角色，你能够在一个地方做改动，并将改动散播给所有类似的用户（至少被指派到那个角色的用户）。

  ● 创建用户定义角色

我们使用sp_addrole系统存储过程来创建我们自己的角色。其语法非常简单：

sp_addrole [@rolename =] <'角色名'>

[,[@ownername =] <'所有者'>]

role name只不过是想要用来称呼那个角色的名称。常见的命名模式的例子包括：以部门来命名（Accounting、Sales、Marketing等），或者以具体的工作来命名（CustomerService、Salesperson、President等）。使用这样的角色的确能够让向系统中添加新用户的工作变得容易。如果会计部门新近雇用了某人，你只需把他（或她）添加到Accounting角色中（或者，如果更加精确，甚至可以是AccountsPayable角色），然后，就可以丢开这件事了——无需研究“这个人应当具有什么权限呢？”

此处的owner与系统中所有其他对象上的owner是相同的事物。默认是数据库的所有者，并且，我强烈建议让它保持那样（换句话说，只需忽略这个可选参数即可）。

接下来，创建我们自己的角色：

当执行上面的语句时，将返回给你一个友好的消息，告诉你新的角色已经加入。

现在，我们需要为这个角色实际指派一些权限，以这种方式为这个角色增加一些价值。要完成这一任务，只需像本章前面对实际的用户所做的那样，使用GRANT、DENY或REVOKE语句：

现在，所有属于我们角色的人都拥有了到Territories表的SELECT访问权限（除非在他们的安全性信息中的其他地方有DENY）。

此刻，已经准备好添加用户了。

  ● 向角色中添加用户

有了所有这些角色固然不错，但是，如果没有把任何人指派给他们，则角色将没什么用处。向角色中添加用户非常简单，就是使用系统存储过程sp_addrolemember并提供数据库名和登录ID：

sp_addrolemember [@rolename =] <角色名>,

[@membername =] <登录ID>

关于该存储过程的参数，一切都是非常一目了然的，因此，我们直接进入一个例子。

先从证实TestAccount不具有到Territories表的访问权限开始：

果不其然，我们被拒绝了（眼下尚没有访问的权限）：

现在，把我们的Windows用户TestAccount添加到OurTestRole角色中：

同样，我们收到一条确认消息，告知事情正确完成了：

此时，到了再次尝试并运行SELECT语句的时候了——这一次顺利得多（会得到大约53个返回行）。

  ● 从角色中删除用户

有起必有落，添加到角色中的用户势必也将从角色中删除。

从角色中删除用户的操作与把用户添加到角色中的操作几乎一样，只不过这里使用的是一个名为sp_droprolemember的不同的存储过程，使用的形式如下：

sp_droprolemember [@rolename =] <角色名>,

[@membername =] <安全账号>

接下来，返回到我们的例子，并从OurTestRole数据库角色中删除TestAccount：

你会收到另一个确认消息，告知一切顺利。现在，再试试我们的SELECT语句：

果然，我们又一次收到了说明我们没有访问权限的错误消息。

可以用这种方式向任何角色中添加用户以及从任何角色中删除用户——角色是用户定义角色还是固定角色并不重要，是服务器角色还是数据库角色也没什么关系。无论在什么情况下，它们的操作几乎完全一样。

还要注意的是，所有这些工作都可以在Management Studio中进行。要更改与角色相关联的权限，只需单击数据库结点的角色成员，然后使用复选框指派权限即可。当想要向角色中添加用户时，只需去到用户的属性对话框中，选择服务器或数据库角色选项卡，然后，在所有想要用户拥有其角色成员身份的角色上打上勾号。

  ● 删除角色

删除角色与添加角色一样容易。其语法很简单：

EXEC sp_droprole <'角色名'>

执行后，角色就被删除了。

6.5  应用程序角色

应用程序角色与数据库角色和服务器角色有些不同。的确，由于对它们都使用角色这一术语，这很容易让人觉得它们比较相近——但其实不是这样。

实际上，应用程序角色更像是用户的安全性别名。应用程序角色允许定义一个访问列表（由数据库单独的权限或分组构成）。另外，由于应用程序角色有其自身的密码，这让它们与用户有些类似。尽管如此，应用程序角色与用户登录名是不同的，因为应用程序角色不能那样“登录”——用户账户必须先登录，然后，他（或她）可以激活应用程序角色。

那么，我们为了什么而需要应用程序角色呢？为了应用程序——还有什么？你将经常遇到这样的情形，希望用户根据他（或她）在什么上下文中访问数据库而拥有一组不同的权限。使用应用程序角色，你能够实现这样的事情：只授予用户到数据库的只读访问权限（只能执行SELECT语句），但是，当用户在应用程序的范围内访问数据库时，还是会允许他们修改数据。

注意，应用程序角色是单向的。即是说，对于一个指定的连接，一旦确定已经激活应用程序角色，则对那个连接来说，无法再回到用户自己的安全性上下文。为了回到用户自己的安全上下文，他们必须终止这个连接，并再次登录。

应用程序角色的作用过程类似下面这样：

(1) 用户登录（很可能使用应用程序提供的登录窗口）；

(2) 验证登录，用户获得了他（或她）的访问权限；

(3) 应用程序执行名为sp_setapprole的系统存储过程，并提供角色名和密码；

(4) 验证应用程序角色，然后，连接被切换到应用程序角色的安全上下文（失去了用户拥有的所有权限——现在，他（或她）拥有的是应用程序角色的权限）；

(5) 在整个连接期间，用户将继续保持基于应用程序角色的访问权限，而非基于他（或她）个人登录名的访问权限——用户不能回到他或她自己的访问信息。

你只会想把应用程序角色作为真正的应用程序情形的一部分来使用，并且，你将直接在应用程序中构建设置应用程序角色的代码。另外，你也将需要把密码编译到应用程序中，或者把这一信息存储在某个本地文件中，以便在需要的时候进行访问。

6.5.1  创建应用程序角色

要创建应用程序角色，可以使用一个新的称为sp_addapprole的系统存储过程。该存储过程是另一个使用起来相当简单的存储过程，其语法如下：

sp_addapprole [@rolename =] <角色名>,

[@password =] <'密码'>

与本章中许多存储过程一样，其参数是一目了然的。因此，我们直接进入到它的使用，创建我们自己的应用程序角色：

'密码'

就是这样快，我们已经创建了应用程序角色。

6.5.2  向应用程序角色添加许可权限

向应用程序角色中添加许可权限与向任何其他事物中添加许可权限一样。只需在使用用户登录ID或者常规的服务器或数据库角色的地方，把它们替换成应用程序角色的名字即可。

同样，我们马上进入到一个例子：

现在，应用程序角色在Region表上拥有了SELECT权限——到此为止，它还没有任何其他的权限。

6.5.3  使用应用程序角色

应用程序角色的使用是这样一个过程：调用系统存储过程（sp_setapprole），并提供应用程序角色的名字和相应的密码。其语法如下：

sp_setapprole [@rolename =] <角色名>,

[@password =] {Encrypt N'密码}|'密码'

[,[@encrypt =] '<加密选项>']

role name就是你想要激活的应用程序角色的名字。

password或者原样提供，或者使用ODBC encrypt函数进行加密处理。如果准备加密密码，那么，需要在Encrypt关键字之后，用引号引住密码，并在前面放置一个大写的N——这向SQL Server表明，正在处理的是Unicode字符串，并且应当被如此对待。注意，为加密参数使用的是花括号｛｝，而不是圆括号。如果不希望加密，则不必使用Encrypt关键字来提供密码。

仅当为密码参数选择了加密选项时，才需要使用encryption style（加密类型）。如果对密码进行了加密，那么以“ODBC”作为加密类型。

值得注意的是，只有在使用ODBC或OLE DB客户端时，才能使用加密选项。不能把DB-lib与加密一起使用。

接下来进入示例的讲述，我们从验证几件与TestAccount用户的状态有关的事情开始。本章到了这里（假设你逐一学习了所有的例子），TestAccount用户不能访问Region表，但是，能够访问EmployeeTerritories表。通过运行两个SELECT语句，可以验证这种状态：

从第一个SELECT语句中会得到一个错误，而第二个语句则会返回大约50行记录。

现在，激活我们前面创建的应用程序角色，使用TestAccount用户输入下面的代码：

执行完上面的语句后，会得到一个确认消息，告知你应用程序角色已经“激活”。

通过运行前面的两条SELECT语句进行测试——会发现原来能做的和不能做的事情完全反过来了。即是说，尽管TestAccount能够访问EmployeeTerritories，但在使用应用程序角色时，失去了这种访问权限。TestAccount对Regions表没有访问权限，但是，眼下，应用程序角色提供了这种访问权限。

对于当前的连接，无法终止应用程序角色，因此，你可以终止你的TestAccount连接。然后，使用Windows安全性为TestAccount创建一个新的连接。再次运行那两条SELECT语句，会发现恢复了以前的访问权限。

6.5.4  删除应用程序角色

当服务器上不再需要应用程序角色时，可以使用sp_dropapprole从系统中删除它。其语法如下：

sp_dropapprole [@rolename =] <角色名>

要从系统中删除我们的应用程序角色，只需发出下面的命令（从sa）：

 

转自：http://www.17xie.com/read-105583.html