华联学院《SQL数据库系统》课件04数据库系统维护

1、1/204-216第四章 数据库系统维护2/204-216能力目标 (1)会进行SQL Server安全验证模式设置。(2)会创建和管理数据库服务器登录账号。(3)会创建和管理数据库角色和用户。(4)能进行权限的设置。(5)能使用游标遍历数据。(6)能根据实际情况进行数据库备份与还原。(7)会导入和导出数据库中的数据。(8)会分离和附加数据库。(9)会根据实际需求选择数据库安全策略。 3/204-216知识目标 (1)了解数据库安全的基本概念。(2)掌握SQL Server安全验证模式及其特点。(3)掌握数据库登录账号和数据库用户概念。(4)掌握角色和权限的概念。(5)掌握游标的使用方法。(6

2、)掌握数据库的备份和还原方法。(7)掌握数据库数据的导入与导出方法。(8)了解数据库的备份策略。 4/204-2164.1 创建用户并为之授权 数据库最大的特点就是数据共享，但同时数据共享会带来多方面的干扰和破坏问题，如因系统故障或人为破坏而造成的数据丢失问题，因不合法的使用而造成的数据泄密或破坏等。所以数据库管理员必须能够针对上述不同的情况，在技术上对数据进行安全性保护。它涉及SQL Server的认证模式、登录账号、数据库用户、角色和存取权限的管理。5/204-2164.1.1 SQL Server 2008的安全机制 目前，SQL Server 2008的安全性机制主要划分为4个等级，分

3、别为：(1)客户机操作系统的安全性。(2)SQL Server 2008的登录安全性登录账号和密码。(3)数据库的使用安全性该用户账号对数据库的访问权限。(4)数据库对象的使用安全性该用户账号对数据库对象的访问权限。6/204-2161 操作系统的安全性 SQL Server 2008与其它数据库管理系统一样，运行在某一个特定操作系统平台下的应用程序，因此操作系统的安全性直接影响到SQL Server 2008的安全性。在用户使用客户机通过网络实现对SQL server 2008服务器的访问时，用户首先要获得客户机操作系统的使用权。7/204-2162 SQL Server 2008的登录安全

4、性 SQL Server 2008服务器级的安全性建立在控制服务器登陆账号和密码的基础上。用户在登录时提供的登录账号和密码，决定了用户能否获得SQL Server 2008的访问权，以及在登录以后，用户在访问SQL Server 2008进程时可以拥有的权利。8/204-216 3 数据库的使用安全性 在用户通过SQL server 2008服务器的安全性检验后，将直接面对不同的数据库入口，这是用户接受的第三次安全性检验。 在创建用户登录账号时，SQL Server 2008会提示用户选择默认数据库，以后用户每次连接上服务器后，都会自动转到默认的数据库上。对任何用户来说，master数据库的大

5、门总是打开的，如果在设置登录账号时没有指定默认数据库，则用户的权限局限在master数据库内。9/204-2164 数据库对象的使用安全性 在创建数据库对象时，SQL Server 2008自动把该数据库对象的拥有权赋予该对象的创建者。默认情况下，只有数据库的拥有者可以在该数据库中进行操作。 当一个非数据库拥有者想操作数据库中的对象时，必须先由数据库拥有者赋予该对象执行特定的操作权限。10/204-2164.1.2 SQL Server 2008的验证模式 客户机连接到SQL Server 2008数据库服务器时，必须要以账户和密码登录，称为身份验证。SQL Server 2008支持两种身份

6、验证模式：Windows 身份验证模式和混合身份验证模式。(1)Windows 身份验证模式 Windows身份验证是由Windows操作系统来验证用户身份。SQL Server 2008数据11/204-216库系统通常运行在Windows服务器上，而Windows作为网络操作系统，本身就具备管理登录、验证账户合法性的能力，因此Windows验证模式正是利用了这一用户安全性和账号管理的机制，允许SQL Server 2008可以使用Windows的用户名和口令。在这种模式下，用户只需要通过Windows的验证，就可以连接到SQL Server 2008服务器，而服务器本身也就不需要管理一套登

7、录数据。12/204-216Windows身份验证模式下主要有以下优点：1)数据库管理员的工作可以集中在管理数据库方面，而不是管理用户账户。对用户账户的管理可以交给Windows 操作系统去完成。2)Window操作系统有着更强的用户账户管理工具。可以设置账户锁定、密码期限等。如果不是通过定制来扩展SQL Server，SQL Server是不具备这些功能的。3)Windows 操作系统的组策略支持多个用户同时被授权访问SQL Server 2008。13/204-216 (2)混合身份验证模式 SQL Server身份验证模式允许用户使用SQL Server登录 ID连接到SQL Serve

