SQL Server 2005学习心得(已变成DLL控件可直接调用)

1、 C#编程连接SQL Server 2005 数据库的代码：

答： SqlConnection myconnection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=' 数据库名 ';Data Source='服务器名';User ID='用户登录名';Password='密码';Connect Timeout=30");

myconnection.Open(); //打开数据库

obj SqlConnection.Close();//关闭数据库

2、 C#编程访问数据表中的数据：

答：string SQL = "select EmpName From tb_EmpInfo";

SqlCommand thisCommand = new SqlCommand(SQL,myconnection);

SqlDataReader thisReader = thisCommand.ExecuteReader();

while (thisReader.Read())//读取数据关键代码

{

MessageBox.Show(thisReader["EmpName"].ToString());//输出数据

}

3、 C#编程访问查询数据表的数据

答 string SQL = "select EmpName From tb_EmpInfo where EmpName=‘邵珠勇’";

SqlCommand thisCommand = new SqlCommand(SQL,myconnection);

SqlDataReader thisReader = thisCommand.ExecuteReader();

while (thisReader.Read())//读取数据关键代码

{

MessageBox.Show(thisReader["EmpName"].ToString());//输出数据

}

4、 C#编程访问并往数据表中插入数据

string SQL = "insert into

tb_EmpInfo(EmpId,EmpName,EmpLoginName,EmpLoginPwd,EmpSex,EmpBirthDay,EmpDept,EmpPost,EmpPhone,EmpPhoneM,EmpAddress,EmpFalg) values('12345678','束同同','zhanghan','123','男','1990/02/21','食品部','组长','1234567','150xxxxxxxx','安徽省',0)";

SqlCommand thisCommand = new SqlCommand(SQL, myconnection);

thisCommand.ExecuteNonQuery();

5、 C#编程访问并更新数据表中的数据内容

答：string SQL = "update tb_EmpInfo set EmpName= '束同',EmpSex='女'" + " where EmpID='12345678'"; SqlCommand thisCommand = new SqlCommand(SQL, myconnection);

thisCommand.ExecuteNonQuery();

6、 C#编程访问并删除数据表中的数据内容

答： string SQL = "delete from tb_EmpInfo where EmpID='12345678'";

SqlCommand thisCommand = new SqlCommand(SQL, myconnection);

thisCommand.ExecuteNonQuery();

7、 C#中查询代码

答：string SQL = "select EmpName From tb_EmpInfo"; DataSet ds = new DataSet();

SqlDataAdapter obj=new SqlDataAdapter();

obj.SelectCommand = new SqlCommand(SQL,myconnection); obj.Fill(ds, "tb_EmpInfo");

dataGridView1.DataSource = ds.Tables[0];

第二篇：SQL Server 20xx安全性

SQL Server 2005安全性

IT Tech 2008-07-17 15:59:37 阅读356 评论0 字号：大中小 订阅

关于安全性首先要理解的一点是，没有彻底安全的应用程序。如果你能够让应用程序安全，就必定在某处有某人能挫败你的努力并“偷偷潜入”系统中。然而，即便有这样的认识，为了把讨厌的入侵者阻挡在系统之外，依然要努力实现这一目标。好消息是，大多数情况下，你能够很容易地让事情变得十分费劲，以致99.999％的人不想为此劳神。至于另外的那0.001%的人，我只能鼓励你去确信你所有的雇员都热爱生活，这使得他们属于99.999％的那类人。这0.001%的人有望访问到其他一些地方。

在SQL Server 2005中，微软对于SQL Server的安全性非常重视。这里有大量的新特性，并且，尽管以前已经有专门针对SQL Server安全性的书籍，但是现在，我可以想见，它们将是更加庞大的长篇大论——在这个版本中，该主题的内容大大增加了。

本章中，我们将讲述下面的内容：

l 安全性基础；

l SQL Server安全性选项；

l 数据库和服务器角色；

l 应用程序角色；

l 凭据；

l 模式管理；

l XML集成安全性问题；

l 更高级的安全性。

我们将发现，这里有大量不同的方法来处理安全性问题。安全性不仅仅是给某人一个用户ID和密码——你会看到有许多问题需要考虑。

在开始本章的任何示例之前，需要载入并执行名为NorthwindSecure.sql脚本。这个脚本会创建一个特别的数据库，我们将在本章使用该数据库。可以从本书的网站下载学习所需要的东西。

是的，为了运行本章的示例，我不得不让你创建一个工作数据库——很抱歉。这里将使用的是旧的Northwind数据库，只不过要删除所有对权限的修改。我们将在整个这一章中使用的NorthwindSecure数据库，它是一种更典型的数据库场景——即是说，除了随着创建表和对象自然带来的权限外，绝对没有添加任何权限（即没有）。随着本章讲述的深入，我们将学习如何处理权限，以及如何明确添加想要的权限。

22.1 安全性基础

我相信，就我们将在本节中查看的内容而言，有相当一部分似乎极为愚蠢——我的意思是，这些东西难道不是尽人皆知的吗？然而，即便是这些规则中最简单的一个，违背它的情形也屡屡发生，从这些频繁发生的对规则的违反来判断，我要说，“不，显然它们不是。”所有我能做的就是请你耐心一些，不要跳过这里的内容。这里的某些内容看起来似乎很显而易见，但令人吃惊的是，它们常常被忘记或者被忽略。

在种种基础知识中，这里要讨论的是：

l 一个人，一个登录名，一个密码；

l 密码过期；

l 密码长度和组成；

l 尝试登录的次数；

l 用户ID和密码信息的存储。

22.1.1 一个人，一个登录名，一个密码

不断让我感到震惊的事情是，无论我到哪里，几乎总能发现机构中至少有一个“全局的”用户

——网络或者特定的应用程序中的某个登录，该登录经常被部门或者甚至整个公司里几乎所有的人所知道。通常，这个“全局的”用户拥有全权的（换言之，完全的）访问权。对于SQL Server来说，安装甚至不把sa的密码设置为非空白的密码，这在过去是很平常的。

在SQL Server 2000之前，sa账户的默认密码是空密码——即是说，它没有密码。谢天谢地，SQL Server 2000不仅改变了这种默认情况，而且，现在SQL Server还会预先告知，如果你坚持让该密码为空白，那你一定是不折不扣的白痴——这对开发团队十分有益。需要当心的事情是，在进行开发时，还是会将它设置得很“简便”，这确实是很普遍的现象。在投入生产之前，你还必须记住对它进行修改，或者，如果开发服务器将直接暴露在因特网上或暴露给其他不可信的访问，则必须从一开始就将它设置得很困难。

即使现在，当多数的安装都的确有了非空的密码时，许多人知道密码是什么的情形也极为普遍。

那么，第一个基础知识是，如果所有的人都能访问某个用户ID，则该用户ID本质上是匿名的（如果所有的人都知道它，则任何人都有可能使用它），并且能访问任何事情，那么，你已经彻底击败了你的安全模型。同样，如果给每一个用户一个在所有事情上拥有完全访问权限的登录名，则也已经严重破坏了安全前景。剩下的唯一真正的好处是，对于任何时刻谁有关联的问题，能够说出显著的人（假设他们确实使用了他们个人的登录名，而不是使用全局的登录名）。

应当把拥有全权访问权限的用户限制为仅仅一、两个人。理想的情况是，如果需要这种全权访问的密码，那么，你会想要不同的登录名各自拥有这样的访问权，而对于每一个登录名来说，只会有一个人知道其密码。

你将发现，用户常常与其他人共享他们的密码，以便让某人临时获得某种级别的访问（通常，这是由于该登录ID的所有者要么在休假，要么在那时没有时间，以致无法亲自去完成此事）——如果可能的话，应当严禁这样做。

