《数据库原理与应用》课件第9章 2

第9章数据库的管理9.1SQLServer2005安全机制9.2登录管理9.3用户管理9.4角色管理9.5权限管理9.6数据库管理【小结】【习题】【课程要求】

1．理解SQLServer2005客户机的安全机制。

2．能进行登录管理。

3．能进行用户管理。

4．能进行角色管理。

5．能对数据库进行管理。

6．能对数据表进行管理。

7．能对数据库进行备份、恢复、分离与附加及数据导入/导出等管理。

【本章涉及的需求分析内容】

1．SQLServer2005客户机的安全机制

主要包括5个方面：

(1) SQLServer2005客户机的安全机制；

(2)网络传输的安全机制；

(3) SQLServer2005服务器的安全机制；

(4)数据库的安全机制；

(5)数据对象的安全机制。

2．登录管理

分别设置两种身份验证模式：

(1) Windows身份验证模式；

(2) SQLServer和Windows混合身份验证模式。

3．用户管理

(1)修改数据库用户的属性；

(2)创建数据库用户：学生用户、任课教师用户、管理员用户。

4．角色管理

(1)服务器角色管理；

(2)数据库角色管理。

5．权限管理

(1)学生用户通过权限认证，可查询个人信息及成绩管理；

(2)任课教师用户通过权限认证，可进行成绩管理；

(3)管理员用户通过权限认证，可进行学生信息管理、专业管理、课程管理、成绩管理、权限管理。

6．数据库管理

(1)数据库备份；

(2)数据库恢复；

(3)数据库分离与附加；

(4)数据导入/导出。

【课程内容】

数据库的管理包括安全性管理和日常管理操作。安全性管理包括服务器安全性管理、数据库安全性管理和数据对象的安全性管理；日常管理包括数据库备份、数据库恢复、数据库分离与附加及数据导入/导出。

9.1SQLServer2005安全机制

从SQLServer2005网络的结构来看，一个SQLServer2005网络的安全机制主要包括5个方面，如图9-1所示。图9-1SQLServer2005的安全机制由图9-1可能看出，SQLServer2005的安全机制包括以下5个方面：

(1) SQLServer2005客户机的安全机制；

(2)网络传输的安全机制；

(3) SQLServer2005服务器的安全机制；

(4)数据库的安全机制；

(5)数据对象的安全机制。9.1.1SQLServer2005客户机的安全机制

用户首先必须能够登录到SQLServer2005客户机，然后才能使用SQLServer2005应用系统或者客户机管理工具。所以客户机的安全机制是SQLServer2005网络安全机制的第一道防线。

对于Windows系统上的SQLServer2005客户机，其安全机制主要涉及的是操作系统的安全，主要的就是Windows账号的安全。如果用户连客户机都登录不了，那么在第一道防线就可以阻止非法用户的使用。

总之，SQLServer2005客户机的安全机制决定了用户能不能使用客户机。9.1.2网络传输的安全机制

网络传输的安全涉及网络的加密和解密技术。SQLServer2005网络支持采用SSL(SecureSocketLayer，安全套接字层)的TCP/IP传输加密的数据。

一般的SQLServer2005网络的数据是以明文传送的，所以黑客利用一些网络数据包截获工具，截获到SQLServer2005网络的数据包后，通过分析，可以得到一些安全信息。加密的SQLServer2005网络速度较慢，所以一般对安全性要求不是特别严格的网络都采用明文方式。

总之，网络传输的安全机制决定了网络传输的数据是明文还是密文。(网络传输的安全机制不属于本书介绍的内容。)9.1.3SQLServer2005服务器的安全机制

用户在使用SQLServer2005客户机登录SQLServer2005服务器时，必须使用一个账号和密码。这里的账号称为“登录名”。

这里的账号和密码不是客户机上Windows操作系统的账号和密码，而是SQLServer2005服务器的账号和密码。SQLServer2005服务器会按照不同的身份验证方式来判断这个账号和密码的正确性。

SQLServer2005服务器提供了两种身份验证方式：Windows身份验证模式及SQLServer和Windows混合身份验证模式。

总之，SQLServer2005服务器的安全性机制就是身份验证机制，它决定了SQLServer2005客户机能否登录到服务器上。9.1.4数据库的安全机制

任何一个能够登录到SQLServer2005服务器的账号和密码都对应有自己的一个默认工作数据库。

SQLServer2005对数据库级的权限管理采用的是“数据库用户”的概念。数据库的安全机制就要求对不同的账号设计不同的默认工作数据库。

总之，数据库的安全机制决定了客户机能够使用哪个数据库。9.1.5数据对象的安全机制

客户机通过前面四道防线后才能够访问数据库中的数据对象。对数据对象能够做什么样的访问称为访问权限。常用的访问权限包括：SELECT、UPDATE、INSERT和DELETE

4种。

SELECT权限：对数据对象数据的查询权限。

UPDATE权限：对数据对象数据的更新权限。

INSERT权限：对数据对象数据的录入权限。

DELETE权限：对数据对象数据的删除权限。

总之，数据对象的安全机制决定了客户机如何使用数据对象。

9.2登录管理

如前所述，SQLServer2005服务器提供了两种身份验证模式：Windows身份验证模式及SQLServer和Windows混合身份验证模式。9.2.1Windows身份验证模式

在Windows身份验证模式下，SQLServer检测当前使用Windows的用户账户，并在系统表中查找该用户，以确定该用户账户是否有权限登录。在这种方式下，用户不必提交登录名和密码让SQLServer验证。

Windows身份验证模式主要有以下优点：

(1)数据库管理员的工作可以集中在管理数据库上面，而不是管理用户账户。对用户的管理可以交给Windows去完成。

(2) Windows有着更强的用户账户管理工具。可以设置账户锁定、密码期限等。如果不是通过定制来扩展SQLServer，那么SQLServer是不具备这些功能的。

(3) Windows的组策略支持多个用户同时被授权访问SQLServer。9.2.2SQLServer和Windows混合身份验证模式

