数据库原理与应用 学习情境10：数据库系统的管理和维护

学习目标： 3 1 2 3 4 培养团队协作精神、语言表达能力 熟悉数据安全认证的内容，实现数据安全设置 熟练掌握数据库备份和还原的方法 熟练掌握数据导入导出的方法 3 培养团队协作精神、语言表达能力 4

任务的提出 在部分将首先介绍SQL Server的安全访问机制，介绍SQL Server系统中登录账户、用户、角色和权限的管理方法与操作技巧。最后还将介绍SQL Server系统中实现数据安全的保障机制——数据备份与恢复。主要介绍数据库备份与恢复的策略和原则，实现数据库备份与恢复的方法和操作技巧。

10.2 SQL Server 2000的安全机制 图10.1 登录账户和用户之间的关系 在SQL Server中工作时，用户必须要经过两个安全性阶段：身份验证和授权（权限验证）。身份验证是使用登录账户登录系统，并只验证用户连接SQL Server实例的能力。如果身份验证成功，用户即可连接到SQL Server实例上。然后用户需要拥有访问服务器上数据库的权限，权限验证阶段则控制用户在SQL Server数据库中所允许进行的活动

10.2.1 Windows身份验证模式 当用户通过Windows用户账户进行连接时，SQL Server通过回叫Windows 系统以获得信息，并重新验证账户名和密码。SQL Server通过使用网络用户的安全特性控制登录访问，以实现与Windows系统的登录安全机制集成。 用户的网络安全特性在网络登录时建立，并通过Windows 域控制器进行验证。当网络用户尝试连接时，SQL Server使用基于windows系统的功能确定经过验证的网络用户名。SQL Server将验证此用户是否是合法用户，然后只基于网络用户名允许或拒绝登录访问，而不要求单独的登录名和密码。登录安全集成在SQL Server中任何受支持的网络协议上运行。

10.2.2 混合验证模式 当系统采用SQL Server身份验证模式进行连接时，SQL Server将通过检查是否己设置SQL Server登录账户，以及指定的密码是否与以前记录的密码匹配进行身份验证。如果SQL Server未设置登录账户，则身份验证将失败，而且用户会收到错误信息。在这种方式下，用户必须提供密码，让SQL Server验证。

SQL Server中的账户包含两种：登录账户、数据库用户账户。 10.3 登录账户管理 SQL Server中的账户包含两种：登录账户、数据库用户账户。 登录账户是面对整个SQL Server 管理系统的，某位用户必须使用特定的登录账户才能连接到SQL Server ，但连接上并不说明就有访问数据库的权力。 数据库用户账户则针对SQL Server 管理系统中的某个数据库而言，当某位用户用合法登录账户连接到SQL Server 后，还必须在所访问的数据中创建数据库用户账户。

（2）将用户帐户设置SQL Server登录账户。 【任务10.2】 《晓灵学生管理系统》中要求学生既可以通过校园网登录服务器查询自己的成绩，也可以通过互联网登录服务器来查询自己的成绩。问：对于这种情况应如何设置其登录账户和数据用户。 （l）要使两类用户都能访问SQL Server，必须将SQL Server服务器的身份验证模式设为“SQL Server 和Windows[S]”。 （2）将用户帐户设置SQL Server登录账户。 （3）设置完登录账户后，可在myDB数据库的用户中加入上述登录账户，即可将其映射为合法的数据库用户。

10.3.1 设置Windows用户连接SQL Server系统 1．使用企业管理器管理登录账户 具体操作步骤如下： ① 在企业管理器中，展开服务器组后再展开一个服务器。 ② 展开【安全性】节点，然后单击【登录】。 ③ 右击详细信息窗格的空白处，然后选择【新建登录】。 ④ 在出现的界面中选择【常规】选项卡，如图10.4所示。 ⑤ 在【名称】一栏中，单击按钮，然后选择要加入的windows 2000用户“student”，单击【添加】按钮。 ⑥ 选择【允许访问】单选钮。 ⑦ 【默认设置】一栏中的语言选项按系统的默认值设定。数据库选项设置为“myDB”。 ⑧ 单击【确定】按钮，即可成功创建登录。