8、r 2008服务器。在该验证模式下，用户在连接SQL Server 2008服务器时必须提供登录名和登录密码，这些登录信息存储在系统表syslogins中，与Windows的登录账号无关。如果用户无法提供 SQL Server 2008 登录 ID，则使用 Windows 身份验证对其进行身份验证。14/204-216 混合验证模式具有以下优点：1)创建了Windows 操作系统之上的另外一个安全层次。2)支持更大范围的用户，如非Windows NT用户、Novell网用户等。3)一个应用程序可以使用单个的SQL Server登录账号和口令。 需要说明的是，混合验证模式是为了向后兼容以及满足非

9、Windows用户的需求，在实际的使用中，对于安全性要求较高的应用系统，建议优先考虑使用Windows身份验证模式。15/204-216通过以下方法设置服务器身份验证模式： 启动 SSMS窗口，在“对象资源管理器”中右键单击数据库服务器，选择“属性”选项，打开服务器属性对话框，在对话框中选择“安全性”选项页，选择服务器的身份验证模式，如图4-1所示。16/204-216图4-1 设置服务器身份验证模式17/204-2164.1.3 SQL Server的登录账号(1)登录账号 登录账号是基于服务器使用的用户名，是系统级信息，存在于master数据库的syslogins系统表中。在Windows

10、身份验证模式下，可以创建基于Windows组或用户的登录账号；在混合身份验证模式下，除了可以创建基于Windows组或用户的登录账号外，还可以创建SQL Server自己的登录账号。创建SQL Server登录账号只能由系统管理员完成。18/204-216 在安装好SQL Server 2008后，系统会自动创建两个内置的登录账号：Windows系统管理员组账号“计算机名Administrator”和系统管理员账号“sa”。其中，sa是为了向后兼容提供的特殊登录账号，默认情况下，它指派给固定服务器角色sysadmin作为混合身份验证模式下SQL Server的系统管理员。 需要说明的是，虽然S

11、QL Server有两个内置的管理员账号，但在平时的使用过程中，不应直接使用它们。数据库管理员应该19/204-216(2)使用对象资源管理器创建和管理登录账号 建立自己的系统管理员账号，使其成为sysadmin固定服务器角色的成员。只有在某种情况下没有办法登录到SQL Server 2008服务器时，才使用内置的管理员登录。1)创建登录账号。【例4-1】使用对象资源管理器创建一个用户名为“s_CeShi”，密码为“123456”的SQL Server用户。 启动 SSMS窗口，选择使用Windows身份20/204-216 验证登录。 在“对象资源管理器”中依次展开“服务器”“安全性”节点。

12、 右键单击“登录名”，在弹出的菜单中选择“新建登录名”选项，打开“新建登录名”对话框，如图4-2所示。 在登录名栏中输入要创建的登录名“s_CeShi”，选择“SQL Server身份验证”，输入密码“123456”，去掉“强制实施密码策略”复选框，其它使用默认选项，点击“确定”按钮，用户“s_CeShi”创建完成。21/204-216图4-2 “新建登录名”对话框22/204-2162)查看登录账号。启动 SSMS窗口，选择使用Windows身份验证登录。在“对象资源管理器”中依次展开“服务器”“安全性” “登录名”节点，查看所有的服务器登录用户。通过鼠标右键单击某个用户，在弹出的菜单中选择

13、“属性”选项打开属性对话框，如图4-3所示。在此对话框中可以修改用户密码或者选择密码策略。23/204-216图4-3 “sa用户属性”对话框24/204-216(3)使用T-SQL语句创建和管理登录账号1)创建登录账号。用户可以通过执行系统存储过程sp_addlogin来创建用户，语法格式如下：SP_ADDLOGIN 登录的名称 , 登录密码 , 登录的默认数据库 , 默认语言 , 安全标识号 , 加密 25/204-216 说明： 登录密码：默认设置为 NULL，执行后，password 被加密并存储在系统表中。 登录的默认数据库：登录后所连接到的数据库，默认设置为 master。 默认语

14、言：用户登录到 SQL Server 时系统指派的默认语言，默认设置为 NULL。如果没有指定 language，那么 language 被设置为服务器当前的默认语言。26/204-216 安全标识号 (SID)：sid 的数据类型为 varbinary(16)，默认设置为 NULL。如果 sid 为 NULL，则系统为新登录生成 SID。 加密：指定当密码存储在系统表中时，密码是否要加密。encryption_option 的数据类型为 varchar(20)。27/204-216【例4-2】使用T-SQL语句创建创建一个用户名为“S_CeShi”，密码为“123456”的SQL Serve