混合身份验证模式允许以SQLServer验证模式或者Windows验证模式来进行验证。使用哪个模式取决于在最初的通信时使用的网络库。如果一个用户使用的是TCP/IPSockets进行登录验证，则将使用SQLServer验证模式；如果用户使用命名管道，则登录时将使用Windows验证模式。这种方式能更好地适应用户的各种环境。但是对于Windows9X系列的操作系统，只能使用SQLServer验证模式。

SQLServer验证模式处理登录的过程为：用户在输入登录名和密码后，SQLServer在系统注册表中检测输入的登录名和密码。如果输入的登录名存在，而且密码也正确，就可以登录到SQLServer上。

混合身份验证模式主要有以下优点：

(1)创建了Windows之上的另外一个安全层次。

(2)支持更大范围的用户，例如非Windows客户或Novell网络等。

(3)一个应用程序可以使用单独的SQLServer登录名和口令。9.2.3设置验证模式

在第一次安装SQLServer，或者使用SQLServer连接其他服务器的时候，需要指定验证模式。对于已经指定验证模式的SQLServer服务器，在SQLServer中还可以对验证模式进行修改。操作步骤如下：

(1)打开SQLServerManagementStudio窗口，在【对象资源管理器】窗口中选择服务器，然后右击鼠标，在弹出的快捷菜单上选择【属性】命令，再在打开的【服务器属性】对话框中选择【安全性】选项，打开【安全性】选项卡，如图9-2所示。图9-2【服务器属性】对话框

(2)在【服务器身份验证】栏中设置验证模式后，单击【确定】按钮，打开提示对话框，提示重新启动SQLServer，才能使新的设置生效，单击【确定】按钮即可。

(3)单击【确定】按钮，然后重新启动SQLServer，以新的验证模式登录服务器。9.2.4创建登录账号(登录名)

在SQLServer2005中，创建登录账号(登录名)的步骤如下：

(1)打开SSMS窗口，在【对象资源管理器】窗口中打开服务器，然后在【安全性】下面的【登录名】上右击鼠标，在打开的快捷菜单中选择【新建登录名】命令，如图9-3所示。图9-3【新建登录名】命令其中两个默认登录账号的含义如下：

① BUILTIN\Administrators：凡属于Windows中Administrators组的账户都允许登录SQLServer。

② sa：超级管理员账号，允许SQLServer的系统管理员登录，此SQLServer的管理员不一定是Windows管理员。

(2)打开【登录名-新建】窗口的【常规】选项卡，在【登录名】栏输入登录名“student”，在【SQLServer身份验证】栏输入密码和确认密码，在【默认数据库】栏选择默认数据库“StudentScoreManager”，如图9-4所示。图9-4【登录名-新建】对话框

(3)在【选择页】列表中选择【服务器角色】选项卡，如图9-5所示。在此选项卡中，可以设置登录账号所属的服务器角色。关于服务器角色的内容将在9.4节详细介绍。图9-5【服务器角色】选项卡

(4)在【选择页】列表中选择【用户映射】选项，打开【用户映射】选项卡。单击选择StudentScoreManager数据库前面的复选框。此时，用户列出现student用户，表示该登录账号允许student用户访问StudentScoreManager数据库，如图9-6所示。

(5)设置完成后，单击【确定】按钮即可创建一个名称为student的登录账户，同时在StudentScoreManager数据库中自动创建了一个用户student。

(6)如果在图9-5中选择了【Windows身份验证】单选按钮，则【登录名】文本框后面的【搜索】按钮被激活，单击之可打开【选择用户或组】对话框，如图9-7所示。图9-6【用户映射】选项卡图9-7【选择用户或组】对话框创建完毕后，如果要修改登录账号的属性，则可打开SSMS窗口，在【对象资源管理器】窗口中找到student登录名，并在登录名上单击右键，然后在弹出的快捷菜单中选择【属性】命令，如图9-8所示，即可打开登录账号的属性对话框。它与图9-4【登录名-新建】对话框相似，可以在其中修改student登录名的属性。图9-8登录名快捷菜单如果要删除一个登录账号，则可打开SSMS窗口，在【对象资源管理器】窗口中找到相应登录名，并在登录名上单击右键，然后在弹出的快捷菜单中选择【删除】命令，如图9-8所示。

9.3用户管理

每个登录账号在一个数据库中只能有一个用户账户，但是每个登录账号可以在不同的数据库中各有一个用户账户。如果在新建登录账号的过程中指定数据库具有存取权限(有关权限的知识将在9.5节讲述)，则在图9-6【用户映射】选项卡中可以通过用户来创建一个与该登录账号同名的用户账户。例如，在9.2节介绍创建登录名时，例子中曾指定登录名“student”在StudentScoreManager数据库中映射数据库用户“student”，这样将自动在数据库StudentScoreManager中创建一个名为“student”的数据库用户。

如果在创建登录账号时没有指定对某个数据库的存取权限，则在该数据库中，创建一个新的用户账户，并关联到该登录账号，则该登录账号会自动具有对该数据库的访问权限。9.3.1修改数据库用户的属性

下面介绍如何修改“student”数据库用户的属性。

(1)启动SSMS，在【对象资源管理器】窗口中选择服务器下的【数据库】文件夹，展开数据库【StudentScoreManage】文件夹下的【安全性】文件夹，然后在【用户】文件夹下右击【student】用户，在弹出的快捷菜单中选择【属性】选项，如图9-9所示。图9-9选择修改用户的属性

(2)出现如图9-10所示的数据库用户属性对话框，默认选中【常规】选项卡中。

其中，【用户名】文本框中显示的是数据库用户名称；【登录名】文本框中显示的是映射到该数据库用户的登录名；【默认架构】文本框中显示的是该数据库用户的默认架构；【数据库角色成员身份】列表框中可选择给用户赋予什么样的数据库角色(关于角色的内容将在9.4节中介绍)。图9-10数据库用户属性的【常规】选项卡9.3.2创建数据库用户