由共享密码导致的问题是多方面的。第一，一些用户获得了对某些事物的访问权，而这种访问权最初是决定不给予他们的（否则，为什么他们自己没有这种必要的权限呢？）——如果以前不准备让他们访问，现在为什么又想让他们访问了呢？第二，不允许进行访问的用户现在可能暂时拥有了访问权。由于用户几乎从不更换他们的密码（除非强迫他们这样做），被给予密码的人员多半能不定期地使用那个登录ID——并且，我向你保证，他们一定会的！第三，你再次失去了审核。你可能有一些基于登录ID来跟踪哪一个用户做了什么的东西。如果有不止一个人有那个登录ID的密码，如何分辨在某个时候是哪一个人用那个登录ID登录进来的呢？

这就意味着，如果有人在某个时候将不在办公室，这或许是因为此人生病了，或者在度假，那么，将由另外的人临时接替此人去完成其工作，于是，应当明确为那个接替的人创建新的登录ID和密码（或者，应当修改其现有登录ID的访问权限），然后，一旦原来的人返回工作岗位，则应当删除新的登录ID。

总之，尽量别碰全局的用户账户。如果必须使用它们，则要把它们的使用限制在尽可能少的人之中——通常，应当保持在仅仅两个人（一个是主要的使用者，而另一个是在第一个人无法完成任务的情况下作为候补之用）。如果确实必须让不止一个人有重要的访问权，那么，可以考虑创建多个具有必要的访问权限的账户（一人一个账户）。遵照这些简单的步骤，就能为系统的安全性和审核做出大量的贡献。

22.1.2 密码过期

密码过期的使用往往倾向于要么被滥用，要么被忽视。因为它是一个很好的主意，但常常会变得不利。

密码过期背后的原理是：对系统进行设置，使密码经过一段特定的时间后会自动过期。在到

达时间期限后，用户必须更改密码才能继续访问账户。这一概念已经存在了许多年，如果你在较大的公司里工作，来自会计师事务所的审计师很可能已经在强调：必须实现某种形式的密码过期。

从SQL Server 2005开始，如今，你甚至可以为SQL Server专用密码强制使用Windows安全策略机制。或者，你也可以只使用基于Windows的安全性（下一节中将有更多有关的内容）。

1．做这些努力，收益是什么

那么，密码过期能让你得到什么？还记得我在前一节的最后说过，一旦密码被共享，用户将始终拥有那种访问权吗？好吧，这里是例外的情况。如果让密码过期，那么，就更新了安全级别——至少暂时如此。为了让用户重新得到访问权，可能必须再次共享密码。尽管这远远不算完美（该登录ID的拥有者经常会很乐意再次共享它），但当密码的共享确实只为了一次性的使用时，这的确处理了这种情况。通常，数月之后，共享他们密码的用户甚至不会意识到共享了密码；而另外的用户手里依然有密码，并且，可能间或会使用一下这个密码，以获得他们自身的安全级别所不应拥有的访问权。

2．下面是不利之处

好事做过了头，很可能变成坏事。我在前面说过，许多会计师事务所都希望他们的客户实现这样的模式：用户的密码会有规律地过期，例如，每30天——实际上，这是很糟的主意。 在我见过的所有这样做了的SQL Server安装中（无一例外），执行了30天过期的策略后，安全性变得更糟了。正如你可能想象的那样，问题本质上是多方面的。

l 第一，技术支持的需求大大增多了。当用户如此频繁地更改密码时，他们完全不能记住所有这些密码。他们无法记住应该使用哪一个月的密码，由于忘记了密码是什么，因而经常需要技术支持来重置密码。

l 第二，这一点更为重要，用户对于想出新密码并记住它们感到厌烦。经验显示，在使用了30天过期策略的安装中，90%以上的用户把他们的密码改成具有惊人的可预测性的（因而是可被入侵的）单词或者单词和数字的组合。事实上，这常常发展到这种程度：50%或者更多的用户使用相同的密码——他们都使用类似MMMYY的东西作为密码，其中MMM是月份，而YY是年份。例如，在19xx年一月，他们可能使用JAN96作为密码。很快，这里的所有人都会做这样的事情。

我曾见过一些公司试图通过实施密码嗅探器（当要更改密码时，它会检查密码）来应对这种情况。嗅探进程寻找混合了姓名或以月份前缀开头的密码。这种机制再好，其作用也是很微弱的。

用户远比你想象的聪明。多数用户大约用一星期就能规避第一个嗅探器——他们只不过把密码更改为以“X”为前缀，这样就能继续使用以前所用的MMMYY格式了。简言之，嗅探器最终所起的作用微乎其微。

这里的实质问题并非要避免使用过期策略。应当使密码过期的时间足够短，以获得合适的新旧更替并应对共享或窃取密码的情况，但是，又不能太频繁，否则会导致用户产生抗拒心理并开始使用弱密码。我个人建议过期的时间要多于90天但少于180天。

22.1.3 密码长度和组成

在这一领域，应为SQL Server感到欢欣鼓舞。在以前的版本中，如果使用SQL Server安全性，则实际上对这一方面做不了多少控制。现在，能够让SQL Server使用Windows密码策略（可以通过Windows中的实用工具进行调整）。

1．密码长度

要知道，用户每在密码中包含进一个可能的字母数字位，可能的密码数量就将至少增加36倍（实际上，倘若包含特殊的字符则会更多一些，但是，在这里，即使36倍也足以说明问题）。这意味着，对于一个字符的密码来说，仅有36种可能性，但两个字符的密码将有1296

种可能性。如果密码包含的字符数增加到3个，可能性将增长到46 656。当向密码中加入第4个字符时，可能性将超过一百万种。随着要求密码包含的字符数越来越多，排列的方式也不断增加。当然，如果处处都要求超过5或6个字符，那么，终端用户将产生极大的反感。

2．密码组成

是的，我已经指出过，如果要求使用至少4位字母数字的密码，则在这种情况下，将有超过百万种可能的密码组合。其实，人们不会使用所有这些组合（他们将使用熟悉的单词或者名字），当你意识到这一点时，问题就来了。考虑到一般人经常使用的单词只有5000个左右，因而对于想要入侵的人来说，并不需要尝试太多的单词即可得逞。

如果你实施的不是默认的Windows密码策略，那么，要考虑要求至少一个字符是字母的（不是数字，只是字母），同时，至少一个字符是数字的。简单的数字（实际上，人们喜欢使用他们的社会安全号、电话号码或生日）以及完全的单词都很容易被猜到，这排除了这种可能性。用户依然能够创建对于他们来说很容易记住的密码——如“77pizzas”，但是，这样的密码不可能从字典里找出。为了尝试并入侵，任何黑客都不得不真正尝试每一种组合。 22.1.4 尝试登录的次数

无论物理上如何存储用户和密码信息，登录界面都应当与之有联系，从而限制某人尝试登录的次数。当用户超过了登录限制时，做出反应的程度可以是不同的，但是，要确保有某种方法，让用程序的方式尝试所有密码的做法变得很困难。

只要允许的登录尝试的次数是比较小的数值，究竟允许多少次其实并不是那么重要——我通常使用3次，但是，我也在一些地方见过用4和5次的，而那样也是不错的。

如果执行的是Windows密码策略，那么SQL Server将相对于错误密码限制来检查登录尝试并强制该策略。

22.1.5 用户和密码信息的存储

显然，仅当你使用自己的安全系统而不是使用内置的Windows和（或）SQL Server安全系统时（但许多Web应用程序将会这样做），这才适用，而且，在大多数时候，如何存储用户配置和密码信息并不是什么高深的学问。尽管如此，这里还是有几件事情需要考虑： l 由于需要能在最初获得信息，你必须做如下三件事情之一：

编译密码到客户端应用程序或组件中（然后，确保在你安装应用程序的服务器上创建了正确的登录名和密码）。