15、r用户。EXEC SP_ADDLOGIN s_CeShi,1234562)删除登录账号。删除数据库登录账号使用系统存储过程sp_droplogin，语法格式如下：EXEC SP_DROPLOGIN 登录名说明：登录名：要删除的登录，没有默认值，必须已存在于 SQL Server 中。28/204-2164.1.4 SQL Server的数据库用户(1)数据库用户 在实现安全登录后，如果系统管理员没有授予该用户访问某数据库的权限，则该用户仍然不能访问此数据库。数据库的访问权限是通过映射数据库用户与登录账号之间的关系来实现的。 当登录账号通过了Windows身份验证或SQL Server混合身份验

16、证后，必须设置数据库用户才可以对数据及其对象进行操作。所以，一个29/204-216登录账号在不同的数据库中可以映射成不同的数据库用户名称，从而获得不同的操作权限，如登录账号sa自动与每一个数据库用户dbo相关联。 数据库用户用来指定哪些用户可以访问哪些数据库，它是数据库级的安全实体，就像登录账号是服务器级的安全实体一样。 SQL Server 2008的数据库级别上有两个特殊的数据库用户：dbo用户和guest用户。30/204-216dbo用户是数据库拥有者在安装SQL Server 2008时被设置在model系统数据库中的，它对应于创建该数据库的登录账号。 dbo用户存在于每一个数据库

17、中，且不能被删除。所用系统数据库的dbo用户都对应于sa登录账号，它是数据库的最高权力拥有者，可以在数据库范围内执行所有操作。 guest用户是一个能加入到数据库，并允许具有有效SQL Server登录的任何人访问数据库的一个特殊用户，以guest用户访问数据库的用户被认为是guest用户的身份并31/204-216且继承guest用户的所有权限和许可。但是与 SQL Server 2000中不同，在SQL Server 2008中guest用户已经默认存在于每个数据库中，但默认情况下，会在新数据库中禁用guest。一旦启用guest用户，所有可以登录SQL Server的任何人，都可以用gu

18、est身份来访问数据库，并拥有guest用户的所有权限和许可。同样，guest用户在数据库中也不能被删除。32/204-216(2)使用对象资源管理器创建和管理数据库用户1)创建数据库用户。【例4-3】使用对象资源管理器为登录账号s_CeShi创建名为d_CeShi数据库用户。 启动 SSMS窗口，在“对象资源管理器”中依次展开“数据库”“student”数据库“安全性”节点。 右击“用户”，在弹出的菜单中选择“新建用户”选项，打开“新建数据库用户”对话框，如图4-4所示。33/204-216图4-4 “新建数据库用户”对话框34/204-216 在对话框的用户名栏输入“d_CeShi”，点击

19、登录名后面的按钮打开“选择登录名”对话框，如图4-5所示。图4-5 “选择登录名”对话框35/204-216 在“选择登录名”对话框中点击“浏览”按钮，打开“查找对象”对话框，在对话框的“匹配的对象”中找到“s_CeShi”，并将其前面的复选框打上勾，如图4-6所示。图4-6 “查找对象”对话框36/204-216点击3次“确定”按钮后，登录名s_CeShi对应的数据库用户d_CeShi就创建好了。2)查看数据库用户。展开某一个数据库的“安全性”“用户”节点，会看到所有该数据库的用户，可以通过鼠标右键单击某个用户，在弹出的菜单中选择“属性”选项打开属性对话框查看并设置相应的属性。37/204-

20、216(3)使用T-SQL语句创建和管理数据库用户1)创建数据库用户。用户也可以通过执行系统存储过程sp_grantdbaccess来创建数据库用户，语法格式如下：SP_GRANTDBACCESS 登录名 , 数据库用户名 OUTPUT 说明：登录名：当前数据库中新安全账户的登录名称。数据库用户名：数据库中账户的名称，如果该参数缺省，新建的数据库用户名默认和登录名一样。38/204-216【例4-4】使用T-SQL语句为登录账号s_CeShi创建名为d_CeShi数据库用户。USE studentGOEXEC SP_GRANTDBACCESS s_CeShi,d_CeShiGO2)删除数据库用

21、户。删除数据库用户使用系统存储过程sp_revokedbaccess，语法格式如下：EXEC SP_REVOKEDBACCESS 用户名39/204-216说明：用户名：要删除的数据库用户名称，无默认值，可以是服务器登录、Windows 登录或 Windows 组的名称，并且必须存在于当前数据库中。40/204-2164.1.5 SQL Server 2008的权限管理(1)SQL Server 2008的权限 SQL Server 2008对象的使用权限为访问数据库设置的最后一道安全设施。权限确定了数据库用户可以在SQL Server 2008数据库中的操作。用户在执行更改数据库定义或访问数