创建数据库用户有两种方式：使用SSMS和使用SQL语句。

1．使用SSMS创建数据库用户

以创建“管理员用户”数据库用户为例，具体操作步骤如下：

(1)在图9-9中的快捷菜单上选择【新建用户】命令，出现如图9-11所示的对话框。图9-11【数据库用户-新建】对话框在该对话框中进行如下设置：

【用户名】文本框：输入用户名，例如admin。

【登录名】单选按钮：选中该单选按钮，打开【选择登录名】对话框，单击【浏览】按钮，打开【查找对象】对话框，选择BUILTIN\Administrators登录名，如图9-12所示。图9-12【查找对象】对话框

(2)单击【确定】按钮，返回到【选择登录名】对话框。此时单击【确定】按钮，即可创建一个新用户。但是此时并没有为该用户指定任何权限。要为该用户指定权限，可参见“9.5.1节用户权限”相关内容。

2．使用SQL语句CREATEUSER创建数据库用户

CREATEUSER语句的语法格式为：

CREATEUSERuser_name

[{{FOR|FROM}

LOGINlogin_name}

|WITHOUTLOGIN]

参数说明：

user_name：指定在此数据库中用于识别该用户的名称。user_name是sysname，它的长度最多是128个字符。

LOGINlogin_name：指定要创建数据库用户的SQLServer登录名。login_name必须是服务器中有效的登录名。当此SQLServer登录名进入数据库时，它将获取正在创建的数据库用户的名称和ID。

WITHOUTLOGIN：指定不应将用户映射到现有登录名。

(1)如果已忽略FORLOGIN，则新的数据库用户将被映射到同名的SQLServer登录名。

(2)如果未定义DEFAULT_SCHEMA，则数据库用户将使用dbo作为默认架构。可以将DEFAULT_SCHEMA设置为数据库中当前不存在的架构。DEFAULT_SCHEMA可在创建它所指向的架构前进行设置。在创建映射到Windows组、证书或非对称密钥的用户时,不能指定DEFAULT_SCHEMA。

重要提示：如果用户是sysadmin固定服务器角色的成员，则忽略DEFAULT_SCHEMA的值。sysadmin固定服务器角色的所有成员都有默认架构dbo。

例如，创建数据库用户。以下示例首先创建名为myadmin且具有密码的服务器登录名，然后在StudentScoreManager中创建对应的数据库用户teacher。CREATELOGINmyadmin

WITHPASSWORD='340$Uuxwp7Mcxo7Khy';

USEStudentScoreManager;

CREATEUSERteacherFORLOGINteacher;

GO9.3.3删除数据库用户

删除数据库用户也有两种方式。

1．使用SSMS删除数据库用户

以删除“student”用户为例，具体操作步骤如下：

在图9-9中的快捷菜单上选择【删除】命令，出现如图9-13所示的对话框。单击【确定】按钮，即可删除该数据库用户。图9-13【删除对象】对话框

2．使用SQL语句DROPUSER删除数据库用户

DROPUSER语句的语法格式为：

DROPUSERuser_name

其中，user_name指定在此数据库中用于识别该用户的名称。

说明：

(1)不能从数据库中删除拥有安全对象的用户。必须先删除或转移安全对象的所有权，之后才能删除拥有这些安全对象的数据库用户。

(2)不能删除guest用户，但可在除master或tempdb之外的任何数据库中执行REVOKECONNECTFROMGUEST来撤消它的CONNECT权限，从而禁用guest用户。

(3)需要对数据库具有ALTERANYUSER权限。

例如，从StudentScoreManager数据库中删除数据库用户student。

USEStudentScoreManager;

DROPUSERstudent;

GO

9.4角色管理

角色是一组用户构成的组，可分为服务器角色、数据库角色和应用程序角色。

(1)服务器角色：由用户账户组成的组，负责管理和维护SQLServer组。

(2)数据库角色：由数据库成员组成的组，此成员可以是用户或者其他的数据库角色。

(3)应用程序角色：用来控制应用程序存取数据库，本身并不包含任何成员。9.4.1服务器角色管理

服务器角色是负责管理和维护SQLServer的组，一般只会指定需要管理服务器的登录者属于的服务器角色。SQLServer2005在安装过程中定义了几个固定的服务器角色，其具体权限如表9-1所示。要给登录名赋予某些服务器角色，除了修改登录名属性外，还可以通过查看服务器角色的成员来实现。查看服务器角色的成员的步骤如下：

(1)启动SSMS，在【对象资源管理器】窗口中选择服务器→【安全性】文件夹→【服务器角色】文件夹→【sysadmin】，单击鼠标右键，在出现的快捷菜单中选择【属性】选项，如图9-14所示。图9-14选择查看服务器角色属性

(2)出现如图9-15所示的【服务器角色属性】的【常规】选项卡。在这里可以将服务器角色授予某登录名，或者删除某个登录名具有的服务器角色。图9-15【服务器角色属性】的【常规】选项卡9.4.2数据库角色管理

数据库角色是对数据库对象操作的权限的集合。SQLServer2005的数据库角色分为两种。

(1)固定的数据库角色(系统创建的)。

(2)用户自定义数据库角色。用户自定义数据库角色又可以分为标准角色和应用程序角色两种。

用户定义的标准角色通过对用户权限等级的认定而将用户划分为不同的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。在数据库创建时，系统默认创建了10个固定的数据库角色。打开StudentScoreManager数据库，然后选择【安全性】→【角色】→【数据库角色】，即可看到默认的数据库角色，如图9-16所示。

SQLServer2005的固定数据库角色如表9-2所示。图9-16数据库角色可以查看角色的属性，也可以将一个用户添加到角色中。下面以StudentScoreManage数据库角色为例，来查看它的属性，并将用户student加入到该角色中。操作步骤如下：

(1)启动SSMS，在【对象资源管理器】窗口的数据库StudentScoreManager文件夹下，选择【安全性】→【角色】→【数据库角色】。