利用SQL Server新的加密技术在数据库中加密和解密数据。

要求某种双密码的情形——一个让用户到达常规的密码信息，而另一个让用户到达真正的应用程序。通常，迫使用户两次登录让人无法接受，因而，在大多数情况下，你会返回到其他两种选择之一。

l 如果处于要使用双密码的场景中，可能的话，将会想要把第一个登录的访问限制在只能执行存储过程上。通过这样做，能够允许第一个登录获得其所需要的验证，但不会把任何东西暴露给试图通过Management Studio登录的任何人。使你的存储过程（sproc）接受用户ID和密码，并且，只传回布尔值（能够登录真或假）或者传回一个记录集，记录集中列出了用户能够在客户端看到什么窗口和函数。如果使用原始的SELECT语句，则无法对用户能够看到的事情进行限制。

我曾实现过的类似这种场景的方案是，用一个视图把当前SQL Server登录映射到其他登录信息。在这种情况下，应用程序角色用来让应用程序获得对所有事情的完全访问权——应用程序必须知道用户能够和不能够做什么。该存储过程如下所示：

l 如果你准备把密码信息存储在系统中——请加密密码！！！这一点我无论强调多少次也不

过分。多数用户会把密码用在不止一件事情上——当需要记住的事情较少时，生活会容易得多。通过在数据被放入到数据库中之前对数据进行加密，确保了没有人会知晓用户的密码信息——即使是偶然得到。他们可能会看到它，但是，如果没有解密的密钥，他们所看到的没有任何用处。

22.2 安全性选项

就内置选项来说，关于如何在SQL Server中设置安全性的问题，你有两个选择。

l Windows集成安全性——用户登录到Windows中而非SQL Server中。身份验证是通过Windows以可信的连接来完成的。

l 标准的安全性——与登录到Windows中分离开，用户单独登录到SQL Server中。身份验证是使用SQL Server来完成的。

我们来看看这两种选择。

22.2.1 SQL Server安全性

先从SQL Server的内置登录模式谈起。在SQL Server 2005中，这方面变得更加健壮。尽管依然可以使用相对简单的模式，但是，现在你可以做大量其他的事情，以便增加服务器和数据库的安全。

在使用SQL Server安全性时，是完全脱离开网络登录信息来创建登录ID的。使用SQL Server安全性的有利之处包括：

l 用户不必为了访问到系统而成为域用户；

l 更容易对用户信息进行程序化的控制。

一些不利之处是：

l 用户可能必须登录两次或者更多次——一次是登录到他们拥有访问权的网络中，而一次是针对用户从单独的应用程序所创建的每一个连接，登录到SQL Server中；

l 两次登录意味着DBA要做更多的维护；

l 如果需要多个密码，则它们很容易变得不一致，这会导致大量的登录失败和密码遗忘。（这听上去耳熟吗，“现在我们来看看，哪一个密码才是用于这个登录的？”）

用SQL Server安全性进行登录的一个例子是使用sa账户的情形，在本书的大部分地方可能都会使用该账户。使用SQL Server安全性进行登录时，你是如何登录到网络中的并不重要，可以使用sa登录ID和单独的密码登录到SQL Server中（希望该密码设置得非常安全）。 实际上，从经常性看，你不会日复一日地以sa来登录。为什么呢？或许，只花一、两分钟想一想就能想出，当使用sa账户时，在纯粹意外的情况下能够发生多少可怕的事情。使用sa意味着对任何事情都有完全的访问权，这就是说，当你在错误的数据库中执行DROP TABLE语句时，事实上，将按照你告知的那样去执行——删除那个表！！！对此，你只剩下说“哎呀！”了。而你的老板可能会说出完全不同的事情。

即使你确实需要总是拥有完全访问权，也应该只用sa账户让你的用户账户成为sysadmins服务器角色中的一员。那样做不仅赋予了你sa的权力，而且，单独的密码以及对当前是谁登录到系统中的审核跟踪（在跟踪工具Profiler中或者当查看系统活动时），使你获得了额外的安全性。

22.2.2 创建和管理登录

目前，要在SQL Server中创建登录名，有4种方式：

l 使用CREATE LOGIN；

l 使用Management Studio；

l SQL管理对象（SMO）；

l 使用一些其他的选项，这些选项完全是为了向后兼容性而保留下来的。

1．CREATE LOGIN

CREATE LOGIN是SQL Server 2005中新增的语句，是微软为了让用来创建数据库和服务器对象的语法标准化所做的努力的一部分。不赞成使用sp_addlogin，在以前的版本中，sp_addlogin是用来添加登录名的过程化的方式，它看起来类似我们多次在SQL中见过的CREATE <object> <object type>语法，只是这里有一些额外的选项要求，这些选项要求是我们在诸如存储过程见过的。

最基本的语法是很简单明了的，但是，这些选项能够如何混合在一起，这一点则可能有些难于理解。整个语法如下所示：

CREATE LOGIN <登录名>