2．用存储过程设置Windows用户连接SQL Server 其语法格式为：sp_grantlogin [@loginame =] 'login' 参数说明：[@loginame =] 'login'：是要添加的Windows用户或组的名称。Windows组和用户必须用Windows域名限定，格式为“域\用户”，例如zhenaj\student。

10.3.2 设置SQL Server 登录账户 1．使用企业管理器添加SQL Server 登录账户 其语法格式如下： sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ] 参数说明： [@loginame =] 'login'：登录的名称。 [@passwd =] 'password'：登录密码。 [@defdb =] 'database'：登录的默认数据库（登录后登录所连接到的数据库）。 [@deflanguage =] 'language'：用户登录到 SQL Server 时系统指派的默认语言。 [@sid =] sid：安全标识号 (SID)。 [@encryptopt =] 'encryption_option'：指定当密码存储在系统表中时，密码是否要加密。

10.3.3 修改登录账户的属性 对于创建好的登录账户，可修改的属性有： 默认数据库 默认语言 登录账户是SQL Server 账户的口令 其操作步骤如下。 ① 在企业管理器中，展开【SQL Server 组】后再展开要管理的服务器。 ② 展开【安全性】节点，然后单击【登录】按钮。 ③ 在右边的登录账户列表中双击要修改的账户④ 修改相应选项后单击【确定】按钮。

1．暂时禁止 10.3.4 禁止登录账户 对于使用SQL Server 身份验证的登录账户，只要修改登录密码就可。 10.3.4 禁止登录账户 1．暂时禁止 对于使用SQL Server 身份验证的登录账户，只要修改登录密码就可。 对于使用Windows 身份验证的登录账户，可按如下步骤操作： ① 在企业管理器中，展开【SQL Server 组】后再展开要管理的服务器。 ② 展开【安全性】节点，然后单击【登录】按钮。 ③ 在右边的登录账户列表中双击要禁止的账户，出现如图10.4所示的窗口。 ④ 选择【拒绝访问】单选钮后单击【确定】按钮完成对登录账户“zhenaj\student”的禁止。 2．永久禁止 要永久禁止一个登录账户连接到SQL Server，应当将该登录账户删除。

10.3.5 删除登录账户 1．使用企业管理器删除登录账户 操作步骤如下： ① 在企业管理器中，展开【SQL Server 组】后再展开要管理的服务器。 ② 展开【安全性】节点，然后单击【登录】。 ③ 在右边窗口中选择要删除的登录名。 ④ 右键单击该登录名后选择【删除】命令，也可直接按Delete 键。 ⑤ 在弹出的对话框中单击【确定】按钮即可成功删除。

2．使用存储过程删除登录账户 可以使用存储过程sp_droplogin删除SQL Server登录，以阻止使用该登录名访问SQL Server实例。其语法格式如下： sp_droplogin [ @loginame = ] 'login' 【任务10.7】要删除一个使用SQL server 身份验证的登录账户“STU”和一个使用Windows身份验证的登录账户“zhenaj\student”。 EXECUTE sp_droplogin 'STU' EXECUTE sp_revokelogin ' zhenaj\student'

10.4 数据库用户管理 10.4.1 创建数据库用户 1．使用企业管理器创建数据库用户 2．使用存储过程创建数据库用户 其语法格式为： 10.4.1 创建数据库用户 1．使用企业管理器创建数据库用户 2．使用存储过程创建数据库用户 其语法格式为： sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name_in_db' [OUTPUT]] 参数说明： [@loginame =] 'login'：当前数据库中新安全账户的登录账户名称。登录不能使用数据库中已有的账户作为别名。 [@name_in_db =] ‘name_in_db’ [OUTPUT]：数据库中用户账户的名称。如果没有指定，则使用登录账户名称。 【任务10.8】假如有使用SQL Server 身份验证的登录账户“TEA”，为其在数据库myDB中添加一个数据库用户账户。 可以在查询分析器中执行下列语句： use myDB go EXECUTE sp_grantdbaccess 'TEA','TEA'