(2)在db_owner角色上右击鼠标，然后选择【属性】命令，打开【数据库角色属性-db_owner】对话框，如图9-17所示。图9-17【数据库角色属性-db_owner】对话框说明：

①默认每个数据库的db_owner中一定包括dbo用户，这是数据库的创建者；

②单击“添加”按钮可为该角色添加用户；

③单击“删除”按钮可删除除dbo以外的用户。

(3)要将用户student加入到该角色中，可单击【添加】按钮，打开【选择数据库用户或角色】对话框，直接输入对象名称或单击【浏览】按钮进行选择，设置完成后单击【确定】按钮，返回到【选择数据库用户或角色】对话框，如图9-18所示。图9-18【选择数据库用户或角色】对话框

(4)单击【确定】按钮，即可将用户student加入到db_owner数据库角色中。

将用户student加入到db_owner数据库角色中,也可以使用系统存储过程sp_addrolemember。其语法格式为：

sp_addrolemember[@rolename=]'role',

[@membername=]'security_account'

参数说明：

[@rolename=]'role'：当前数据库中的数据库角色的名称。role数据类型为sysname，无默认值。

[@membername=]'security_account'：添加到该角色的安全账户。security_account数据类型为sysname，无默认值。security_account可以是数据库用户、数据库角色、Windows登录或Windows组。说明：

(1) sp_addrolemember不能向角色中添加固定数据库角色、固定服务器角色或dbo。不能在用户定义的事务中执行sp_addrolemember。

(2)只能使用sp_addrolemember向数据库角色添加成员。不能向服务器角色添加成员。

(3)返回代码值：0(成功)或1(失败)。

(4)向用户自定义数据库角色添加成员需满足以下条件之一：

①具有db_owner固定数据库角色的成员身份；

②具有db_securityadmin固定数据库角色的成员身份；③具有拥有该角色的成员身份；

④对该角色拥有ALTER权限。

(5)向固定数据库角色添加成员要求具有db_owner固定数据库角色的成员身份。

例如，将数据库用户wangshuang添加到当前数据库的student数据库角色中。

EXECsp_addrolemember'student','wangshuang'9.4.3创建数据库角色

可以创建一个新的角色。以下以StudentScoreManager为例，讲述创建一个数据库角色的三种方式。

1．使用SSMS创建一个数据库角色

具体的操作步骤如下：

(1)启动SSMS，在【对象资源管理器】窗口的数据库StudentScoreManager文件夹下，选择【安全性】→【角色】→【数据库角色】。

(2)在【数据库角色】上右击鼠标，在弹出的快捷菜单中选择【新建数据库角色】命令，打开【数据库角色-新建】对话框，如图9-19所示。在角色名称后的文本框中输入角色名称“teacher”，在所有者后的组合框中单击选择所有者“dbo”，然后单击【添加】按钮，可为该角色添加用户。图9-19【数据库角色-新建】对话框

(3)在【选项页】列表中，选择【安全对象】选项，打开【安全对象】选项卡。在该选项卡中，可以设置角色的权限。操作步骤和设置用户权限操作步骤相同，详细步骤参见9.5节。

2．使用sp_addrole系统存储过程创建一个数据库角色

sp_addrole的语法格式为：

sp_addrole[@rolename=]'role'

[,[@ownername=]'owner']

参数说明：

[@rolename=]'role'：待创建角色的名称。role的数据类型为sysname，没有默认值。role必须是有效标识符(ID)，并且不能已经存在于当前数据库中。常见的命名模式的例子包括以部门来命名(computerdept、electronicdept、teachingaffairoffice等)或者以具体的工作来命名(teacher、student等)。使用这样的角色的确能够让向系统中添加新用户的工作变得容易。如果计算机工程系新聘用了某位教师，只需把他(或她)添加到computerdept角色中，即可使之拥有相应权限。

[@ownername=]'owner'：新数据库角色的所有者。owner的数据类型为sysname，默认值为当前正在执行的用户。owner必须是当前数据库的数据库用户或数据库角色。说明：

(1)返回代码值：0(成功)或1(失败)。

(2)需要对数据库具有CREATEROLE权限。如果创建架构，则需要对数据库具有CREATESCHEMA权限。如果将owner指定为用户或组，则需要对此用户或组具有IMPERSONATE权限；如果将owner指定为角色，则需要对此角色或此角色成员具有ALTER权限；如果将所有者指定为应用程序角色，则需要对此应用程序角色具有ALTER权限。

例如，向当前数据库中添加名为teacher的新角色：

EXECsp_addrole'teacher'

3．使用CREATEROLE语句创建一个数据库角色

CREATEROLE的语法格式为：

CREATEROLErole_name[AUTHORIZATIONowner_name]

参数说明：

role_name：待创建角色的名称。

AUTHORIZATIONowner_name：将拥有新角色的数据库用户或角色。如果未指定用户，则执行CREATEROLE的用户将拥有该角色。

说明：需要对数据库具有CREATEROLE权限。使用AUTHORIZATION选项时，还需要具有下列权限：

①若要将角色的所有权分配给另一个用户，则需要对该用户具有IMPERSONATE权限。②若要将角色的所有权分配给另一个角色，则需要具有被分配角色的成员身份或对该角色具有ALTER权限。

③若要将角色的所有权分配给应用程序角色，则需要对该应用程序角色具有ALTER权限。

例如，创建用户huangxiaoming拥有的数据库角色student：

USEStudentScoreManager;

CREATEROLEstudentAUTHORIZATIONhuangxiaoming;

GO9.4.4删除数据库角色

要删除用户自定义数据库角色，也可以通过以下三种方式。

1．使用SSMS删除数据库角色

具体操作为：在SSMS的【对象资源管理器】中选择所要删除的数据库角色名，在其上按Delete键，或者点击右键并在弹出的快捷菜单上单击【删除】命令。

2．使用系统存储过程sp_droprole删除数据库角色