[ { WITH

PASSWORD = '<密码>' [ HASHED ] [ MUST_CHANGE ]

[, SID = <SID>

| DEFAULT_DATABASE = <数据库>

| DEFAULT_LANGUAGE = <语言>

| CHECK_EXPIRATION = { ON | OFF}

| CHECK_POLICY = { ON | OFF}

[ CREDENTIAL = <凭据名>

[, ... <下一选项>] ]

} |

{ FROM

WINDOWS

[ WITH DEFAULT_DATABASE = <数据库>

| DEFAULT_LANGUAGE = <语言> ]

| CERTIFICATE <证书名>

| ASYMMETRIC KEY <不对称密钥名>

}

]

紧接着登录名后，是选择FROM子句还是选择WITH子句，这是决定整个事情基调的关键部分，因此，我们结合属于FROM或WITH子句的选项来查看这些内容。

● CREATE LOGIN...WITH

WITH子句直接让你进入到与基于登录名的SQL Server身份验证有关的选项定义中，而非任何其他的验证方法。当激活了SQL Server安全性（相对于只是Windows安全性）时，它才是有关联的。这里的选项数量很多，看上去有些令人生畏，因此，把它们拆开来讲述。 这些选项中的大多数是真正全新的。好吧，当然，你会说：“我以为这整个命令都是新的？”是的，它的确是新的，但是，CREATE LOGIN...WITH本质上是CREATE LOGIN中取代了旧的sp_addlogin的部分。在SQL Server 2005中，即使是针对基于SQL Server的安全性，我们也极大改进了安全性选项，这反映在CREATE LOGIN...WITH中不存在于sp_addlogin里的选项上。

选 项

PASSWORD 说 明 显然，该选项正像它听上去那样。这里要小心处理的地方是下

面的问题：密码是明文的（在这种情况下，SQL Server将在添

加密码时对密码进行加密），还是已经经过散列运算（在这种

情况下，需要提供HASHED关键字，后面将讲述该关键字）

该选项跟在密码后面，并且，仅当密码已经经过散列运算（加HASHED

选 项 说 明

密）时才使用它。在那种情形下，SQL Server添加密码时不会

再次对密码进行加密

MUST_CHANGE 这又是一个可以“顾名思义”的选项。简言之，如果提供了该选

项，那么，在用户首次登录时，将提示他们更改密码

SID 你可以手工指定SQL Server使用什么GUID来验证这一登录

名。如果未指定该选项（我认为这样做是一种极端的情况），

则SQL Server将为你生成一个

DEFAULT_DATABASE 这是该用户每次登录时的当前数据库

DEFAULT_LANGUAGE 这是在给用户发送错误或者其他系统消息时所使用的语言 CHECK_EXPIRATION 设置SQL Server是否强制实施密码过期策略。默认情况下，密

码不会过期。若将该选项设置为ON，将强制密码过期策略

CHECK_POLICY 设置SQL Server是否强制实施密码策略（长度、字符要求，等

等）。默认情况下，密码必须满足Windows密码策略。如果把

该选项设置为OFF，实际上将允许使用任何密码

CREDENTIAL 指定该登录名将映射到的凭据名称（关于这是什么的问题，在

后面将有更多的了解）。简言之，该选项把登录名映射到一组

许可上，从而允许在SQL Server之外执行操作（如网络访问之

类）

所有这些选项都可以不提供，并且，仅在使用HASHED和MUST_CHANGE时（如果要使用它们，必须跟随在PASSWORD选项之后使用），提供选项的顺序才是要紧的。 ● CREATE LOGIN...FROM

FROM子句表明，该登录名不是SQL Server专用的。FROM子句指定登录名的来源。来源分为几种不同的类别：

l WINDOWS——在这种情况下，我们把登录名映射到现有的windows登录名或组上。基本上，这是表示，“获取这个现有的Windows用户或组，并赋予它们访问SQL Server的权力。”你可以把SQL Server登录名映射到Windows组上，这表明，将把到SQL Server的那种级别的访问授予该组中所有的成员。实际上，这对于网络中的用户管理非常方便。例如，如果想要会计部门中的所有人在SQL Server中都拥有一组特定的权力，可以创建一个名为Accounting的Windows组，并把该组映射到一个SQL Server登录名。假如你新近雇用了某人，那么，一旦把他们加入到Accounting组中，他们不仅将获得Accounting组所拥有的Windows资源的访问权，而且，还将拥有Accounting组所拥有的所有SQL Server许可权限。 如果用Windows作为FROM的来源，那么，与基于SQL Server的登录名类似，你也可以提供一个WITH子句，只是这里局限于默认的数据库和语言。

l CERTIFICATE——这种登录名是基于X.509证书的，你已经通过CREATE CERTIFICATE命令将证书与服务器相关联。可以用几种不同的方式来使用证书，但最终，在本质上，它们将作为已认可的安全加密密钥。SQL Server有它自己的“证书颁发机构”，或者，也可以导入从其他的源产生的那些。本质上，需要提供证书作为登录到SQL Server中的授权。

l ASYMMETRIC KEY——非对称密钥与证书只是同一概念的两种方式。实质上，如果所给出的密钥是SQL Server信任的，则允许访问。非对称密钥只不过是表示安全密钥的另一种方法。

● ALTER LOGIN

像我们在SQL中见过的大多数CREATE语句一样，CREATE LOGIN有一个ALTER LOGIN形式的附加语句。像大多数ALTER语句一样，其语法主要是相关的CREATE语句中的选项

的子集：

ALTER LOGIN <登录名>

[ { ENABLE | DISABLE } ]

[ { WITH

PASSWORD = '<密码>'

[ { OLD_PASSWORD = '<旧密码>'

| [ UNLOCK ] [ MUST_CHANGE ] }

| DEFAULT_DATABASE = <数据库>

| DEFAULT_LANGUAGE = <语言>

| NAME = <新的登录名>

| CHECK_EXPIRATION = { ON | OFF}

| CHECK_POLICY = { ON | OFF}

[ CREDENTIAL = <凭据名>

| NO CREDENTIAL

其中的大多数与它们在CREATE语句中完全一样，让我们来看一些不同之处。

选 项

ENABLE

DISABLE 说 明 | 启用或禁用该登录。这有点像一个指示器，用以表示该登录在系统中是否被认为是活动的，另外，不要把ENABLE与UNLOCK混同起来

（它们是不同的事情）。禁用登录时，登录依然在那里，只是不允许

使用登录而已。启用将重新激活登录

仅当指定的登录要使用ALTER LOGIN来更改它自己的密码时，该选

项才适用。具有更改密码权限的安全管理员根本不可能知道旧的密

码，并且，安全管理员无需了解旧密码，就拥有设置新密码的权限

当登录由于超过了设定的错误密码计数而被锁定时，该选项允许用户

再次尝试登录

该选项允许你更改登录名，然而，另一方面，将保留该登录所有旧的

权限和其他属性

使登录与登录以前被映射到的所有凭据脱离开 OLD PASSWORD UNLOCK NAME NO

CREDENTIAL

● DROP LOGIN

该语句与SQL Server中其他的DROP语句类似。

DROP LOGIN <登录名>

执行语句后，登录就被删除了。

2．使用Management Studio创建登录

使用Management Studio创建登录账户非常简单，并且，这与创建SQL Server中大多数其他对象一样。只需要在“对象资源管理器”中导航到相应的结点（这里是“安全性”→“登录名”），右键单击，然后选择“新建登录名……”，如图22-1所示。

图 22-1

这将打开我们在本书中多次见过的典型的“新建”对话框，不过，这里的对话框针对适用于登录名的属性做了相应的调整，如图22-2所示。

图 22-2

图中，只有第一组属性（“常规”属性）可以映射到CREATE LOGIN语法。另外的选项卡将映射到其他的对象，我们将在本章接下来的讲述中创建这些对象。

我们将回顾其他几种以某种形式与登录名有关联的对象。眼下要注意的是，Management Studio中的用户界面是如何让你一次完成所有的事情的。在继续本书的讲述时将看到，当使用代码创建这些对象时，我们不得不分别完成每一步，而不能像Management Studio提供的那样一次完成全部的事情（正如你可能想象的，实际上，它不过是预先把所有必需的信息集中到一起，然后，为我们发出所有单独的程序化步骤）。

3．SQL管理对象

这超出了本章的范围（我们将在后面SMO自己的一章中涵盖其内容），但是，我确实想要特别指出，SMO能够使用直截了当的为对象模型创建登录账户，而不是使用CREATE语句这样的方法。更多相关内容请见第25章。

4．遗留选项

在以前版本的SQL Server中，当考虑创建登录账户的方法时，有3个较早的重要选择。 l sp_addlogin和相关的存储过程——除了CREATE LOGIN语句的几个部分所实现的事情在SQL Server 2000和更早的版本中不被支持外，本质上，sp_addlogin存储过程对应于CREATE LOGIN语句。不过，基本要素（创建典型的登录账户，而非证书或非对称密钥方法）都在这里。我们马上会更详尽地查看sp_addlogin。

l SQL-DMO——在SQL Server 2005中，已经完成了对分布式管理对象（DMO）的废除。这里说“完成”是因为，在SQL Server 2000面世的时候，WMI本来是做事情的一种新的方法——唔，它没有那样好。在2005的开发周期中尚早的时候，就决定了要使用一种更面向.NET的模型，于是，SMO诞生了。SMO自己就是一本书，并且，它完全超出了本书的范围。由此，可以把DMO想成是SMO出现之前的SMO。尽管模型有很大的差异，但是基本目的是相同的。

l WMI——Windows Management Instrumentation是工业标准Web管理协议的实现。正如

我刚才说过的，在SQL Server 2000最初面世的时候，认为基于WMI的模型将接管成为自动化SQL Server管理的主要方式。最终，没有基于WMI的模型接近达到暴露我们在SQL Server中所需的所有事情的任务，而那样的努力似乎很大程度上也白费了。与DMO很类似，WMI眼下位于本书的范围之外，不过要认识到，当你需要管理较早版本的SQL Server时，它在那里并仍然是一种选择。

● sp_addlogin概览

该存储过程做的正是它所表达的事情，而且，它是一种旧的方法，它所实现的是今天由CREATE LOGIN为我们实现的事情。该存储过程必需的参数只有一个，但是，多数时候将使用2个或3个参数。这里有几个额外的参数，但是，它们极少被用到。该存储过程的语法如下：

EXEC sp_addlogin [@loginame =] <'登录名'>

[,[@passwd =] <'密码'>]

[,[@defdb =] <'数据库'>]

[,[@deflanguage =] <'语言'>]

[,[@sid =] 'SID']

[,[@encryptopt =] <'加密选择'>]

参 数

@loginame

@passwd

@defdb 说 明 正如它听上去那样——这是要使用的登录ID 更是如它听上去那样——用上述的登录ID进行登录时所使用的密码 默认数据库。该参数定义了用户登录时首先连接到的“当前的”数据库。

通常，这将是应用程序使用的主要的数据库。如果不指定该参数，则默

认数据库将是master数据库（你一般不想要这样，因此，务必要提供该

参数）

用户的默认语言。如果你支持本地化，可以使用该参数覆盖系统的默认

值

该参数是一个二进制数，它成为登录ID的安全标识号（SID）。如果不提

供SID，SQL Server将为你生成一个。由于SID必须是唯一的，因此，

提供的任何SID一定不能已经存在于系统中

当你在把数据库还原到不同的服务器上时，或者当迁移登录信息时，使

用明确的SID能够十分便利

说 明

用户的登录ID和密码信息存储在master数据库中的sysusers表里。

@encryptopt指定存储在master数据库中的密码是否加密。默认情况下

（或者当在参数中提供NULL时），要对密码进行加密。另一个选择是

skip_encryption，该值正如它所说明的那样——不对密码进行加密，而

skip_encryption_old仅仅是为了向后兼容性，不应当再继续使用这一选项 @deflanguage @sid （续） 参 数 @encryptopt

正如你能够看出的，这里多数的项都直接映射到CREATE LOGIN，并且，如果不必为了向后兼容性的原因而使用sp_addlogin的话，建议你使用CREATE LOGIN方法来创建登录账户。 ● sp_password

既然我们看了sp_addlogin，那么，我们应该看看sp_password。尽管ALTER LOGIN使你能够在登录上进行密码维护，sp_addlogin却没有这种功能——sp_password将处理那样的事情。其语法非常简单：

sp_password [[@old =] <'旧密码'>,]

[@new =] <'新密码'>

[,[@loginame =] <'登录名'>]

无疑，新密码和旧密码参数就像你期望的那样工作。你需要从用户那里接受它们，然后把它们传入存储过程中。然而，要注意，登录名是可选参数。如果不提供登录名，则将假定你想要在当前连接所使用的登录名上更改密码。注意，sp_password不能作为事务的一部分来执行。

你可能会想着这样的问题，“大多数系统都要求输入两次新密码，难道不是吗？”的确如此。那么，接下来的问题是，“为什么sp_password不这样做呢？”答案很简单——因为SQL Server把这一问题留待你来决定。你可以在客户端应用程序中包含一个逻辑，该逻辑在你使用sp_password之前，对两条新密码进行检查。对于ALTER LOGIN，也存在同样的问题。 ● sp_grantlogin

该存储过程模拟了CREATE LOGIN...FROM与Windows登录有关的功能（由于此刻早于SQL Server 2005，因而源自证书和非对称密码的映射尚不存在）。其语法非常简单：

sp_grantlogin [@loginname = ]'<域名>\<Windows用户名>'

22.2.3 Windows集成的安全性

Windows安全性让我们能够把登录从信任的Windows域映射到SQL Server中。

这只是一种模式，在这种模式中，你取用现有的Windows域用户账户或组，并直接给它们提供SQL Server权限，而不是强迫用户保存单独的密码和进行单独的登录。

Windows安全性允许：

l 只从一个地方就能维护用户的访问；

l 只需简单地把用户加入到Windows组中，就能够授予用户SQL Server权限（这意味着，通常，为了准予用户访问，你甚至无需进入到SQL Server中）；

l 用户只需记住一个密码和登录名即可。

不过，接下来，还是让我们看一下如何授予特定的用户特定的权限。

实际上，在SQL Server 2005中，几乎没有理由不加密数据。现在，我们有大量非常、非常安全的加密选项可用（在本章的后面将有更多相关的内容）——请使用它们！

22.3 用户权限

关于什么是用户权限，最简单的定义可能是，“用户能做什么和不能做什么。”在这里，简单的定义就相当不错了。

用户的权限分为3类：

l 登录的权限；

l 访问特定数据库的权限；

l 在数据库中具体的对象上执行特定操作的权限。

既然我们已经看过了创建登录账户，这里将把重点放在登录账户能够拥有的特定权限上。 22.3.1 授予访问特定数据库的权限

如果想要一个用户可以访问数据库，你需要做的第一件事情是授予用户访问那个数据库的权限。可以在Management Studio中，通过把用户加入到服务器的数据库结点的用户成员中来实现。如果要用T-SQL来添加用户，需要使用CREATE USER或遗留的存储过程sp_grantdbaccess。

注意，当你在数据库中CREATE一个用户时，实际上，那些许可权限被存储在数据库中，并映射到那个用户的服务器标识符上。当还原数据库时，可能不得不在还原数据库的地方，重新把用户权限映射到服务器标识符。

1．CREATE USER

CREATE USER命令把新用户添加到数据库中。用户可以源自现有的登录名、证书或非对称密钥，用户也可以是只能在当前数据库中的本地用户。其语法如下：

CREATE USER <用户名>

[ { { FOR | FROM }

{

LOGIN <登录名>

| CERTIFICATE <证书名>

| ASYMMETRIC KEY <密钥名>

}

| WITHOUT LOGIN ]

[ WITH DEFAULT_SCHEMA = <模式名> ]

对于这些元素，我们概略看一下其中一些元素的含义是什么：

选 项

LOGIN

CERTIFICATE

ASYMMETRIC KEY

WITHOUT LOGIN 说 明 想要授予访问当前数据库的权限的登录名 与用户关联的证书的逻辑名称。注意，必须已经使用CREATE CERTIFICATE命令创建了证书 与用户关联的非对称密钥的逻辑名称。注意，必须已经使用CREATE ASYMMETRIC KEY命令创建了密钥 创建只能在当前数据库中活动的用户。可以用它来建立特定的安

全上下文，但是，该用户不能映射到当前数据库之外的登录名，

也不能访问任何其他的数据库

设立不是默认的“dbo”的模式，以作为当前用户的默认模式 WITH

DEFAULT_SCHEMA

2．sp_grantdbaccess

这是遗留的方法，用来授予登录名到特定数据库的访问权限。其语法如下：

sp_grantdbaccess [@loginame =] <'登录名'>[, [@name_in_db =] <'数据库中的别名'>

注意，授予的是当前数据库的访问权限——即是说，你必须确保想要用户能够访问的数据库是发出该命令时的当前数据库。登录名是用来登录到SQL Server中的实际的登录ID。参数name_in_db允许给该用户另外的识别名称。这个别名只适用于此处的数据库——其他所有的数据库仍将使用该登录ID的默认名称，或者使用在授予用户那个数据库的访问权限时所定义的别名。定义别名将影响身份识别函数，如USER_NAME()。系统级别的函数（如SYSTEM_USER）将返回基础的登录ID。

22.3.2 授予数据库中对象的权限

好吧，用户拥有了登录名，并且，能够访问你想要他或她可以访问的数据库，那么，是否现在就万事大吉了呢？如果事情真有那么简单就好了！现在当然还没有一切就绪。

在用户能够访问什么的问题上，SQL Server给了我们级别相当精细的控制。多数时候，一些信息是希望用户能够访问到的，但是，数据库中也有另一些信息是不希望用户访问的。例如，你可能想要客户服务人员能够查看和维护订单信息，但是可能不希望他们乱看工资信息。或许，反之亦然——你需要人力资源人员能够编辑雇员记录，但是，或许不想要他们在交易上给某人很大的折扣。

SQL Server允许你给SQL Server中一些不同的对象指派一组不同的权限。能够为其指派权限的对象包括表、视图和存储过程。触发器隐含具有创建它们的人的权限。

对象上的用户权限分为6种不同的类型。

用户权限

说 明

用户权限

SELECT 说 明 允许用户“看到”数据。如果用户拥有该权限，则用户能够在其被授予权

限的表或视图上运行SELECT语句

INSERT 允许用户创建新的数据。具有这种权限的用户能够运行INSERT语句。

注意，与许多系统不同，具有INSERT能力并不一定意味着拥有SELECT

权限

UPDATE 允许用户修改已有的数据。具有这种权限的用户能够运行UPDATE语

句。类似于INSERT语句，具有UPDATE能力并不一定意味着拥有

SELECT权限。

DELETE 允许用户删除数据。具有这种权限的用户能够运行DELETE语句。同样，

具有DELETE能力不一定意味着拥有SELECT权限

REFERENCES 在要插入行的表中有引用另一个表的外键约束，而用户在那个表上没有

SELECT权限，REFERENCES权限允许用户插入行

EXECUTE 允许用户EXECUTE指定的存储过程

在你正在把权限指定到其上的特定的表、视图或存储过程中，可以在需要时混合搭配这些权限。

可以在Management Studio中指派这些权限，你只需导航到服务器的“安全性”结点的“登录名”选项上。在用户上右击，并选择“属性”。根据你是在数据库中还是在安全性结点中，打开的对话框将有所不同，但是，无论哪一种情况，都能够得到设置权限的选项。使用T-SQL指派权限会使用三个命令，了解这三个命令是有益的，即使你只准备通过Management Studio来指派权限（术语是相同的）。

1．GRANT

GRANT把对象上指定的访问权限给予指定的用户或角色，对象是GRANT语句的主体。 GRANT语句的语法如下所示：

GRANT

ALL [PRIVILEGES] | <权限>[,...n]

ON

<表名或视图名>[(<列名>[,...n])]

|<存储过程或扩展存储过程名>

TO <登录ID或角色名>[,...n]

[WITH GRANT OPTION]

[AS <角色名>]

ALL关键字表示你想要授予的是适用于那个对象类型的所有权限（EXECUTE绝不适用于表）。如果不使用ALL关键字，则需要提供一个或多个具体的权限，这些具体的权限是针对那个对象想要授予的。

PRIVILEGES是一个新的关键字，它除了提供ANSI-92兼容性外没有实际的功能。

ON关键字用作一个占位符，以说明接下来的是想要授予其权限的对象。注意，如果你是在表上授予权限，可以通过明确说明受影响的列的列表来指定下至列级的权限——如果不提供具体的列，则认为将影响所有的列。

在对列级权限的看法上，微软似乎做的是些表面的事情。能够说一个用户可以在特定的表上进行SELECT，但仅限于在该表中特定的列上进行SELECT，这似乎很酷，然而，在列级权限的使用中以及微软为实现列级权限所做的工作中，确实让安全性处理太过错综复杂了。鉴于此，近来关于该主题的文献，以及我从内部人士那里得到的消息，似乎都表明微软想要丢弃列级安全性了。在使用上他们建议——如果需要限制用户只能看到特定的列，请改为考虑

使用视图。

TO语句所做的事情正如你期望的那样——它指定想要把该访问权限授予谁。被授予权限的可以是登录ID或角色名。

WITH GRANT OPTION允许你向其授予访问权限的用户也能向其他用户授予访问权限。 由于使用该选项后，要了解谁获得了访问什么的权限，将很快变得十分痛苦，因此，我建议避免使用该选项。当然，你总是可以进入到Management Studio中来查看对象上的权限，但那是被动反应的方式而非积极主动的方式——你是在查找当前访问级别上哪里出错了，而不是事先停止不希望发生的访问。

最后，但并非最不重要的，是AS关键字。该关键字处理的是一个登录名属于多个角色的问题。

接下来，我们来看一、两个例子。后面将看到，我们已经创建的TestAccount账户，基于其是Public角色（所有的数据库用户都属于的东西，并且，无法从中移除）中的成员而拥有了一些访问权限。然而，尚有大量的项目是TestAccount不具有访问权限的（由于Public是TestAccount唯一属于的角色，因此，Public也不具有那些权限）。

先从以TestAccount用户登录开始。然后在Region表上尝试一个SELECT语句：

很快，你将收到来自SQL Server的消息，告知：你正在尝试去到你所不应该访问的地方。

单独以sa登录——如果你愿意，也可以在同一个查询编辑器实例中，通过选择菜单“文件”→“连接”，来完成这件事情。然后，为新的连接选择“SQL Server身份验证”，并用正确的密码以sa身份登录。现在，执行GRANT语句：

接着，切换回TestAccount连接（要记住，以什么用户进行连接的信息显示在连接窗口的标题栏中），然后，再尝试执行SELECT语句：这一次，得到了好得多的结果：

我们继续尝试另外的语句。这一次，我们在EmployeeTerritories表上运行相同的测试和命令：

该语句执行失败——这同样是由于你不具备相应的权限所致，因此，授予用户该表上的权限：

然后，再次运行SELECT语句，一切进展顺利：

不过，若要再添加一点变化，尝试在这个表中执行INSERT：

SQL Server立即会让我们走开——我们不具备必要的权限，因此，授予用户相应的权限（使用sa连接）：

现在，再次运行INSERT语句：

一切进展顺利。

2．DENY

DENY明确阻止用户获得目标对象上指定的访问权限。DENY的关键所在是，它将覆盖任何GRANT语句。由于用户可以属于多个角色（马上将对此进行讨论），因此，一个用户可能属于被授予了访问权限的角色，但同时又受DENY的影响。如果用户个人的权限和基于角色成员身份所获得的权限混合在一起，DENY和GRANT同时存在于其中，那么DENY总是优先的。简言之，如果用户或用户所属的任何角色在权限问题上有DENY出现，则用户将不能使用在那个对象上的访问权限。

其语法很复杂繁多，看上去与GRANT语句一样：

DENY

ALL [PRIVILEGES]|<权限>[,...n]

ON

<表名或视图名>[(列名[,...n])]

|<存储过程或扩展存储过程名>

TO <登录ID或角色名>[,...n]

[CASCADE]

同样，ALL关键字表明，想要拒绝授予该对象类型上所有可用的权限（EXECUTE绝不适用于表）。如果不使用ALL关键字，则需要提供一个或多个具体的权限，这些具体的权限是针对想要拒绝授予权限的对象的。

PRIVILEGES依然是新关键字，并且，除了提供ANSI-92兼容性外没有任何实际的功能。 ON关键字用作一个占位符，以说明接下来的是想要拒绝授予其权限的对象。

到此为止，所有的事情都与GRANT语句几乎一样。CASCADE关键字与GRANT语句中的WITH GRANT OPTION相对应。CASCADE告诉SQL Server，如果用户在WITH GRANT OPTION规则下授予了其他人访问权限，则对于所有这些人，也拒绝他们的访问。

为了在DENY上运行一个例子，我们使用TestAccount登录名尝试执行一个简单的SELECT语句：

运行该语句后，将返回大约9条记录。在我们不曾授予TestAccount该权限时，它是如何获得访问权限的呢？原因是，TestAccount属于Public，而Public被授予了Employees上的访问权限。

假如我们不希望TestAccount能够访问Employees。无论什么原因，TestAccount是一个例外，并且我们不希望该用户查看那些数据——我们只需发出DENY语句（记住要使用sa登录名来运行DENY）：

当再次用TestAccount登录名运行SELECT语句时，将得到一个错误——你不再能够访问。此外还要注意，由于我们使用了ALL关键字，因此，也拒绝将Public所拥有的INSERT、DELETE和UPDATE访问权限授予TestAccount。

注意，DENY是SQL Server 7.0中新增的语句。在6.5版本中有拒绝授予权限的概念，但是，其实现方式是不同的。在6.5版中，不是使用DENY，而是发出两次REVOKE语句。新的DENY关键字让事情更加清晰明了。

3．REVOKE

REVOKE将消除以前发出的GRANT或DENY语句的影响。可以把该语句想成是有针对性的“撤销”语句。

REVOKE的语法混合了GRANT和DENY语句：

REVOKE [GRANT OPTION FOR]

ALL [PRIVILEGES] | <权限>[,...n]

ON

<表名或视图名>[(列名 [,...n])]

|<存储过程或扩展存储过程名>

TO | FROM <登录ID或角色名>[,...n]

[CASCADE]

[AS <角色名>]

实际上，这里要做的说明与对GRANT和DENY语句的说明相同——然而，我将在这里再次讲述，以免你为了快速查找有关REVOKE的说明而向前翻阅本书。

同样，ALL关键字表明，想要撤销在该对象类型上所有可用的权限。如果不使用ALL关键字，则需要提供一个或多个具体的权限，这些具体的权限是针对那个对象想要撤销的权限。 PRIVILEGES除了提供ANSI-92兼容性外，依然没有任何实际的作用。

ON关键字用作一个占位符，以说明接下来的是想要撤销其权限的对象。

CASCADE关键字与GRANT语句中的WITH GRANT OPTION相对应。CASCADE告诉SQL Server，如果用户在WITH GRANT OPTION规则下授予了其他人访问权限，则对于所有这些被授予权限的人，也将撤销他们的访问权限。

同样，AS关键字只是用来说明想要基于哪个角色发出这一命令。

我们使用sa连接，撤销授予的到NorthwindSecure中的Region表的访问权限。

执行完该语句后，TestAccount将不能再在Region表上运行SELECT语句。

为了撤销DENY，我们同样也发出一个REVOKE语句。这一次，将重新获得到Employees表的访问权限：

现在，我们已经了解所有这些命令是如何针对单个用户来控制访问权限的，接下来，看这样一种方法，该方法通过分组管理来极大简化对这些权限的管理。

22.3.3 用户权限和语句级别的许可

用户许可权限并不仅仅局限于数据库中的对象上——它们也能扩展到某些其他的语句，这些

语句不直接与任何特定的对象束缚在一起。SQL Server允许你对运行几种不同的语句的许可权限进行控制，这些语句包括：

l CREATE DATABASE；

l CREATE DEFAULT；

l CREATE PROCEDURE；

l CREATE RULE；

l CREATE TABLE；

l CREATE VIEW；

l BACKUP DATABASE；

l BACKUP LOG。

到现在为止，除了两个备份命令外，其他所有的这些命令我们都已经在操作中见过了。（备份命令要做什么是不言自明的，因此，眼下不准备在这上面花费时间，我们将在第24章中讨论它们——只需记住，它们是你能够在语句级别进行控制的东西。）

那么，我们如何指派这些许可权限呢？实际上，现在你已经见过GRANT、REVOKE和DENY针对对象的运作，那么，在语句级别的许可权限上你也已经有了相当的了解。从语法构成上说，它们与对象级别的许可权限基本相同，除了它们更加简单（你不必填入那样多的东西）。其语法如下：

GRANT <ALL | 语句[,...n]> TO <登录 ID>[,...n]

很简单吧？接下来，通过验证我们的测试用户尚没有权力执行CREATE，以进行一次快速的试验。确保以TestAccount登录，然后，运行下面的命令（在下面的语句中，不要忘记把ARISTOTLE转换到你的域名）：

我们运行上面的命令是完全行不通的：

现在，使用sa账户（或者其他具有NorthwindSecure的dbo权力的账户）登录到SQL Server中。然后，运行命令以授予许可权限：

你会得到命令成功执行的确认消息。然后，再次尝试运行CREATE语句（记住使用TestAccount登录）：

这一次一切顺利。

在对象级别的许可权限上，DENY和REVOKE也以同样的方式工作。

22.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时就已经建立于其中的角色，并且，它在这里主要是为了进行系统的维护以及授予完成非数据库特有的事情的能力，如创建登录账户和创建链接服务器。

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

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

22.4.1 服务器角色

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

角 色

sysadmin 特 性 该角色能够执行SQL Server上的任何操作。本质上，任何具有这种角色

成员身份的人都是那个服务器上的sa。这种服务器角色的创建为微软提

供了某一天去除sa登录的能力——实际上，联机丛书把sa称作本质上

为遗留物的东西

值得注意的是，在SQL Server上，Windows的Administrators组被自动

映射到sysadmin角色中。这意味着服务器的Administrators组中的任何

成员同时也具有对SQL数据的sa级别的访问权限。如果需要，你可以

从sysadmin角色中删除Windows的administrators组，以提高安全性、

防范漏洞

该角色能设置服务器范围的配置选项或关闭服务器。尽管它在范围上相serveradmin

角 色 特 性

当有限，但是，由该角色的成员所控制的功能对于服务器的性能会产生

非常重大的影响

该角色仅限于管理链接服务器和启动过程

对于专门创建出来用于管理登录名、读取错误日志和创建数据库许可权

限的登录名来说，该角色非常便利。在很多方面，该角色是典型的系统

操作员角色——它能够处理多数的日常事务，但是，却不具备一个真正

无所不能的超级用户所拥有的那种全局访问

能够管理SQL Server中运行的进程——必要的话，该角色能够终止长时

间运行的进程

该角色仅限于创建和更改数据库

管理磁盘文件（指派给了什么文件组、附加和分离数据库，等等）

该角色有些怪异。它被明确创建出来，用于执行BULK INSERT语句的

权限，否则的话，只能由具有sysadmin权限的人来执行BULK INSERT

语句。坦白地说，我不明白为什么该语句不能像其他事情那样通过

GRANT命令来授予权限，但它的确没有。要记住，即使把一个用户加

入到了bulkadmin组中，也只是给了他们访问那个语句的权限，对于运

行该语句的表，并没有授予用户访问那个表的权限。这意味着不仅需要

把用户添加到bulkadmin中，而且，对于想要用户能在其上执行BULK

INSERT的表，还要授予（GRANT）用户INSERT许可权限。此外，对

于将在BULK INSERT语句中引用的所有表，还要确保用户拥有正确的

到那些表的SELECT访问权限 setupadmin securityadmin processadmin dbcreator diskadmin bulkadmin

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

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

22.4.2 数据库角色

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

1．固定数据库角色

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

角 色

db_owner 特 性 该角色表现得就好像它是所有其他数据库角色中的成员一样。使用

这一角色能够造就这样的情形：多个用户可以完成相同的功能和任

务，就好像他们是数据库的所有者一样

实现类似于securityadmin服务器角色所实现功能的一部分，只不

过这一角色仅局限于指派它并创建用户的单个数据库中（不是单个db_accessadmin

角 色 特 性

的权限）。它不能创建新的SQL Server登录账户，但是，该角色中

的成员能够把Windows用户和组以及现有的SQL Server登录账户

加入到数据库中

能够在数据库中所有的用户表上执行SELECT语句

能够在数据库中所有的用户表上执行INSERT、UPDATE和

DELETE语句

能够在数据库中添加、修改或删除对象

securityadmin服务器角色的数据库级别的等价物。这一数据库角色

不能在数据库中创建新的用户，但是，能够管理角色和数据库角色

的成员，并能在数据库中管理语句和对象的许可权限

备份数据库（打赌你不会想到那样一个角色！）

提供一种等同于在数据库中所有表和视图上DENY SELECT的效

果

类似于db_denydatareader，只不过这里影响的是INSERT、UPDATE

和DELETE语句 db_datareader db_datawriter db_ddladmin db_securityadmin db_backupoperator db_denydatareader db_denydatawriter

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

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 <'角色名'>

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

22.5 应用程序角色

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

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

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

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

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

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

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

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

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

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

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

22.5.1 创建应用程序角色

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

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

[@password =] <'密码'>

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

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

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

向应用程序角色中添加许可权限与向任何其他事物中添加许可权限一样。只需在使用用户登

录ID或者常规的服务器或数据库角色的地方，把它们替换成应用程序角色的名字即可。 同样，我们马上进入到一个例子：

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

22.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语句，会发现恢复了以前的访问权限。

22.5.4 删除应用程序角色

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

sp_dropapprole [@rolename =] <角色名>

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

22.6 更高级的安全性

实际上，本节不过是关于“另外要考虑的问题”的一节。这一节中的所有内容都位于本章前面阐释的基本规则的范围之外，但是，它们提供了一些问题的解决思路，并且，也给出了如何关闭系统中一些常见漏洞的方法。

22.6.1 关于guest账户应该怎么办

guest账户提供了一种拥有默认访问权限的方法。当激活guest账户时，会发生如下一些事情： l 对于没有明确授予登录账户对其进行访问的权限的数据库，登录账户能获得guest级别的访问权限。

l 外部的用户能够通过guest账户进行登录，从而获得访问权限。虽然，这需要他们知道guest的密码，但是，他们已经知道该用户是存在的（他们或许也知道sa账户是存在的）。 就我个人而言，我对SQL Server首先要做的事情之一就是，去除guest账户所拥有的每一点访问权限。这是一个漏洞，它最终会以你不曾意识到的方式提供访问权限（你或许认为，当给某人指派权限时，你所指派的权限就是此人拥有的所有权限。但是，在激活了guest时，就不一定是这样了）。建议你跟我一样。

然而，我意识到，实际上guest账户能提供一种很圆滑的用途——当它与应用程序角色一起使用时。在这种情况下，guest账户能够访问数据库，但是，除了登录到数据库中外，它没有任何权限——guest账户只是让登录成为数据库中“当前的”登录。然后，就能够使用sp_setapprole来激活应用程序角色，于是，就有了一种方式，可以让匿名用户登录到服务器中，并具有适当的权限。尽管如此，如果要使用应用程序，只有在执行了任何可用的登录后才能够使用。

这绝对是想要保护应用程序角色的密码的场景，就好像你的工作都靠它一样（可能是这样的）。请使用ODBC加密选项——尤其是当连接是通过因特网进行时！

22.6.2 TCP/IP端口设置

默认情况下，在使用TCP/IP时，SQL Server会使用1433端口号。可以把端口想成是类似无线电信道的东西——在什么信道上进行广播并不重要，如果没有任何人侦听那个信道，则这样做没什么用处。

让端口设置保持默认值1433可能会非常方便——如果没有另外指定的话，所有的客户都将自动使用1433端口，因此，这意味着如果就这样听其自然，那么，关于设置是否正确的问题，你少了一件要担心的事情。

然而，问题在于任何潜在的SQL Server黑客也同样知道，在所有的SQL Server中，大约有99%都会侦听1433端口。如果你的SQL Server有直接到因特网的连接，我强烈建议将端口更改为非标准的端口号——与你的网络管理员商量，看看他（或她）推荐以什么端口作为可用的端口。只是要记住，当修改了服务器“侦听”的端口时，同时也需要修改所有基于IP的客户端所使用的端口。例如，如果要更改为使用1402端口，我们可以进入到客户端网络实用工具，并用1402作为要使用的IP端口为我们的服务器建立一个特定的条目。

从SQL Server 2000开始，我们还有一个选择，这就是让客户端通过选中动态确定端口框来动态确定端口。

22.6.3 别使用sa账户

所有在SQL Server上学习过10分钟以上的人，都会对系统管理员账户有一些了解。现在，SQL Server有了sysadmin固定服务器角色，我强烈建议把真实的登录账户添加到那个角色中，然后，将sa的密码更改为非常长并且非常难以理解的密码——不值得为了潜入系统而在这样的密码上耗费时间。如果只需要Windows安全性，则关闭SQL Server安全性，那样的话，就可以一劳永逸地处理有关sa账户的问题。

22.6.4 让xp_cmdshell保持隐秘

你准备授予谁使用xp_cmdshell的访问权限，在这一问题上一定要非常小心。它可以运行任何DOS或Windows命令提示符命令。授予了用户多大的权限，这取决于SQL Server运行在什么账户之下。如果是系统账户（多数是这样），则xp_cmdshell的用户将拥有到服务器的很高的访问权限（例如，他们能够把文件从网络的其他地方复制到服务器上，然后执行这些文件）。让我们再提高一点利害关系：在Windows域管理员账户的安全上下文之下，有大量的服务器运行于其中，现在，任何使用xp_cmdshell的人，对你的整个网络访问都拥有相当开放的访问权限。

对于所有你不预备给予其对服务器甚至对域的管理权限的人，不要给予他们到xp_cmdshell的访问权限。

22.6.5 不要忘记作为安全手段的视图、存储过程以及UDF

记得视图、存储过程和UDF都在隐藏数据的问题上做出了大量的贡献。通常，视图能够取代列级安全性。它们能迷惑用户，让用户以为访问到了整张表，而实际上，只访问到了整张表的子集（还记得我们筛选敏感的雇员信息的例子吗？如工资信息？）。存储过程和UDF能够做的事情大致相同——你能够授予对存储过程或UDF执行的权限，但是，那并不意味着用户从表中得到了所有的数据（用户得到的是存储过程或UDF给予他们的）——最终的用户甚至不知道是由什么底层表提供的数据。此外，视图、存储过程和UDF有它们自己隐含的权限——即是说，不能因为视图和存储过程使用表，就意味着用户具有对那个表的访问权限。

22.7 证书和非对称密钥

在本书中一些不同的地方（包括本章的前面），我们提到过加密的概念。对于服务器体系结构的不同级别来说，证书和非对称密钥是定义加密密钥的主要的加密机制。这二者是完成相同的基本事情的不同方法，并且，它们在很大程度上是可互换的。无论使用的是证书还是非对称密钥，都需要记住，它们很像是你房间的钥匙——如果让所有人都拥有的话，则它们很快将失去自身的价值（现在，既然所有的人都能进来，那么，为什么要多此一举地把所有人锁在外面呢？）。

你可能想要在不同的加密密钥下分隔开若干不同的控制筒仓，基于这种打算，SQL Server在几个不同的级别支持密钥的概念。在每一个服务器安装中，SQL Server都维护有一个服务主密钥。它由Windows级别的服务主密钥加密。同样，每一个数据库都包含一个数据库主密钥，如果选择，它本身可以基于服务主密钥进行加密。然后，在每一个数据库中，可以定义证书和（或）非对称密钥（这二者都是某种形式的密钥）。总的来说，加密层次结构如图22-3所示：

数据

数据

数据

对称密钥

对称密钥

对称密钥

非对称密钥

证书

数据库主密钥

服务主密钥 用DPAPI加密

服务主密钥

数据库级别

SQL Server级

别

Windows

别

级

图 22-3

22.7.1 证书

从SQL Server 2000开始，SQL Server拥有了它自己的证书颁发机构，或称CA。同样，也支持第三方的CA。CA颁发证书，颁发的证书中包含加密密钥，以及一些与证书有关的基本信息，如证书有效的时间范围（一个起始日期和一个到期日期）、证书持有者的名字、证书颁发机构的信息。使用CREATE CERTIFICATE命令把证书添加到服务器中。

22.7.2 非对称密钥

非对称密钥与证书的工作方式大体相同，但指定起来更加直接，并且，不由任何发行机构验证。与证书一样，指定加密密钥，然后利用加密密钥来加密敏感信息。使用CREATE ASYMMETRIC KEY命令把非对称密钥添加进来。