10.4.2 修改数据库用户 1．使用企业管理器修改数据库用户 2．使用存储过程修改用户账户所属的角色 10.4.2 修改数据库用户 1．使用企业管理器修改数据库用户 2．使用存储过程修改用户账户所属的角色 通过T-SQL语句修改用户账户所属角色时，要用到以下两个系统存储过程： sp_addrolemember将数据库用户添加到一个数据库角色 sp_droprolemember 从一个数据库角色中删除一个用户账户 它们的语法格式如下： sp_addrolemember [ @rolename = ] 'role' , [ @membername = ] 'security_account' sp_droprolemember [ @rolename = ] 'role' , [ @membername = ] 'security_account' 参数说明： [@rolename =] 'role'：当前数据库中SQL Server角色的名称。 [@membername =] 'security_account'：添加到角色的数据库用户账户。

【任务10.9】将登录账户“LEAD”添加到数据库myDB，使其成为数据库用户“LEAD”，然后将“STU”添到数据库myDB的db_accessadmin 角色中。 可以在查询分析器中执行下列SQL语句： use myDB EXECUTE sp_grantdbaccess 'LEAD', 'LEAD' EXECUTE sp_addrolemember 'db_accessadmin','STU' EXECUTE sp_helpuser 'STU' --查看用户STU的相关信息

sp_revokedbaccess [ @name_in_db = ] 'name' 参数说明： 10.4.3 删除数据库用户 1．使用企业管理器删除数据用户 2．使用存储过程删除数据用户 sp_revokedbaccess [ @name_in_db = ] 'name' 参数说明： [@name_in_db =] 'name'：是要删除的账户名称。账户名必须存在于当前数据库中。 【任务10.10】从当前数据库myDB中删除用户账户STU。 EXECUTE sp_revokedbaccess 'STU'

在SQL Server 2000 中，角色分为以下两类： 服务器角色：是服务器级的一个对象，只能包含登录。分配了一定的服务器操作权限。 10.5 角色管理 在SQL Server 2000 中，角色分为以下两类： 服务器角色：是服务器级的一个对象，只能包含登录。分配了一定的服务器操作权限。 数据库角色：是数据库级的一个对象，只能包含数据库用户而不能包含登录。分配了一定的数据库操作权限。

10.5.1 服务器角色和数据库角色 数据库角色分为固定数据库角色和自定义数据库角色两类。在安装完SQL Server后，系统也为每个数据库自动创建了10个固定的数据库角色，它们提供了数据库一级的管理权限集。服务器角色存放在每个数据库的sysuser表中。 固定数据库角色 描述 db_owner 在数据库中有全部权限。 db_accessadmin 可以添加或删除用户ID。 db_securityadmin 可以管理全部权限、对象所有权、角色和角色成员资格。 db_ddladmin 可以发出ALL DDL，但不能发出GRANT、REVOKE或DENY语句。 db_backupoperator 可以发出 DBCC、CHECKPOINT 和 BACKUP 语句。 db_datareader 可以选择数据库内任何用户表中的所有数据。 db_datawriter 可以更改数据库内任何用户表中的所有数据。 db_denydatareader 不能选择数据库内任何用户表中的任何数据。 db_denydatawriter 不能更改数据库内任何用户表中的任何数据。 public 维护所有的默认权限。不能被删除。

1．向固定服务器角色添加成员 10.5.2 服务器角色的应用 10.5.2 服务器角色的应用 1．向固定服务器角色添加成员 sp_addsrvrolemember [ @loginame = ] 'login', [@rolename =] 'role' 参数说明： [@loginame =] 'login'：是添加到固定服务器角色的登录名称。 [@rolename =] 'role'：要将登录添加到的固定服务器角色的名称。 【任务10.12】使用T-SQL语句解决【任务10.11】的问题可在查询分析器中执行下列T-SQL语句： EXECUTE sp_addsrvrolemember 'myadmin','sysadmin'

2．删除固定服务器角色内的成员 使用存储过程sp_dropsrvrolemember。其语法格式：sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename = ] 'role' 参数说明： [@loginame =] 'login'：将要从固定服务器角色删除的登录账户。 [@rolename =] 'role'：有效的固定服务器角色的名称。 【任务10.14】使用T-SQL语句解决【任务10.13】的问题，可以在查询分析器中执行下列T-SQL语句： EXECUTE sp_dropsrvrolemember 'myadmin','sysadmin'

10.5.3 数据库角色的应用 创建自定义角色 use dbname exec sp_addrole ‘rolename’ 10.5.3 数据库角色的应用 创建自定义角色 use dbname exec sp_addrole ‘rolename’ 向角色中添加成员 use dbname exec sp_addrolemember ‘rolename’ ‘db_user_name’ 删除数据库角色 use dbname exec sp_droprole ‘rolename

