第5章 数据库管理与保护 2021/6/71数据库管理系统对数据库提供一些监控和管理功能，以保证整个系统的正常工作和实现异常情况下的数据保护，防止数据意外丢失或产生不一致数据，限制数据的非法访问和使用等。主要管理和保护技术包括：v并发控制v数据库恢复v完整性控制v安全性控制 这些技术以事务为基础。2021/6/725.1使用事务保持数据完整性2021/6/73【例例5.15.1】修改修改SchoolSchool的的StudentStudent表，将所有女生是表，将所有女生是否住校否住校LiveInDomLiveInDom设置为设置为1 1。从从用用户户的的观观点点来来看看，对对数数据据库库的的某某些些操操作作应应该是一个整体，不能分割。该是一个整体，不能分割。5.1.1事务概念这这条条语语句句影影响响表表中中多多行行数数据据，必必须须保保证证对对表表中中所所有有符符合合条条件件的的数数据据要要么么都都修修改改，要要么么都都不不修修改改，否则数据库中的数据将处于不一致状态。否则数据库中的数据将处于不一致状态。 UPDATE UPDATE Student Student SET SET LiveInDom=1 LiveInDom=1 WHERE WHERE Sex=Sex=女女 2021/6/74【例例5.25.2】完成一个学生选课的操作（学号为完成一个学生选课的操作（学号为“1103”1103”的学的学生选课号为生选课号为“106”106”的课程），该操作包括两步：将学号的课程），该操作包括两步：将学号（StudentCodeStudentCode）、课程号（）、课程号（CourseCodeCourseCode）添加到）添加到GradeGrade表；表；将将CourseCourse表中相应课程的剩余名额（表中相应课程的剩余名额（LeftSeatsLeftSeats）减）减1 1。学生选课操作需要两条SQL语句完成：INSERT INTO Grade(StudentCode,CourseCode) VALUES(1103, 106)UPDATE Course SET LeftSeats=LeftSeats-1 WHERE CourseCode=106这两条SQL语句如果其中有一条没有正确执行，那么数据库中的信息就会不一致。所以必须保证这两条SQL语句的整体性。为解决为解决例例5.15.1、例、例5.25.2类似的类似的问题，问题，数据库管理系统引入了数据库管理系统引入了事务事务机制。机制。2021/6/75事务（Transaction）是一个包含了一组数据库操作命令的序列，所有的命令作为一个整体，一起向系统提交或撤销，操作命令要么都执行，要么都不执行。因此，事务是一个不可分割的逻辑工作单元，是数据库运行的最小逻辑工作单位。1事务的定义在在关关系系数数据据库库中中，一一个个事事务务可可以以是是一一条条SQLSQL语语句，也可是一组句，也可是一组SQLSQL语句或整个程序。语句或整个程序。事事务务和和程程序序是是两两个个不不同同的的概概念念。一一般般来来讲讲，事事务务使使用用专专门门的的命命令令来来定定义义, ,它它蕴蕴含含在在程程序序当当中中，一个程序中可包含多个事务。一个程序中可包含多个事务。2021/6/762事务的特性（ACIDACID特性）特性） (2)事务的事务的一致性一致性（ConsistencyConsistency）指事务在完成时，必须使所有的数据都保持一致状态，仍然指事务在完成时，必须使所有的数据都保持一致状态，仍然满足相关约束规则，以保持所有数据的完整性。满足相关约束规则，以保持所有数据的完整性。 (1)事务的事务的原子性原子性（AtomicAtomic）组成一个事务的多个数据库操作是一个不可分隔的单元，只组成一个事务的多个数据库操作是一个不可分隔的单元，只有所有的操作执行成功，整个事务才被提交。如任何一有所有的操作执行成功，整个事务才被提交。如任何一个操作失败，已经执行的操作都必须撤销，让数据库返个操作失败，已经执行的操作都必须撤销，让数据库返回到该事务执行前的初始状态。回到该事务执行前的初始状态。(3)事务的事务的隔离性隔离性（IsolationIsolation）指当有多个事务并发执行时，彼此互不干扰，与它们先后单指当有多个事务并发执行时，彼此互不干扰，与它们先后单独执行时的结果一样。独执行时的结果一样。(4)事务的事务的持久性持久性（DurabiliyDurabiliy）指一个事务完成之后，它对于数据库的所有修改永久性有效，指一个事务完成之后，它对于数据库的所有修改永久性有效，即使出现系统故障造成数据错误或丢失也能恢复。即使出现系统故障造成数据错误或丢失也能恢复。 2021/6/77事务的事务的ACID原则保证了一个事务或者提原则保证了一个事务或者提交后交后成功执行成功执行，或者提交后，或者提交后失败回滚失败回滚，二者，二者必居其一；事务对数据的修改具有可恢复性，必居其一；事务对数据的修改具有可恢复性，即当事务失败时，它对数据的修改都会恢复即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。到该事务执行前的状态。如果使用批处理，则有可能有的语句被如果使用批处理，则有可能有的语句被执行，而另外一些语句没有被执行的情况，执行，而另外一些语句没有被执行的情况，从而会造成数据的不一致，使用事务可以避从而会造成数据的不一致，使用事务可以避免这种情况的发生。免这种情况的发生。 2021/6/785.1.2事务的应用数据库管理系统可按照系统默认的规定自动划分事务并强制管理，一般一条语句就是一个事务。 用户可根据数据处理需要自己定义事务。例5.2的操作可定义为一个事务。例5.1中的UPDATE语句，系统自动通过事务保证该语句正确完成对所有女生的住校与否字段LiveInDorm的值都设置为1。 在定义事务时要注意所定义的数据修改顺序要与业务规则一致，这样，通过数据库管理系统强制该事务执行后，就可以保持数据的一致性。 2021/6/79在SQL语言中，事务的定义用以下命令完成： 开始事务。标识一个事务的开始。BEGIN TRANSACTION 提交事务。如果所有的操作都已完成，向系统提交事务来结束事务。COMMIT TRANSACTION 取消事务。也称为回滚事务，即结束当前事务，并放弃自事务开始以来的所有操作。ROLLBACK TRANSACTION事务提交之后，所有的修改将会生效，在没有提交之前，事务提交之后，所有的修改将会生效，在没有提交之前，所有的修改都可以通过回滚事务来撤销。即只有执行到所有的修改都可以通过回滚事务来撤销。即只有执行到COMMIT TRANSACTION命令时，事务中对数据库的更新命令时，事务中对数据库的更新才算确认。才算确认。2021/6/710【例5.3】利用事务机制完成例5.2的学生选课操作。BEGIN TRANSACTION /*开始一个事务开始一个事务*/*插入选课信息到插入选课信息到Grade表中表中*/INSERT INTO Grade(StudentCode,CourseCode) VALUES(1103,106)/*检查更新成功否，全局变量检查更新成功否，全局变量ERROR返回上一个返回上一个SQL语句状态，语句状态，非零即出错，则回滚之非零即出错，则回滚之*/IF ERROR = 0 PRINT 学生选课信息插入成功学生选课信息插入成功ELSE BEGIN PRINT 学生选课信息插入错误学生选课信息插入错误ROLLBACK TRANSACTION -回滚事务回滚事务 RETURN -返回返回 END/*更新更新Course表剩余名额表剩余名额*/UPDATE Course SET LeftSeats=LeftSeats-1 WHERE CourseCode=106IF ERROR = 0 PRINT 选课人数修改正确选课人数修改正确ELSE BEGIN PRINT 选课人数修改错误选课人数修改错误ROLLBACK TRANSACTION -回滚事务回滚事务 RETURN -返回返回 ENDCOMMIT TRANSACTION -提交事务提交事务2021/6/711在查询编辑窗口编辑该事务代码来检验事务的执行情况: 第一次执行正常 第二次执行，因为向Grade插入重复记录，记录不能插入，Course表的名额修改也不进行。修改代码，换一个学号，并将LeftSeats=LeftSeats-1为LeftSeats=LeftSeats-2，向Grade插入记录正确，但Course表修改记录失败（由于Course表的触发器（例4.65）要求LeftSeats= TotalSeats-选修该课程的人数） ，对Grade的插入操作也被回滚，两个表都不发生变化。2021/6/7125.1.3 SQLServer的事务管理机制强强制制管管理理：强制保持事务的原子性和一致性。事务启动之后，就必须成功完成，否则SQL Server将撤消该事务启动之后对数据所作的所有修改。 SQL Server通过强制事务管理和事务处理，保证每个事务符合ACID特性。主要管理机制有：锁锁定定机机制制：通过加锁使事务相互隔离，保持事务的隔离性，支持多个事务并发执行。 记记录录机机制制：将事务的执行记录在事务日志文件中，保证事务的持久性。当系统故障时，可通过回滚事务恢复数据。 2021/6/7135.2使用锁定机制实现并发访问控制2021/6/7145.2.1并发访问问题在一个多用户的数据库系统中，可并行运行多个事务并同时访问数据库，而且可能同时操作同一张表、甚至同一条记录、同一数据项。一种简单的方法是让这些事务排队依次执行，这会使系统效率非常低。如果允许数据库中的相同数据同时被多个事务访问，又没有采取必要的隔离措施，就会导致各种并发问题，破坏数据的一致性、完整性，在某些场合对数据的影响甚至是致命的。2021/6/715不加控制的并发访问会出现的问题： 丢失修改：当两个事务同时对同一数据修改时，最后只有一个生效，另一个修改丢失。 脏读 ：当一个事务恰好读取了另一个事务在回滚之前修改的数据，那么这个数据与数据库的实际数据不符合，该数据被称为“脏数据”。不可重读：一个事务读取某数据后，该数据被另一事务更新，当该事务再读该数据进行校验时，会发现数据不一致 。如，两个学生将各自的选课记录增加到选课表，然后恰好同时对剩余如，两个学生将各自的选课记录增加到选课表，然后恰好同时对剩余选课名额进行减去选课名额进行减去1 1的修改操作，如果只有一人修改有效的话，那么剩的修改操作，如果只有一人修改有效的话，那么剩余名额将与实际不符余名额将与实际不符。例如，一个选课事务修改了剩余选课名额，但由于异常事务被回滚取例如，一个选课事务修改了剩余选课名额，但由于异常事务被回滚取消，而恰在回滚前读取到剩余名额的另一事务就获取了消，而恰在回滚前读取到剩余名额的另一事务就获取了“脏数据脏数据”。例如，一个事务读取剩余选课名额，然后另一事务修改了剩余选课名额，例如，一个事务读取剩余选课名额，然后另一事务修改了剩余选课名额，当该事务再读剩余名额进行校验时，与刚读到的数据不一致。当该事务再读剩余名额进行校验时，与刚读到的数据不一致。2021/6/716系统不允许其他用户对当前锁定的数据资源产生负面影响的操作。5.2.2并发访问控制1锁定机制实现并发控制锁用于表明用户与资源有某种相关性，由数据库管理系统在内部管理，并基于用户所执行的操作分配和释放。锁定管理包括：加锁、锁定和解锁。数据库管理系统通过锁定机制避免并发访问所带来的数据不一致问题。 事务在访问某数据对象之前，向系统申请加锁，被锁事务在访问某数据对象之前，向系统申请加锁，被锁定的数据对象在被其它事务访问时受到限制，待事务完成定的数据对象在被其它事务访问时受到限制，待事务完成后锁被释放。后锁被释放。2021/6/717为了优化系统的并发性，根据事务的大小为了优化系统的并发性，根据事务的大小和系统活动的程度，支持多个锁定粒度来锁定和系统活动的程度，支持多个锁定粒度来锁定不同范围的数据资源。不同范围的数据资源。资资 源源描描 述述行行锁定表中的一条记录页页对一个数据页或索引页锁定，每页大小8 KB 簇簇对相邻的8个数据页或索引页构成的一组数据锁定表表对包括所有数据和索引在内的整个表锁定数据库数据库对整个数据库锁定2SQL Server的锁粒度锁定在锁定在较小较小的粒度（例如行）的粒度（例如行），事务的事务的等待时间减少等待时间减少，增加系统增加系统并发访问能力并发访问能力，但但需要较大的系统开销需要较大的系统开销，因为锁定多行需要控制更多因为锁定多行需要控制更多的锁；的锁；锁定在锁定在较大较大的粒度（例如表），限制了其它事务的访问内部对象，的粒度（例如表），限制了其它事务的访问内部对象，使使并发性降低并发性降低，但但需要维护的锁较少需要维护的锁较少，系统开销小系统开销小。2021/6/718锁模式锁模式描描 述述共享锁(S)在不更改或不更新数据的操作（只读操作）时使用，如 SELECT 语句。有共享锁存在时，任何其他事务不能修改数据，一旦读数据结束，释放共享锁更新锁(U)用于可更新的资源中。更新锁在修改数据时可以升级为独占锁，在没有修改操作时降为共享锁。一次只允许有一个事务可获得资源的更新锁。独占锁(X)用于数据修改操作，如 INSERT、UPDATE 或 DELETE，确保不会同时对同一资源进行多重更新意向锁(IS、IX、SIX）表示SQL Server需要在层次结构中的某些底层资源（如表中的页或行）上获取共享锁或排他锁。防止其他事务对某个数据单元加独占锁结构锁在执行对表结构操作时使用，当执行 DDL语言时（增加列或删除表），使用结构修改(Sch-M)锁，其他情况(如编译查询)使用结构稳定性(Sch-S)锁。大容量更新锁(BU)向表中大容量复制数据并指定了 TABLOCK 提示时使用3SQL Server的锁模式锁模式用于确定并发事务访问资源的方式2021/6/719不同的锁模式锁定强度不同，适用场合不同。SQL Server还控制锁模式的兼容性。 一般来说，读（SELECT）操作使用共享锁（S锁），写（UPDATE,INSERT和DELECT）操作使用独占锁（X锁）。更新锁（U锁）是建立在页级上的，它在一个更新操作开始时获得，当要修改这些页时，U锁会升级为X锁。2021/6/720系统可以检测死锁，即两个事务互相阻塞的情况。如果检测到死锁，SQL Server将终止一个事务，以使另一个事务继续。SQL Server会自动对事务处理范围内所需要的数据资源执行锁定。但用户也可在应用程序中通过SQL语句或数据库访问API（如ADO.NET）来定义锁定模式、粒度、持续时间等，从而达到更符合用户需要的并发控制功能。2021/6/7215.3 数据库备份和恢复 2021/6/722备份或还原操作中使用的磁盘或磁带机称为备份设备。在创建备份时，可以直接将备份文件创建到指定的磁盘文件位置，也可以创建到事先定义的备份设备上。数据库管理系统通过事务的原子性和持久性来保证数据的可恢复，利用备份和恢复技术可以把数据库恢复到发生故障前某一时刻的正确状态。5.3.1 数据库备份和恢复概述 备份：用来还原和恢复数据库的资料副本称为备份。备份的本质是备份的本质是“冗余冗余”，即数据的重复存储。，即数据的重复存储。数据库备份的内容包括数据库中的所有对象。数据库备份的内容包括数据库中的所有对象。 2021/6/7231数据库备份类型 (1)完整数据库备份根据数据库的容量和数据重要性可选择不同的备份类型，制定备份策略。SQL Server提供了三种不同的备份类型：完整备份、差异备份和事务日志备份。完整备份是对所有数据库对象和事务日志中的事务进行备份。使用完整备份可重建或恢复数据库到备份时刻的数据库状态。 完整备份因为是对全库备份，所以占用存储空间比较大，备份完成所需时间较长，如果数据库较大，不宜频繁使用。2021/6/724与完整备份相比，差异备份较小且速度快，便于进行较频繁的备份。但在还原差异备份之前，必须先还原作为“基准”备份的完整备份。(2)数据库差异备份差异备份仅记录自上次完整备份后更改过的数据。完整备份是差异备份的“基准”。(3)事务日志备份事务日志备份是对数据库发生的事务进行备份，包括从上次进行事务日志备份、数据库完整备份、差异备份之后所有已经完成的事务。在完整备份和差异备份恢复之后，进一步依据事务日志备份可以恢复到该日志备份中的某一时刻。事务日志备份占用的资源比较小。2021/6/7252数据库恢复模式(2)完整恢复 (1)简单恢复 SQL Server三种恢复模式：(3)大容量日志恢复数据库恢复是与备份相对应的操作，一旦数据库出现问题，就可以使用其备份文件将受损数据库恢复到破坏前某时刻的状态，使数据的损失减到最小。 另外还可以使用恢复技术在另一个服务器上重建数据库，实现数据库的复制或移动。2021/6/726依据完整备份和差异备份恢复数据库到最后一次备份的执行时刻。该策略操作简单，但备份操作点之后的数据丢失，需要重建，适用于规模较小或数据不经常改变的数据库。(1)简单恢复 备份时选“简单恢复”策略，备份将只简略地记录大多数事务，所记录的信息只是为了确保在系统崩溃或还原数据备份时使用。 2021/6/727依据完整备份、差异备份和事务日志备份将数据库恢复到任意的时刻点，包括故障时间点（假定在故障发生之后备份了日志尾部）。该恢复操作复杂，并且需要大量磁盘空间，对操作频繁且数据恢复非常重要的数据库才使用该策略。(2)完整恢复 备份时选“完整恢复”策略，将完整地记录所有的事务，并保留所有的事务日志记录，供还原数据库时使用。2021/6/728(3)大容量日志恢复依据完整备份、差异备份和事务日志备份恢复数据库。该模式允许使用较小日志空间对大容量复制操作的恢复。 备份时选“大容量日志恢复”策略，将简略地记录大多数大容量操作（例如，索引创建和大容量加载），完整地记录其他事务，节省日志空间，但没有“完整恢复”安全。 2021/6/7295.3.2 SQL Server数据库备份和恢复方法【例例5.4】将将School数据库完整备份到磁盘文件数据库完整备份到磁盘文件C:BakSchoolDB.bak。1使用Management Studio备份数据库 右击“School数据库”，选择“任务/备份” 菜单。在“备份数据库”窗口有“常规”和“选项”两个选项页。在“常规”页的上部，可以选择备份数据库“School”、恢复模式“SIMPLE”、备份类型“完整”、备份组件“数据库”。中间可以设置备份集的名称、说明以及备份集过期时间。2021/6/730 在“常规”页的下部可以设置备份的目标，默认值为“磁盘”上SQL Server安装目录下的Backup文件夹，文件名为“数据库名.bak”；如果不使用该文件，可先删除，然后单击“添加”按钮，打开“选择备份目标”对话框，可选中“文件名”单选按钮来指定文件名和路径，本例为“C:BakSchoolDB.bak” 。 “选项”页上部是“覆盖媒体”，选择“覆盖所有现有媒体集”会覆盖原有文件，如果选择“追加到现有备份集”则在原有备份文件基础上增加新的备份副本。 单击“确定”按钮执行备份，左下角的进度窗格显示备份正在执行的动态环，完成后会弹出完成对话框。在磁盘上可以找到文件“C:BakSchoolDB.bak”。2021/6/7312使用Management Studio还原数据库【例5.5】使用School数据库完整备份恢复数据库。 在对象资源管理器中，右击“数据库”，选择“还原数据库”菜单，打开“还原数据库”对话框。 在“常规”页，“还原的目标”目标数据库处选择“School”，目标的时间点“最近状态”；在“还原的源”选项区中，选择源数据库“School”。如果该数据库已经执行了备份，那么在下部表格就会显示备份历史，从中选择用于还原的备份集。 在“选项”页，进行其他设置。 按“确定”按钮执行还原操作，还原操作成功完成后会弹出提示对话框。可以使用备份文件将数据库还原到原数据库上，也可以还原为一个新的数据库；可以还原到本服务器上，也可以还原到其他服务器上。2021/6/732【例5.6】用School数据库的完整备份文件新建数据库Education，数据库文件存放D:。 在对象资源管理器中，右击“数据库”，选择“还原数据库”菜单，打开“还原数据库”对话框。 在“常规”页，“还原的目标”的目标数据库处输入“Education”；在还原的源选项区中，选择源设备，点击“”按钮，弹出指定备份对话框，添加备份文件“C:BakSchoolDB.bak”。返回“还原数据库”对话框，选中用于还原的备份集。 在“选项”页，在“将数据库文件还原为”处重新设置还原为的目标地址和文件名，本例数据库文件和日志文件分别设置为“D:Education.mdf” “D:Education_log.ldf”。按“确定”后即建立数据库Education。2021/6/7331. 正在使用的数据库不能被还原。提示：提示：2. 当异地还原数据库时，必须在“选项”页重新设置“还原为”的文件位置和文件名，因为默认的还原路径和文件可能不存在，无法实现还原。 2021/6/7343数据导入和导出通通过过导导入入和和导导出出操操作作可可以以在在SQL Server数数据据库库和和其其他他数数据据源源（例例如如另另一一个个SQL Server数数据据库库、Oracle数数据据库库或或Excel文文件件等等）之之间间转转移移数据。数据。“导导出出”是是将将数数据据从从当当前前SQL Server数数据据库库复制到其他数据源。复制到其他数据源。“导导入入”是是将将其其他他数数据据源源的的数数据据加加载载到到当当前前SQL Server数据库。数据库。2021/6/735【例5.7】将数据库School中班级表Class和课程表Course的数据导出到Excel文件E:Class.xls。 在“对象资源管理器”中右击数据库“School”，选择“任务/导出数据”菜单，进入“SQL Server导入和导出向导”首页，跟随向导一步一步完成数据导出。 分别在数据源窗口和目的窗口完成设置。选择数据源为SQL Native Client，服务器为本机名称或 “.”，数据库为“School” 。选择目标文件为“Microsoft Excel”，文件名为“E:Class.xls” 。2021/6/736 在指定表复制或查询窗口选择“复制一个或多个表或视图的数据” 。在选择源表和源视图窗口中，选中要导出的数据表Class和Course。点击“预览”按钮可查看将导出的数据。 选择立即执行或保存SSIS 包以后执行。 2021/6/737 选择立即执行或保存SSIS 包以后执行。按“完成”按钮开始执行导出，会显示导出进度和导出对象，成功完成后显示提示信息。可以在磁盘文件查看“E:Class.xls”，它包括两个工作表，即Class和Course。 2021/6/738说明：说明：1.数据导入过程和导出过程使用同一个向导，操作数据导入过程和导出过程使用同一个向导，操作过程相似。过程相似。2.数据导入数据导入/导出对象可以是表或视图，一次可导入导出对象可以是表或视图，一次可导入或导出多个对象。或导出多个对象。3.注意数据源和目标的格式要兼容，例如注意数据源和目标的格式要兼容，例如Student表在导出到表在导出到Excel文件时，不能包含定义为文件时，不能包含定义为image类型的类型的Photo字段。字段。提示：如果导出有问题，可先在提示：如果导出有问题，可先在Excel中创建一个空文件中创建一个空文件D:Class.xls后将该文件关闭。然后再到后将该文件关闭。然后再到SQL Server中进中进行数据表的导出。行数据表的导出。2021/6/739SQL Server提供了以数据库文件复制和加载数据库的方法，可作为数据库转移或数据备份功能来使用，但在复制数据库前数据库必须脱离SQL Server。【例5.8】将School数据库分离，然后将数据库文件和日志文件复制到C:Bak，再附加该数据库。 分离数据库。右击 “数据库/School”，选择“任务/分离” 菜单。 在“分离数据库”对话框中，当状态显示“就绪”时，单击“确定”按钮，即完成分离。已分离的数据库School从“对象资源管理器”中被删除。4附加和分离数据库2021/6/740 复制数据库文件。利用Windows操作系统中的文件复制功能将数据库文件和事务日志文件（“School_Data.MDF”和“School_log.LDF”）拷贝到“C:Bak”。 附加数据库。右击“数据库”，选择“附加” 菜单。在“附加数据库”对话框中，单击“添加”按钮。在“定位数据库文件”对话框中选择要附加的数据库文件名“C:BakSchool_Data.MDF”。 “School数据库详细信息”列出了数据库中的所有文件（数据文件和日志文件）的详细信息。在“附加为”框内，默认为 “School”，也可输入新的数据库名，但要注意数据库名不能与SQL Server下任何现有数据库重名。单击“确定”按钮完成附加。新附加的数据库School即出现在“数据库”文件夹中。2021/6/741提示：提示：1.在分离数据库之前需关闭所有与数据库连接的在分离数据库之前需关闭所有与数据库连接的应用程序、操作窗口，如果存在应用程序、操作窗口，如果存在“活动连接活动连接”，则处于，则处于“未就绪未就绪”状态，不能分离。状态，不能分离。2.分离数据库只是数据库脱离分离数据库只是数据库脱离SQL Server管理，管理，并并不是将数据库从磁盘上删除不是将数据库从磁盘上删除。2021/6/7425.4数据库安全性控制2021/6/743数据库安全性控制指保护数据库，防止因用户非法使用数据库造成数据泄露、更改或破坏。SQL Server数据库安全控制技术主要是用户访问控制,另外视图、审计和加密等技术的应用也可以提高数据安全性。本节主要介绍用户访问控制技术。2021/6/7445.4.1 SQL Server 登录管理1SQLServer的用户访问控制SQL Server 提供了对数据库及所包含数据对象的用户访问控制，在三个层次上进行安全管理 。 用用户户身身份份验验证证。首先，用户必须通过身份验证来登录SQLServer。 数数据据库库的的访访问问权权限限控控制制。用户对SQLServer上特定数据库必须有权限才能访问。 数数据据库库中中对对象象的的访访问问权权限限控控制制。用户对数据库中的特定对象必须有权限才能访问，进行符合权限的数据存取操作。2021/6/7452SQL Server的身份验证（1）SQL Server的身份验证模式SQL Server提供了两种安全验证模式，即Windows身份验证模式和SQL Server身份验证模式。 Windows身份验证模式（集成身份验证模式）：身份验证模式（集成身份验证模式）：允许一个已登录Windows的用户不必再提供一个单独的登录账号就能登录到该系统中的一个SQL Server服务器，从而实现SQL Server服务器与Windows登录的安全集成。 SQL Server身份验证模式身份验证模式 ：要求用户必须输入一个SQL Server用户名及口令。这个用户名是独立于操作系统、在SQL Server中建立的，从而可以在一定程度上避免操作系统层上对数据库的非法访问。2021/6/746 在“安全性”页。“服务器身份验证”区域有两个选项，“Windows身份验证模式(W)”和“SQL Server和Windows身份验证模式(S)”，选中某一个按“确定”按钮设置完成。（2）设置身份验证模式 在“对象资源管理器”中右击需要设置的SQL Server服务器，选择“属性”菜单，弹出“服务器属性” 对话框。提示：提示：如果选择前者，只能采用如果选择前者，只能采用WindowsWindows身份验证；选身份验证；选择后者为混合身份验证模式，可以在登录时再选择择后者为混合身份验证模式，可以在登录时再选择是使用是使用“WindowsWindows身份验证模式身份验证模式”还是还是“SQL SQL ServerServer身份验证模式身份验证模式”。 转换模式需重新启动转换模式需重新启动SQL ServerSQL Server服务（使用服务（使用SQL SQL Server Configuration ManagerServer Configuration Manager启动）启动）。2021/6/747（3）登录账号管理在SQL Server中，系统已经自动建立了一些内置账号。登录账号的信息是系统信息，存储在master数据库中。展开“对象资源管理器”中的“安全性”选项，可以看到内置账号信息： sa被称为系统管理员（System Administrator），拥有SQL Server全部权限，可以执行所有的操作。 Builtin/Administrators是为Windows操作系统系统管理员提供的默认账号，拥有SQL Server全部权限，可执行所有操作实际的使用过程中，管理员需要添加一些登录账号以赋予用户以有限的访问权限。用户可以将Windows账号添加到SQL Server中，也可以新建SQL Server账号。2021/6/748【例例5.105.10】 创建一个名称为创建一个名称为“WanghongWanghong”的的SQL ServerSQL Server用户用户帐号，其密码为帐号，其密码为“123123”，默认访问的数据库为，默认访问的数据库为SchoolSchool。 在“对象资源管理器”中，右击“安全性/登录名”，选择“新建登录名” 菜单。在“登录名”对话框中，选择“SQL Server身份验证”单选钮，设置登录名“Wanghong”，密码为“123”；设置默认数据库“School。也可以通过“新建登录”中“服务器角色”和“用户映射”选项页设定该登录名的服务器角色和可访问的数据库，这样该登录账号同时也作为该数据库的合法用户。 2021/6/749说明说明：如果用户想使用“Wanghong”登录SQL Server，必须先设置SQL Server的身份验证方式为“SQL Server和Windows身份验证模式”。设置成功后，当用户连接数据库时，选择“SQL Server 身份验证”，可用账号“WangHong”登录SQL Server。登录后，由于“Wanghong”可以访问的默认数据库为School，但对系统其他数据库无访问权限，甚至无法展开查看，例如访问SchoolTest，会报错。 删除数据库账户使用Management Studio也可以删除数据库帐户。步骤为：展开“服务器/安全性/登录名”，右击要删除的登录名，选择“删除”菜单即可。2021/6/7505.4.2 数据库级管理1数据库用户及其权限管理（1）数据库用户SQL Server用户帐号建好后，需要映射为数据库用户，才能访问数据库。在建立用户帐号时，选择默认访问的数据库可以将账号映射为数据库用户。说明：说明：例例5.10建立的登录帐号建立的登录帐号 “Wanghong”即成为即成为School数据库的用户。也可以将数据库的用户。也可以将“Wanghong”映射为其他数据库的映射为其他数据库的用户。数据库的用户可以重新命名，但一般与用户。数据库的用户可以重新命名，但一般与SQL Server登录帐号采用同名。登录帐号采用同名。2021/6/751【例例5.115.11】 将将SQL ServerSQL Server用户帐号用户帐号“WanghongWanghong”映射为数据库映射为数据库SchoolTestSchoolTest的用户。的用户。在Management Studio中，选择要操作的数据库，展开“安全性/用户”，右击要删除的数据库用户，选择“删除”菜单即可。 创建用户：在“对象资源管理器”中，右击“数据库/SchoolTest/安全性/用户”，选择“新建用户” 菜单。在“新建用户”对话框中，用户名填写“Wanghong”， 登录名选择“Wanghong”，按“确定”后关闭窗口，即可在SchoolTest数据库用户列表中出现。 删除用户：2021/6/752数据库用户登录SQL Server具有访问该数据库的权限，但要访问具体的数据库对象，仍需要被赋予权限。（2）数据库用户权限管理数据对象数据对象操作权限操作权限表、视图SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGE列SELECT, UPDATE，Refrences 存储过程和函数EXECUTE， EXECUTE, View Definition, Alter, Control, Take Ownership, SQL Server中可访问的主要数据对象与操作权限中可访问的主要数据对象与操作权限2021/6/753【例例5.125.12】为为SchoolSchool数数据据库库的的用用户户“Wanghong”Wanghong”设设置置权权限限：对对StudentStudent表表具具有有SelectSelect、InsertInsert权权限限，禁禁止止执执行行DeleteDelete操操作作，仅对仅对“TelephoneTelephone列具有列具有UpdateUpdate权限。权限。 授予权限授予权限：表示授予该项操作权限，但可被用户所属的其他角色的拒绝权限覆盖。 具有授予权限具有授予权限：使具有授予权限。点击后解除“拒绝权限”，并设置“授予权限”。 拒绝权限拒绝权限：表示禁止该项操作权限，并覆盖用户从所属的其他角色获得的权限。 未设置未设置:表示不能执行该项操作，但可被用户所属的其他角色的权限覆盖。SQL Server权限管理包括以下操作：2021/6/754首先使用“Windows身份验证”或“SQL Server身份验证”的sa用户登录。在“对象资源管理器”中，选中要设置权限的数据库对象（例如表、视图等），选择“属性”窗口在权限页中添加用户“Wanghong”，并设置权限。 右击“数据库/School/安全性/用户/Wanghong”，选择“属性” 菜单，出现“数据库用户”窗口，选择“安全对象”选择页。 点击“添加”按钮，弹出“添加对象”对话框，选择“特定类型的所有对象”；在“选择对象类型”对话框中选择“表” 。2021/6/755 返回“数据库用户”窗口，在“安全对象”列表中选中“Student”,在“显式权限”列表中选中“Select”、“Insert”行， 在“授予”处点击出现“”表示授予该操作权限；选中“Delete”行，在“拒绝”处点击出现“”表示禁止该操作；选中“Update”行，点击“列权限”按钮，出现列权限窗口，选中“Telephone”行，在“授予”处点击出现“”表示授予该操作权限。 点“有效权限”按钮，可查看“Wanghong”对“Student”表的所有操作权限。2021/6/756（3）特殊数据库用户）特殊数据库用户 SQL Server为每个新创建的数据库预定义了几个特殊数据库用户 。 dbo。dbo是数据库所有者（DataBase Owner），是数据库最高权力所有者，创建数据库的用户即为数据库所有者。具有所有数据库操作权限，并可向其他用户授予权限。dbo不能被删除。 Guest。允许具有SQL Server登录帐户但在数据库中没有数据库用户的使用者用Guest的用户身份和权限访问数据库。Guest可以删除。例如：例如：用“Windows身份验证”或SQL Server的“sa”账号登录后都自动映射为dbo，对所有数据库及其包含的对象具有全部操作权限。2021/6/7572数据库角色及其权限管理按赋予操作权限的不同建立角色，然后将用户作为角色成员，通过对角色的权限管理，自动实现对其所有用户成员的权限管理。 SQL Server可利用角色来批量管理用户的权限，简化安全管理。一个用户的访一个用户的访问权限集合问权限集合是其所属每个是其所属每个角色的角色的权限权限和其和其自身权限自身权限 的累加。的累加。2021/6/758SQL Server预定义了5个角色。 publicpublic角角色色。每个数据库（包括系统数据库）中都存在。public角色提供数据库中用户的默认权限，不能删除。每个数据库用户都自动是此角色的成员，因此，无法在此角色中添加或删除用户。固固定定服服务务器器角角色色。固定服务器角色可以在服务器上进行相应的管理操作，完全独立于某个具体的数据库。系统提供了8种固定服务器角色。固固定定数数据据库库角角色色：系统提供了10种固定数据库角色，设置对数据库的主要管理权限。用用户户自自定定义义数数据据库库角角色色：根据系统开发和管理的需要为特定数据库自定义数据库新角色。应应用用程程序序角角色色：将数据访问权限授予使用特定应用程序的那些用户。即授权用户使用该应用程序时对该数据对象有访问权限，但其他时候不能访问。 2021/6/759角色角色该角色成员的操作权限该角色成员的操作权限sysadmin系统管理员可以执行SQLServer系统中任何操作serveradmin服务器管理员可以配置和关闭SQLServer服务器setupadmin安装管理员可以添加和删除链接服务器，并执行某些系统存储过程配置与安装有关的设置securityadmin安全管理员管理服务器安全配置和登录，可以创建和删除系统登录帐户、控制创建数据库权限、重设密码等processadmin进程管理员管理在SQLServer实例中运行的进程dbcreator数据库创建者可以实施数据库创建、修改、删除和恢复等数据库修改操作diskadmin磁盘管理员管理和重配数据库存储设备，包括数据文件、日志文件等磁盘文件bulkadmin块拷贝管理员可以执行向数据库的块插入操作表表5.6 SQL Server 固定服务器角色固定服务器角色2021/6/760表表5.7固定数据库角色固定数据库角色角色角色该角色成员的操作权限该角色成员的操作权限public每一用户都自动成为该角色成员，无需指派，不能删除db_owner数据库所有者可执行数据库中的任何操作db_accessadmin数据库访问权限管理者可以增加或删除数据库用户、组和角色db_ddladmin数据库DDL管理员可以增加、修改或删除数据库对象，即可执行DDL语句db_securityadmin数据库安全管理员管理数据库中对象的访问权限和执行语句的权限，例如数据库表的插入、删除、修改操作等db_backupoperator数据库备份操作员实施数据库备份和恢复db_datareader数据库数据读取者可检索数据库中任意表中的数据，即具有Select许可，db_datawriter数据库数据写入者有增加、修改和删除所有表中数据的权限，即有Insert、Update、Delete许可db_denydatareader数据库拒绝数据读取者不能检索任意一个表中数据db_denydatawriter数据库拒绝数据写入者不能修改任意一个表中的数据2021/6/761【例5.13】为School数据库创建一个名称为“Teacher”的数据库角色，并将“Wanghong”添加为其成员。设置角色“Teacher”对Student表具有所有权限。 右击“数据库/School/安全性/角色/数据库角色”，选择“新建数据库角色” 菜单。在角色名称文本框中填写角色名称“Teacher”；“拥有此角色的架构”选“dbo”。 在下部点击“添加”按钮，出现“选择数据库或角色”对话框，浏览添加数据库用户“Wanghong”，按“确定”后返回 “数据库角色窗口”。2021/6/762 选择“安全对象”选项页，为“Teacher”设置权限。2021/6/763通过例5.12和例5.13的操作，数据库用户“Wanghong”实际具有的权限是该用户被授予的权限和其所属于角色“Teacher”具有的权限累加。“Wanghong”对Student表的操作权限：有SELECT、INSERT、UPDATE操作权限禁止DELETE操作2021/6/7645.4.3其他数据库安全性控制1视图机制2审计方法3数据加密为不同的用户定义不同的视图，通过视图机制把要保密的数据对无为不同的用户定义不同的视图，通过视图机制把要保密的数据对无权操作的用户隐藏起来，从而自动地对数据提供一定程度的安全保护，权操作的用户隐藏起来，从而自动地对数据提供一定程度的安全保护，对视图也可以进行授权，使不同的用户看到不同的视图。对视图也可以进行授权，使不同的用户看到不同的视图。 审计功能就是把用户对数据库的所有操作自动记录下来放入审计审计功能就是把用户对数据库的所有操作自动记录下来放入审计日志文件中，一旦发生数据被非法存取，可以利用审计跟踪的信息，日志文件中，一旦发生数据被非法存取，可以利用审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等间和内容等 。根据一定的算法将原始数据（称为明文）变换为不可直接识别的根据一定的算法将原始数据（称为明文）变换为不可直接识别的数据格式（称为密文），从而使得不知道解密算法的人即使进入数据数据格式（称为密文），从而使得不知道解密算法的人即使进入数据库也无法识别数据的内容库也无法识别数据的内容 2021/6/765部分资料从网络收集整理而来，供大家参考，感谢您的关注！