sp_droprole的语法格式为：

sp_droprole[@rolename=]'role'

例如，从StudentScoreManager中删除数据库角色student。

USEStudentScoreManager;

EXECsp_droprole'student';

GO

3．用DROPROLE语句删除数据库角色

DROPROLE的语法格式为：

DROPROLErole_name

说明：

(1)无法从数据库删除拥有安全对象的角色。若要删除拥有安全对象的数据库角色，则必须首先转移这些安全对象的所有权，或从数据库删除它们。无法从数据库删除拥有成员的角色。要删除有成员的角色，必须首先删除角色的成员。

(2)不能使用DROPROLE删除固定数据库角色。

(3)要求对角色具有CONTROL权限，或者对数据库具有ALTERANYROLE权限。

例如，从StudentScoreManager中删除数据库角色student。

USEStudentScoreManager;

DROPROLEstudent;

GO

9.5权限管理

SQLServer2005提供的主要对象权限如表9-3所示。9.5.1用户权限

下面以表StudentScore为例，介绍如何给数据库用户授予对象权限。

(1)启动SSMS，在【对象资源管理器】窗口中选择【数据库】→【StudentScoreManager】→【表】→【StudentScore】，单击右键，在出现的快捷菜单中选择【属性】选项。

(2)切换到如图9-20所示的【表属性】的【权限】选项卡。单击【添加】按钮，添加要授予权限的用户对象，在这里可以给数据库用户授予或者删除特定的对象权限。不同的数据对象其权限可能会有所不同。图9-20【表属性】的【权限】选项卡

【权限】单元格：选择是否将对象权限授予用户。

【具有授予权限】单元格：选择是否允许数据库用户将授予自己的权限再授予其他数据库用户。9.5.2角色权限

在图9-17【数据库角色属性-db_owner】对话框的【安全对象】选项中，可以设置该角色的权限，如图9-21所示。图9-21角色权限设置9.5.3用户和角色权限问题

用户是否具有对数据库存取的权利，要看其权限设置而定，同时还受其所属角色权限的限制。

1．用户权限继承角色的权限

数据库角色中可以包含许多用户，用户对数据库对象的存取权限也继承自该角色。假设用户User1属于角色Role1，角色Role1已经取得对表table1的SELECT权限，则用户User1也自动取得对表table1的SELECT权限。如果Role1已经取得对表table1的INSERT权限，则用户User1最终也取得对表table1的INSERT权限。允许的权限继承关系如表9-4所示。而拒绝的权限是优先的，只要Role1和User1之一拒绝，则该权限就是拒绝的。拒绝的权限继承如表9-5所示。

2．用户分属不同角色

如果一个用户属于不同角色，例如，用户User1既属于角色Role1，又属于角色Role2，则用户User1的权限基本上是Role1和Role2权限的并集。但是其中只要有一个权限是拒绝，则用户User1的该权限就是拒绝。

3．Public数据库角色的权限

Public数据库角色是每个数据库最基本的数据库角色，每个用户可以不属于其他9个固定数据库角色，但是至少会属于Public数据库角色。当在数据库中添加新用户账户时，SQLServer会自动将新用户账户加入到Public数据库角色中。

双击Public角色可以打开其属性对话框，查看它的属性和权限。对于用户建立的数据库对象，例如用户建立的表，Public角色默认为不设置权限。9.5.4使用SQL语句管理权限

SQLServer支持3个与授权有关的Transact-SQL语句：GRANT、DENY、REVOKE。

1．授予权限

GRANT语句用来把权限赋予SQLServer可保护对象。GRANT语句的语法格式为：

GRANTpermission_list[ONscope]

TOprincipal_list[WITHGRANTOPTION]

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

参数说明：

permission_list：被授予权限的语句或对象(通过逗号分开)。

scope：将被授予权限的可保护类或可保护名称。

principal_list：列出了权限授予的所有账户(通过逗号分开)。principal_list的组件可以是一个Windows用户账户、SQLServer登录、映射到证书的登录或用户账户、映射到非对称键的登录、数据库用户、数据库角色或应用程序角色。

说明：SQLServer2000把所有权限分为两个权限组，即语句权限和对象权限。这些权限使用GRANT语句把权限授予Transact-SQL语句和数据库对象，其格式略有不同。而对于两类权限组，SQLServer2005中的GRANT语句具有统一的语法格式。

(1)给用户liyu和wangyun授予多个语句权限：

USEStudentScoreManager

GRANTCREATEDATABASE,CREATETABLETOliyu,wangyun

(2)给用户stud_user、stud_manager授予对StudentInformation表的所有权限。

首先给Public角色授予SELECT权限，然后将特定的权限授予stud_user、stud_manager用户。需要执行如下代码：

USEStudentScoreManager

GO

GRANTSELECTONStudentInformationTOpublic

GO

GRANTINSERT,UPDATE,DELETEONStudentInformationTOstud_use,stud_manager

2．拒绝权限

DENY语句可以阻止用户进行某些动作。这就意味着该语句可以从用户的账户删除某些现有的权限，或防止用户从它所在的组或角色中继承某些权限。该语句的语法格式如下所示：

DENYpermission_list[ONscope]

TOprincipal_list[CASCADE]

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

DENY语句中所有选项的含义与前面GRANT语句中同名选项的含义相同。唯一的区别是CASCADE选项。该选项指出用户A禁止使用此项权限，同时，通过用户A获得此项权限的其他任何用户也不能使用该权限。(如果在DENY语句中没有指定CASCADE选项，而且使用WITHGRANTOPTION授予了相应的对象权限，那么这时将会返回一个错误。)

说明：可以把GRANT语句想象成“正”的用户授权，而把DENY语句想象成“负”的用户授权。通常情况下，DENY语句只是用来剥夺组(角色)中的某几个成员已有的权限。例如，拒绝给用户liyu和wangyun授予多个语句权限：

USEStudentScoreManager