10.6 权限管理 10.6.1 权限的分类 1．对象权限 2．语句权限 3．暗示性权限 10.6.2 权限管理的内容 10.6.1 权限的分类 1．对象权限 2．语句权限 3．暗示性权限 10.6.2 权限管理的内容 权限可由数据所有者和角色进行管理，其内容主要包括以下三方面： l．授予权限 2．禁止权限 3．废除权限

10.6.3 管理数据库用户的权限 2．使用T - SQL语句管理数据库用户的权限 10.6.3 管理数据库用户的权限 2．使用T - SQL语句管理数据库用户的权限 在SQL Server中分别使用GRANT、REVOKE和DENY语句来授予权限、禁止权限和废除权限。其中GRANT和REVOKE语句的语法格式分别如下： GRANT <permission> on <object> TO <user> REVOKE <permission> on <object> TO <user> DENY { ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } TO security_account [ ,...n ] [ CASCADE ]

10.7.1 备份与恢复的基本概念 10.7 备份与恢复的基本概念和策略 所谓备份就是数据管理员定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些设备的数据文本被称为后备副本。当数据库遭到破坏的时候就可以利用后备副本把数据库恢复。 所谓恢复就是在数据库遭到破坏以后，由拥有权限的用户装载最近备份的数据库和应用事务日志来重建数据库到失败点的过程。

1.数据库备份的类型 完全备份 差异备份 事务日志备份 文件和文件组备份

简单恢复模型：允许将数据库恢复到最新的备份。 完全恢复模型：允许将数据库恢复到故障点状态。 大容量日志记录恢复模型：允许大容量日志记录操作。 ．数据库恢复的策略 简单恢复模型：允许将数据库恢复到最新的备份。 完全恢复模型：允许将数据库恢复到故障点状态。 大容量日志记录恢复模型：允许大容量日志记录操作。

10.8 备份数据库 10.8.1 备份设备 在SQL Server2000中可以通过以下两种方法对备份设备进行管理： 使用企业管理器管理备份设备 使用系统存储过程管理备份设备 sp_addumpdevice [ @devtype = ] 'device_type' , [ @logicalname = ] 'logical_name' , [ @physicalname = ] 'physical_name' [ , { [ @cntrltype = ] controller_type | [ @devstatus = ] 'device_status' } ] 参数说明如下： [@devtype =] 'device_type'：备份设备的类型，可以是disk、pipe和tape。 [@logicalname =] 'logical_name'：备份设备的逻辑名称。 [@physicalname =] 'physical_name'：备份设备的物理名称。物理名称必须遵照操作系统文件名称的规则或者网络设备的通用命名规则，并且必须包括完整的路径。

exec sp_dropdevice 'myDB_bak' 【任务10.27】使用存储过程在当前的数据库服务器上添加一个逻辑名为“myDB_bak”的磁盘备份设备，其物理名称为“D:\DataBaseBak\myDB_bak.BAK”。 exec sp_addumpdevice 'disk','myDB_bak','D:\DataBaseBak\myDB_bak.BAK‘ 4．使用存储过程删除备份设备 sp_dropdevice [ @logicalname = ] 'device' [ , [ @delfile = ] 'delfile' ] 参数说明： [@logicalname =] 'device'：备份设备的逻辑名称。 [@delfile =] 'delfile'：指出是否应该删除物理备份设备文件。delfile 的数据类型为 varchar(7)。如果将其指定为 DELFILE，那么就会删除物理备份设备磁盘文件。 【任务10.28】使用存储过程删除一个备份设备。 exec sp_dropdevice 'myDB_bak'

10.8.4 使用T-SQL 命令备份数据库 1．完全备份数据库 BACKUP DATABASE { database_name | @database_name_var } TO < backup_device > [ ,...n ] [ WITH options ] 参数说明： DATABASE：指定一个完整的数据库备份。假如指定了一个文件和文件组的列表，那么仅有这些被指定的文件和文件组被备份。 { database_name | @database_name_var }：指定了一个数据库，从该数据库中对事务日志、部分数据库或完整的数据库进行备份。 2．差异备份 BACKUP命令的语法格式如下： TO < backup_device > [ ,...n ] WITH DIFFERENTIAL [options] 关键字DIFFERENTIAL的作用是，通过它可以指定只对在创建最新数据库备份后数据中发生变化的部分进行备份。