22、据库的任何操作之前，都必须有相应的权限。 在SQL Server 2008中分为3种类型的权限，即对象权限、语句权限和预定义权限。41/204-216 1)对象权限。对象权限是指对特定的数据库对象，如表、视图、字段、存储过程等的操作权限，它决定了数据库用户可以对表、视图等数据库对象执行哪些操作。SQL Server 2008对象权限见表4-1。权限名称 描述 SELECT控制是否能够对表或视图执行select查询语句 INSERT控制是否能够对表或视图执行insert插入语句 UPDATE控制是否能够对表或视图执行update更新语句 DELETE控制是否能够对表或视图执行delete删除语句

23、 EXECUTE控制是否能够执行存储过程 表4-1 SQL Server 2008的操作权限说明42/204-2162)语句权限。语句权限用于控制创建数据库或数据库中的对象所涉及到的权限。如某用户想在数据库中创建数据表，则要求该用户应该先拥有create table语句权限。SQL Server 2008语句权限见表4-2。权限名称 描述 CREATE DATABASE 控制登录用户是否具有创建数据库的权限 CREATE DEFAULT 控制登录用户是否具有创建表的列默认值的权限 CREATE FUNCTION 控制登录用户是否具有创建用户自定义函数的权限 表4-2 SQL Server 20

24、08的语句权限说明43/204-216权限名称 描述 CREATE PROCEDURE 控制登录用户是否具有创建存储过程的权限 CREATE RULE 控制登录用户是否具有创建表的列规则的权限 CREATE TABLE 控制登录用户是否具有创建数据表的权限 CREATE VIEW 控制登录用户是否具有创建视图的权限 BACKUP DATABASE 控制登录用户是否具有备份数据库的权限 BACKUP LOG 控制登录用户是否具有备份日志的权限 表4-2 SQL Server 2008的语句权限说明（续）44/204-216(2)使用对象资源管理器授予权限 3)预定义权限。预定义权限又称为隐含权限

25、，是指系统预定义的固定服务器角色、固定数据库角色、数据库拥有者、数据库对象拥有者所拥有的权限，这些权限不能明确地被赋予或撤销。 在SQL Server 2008中，用户和角色的权限以记录的形式存储在各个sysprotects系统表中，权限操作分为3种情况：授予某项权限、拒绝某项权限、撤销某项权限。下面举例说明如何给数据库用户授予权限，拒绝和撤销权限将在下一任务中详细介绍。45/204-216【例4-5】给数据库用户d_CeShi赋予student表的查询和修改权限。1)在操作系统的“开始”菜单中打开SSMS应用程序，选择使用Windows身份验证登录。2)展开数据库“student”“安全性”

26、“用户”，找到用户“d_CeShi”，右击之，选择“属性”，打开“数据库用户”对话框，选择“安全对象”选项页，如图4-7所示。46/204-216图4-7 “数据库用户”对话框47/204-216 3)点击“搜索”按钮，打开“添加对象”对话框，如图4-8所示。在此对话框中选择“特定对象”单选按钮，点击“确定”按钮，打开“选择对象”对话框，如图4-11所示。图4-8 “添加对象”对话框48/204-216图4-11 “选择对象”对话框4)在“选择对象”对话框中，点击“对象类型”按钮，打开“选择对象类型”对话框，勾选“表”复选框，单击“确定”按钮，如图4-9所示。49/204-216图4-9 “选

27、择对象类型”对话框 5)在“选择对象”对话框中点击“浏览”按钮，在打开的“查找对象”对话框中，勾选50/204-216图4-10 “查找对象”对话框“student”表，如图4-10所示。单击“确定”按钮，返回“选择对象”对话框。51/204-2166)点击“确定”按钮，返回“数据库用户”对话框，选择“安全对象”区域的“student”对象，再从下方的权限列表中勾选 “更新”(或Update)和“选择”(或Select)右边的“授予”复选框，如图4-12所示。52/204-216图4-12 “数据库用户”对话框53/204-216(3)使用T-SQL语句授予权限SQL Server中使用gra

28、nt语句进行授权，语法格式如下：GRANT 权限 , . ON 对象 , . TO 用户名或者GRANT权限, . ON OBJECT :对象, . TO 用户名说明：1)授予的权限，可以是一个，也可以是多个，多个之间用逗号隔开。可能的权限如表4-1和表54/204-2164-2（本文稿P43P45）所示，也有可能是ALL，表示赋予所有权限。2)赋予权限的对象可以是一个，也可以是多个，可能的对象有： table 表、view 、视图、sequence 序列 、index 索引。55/204-216【例4-6】授予用户“zhang”对数据库中student表的select查询、update修改、

29、insert插入权限。GRANT SELECT,UPDATE,INSERT ON student TO zhang或者GRANT SELECT,UPDATE,INSERT ON OBJECT:student TO zhang56/204-216任务实施 王老师是“09计算机应用技术1班”的班主任，他想查看到本班学生的基本情况和成绩信息，并能对本班学生基本情况进行修改，SQL Server数据库管理员将为他设置这些操作的权限。57/204-216 (1)使用Windows身份验证登录，创建数据库登录账号s_TeacherWang，在查询编辑器中输入如下T-SQL语句： EXEC SP_ADDLO