DENYCREATEDATABASE,CREATETABLETOliyu,wangyun

3．撤消权限

REVOKE语句用来删除一个或多个已经被赋予(或禁止)的权限。该语句的语法格式如下所示：

REVOKE[GRANTOPTIONFOR]permission_list[ONscope]

FROMprincipal_list[CASCADE]

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

REVOKE语句中唯一的新选项是GRANTOPTIONFOR，其他所有选项的含义都与前面GRANT语句或DENY语句中同名选项的含义相同。GRANTOPTIONFOR用来消除在相应的GRANT语句中使用WITHGRANTOPTION选项所带来的影响。这就意味着用户仍然拥有原先被授予的权限，但是不能再把该权限授予其他用户。说明：REVOKE语句可以剥夺通过GRANT语句指定的“正”权限，也可以剥夺由DENY语句产生的“负”权限。因此，该语句的功能是使指定的(正的或负的)权限失效。

例如，撤消授予用户账户stud_user的CREATETABLE权限。

REVOKECREATETABLEFROMstud_user

9.6数 据 库 管 理

9.6.1数据库备份

1. SQLServer备份概述

对于生产数据来说，数据的安全性是至关重要的，任何数据的丢失和危险都可能导致严重的后果。此时，备份和恢复数据库就显得非常重要。SQLServer2005提供了高性能的备份和还原功能。SQLServer备份和还原组件提供了重要的保护手段，以保护存储在SQLServer中的关键数据。实施计划妥善的备份和恢复策略可保护数据库，避免由于各种故障造成损坏而丢失数据。“备份”是数据的副本，用于在系统发生故障后还原和恢复数据。备份能够在发生故障后还原数据。通过适当的备份，可以在媒体故障、用户错误、硬件故障或者自然灾难中恢复数据。此外，数据库备份对于例行的工作(例如，将数据库从一台服务器复制到另一台服务器、设置数据库镜像和文件归档等)也很有用。

图9-22中显示了由于灾难或其他原因丢失数据后，从数据库的完整备份还原数据的简单情形。图中，数据库在t1、t2、…、tn等时刻对数据库进行了备份。在tn时刻后，数据发生了损失，则可将数据库恢复到其最近一次的备份，即tn时刻的备份。备份点和故障点之间的所有更新将全部丢失。但是通过添加日志备份，通常可将数据库还原到数据发生故障的时刻，而不会丢失数据(如果使用的SQLServer版本支持时间点恢复)。图9-22数据的备份和恢复示意图

SQLServer2005提供了几种不同的备份类型：数据备份、差异备份以及在完整和大容量日志恢复模式下的事务日志备份。

(1)数据备份：是指包含一个或者多个数据文件的完整映像的任何备份。数据备份会备份所有数据和足够的事务日志，以便恢复数据。可对全部或部分数据库、一个或多个文件进行数据备份。该备份类型需要比较大的存储空间来存储备份文件，备份时间也比较长，在还原数据时，也只要还原一个备份文件。

(2)差异备份：是数据备份的补充。差异备份只备份上次完整备份后更改的数据。相对完整数据库备份来说，差异数据库备份的数据量比完整数据库备份小，备份的速度也比完整数据库备份要快。因此，差异数据库备份通常作为经常用到的备份。在还原数据时，要先还原前一次做的完整数据库备份后再还原最后一次所做的差异数据库备份，这样才能让数据库里的数据恢复到与最后一次差异数据库备份时的内容相同。

(3)事务日志备份：也称为“日志备份”，包括了在前一个日志备份中没有备份的所有日志记录，它记录了上一次完整数据库备份或事务日志备份后数据库的所有变动过程。事务日志记录的是某一段时间内的数据库变动情况，因此在做事务日志备份之前，也必须要做完整数据库备份。与差异数据库备份类似，事务日志备份的备份文件和时间都会比较小，但是在还原数据时，除了要先还原完整数据库备份之外，还要依次还原每个事务日志备份，而不是只还原最近一个事务日志备份。

2.数据备份

SQLServer提供两种方式备份数据库：

(1)备份数据库：对数据库中的数据和对象进行完整备份。

(2)备份事务日志：事务日志记录了上一次事务备份后的改变。

一种好的备份既有完整数据库备份，也有不断增加的事务日志备份。

1)备份设备

创建备份时，必须选择存放备份数据的备份设备。在SQLServer2005中，可以将数据库、事务日志和文件备份到磁盘和磁带设备上。

磁盘备份设备是硬盘或其他磁盘存储媒体上的文件，与常规操作系统文件一样，引用磁盘备份设备与引用任何其他操作系统文件一样。可以在服务器的本地磁盘上或共享网络资源的远程磁盘上定义磁盘备份设备。磁盘备份设备根据需要可大可小，最大的文件大小相当于磁盘上可用的闲置空间。在网络上将文件备份到远程计算机上的磁盘时使用通用命名规则名称(UNC)，以\\Servername\Sharename\Path\File格式指定文件的位置。建议：不要将数据库或者事务日志备份到数据库所在的同一物理磁盘上的文件中。如果包含数据库的磁盘设备发生故障，则由于备份位于同一发生故障的磁盘上，因而无法恢复数据库。

磁带备份设备的用法与磁盘设备大致相同，但是要注意下面一些问题：

①必须将磁盘设备物理连接到运行SQLServer实例的计算机上。不支持备份到远程磁带设备上。

②如果磁带备份设备在备份操作过程中已满，但还需要写入一些数据，SQLServer将提示更换新磁带并继续备份操作。

SQLServer使用物理设备名称或逻辑设备名称标识备份设备。物理备份设备是操作系统用来标识备份设备的名称，如E:\Backup\StudentScoreManager.bak。

可以用SSMS或者使用系统存储过程sp_addumpdevice来创建一个备份设备。

使用SSMS创建备份设备的具体操作步骤如下：

(1)打开SSMS，在【对象资源管理器】窗口中打开【服务器对象】，在【备份设备】项上单击右键，在出现的快捷菜单中选择【新建备份设备】选项，如图9-23所示。图9-23【新建备份设备】操作