3．事务日志备份 从最近一次日志备份以来所有事务日志备份到备份设备。日志备份经常与全库备份和差异备份结合使用。此种备份使用BACKUP命令的语法格式如下： BACKUP LOG { database_name | @database_name_var } { TO < backup_device > [ ,...n ] [ WITH options [ [ , ] NO_TRUNCATE ] [ [ , ] { NORECOVERY | STANDBY = undo_file_name } ] } 4．文件与文件组备份 当一个数据库很大时，对整个数据库进行备份可能花费很多时间，此时可采用文件和文件组备份。此种备份使用BACKUP命令的语法格式如下： BACKUP DATABASE { database_name | @database_name_var } < file_or_filegroup > [ ,...n ] TO < backup_device > [ ,...n ] [ WITH options ] 其中语句< file_or_filegroup >的形式为： < file_or_filegroup > ::= { FILE = { logical_file_name | @logical_file_name_var } | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } }

【任务10.31】 使用BACKUP命令将数据库myDB整个备份到备份设备myDB_bak。 USE myDB go BACKUP DATABASE myDB TO myDB_bak with name='myDB080416‘ 【任务10.32】在【任务10.31】的基础上，使用存储过程进行差异备份。 BACKUP DATABASE myDB TO myDB_bak WITH DIFFERENTIAL,name='myDB差异备份',NOINIT 【任务10.33】 对数据库myDB进行事务日志备份。 BACKUP LOG myDB TO myDB_bak WITH NOINIT 【任务10.34】将数据库myDB的cnc_nk_data文件备份到本地磁盘设备myfileback。 exec sp_addumpdevice 'disk','myfilebackup','D:\DataBaseBak\mymyfilebackup.BAK' BACKUP DATABASE myDB FILE='cnc_nk_data' TO myfilebackup

10.9 恢复数据库 当系统出现问题或者是出于某种原因，要对某个数据库进行恢复时必须具备该数据库的备份文件。在SQL Server2000系统中恢复数据库可以通过以下两种方法： 使用企业管理器恢复数据库 利用T-SQL语句恢复数据库 1．恢复整个数据库 RESTORE DATABASE { database_name | @database_name_var } [ FROM < backup_device > [ ,...n ] ] [ WITH [ RESTRICTED_USER ] [ [ , ] FILE = { file_number | @file_number } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ][ ,...n ] [ [ , ] KEEP_REPLICATION ] [ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] REPLACE ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] ]

2．恢复数据库的部分内容： 用户恢复部分数据库内容的RESTORE语句的语法格式如下： RESTORE DATABASE { database_name | @database_name_var } < file_or_filegroup > [ ,...n ] [ FROM < backup_device > [ ,...n ] ] [ WITH { PARTIAL } [ [ , ] FILE = { file_number | @file_number } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ] [ [ , ] NORECOVERY ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] REPLACE ] [ [ , ] RESTRICTED_USER ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] ]

3．恢复特定的文件或文件组： 用户恢复特定文件或文件组的RESTORE语句的语法格式如下： RESTORE DATABASE { database_name | @database_name_var } < file_or_filegroup > [ ,...n ] [ FROM < backup_device > [ ,...n ] ] [ WITH [ RESTRICTED_USER ] [ [ , ] FILE = { file_number | @file_number } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ][ ,...n ] [ [ , ] NORECOVERY ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] REPLACE ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] ]

4．恢复事务日志： 用户恢复事务日志的RESTORE语句的语法格式如下： RESTORE LOG { database_name | @database_name_var } [ FROM < backup_device > [ ,...n ] ] [ WITH [ RESTRICTED_USER ] [ [ , ] FILE = { file_number | @file_number } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] [ ,...n ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] KEEP_REPLICATION ] [ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] RESTART ] [ [ , ] STATS [= percentage ] ] [ [ , ] STOPAT = { date_time | @date_time_var } | [ , ] STOPATMARK = 'mark_name' [ AFTER datetime ] | [ , ] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ] ] ]

【任务10.36】使用存储过程恢复整个数据库myDB。 RESTORE DATABASE myDB FROM myDB_bak