30、GIN s_TeacherWang, 123456 为了测试刚创建的新用户，可以再打开一个SSMS应用程序，使用s_TeacherWang登录。正常情况下，能够顺利登录，但登录成功后，发现该用户除了能够访问部分系统数据库外，其它数据库都无法访问。为了让用户58/204-216s_TeacherWang能够访问student数据库，还需要为该登录账号创建对应的数据库用户。(2)创建对应的数据库用户d_TeacherWang， 代码如下：USE studentGOEXEC SP_GRANTDBACCESS s_TeacherWang,d_TeacherWangGO59/204-216 (3)创建两

31、个视图，代码如下：CREATE VIEW view_student_1ASSELECT student.*FROM student,classWHERE student.class_id = class.class_idAND class.class_name = 09计算机应用技术1班CREATE VIEW view_score_1ASSELECT score.*,class.class_nameFROM student,class,score60/204-216WHERE student.class_id = class.class_idAND score.s_id = student.s

32、_idAND class.class_name = 09计算机应用技术1班(4)分别为两个视图授予权限，代码如下：GRANT UPDATE ON view_student_1 TO d_TeacherWangGRANT SELECT ON view_score_1 TO d_TeacherWang61/204-2164.2 取消数据库用户权限4.2.1 拒绝权限取消数据库用户的权限有两种方式：拒绝用户权限和撤销用户权限。 在授予了用户权限后，数据库管理员可以根据实际情况，在不撤销用户访问权限的情况下，拒绝用户访问数据库对象，并阻止用户或角色继承权限，该语句优先于其它授予的权利。62/204-2

33、16 (1)使用对象资源管理器拒绝权限。【例4-7】拒绝数据库用户d_CeShi对student表的修改权限。1)在操作系统的“开始”菜单中打开SSMS应用程序，选择使用Windows身份验证登录。2)展开数据库“student”“安全性” “用户”，找到用户“d_CeShi”，点击其右键，选择“属性”，打开“数据库用户”对话框，选择“安全对象”选项页，在“选择”行勾选“拒绝”复选框，点击“确定”按钮。如图4-13所示。63/204-216图4-13 “权限设置”对话框 64/204-216 3)此时，使用“d_CeShi”登录账号登录，执行修改语句就会失败，如图4-14所示。图4-14 “执

34、行修改语句”后果图65/204-216(2)使用T-SQL语句拒绝权限。拒绝对象权限的语法格式如下：DENY 权限, . ON对象, . TO用户名拒绝权限的语句和授权的语法非常类似，只是第一个关键字不同。【例4-8】拒绝用户“zhang”对student表的update修改、insert插入权限。DENY UPDATE,INSERT ON student TO zhang或者DENY UPDATE,INSERT ON OBJECT:student TO zhang66/204-2164.2.2 撤销权限 通过撤销某种权限，停止以前授予或拒绝的权限，但不会显式的阻止用户或角色执行操作，用户或角

35、色仍然能继承其它角色的授予权限。使用撤销类似于拒绝，但是撤销权限是删除已授予的权限，并不妨碍用户或角色从更高级别继承已授予的权限。(1)使用对象资源管理器撤销权限。【例4-9】撤销数据库用户d_CeShi对student表的查询权限。67/204-216 1)在操作系统的“开始”菜单中打开SSMS应用程序，选择使用Windows身份验证登录。2)展开数据库“student”“安全性” “用户”，找到用户“d_CeShi”，点击其右键，选择“属性”，打开“数据库用户”对话框，选择“安全对象”选项页，如图4-15所示。3)去除“查询”行的“授予”复选框，点击“确定”按钮。此时，使用“d_CeShi

36、”登录账号登录，执行select查询语句就会失败。68/204-216图4-15 “权限设置”对话框69/204-216 (2)使用T-SQL语句撤销权限。撤销对象权限的基本语法如下：REVOKE权限, . ON 对象, . TO用户名【例4-10】撤销用户“zhang”对student表的update修改、insert插入权限。REVOKE UPDATE,INSERT ON student TO zhang或者REVOKE UPDATE,INSERT ON OBJECT:student TO zhang70/204-2164.2.3 拒绝权限与撤销权限区别 REVOKE(撤销)和DENY(拒

37、绝)命令都可以取消某个权限，但它们是有本质的区别的。REVOKE取消以前授予或拒绝了的权限，而DENY拒绝授予主体权限，防止主体通过其组或角色成员身份继承权限。 如果用户激活一个应用程序角色，DENY 对用户使用该应用程序角色访问的任何对象没有任何作用。虽然用户可能被拒绝访问当前数据库内的特定对象，但如果应用程序角色能够访问该对象，则当应用程序角色激活时，用户71/204-216 仍可以访问该对象。 使用REVOKE 语句可从用户账户中删除拒绝的权限。安全账户不能访问删除的权限，除非将该权限授予了用户所在的组或角色。使用 GRANT 语句可删除拒绝的权限并将权限显式应用于安全账户。 例如，数据