(2)单击【新建备份设备】选项后，打开【备份设备】对话框，如图9-24所示。在【设备名称】项输入备份设备的名称“StudentScoreManagerbakdevice”，选中【文件】项，在其中输入备份设备的物理名称，或单击其后的浏览按钮并选择备份设备的物理名称；也可以选中【磁带】项，创建一个磁带备份设备。图9-24【备份设备】对话框

(3)设置完成后，单击【确定】按钮，即可创建一个备份设备。

要删除备份设备，也可以用SSMS或者使用系统存储过程sp_dropdevice。

使用SSMS删除备份设备的具体操作步骤如下：

在图9-23中的【对象资源管理器】窗口中打开【服务器对象】→【备份设备】，找到所要删除的备份设备的名称，例如“StudentScoreManagerbakdevice”，单击右键，然后在弹出的快捷菜单中单击【删除】命令即可。

2)备份数据库

SQLServer备份是动态的，也就是说，在任何时候(包括用户使用数据库的时候)都可以备份。但是最好是在数据库没有进行大量的修改时执行备份，因为备份过程会使系统变慢。

以备份StudentScoreManager数据库为例，具体操作步骤如下：

(1)打开SSMS，在【对象资源管理器】窗口中打开【数据库】文件夹，在【StudentScoreManager】项单击右键，并在出现的快捷菜单中单击【任务】→【备份】命令，如图9-25所示。出现如图9-26所示的【备份数据库】对话框。

(2)在【备份数据库】对话框中，分别进行如下设置：

①在【源】区域中：

在【数据库】下拉列表框中选择要备份的数据库。这里选择StudentScoreManager数据库。

在【备份类型】下拉列表框中选择备份类型。这里选择“完整”选项。

在【备份组件】区域中选择备份数据库或者备份文件和文件组。这里选择“数据库”单选框，表示备份数据库。

②在【备份集】区域中：

在【名称】文本框中输入备份的名称。

在【说明】文本框中可以输入对该备份的说明。

在【备份过期时间】区域中设置备份集的过期时间。图9-25数据库备份操作菜单图9-26【备份数据库】对话框

(3)在【目标】区域中，可以选择备份到磁盘或备份到磁带。单击【添加】按钮，打开【选择备份目标】对话框，如图9-27所示。在此对话框中可以选择备份设备，或者设置一个文件名来备份数据库。这里选择前面创建的备份设备StudentScoreManagerbakdevice。图9-27【选择备份目标】对话框

(4)单击【确定】按钮，返回到【备份数据库】对话框。

(5)在【选项页】中，选择【选项】选项，可打开【选项】选项卡，如图9-28所示。在此选项卡中，可以设置备份时的一些选项。

(6)设置完成后，单击【确定】按钮，即可开始备份。备份完成后，会弹出一个提示对话框，提示备份完成，单击【确定】按钮即可。图9-28【选项】选项卡

3)备份系统数据库

和用户数据库一样，系统数据库也要定期进行备份，尤其是master和msdb数据库。

master数据库包含了SQLServer配置的信息和服务器上所有其他数据库的有关信息，因此应该定期备份该数据库。在SQLServer的配置或者它包含的数据库被改变后，都应该备份master数据库。这些改变包括：

①在数据库上使用了CREATE或者ALTER语句。

②使用了DBCCSHRINKDB命令。③添加或删除登录账户，或者改变用户的权限。

④建立、删除或改变数据库或者备份设备的大小。

msdb数据库是SQLServer服务使用的数据库，它是所有调度任务以及这些任务历史的存储区。只要增加或者修改任务、增加或者修改自动备份工作，msdb数据库都会发生改变。9.6.2数据库恢复

数据库被损坏时，可以使用备份来恢复数据库。恢复数据库是一个装载数据库的备份，然后应用事务日志重建的过程。应用事务日志之后，数据库就会回到最后事务日志备份之前的状态。

在数据库的恢复过程中，用户不能进入数据库；当数据库恢复后，数据库中的所有数据都被替换掉。

1.自动恢复

在每次启动时，SQLServer都会进行自动恢复，检查是否有恢复工作需要进行。自动恢复过程检查每个数据库的事务日志。这个过程搜索事务的开始和结束记录，并与数据库中的数据进行对比。在系统崩溃时没有完成的事务会被回滚。

自动恢复过程以master数据库开始，然后转向model数据库。SQLServer用model数据库作为新建数据库的模板。model数据库被恢复之后，自动恢复过程消除tempdb数据库中的所有对象。自动恢复接下来检查msdb数据库和pubs数据库。所有的系统数据库被恢复之后，自动恢复才恢复用户数据库。系统数据库恢复后，用户就能登录到服务器。

2.恢复用户数据库

在恢复用户数据库时，SQLServer自动执行安全检查，防止从不完整、不正确或者其他数据库备份中恢复数据。

恢复数据库可以使用SSMS，也可以使用SQL语句RESTORE。

使用SSMS恢复数据库的具体步骤如下：

(1)打开SSMS，在【对象资源管理器】窗口中打开【数据库】文件夹。

(2)由于数据库的还原操作是静态的，因此在还原数据库前，必须限制其他用户对数据库进行其他操作。右击要还原的数据库，例如StudentScoreManager，执行快捷菜单中的【属性】命令，打开【数据库属性】对话框。在【选项页】列表中，选择【选项】选项，打开【选项】选项卡，在【限制访问】下拉列表中，选择Single，如图9-29所示。

(3)单击【确定】按钮，此时弹出【打开的连接】提示对话框，单击【确定】按钮即可。图9-29【数据库属性】对话框

(4)在StudentScoreManager数据库上右击鼠标，在弹出的快捷菜单中选择【任务】→【还原】→【数据库】命令，打开【还原数据库】对话框，如图9-30所示。

(5)在【还原数据库】对话框中，分别进行如下设置。