38、库用户账户 userA 拥有 employees 表的 SELECT 权限，同时又属于 db_datareader 数据库角色。 如果使用 REVOKE SELECT ON employees FROM userA语句，仅仅是取消72/204-216了显式授予用户账户的 SELECT 权限；userA 同样可以通过 db_datareader 角色获得读取 employees 表的权限； 而如果使用 DENY SELECT ON employees TO userA 语句，则可以彻底禁止 userA 读取 employees 表，因为在评估权限时，DENY 优先于通过其他任何方式获取的权限。7

39、3/204-216 可以使用撤销命令，也可以使用拒绝命令来取消某学生对成绩表的修改权限，代码如下： REVOKE UPDATE ON score TO 0904101101或者 DENY UPDATE ON score TO 0904101101任务实施74/204-2164.3 使用角色管理用户 任务描述 江扬职业技术学院新学期开始，来了很多新生，每位新生需要具有查询成绩的权利，SQL Server数据库管理员需要快速给所有新生设置此权限。任务分析 首先要给所有新生添加服务器登录账号，因为新生人数较多，逐个添加会相当麻烦，而且容易出错，可以考虑使用游标查询出所75/204-216有新生的学号

40、，再遍历游标通过执行相应的系统存储过程创建对应的账号。创建好登录账号后，同样继续使用游标和系统存储过程为登录账号创建数据库用户。为了方便管理这些用户，还需要创建一个角色，然后将所有新生用户添加到角色中，统一给角色授予成绩表(score)的查询权限。为了方便授权，每个学生都有整个成绩表的数据查看权限，不需考虑个别情况。假如“09计算机应用技术1班”为新生班级，完成该班级所有学生的权限分配任务的具体步骤如下：76/204-216(1)使用游标为所有新生创建服务器登录账号。(2)为新生账号创建数据库用户。(3)创建一个角色，将所有新生数据库用户添加到该角色中。(4)给角色授权。77/204-2164

41、.3.1 SQL Server角色 SQL Server的角色是一种权限许可机制，如果数据库有很多用户，且这些用户的权限基本相同，那么单独授权给某个用户的话，过程重复，而且不便于集中管理。当权限发生变化时，管理员需要逐个修改每一个用户的权限，非常麻烦。 自SQL Server 7 版本开始引入了新的概念角色，从而替代以前版本中组的概念。和组一样，SQL Server 管理者可以将某些用户设置为某一角色，这样只对角色进行权限设78/204-216 置便可实现对该角色的所有用户权限的设置，大大减少了管理员的工作量。 SQL Server管理员将操作数据库的权限赋予某个角色，再将数据库用户或登录账号

42、设置为该角色，使得该用户或登录账号拥有相应的权限。当若干个用户都属于同一个角色时，它们就都继承了该角色拥有的权限。若角色的权限发生变化，这些相关用户的权限也会发生相应的变化。因此，SQL Server中通过角色可将用户分为不同的类型，对相同类型的用户进行统一管理，赋予相同的操作权限，从而方便管理人员集中管理用户的权限。79/204-216(1)服务器角色 在SQL Server 中主要有两种角色类型：服务器角色与数据库角色。 服务器角色是指根据SQL Server 的管理任务，以及这些任务相对的重要性等级来把具有SQL Server 管理职能的用户划分成不同的用户组，每一组所具有管理SQL S

43、erver的权限已被预定义。服务器角色适用在服务器范围内，并且其权限不能被修改。例如，具有sysadmin 角色的用户在SQL Server 中可以执行任何管理性的工作，任何企图对其权限进行修改的操作都将会失败。80/204-216 SQL Server 共有8 种预定义的服务器角色，各种角色的具体含义见表4-3。 服务器角色 权限 sysadmin可以在服务器中执行任何活动serveradmin可以更改服务器范围的配置选项和关闭服务器setupadmin可以添加和删除连接服务器，并且也可以执行某些系统存储过程 securitadmin管理登录名及其属性 表4-3 预定义的服务器角色81/20

44、4-216表4-3 预定义的服务器角色（续一）服务器角色 权限 processadmin 管理SQL Server实例中运行的进程dbcreator 可以创建、更改、删除和还原任何数据库diskadmin 管理磁盘文件bulkadmin 可以运行BULK INSERT语句82/204-216 数据库角色是为某一用户或一组用户授予不同级别的管理或访问数据以及数据库对象的权限，这些权限是数据库专有的，并且可以使一个用户具有属于同一数据库的多个角色。SQL Server提供了两种类型的数据库角色：预定义数据库角色和用户自定义数据库角色。1)预定义数据库角色 预定义数据库角色是指这些角色所有具有的管理

45、、访问数据库权限已被SQL Server 定义、并且SQL Server 管理者不能对其所具有的权限进(2)数据库角色83/204-216行任何修改。SQL Server 中的每一个数据库中都有一组预定义的数据库角色，在数据库中使用预定义的数据库角色可以将不同级别的数据库管理工作分给不同的角色，从而很容易实现工作权限的传递。例如，如果准备让某一用户临时或长期具有创建和删除数据库对象(表、视图、存储过程)的权限，那么只要把他设置为db_ddladmin 数据库角色即可。 SQL Server中预定义数据库角色的具体含义见表4-4。84/204-216表4-4 预定义的数据库角色 预定义数据库角色

46、 权限 db_accessadmin可以为 Windows 登录账户、Windows 组和 SQL Server 登录账户添加或删除访问权限 db_backupoperator可以备份该数据库db_datareader可以读取所有用户表中的所有数据db_datawriter可以在所有用户表中添加、删除或更改数据db_ddladmin可以在数据库中运行任何数据定义语言 (DDL)命令 85/204-216表4-4 预定义的数据库角色 （续）预定义数据库角色 权限 db_denydatareader不能读取数据库内用户表中的任何数据 db_denydatawriter不能添加、修改或删除数据库内用

47、户表中的任何数据 db_owner可以执行数据库的所有配置和维护活动 db_securityadmin可以修改角色成员身份和管理权限public一个特殊的数据库角色，通常将一些公共的权限赋给public角色 86/204-216 需要说明的是，数据库中的每个用户都属于public数据库角色，而且这个数据库角色不能被删除。当尚未对某个用户授予安全对象的权限时，该用户将继承授予public角色的权限，由于所有数据库用户都自动成为public数据库角色的成员，因此给该数据库角色指派权限是需要格外谨慎。2)用户自定义数据库角色。当我们打算为某些数据库用户设置相同的权限，但是这些权限不等同于预定义的数据

48、库角色所具有的权限时，我们就可以定义新的数据库角色来满足这87/204-216一要求，从而使这些用户能够在数据库中实现某一特定功能。用户自定义的数据库角色具有以下几个优点： SQL Server 数据库角色可以包含NT 用户组或用户。 在同一数据库中用户可以具有多个不同的自定义角色，这种角色的组合是自由的，而不仅仅是public 与其它一种角色的结合。 角色可以进行嵌套，从而在数据库实现不同级别的安全性。88/204-216 用户定义的数据库角色有两种类型：标准角色和应用角色。 标准角色类似于SQL Server 7 版本以前的用户组，它通过对用户权限等级的认定而将用户划分为不同的用户组，使用

49、户总是相对于一个或多个角色，从而实现管理的安全性。所有预定义的数据库角色或SQL Server 管理者自定义的某一角色(该角色具有管理数据库对象或数据库的某些权限)都是标准角色。 应用角色是一种比较特殊的角色类型。当我们打算让某些用户只能通过特定的应用程序89/204-216间接地存取数据库中的数据(比如通过SQL Server Query Analyzer 或Microsoft Excel)而不是直接地存取数据库数据时，我们就应该考虑使用应用角色。当某一用户使用了应用角色时，他便放弃了已被赋予的所有数据库专有权限，他所拥有只是应用角色被设置的权限。通过应用角色，总能实现这样的目标；即以可控制

50、方式来限定用户的语句或对象权限。90/204-216(3)添加用户数据库角色 使用系统存储过程sp_addrole来添加用户数据库角色，语法格式如下：SP_ADDROLE 角色名，所有者说明：1)角色名。新角色的名称，没有默认值，并且不能已经存在于当前数据库中。2)所有者。新角色的所有者，默认值为 dbo。91/204-216(4)为角色添加成员 使用系统存储过程sp_addrolemember将数据库用户添加到数据库角色中去，使之成为角色的成员之一，语法格式如下：SP_ADDROLEMEMBER 角色名 ,用户名1)角色名。当前数据库中 SQL Server 角色的名称，没有默认值。2)用户

51、名。添加到角色的安全帐户，没有默认值，可以是所有有效的 SQL Server 用户、SQL Server 角色或是所有已授权访问当前数据库的 Microsoft Windows NT 用户或组。92/204-216(5)删除用户数据库角色使用系统存储过程sp_droprole删除数据库角色，语法格式如下：SP_DROPROLE 角色名角色名：要从当前数据库中删除的数据库角色的名称，无默认值，必须已经存在于当前数据库中。角色必须不能包含用户才能被删除。93/204-2164.3.2 游标(1)游标的概念 游标(Cursor)是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集