①在【还原的目标】区域中：在【目标数据库】下拉列表框中选择现有数据库或者输入一个新的数据库名称；单击【目标时间点】文本框后的浏览按钮，出现【时点还原】对话框，如图9-31所示，在其中设置要还原到的时间点。图9-30【还原数据库】对话框图9-31【时点还原】对话框②在【还原的源】区域中：单击【源设备】单选按钮，然后单击其右侧的浏览按钮，打开【指定设备】对话框，如图9-32所示。在【指定设备】对话框的【备份媒体】下拉列表中选择【备份设备】选项。在【备份位置】区域中单击【添加】按钮，可以添加备份设备。单击【确定】按钮，返回到【还原数据库】对话框。图9-32【指定设备】对话框③在【选择页】列表中：选择【选择】选项，打开【选项】选项卡，可以对还原的选项进行设置。

(6)设置完成后，单击【确定】按钮，即可开始还原数据库。

(7)还原完成后，系统会弹出提示对话框，提示还原已经成功。单击【确定】按钮即可。9.6.3数据库分离与附加

除了系统数据库外，其余的数据库都可以从服务器的管理中分离出来，脱离服务器的管理，同时保持数据文件和日志文件的完整性和一致性。这样分离出来的数据库的日志文件和数据文件可以附加到其他SQLServer2005服务器上，构成完整的数据库。数据库的分离好比是将衣服(数据库)从衣架(SSMS)上取下来。与分离操作对应的是附加数据库操作，好比将衣服(数据库)重新挂在衣架(SSMS)上。

1.分离用户数据库

分离用户数据库的步骤如下：

(1)打开SSMS，在【对象资源管理器】窗口中打开【数据库】文件夹。选择StudentScoreManager数据库，单击右键，在出现的快捷菜单中选择【任务】→【分离】选项，如图9-33所示。

(2)出现如图9-34所示的【分离数据库】对话框。在【要分离的数据库】区域可以选择要分离的数据库。图9-33选择分离数据库图9-34【分离数据库】对话框①【数据库名称】单元格：显示数据库的逻辑名称。

②【删除连接】单元格：选择是否断开与指定数据库的连接。

③【更新统计信息】单元格：选择在分离数据库之前是否更新过时的优化统计信息。

④【保留全文目录】单元格：选择是否保留与数据库相关联的所有全文目录，全文目录将用于全文索引。

⑤【状态】单元格：显示的值为“就绪”或“未就绪”。⑥【消息】单元格：当数据库进行复制操作时，【状态】单元格为“未就绪”，【消息】单元格将显示“已复制数据库”；当数据库有一个或多个活动连接时，【状态】单元格为“未就绪”，【消息】单元格将显示“<活动连接数>活动连接”(例如：“1活动连接”)。有活动连接的数据库将无法被分离，除非同时选择删除活动连接。

(3)完成选择后，单击【确定】按钮，即可完成分离数据库。

2.附加用户数据库

分离后的数据库的数据和事务日志文件可以重新附加到同一或其他SQLServer2005实例或移动数据库。

附加用户数据库的步骤如下：

(1)打开SSMS，在【对象资源管理器】窗口中选择【数据库】选项，单击右键，在出现的快捷菜单中选择【附加】选项，如图9-35所示。图9-35选择附加数据库

(2)出现如图9-36所示的【附加数据库】对话框。单击【添加】按钮，出现如图9-37所示的【定位数据库】对话框。在【选择文件】树形列表框中选择要附加的数据库的主要数据文件StudentScoreManager.mdf，单击【确定】按钮。图9-36【附加数据库】对话框图9-37【定位数据库】对话框

(3)出现【附加数据库】对话框，如图9-38所示进行设置。

(4)确认无误后，单击【确定】按钮，即可完成附加数据库。图9-38设置后的【附加数据库】对话框9.6.4数据导入/导出

SQLServer提供了一种很容易在SQLServer数据库或非SQLServer数据库和另外一个SQLServer数据库之间转换数据的方法。通过SSMS，可以在不同的数据库之间转换和传输数据。这种转换过程被称为导入/导出。例如，可以将一个SQLServer数据库中的数据转换到Access(导出)，也可以将文本文件中的数据转换到SQLServer数据库中(导入)。导出操作步骤如下：

(1)打开SSMS，在【对象资源管理器】窗口中打开【数据库】文件夹。

(2)在要转换的数据库上右击鼠标，例如StudentScoreManager数据库。在打开的快捷菜单上选择【任务】→【导出数据】命令，打开【SQLServer导入和导出向导】对话框，如图9-39所示。图9-39【SQLServer导入和导出向导】对话框

(3)单击【下一步】按钮，出现如图9-40所示的【选择数据源】对话框。在【数据源】下拉列表中选择数据源类型，在【服务器名称】下拉列表中选择服务器，并设置验证模式。在【数据库】下拉列表中选择数据库。图9-40【选择数据源】对话框

(4)单击【下一步】按钮，向导提示选择目标数据库，如图9-41所示。在【目标】下拉列表框中选择MicrosoftAccess，表示将数据导出到Access表中。在【文件名】文本框中输入“E:\高晓梅\个人\书稿\TeacherInformation.mdb”，单击【高级】按钮，打开【数据链接属性】对话框，如图9-42所示。分别在【用户名称】和【密码】文本框中输入数据库的用户名和密码信息(关于给Access数据库设置密码的问题，请参考Access相关书籍)，然后单击【确定】按钮，回到图9-41所示的界面，输入相应的用户名和密码。图9-41【选择目标】对话框图9-42【数据链接属性】对话框

(5)单击【下一步】按钮，向导提示用户【指定表复制或者查询】，如图9-43所示。其中两个单选按钮的含义如下：

①【复制一个或多个表或视图的数据】：选择该项，表示直接复制表或者视图的数据。

②【编写查询以指定要传输的数据】：选择该项，表示通过SQL查询语句来获取要传输的数据。例如，可以通过