52、中一次以一行或者多行的方式前进或后退来浏览数据的能力。就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。我们也可以把游标当作一个指针，它可以指定结果集中的任何位置，然后允许用户对指定位置的数据进行处理。94/204-216(2)游标的组成 游标包含两个部分：游标结果集：定义该游标的SELECT语句返回的行的集合。游标位置：指向这个结果集某一行的当前指针。95/204-216从游标定义可以得到游标的如下优点：1)允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。2)提供对基于游标位置的表中的行进行删除和更

53、新的能力。3)游标实际上作为面向集合的数据库管理系统(RDBMS)和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。 使用游标要遵循“声明游标、打开游标、读取数据、关闭游标、删除游标”的顺序。96/204-216(3)游标的生命周期 游标的生命周期包含五个阶段： 1、声明游标：DECLARE 2、打开游标：OPEN 3、提取数据：FETCH 4、关闭游标：CLOSE 5、释放游标：DEALLOCATE97/204-216(4)声明游标其语法格式如下：DECLARE游标的名字 INSENSITIVE SCROLL CURSORFOR SELECT语句FOR READ ONLY |

54、UPDATE OF 列名称 ,.n说明：1)INSENSITIVE。表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一98/204-216 个临时表内(建立在tempdb 数据库下)。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。2)SCROLL。表明所有的提取操作(如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE)都可用。如果不使用该保留字，那么只能进行NEXT 提

55、取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关99/204-216闭再重开游标。 3)SELECT语句。是定义结果集的SELECT 语句。应该注意的是，在游标中不能使用COMPUTE、COMPUTE BY、 FOR BROWSE、 INTO 语句。4)READ ONLY。表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。5)UPDATE OF 列名称 ,.n。定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。100/204-216(5)打开游标 声明游标后，还必须先打开游标，才能从游标中读取数据。打

56、开游标使用open语句，语法格式为：OPEN 游标名说明：游标名：已创建的、未打开的游标，打开的游标不能再次打开，游标成功打开后，游标指针指向结果集的第一行之前。101/204-216(6)提取游标 游标打开后，可以使用fetch语句从中读取数据，语法格式为：FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTEn| RELATIVEnFROM 游标名说明：1)游标名。已经打开的、要从中提取数据的游标名称。2)NEXT。表示返回结果集中当前行的下一行记102/204-216行记录，如果第一次读取则返回第一行。默认的提取选项为NEXT。3)PRIOR。表示返回结

57、果集中当前行的前一行记录，如果第一次提取则没有行返回，并且把游标置于第一行之前。4)FIRST。表示返回结果集中的第一行，并且将其作为当前行。5)LAST。表示返回结果集中的最后一行，并且将其作为当前行。103/204-2166)ABSOLUTEn。如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。7)RELATIVEn。如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。104/204-216(7)FETCH_STATUS全局变量

58、 fetch语句的执行状态保持在全局变量FETCH_STATUS中，它的值为0时表示最近一次fetch语句执行成功，值为-1时表示最近一次fetch语句执行失败，值为-2时表示读取的行已不存在。 一般在循环遍历游标时，使用FETCH_STATUS全局变量作为循环的退出条件。105/204-216(8)关闭和删除游标 使用后的游标要及时关闭，关闭游标使用close语句，方式如下： CLOSE 游标 删除游标使用deallocate语句，方法如下： DEALLOCATE 游标提示：1)游标不关闭也可以直接删除。2)删除游标后，游标使用的任何资源也随之释放。106/204-2163)关闭游标后可以再

59、次使用open语句打开，删除游标后则不能再打开。【例4-11】使用游标查询各门课程的平均分，要求显示课程名称和平均分。 可以考虑使用游标保存各门课程信息，再循环遍历游标计算各门课程的平均分，代码如下： -定义变量 DECLARE cid char(6) DECLARE cName char(20)107/204-216-定义游标DECLARE cur_course CURSORFORSELECT c_id,c_name FROM courseFOR READ ONLY-打开游标OPEN cur_course-循环读取游标WHILE FETCH_STATUS=0108/204-216BEGIN

60、FETCH NEXT FROM cur_course INTO cid,cName SELECT cName,AVG(grade)FROM score WHERE c_id=cidEND-关闭游标CLOSE cur_course-删除游标DEALLOCATE cur_course109/204-216使用角色管理用户任务实施 为新生班级“09计算机应用技术1班”所有学生分配权限，步骤如下：(1)使用游标为所有新生创建服务器登录账号。(2)为新生账号创建数据库用户。(3)创建一个角色，将所有新生数据库用户添加到该角色中。(4)给角色授权。即有：110/204-216(1)在 “开始”菜单中打开S