安全性管理 PPT课件

1、19.1 SQL Server的安全性机制 9.2 SQL Server的登录模式 9.3 SQL Server用户标识和鉴别 9.4 角色与权限 9.5 数据库的并发控制 第第9章章 SQL Server的安全性管理的安全性管理 第1页/共63页29.1 SQL Server的安全性机制的安全性机制 SQL Server数据库系统的安全机制 SQL Server的安全机制可划分为三个层次 数据库管理系统级安全性 控制用户登录到数据库管理系统的安全机制。 数据库级安全性 用户通过认证后，还需具备访问许可（Permission）权限才能够访问SQL Server 中的数据 权限设置通过用户账号来

2、实现以及使用角色代替用户组来简化安全性管理 数据库对象级安全性 在权限范围内对服务器上的数据库进行各种操作的权限 主要是针对数据库对象，如表、视图、存储过程和自定义函数等。 第2页/共63页3 数据库管理系统级安全性控制SQL Server 的登录认证 SQL Server支持两种认证方式 Windows认证模式 混合认证模式9.2 SQL Server的登录模式的登录模式第3页/共63页4 以Windows NT或2000的安全性来确定用户登录的账号是否正确 利用NT或2000本身具有的管理登录、验证用户合法性的能力 允许SQL Server使用NT或2000的用户名和口令登录验证用户的合法

3、性 系统管理员将安全认证模式设为Windows认证模式，以授权给可访问NT或2000的合法用户，而直接成为SQL Server的合法登录用户 连接SQL Server时不需在输入账号和密码 在安装SQL Server后，系统中预设了三个账户，分别为 BUILTINAdministrators 网络名Administrator sa表示在表示在NT或或2000中只要属于中只要属于Administrators组的账号都允组的账号都允许登录许登录SQL Server网络域名网络域名Administrator表示所表示所属网络域中的属网络域中的Administrator账号账号才能登录才能登录SQL

4、Servern Windows认证模式认证模式第4页/共63页5 混合认证模式 由SQL Server自身来执行验证工作 Windows认证和SQL Server 认证两种认证模式都可用 SQL Server 认证模式 SQL Server 自己执行认证处理自己执行认证处理 用户在连接用户在连接SQL Server 时必须提供登录名和密码时必须提供登录名和密码 登录信息存储在系统表登录信息存储在系统表syslogins中中 若输入的登录信息与系统表若输入的登录信息与系统表syslogins 中的某条记录相匹配则表明中的某条记录相匹配则表明登录成功登录成功第5页/共63页6 注意下列三种情况 若

5、用户在登录若用户在登录SQL Server时未给出用户登录名，时未给出用户登录名，则则SQL Server会自动使用会自动使用 Windows认证模式认证模式 若若SQL Server被设置为被设置为Windows认证模式，则用认证模式，则用户在登录时输入的具体登录户在登录时输入的具体登录 名会被名会被SQL Server忽略忽略 若若SQL Server运行在运行在Windows 95/98桌面版上时，桌面版上时，则则Windows认证模式无效认证模式无效第6页/共63页7 通过SQL Server企业管理器进行认证模式的设置第7页/共63页8 用企业管理器新建SQL Server 登录用户

6、第8页/共63页9 若选择为Windows身份验证，可单击“名称”文本框后的“”按钮，打开Windows用户组和用户列表,选择一个用户名，单击“添加”及“确定”按钮，回到“新建登录”对话框中第9页/共63页10 注 选择Windows认证模式 在在“名称名称” 处输入的账号必须是处输入的账号必须是Windows已建的登录者或组已建的登录者或组 “名称名称”的格式为的格式为Windows网络名称网络名称用户名或用户名或Windows主机名主机名用户用户名名 选择Windows认证模式且使用了NT网络 在在“域域”中输入登录账号或组所属的域中输入登录账号或组所属的域 若未使用若未使用NT网络，则在

7、网络，则在“域域”中输入登录账号所属的中输入登录账号所属的NT主机名主机名 选择Windows认证模式且登录账号是Windows NT或Windows 2000中的内建用户组，如：Administrators 必须在必须在“域域”中输入中输入BUILTIN 而不是而不是NT主机名或主机名或NT网络域名网络域名第10页/共63页11 使用系统存储过程新建和管理SQL Server 登录sp_addlogin 功能功能创建新的使用创建新的使用SQL Server 认证模式的登录账号认证模式的登录账号 格式格式sp_addlogin 登录名登录名 ,登录密码登录密码,登录时缺省数据库登录时缺省数据库

8、,登录登录时缺省语言时缺省语言 说明说明登录名和密码最大长度为登录名和密码最大长度为128 个字符，可以是英文字母、数字，个字符，可以是英文字母、数字，但以下但以下3种登录名被视为无效种登录名被视为无效（1）登录名包括）登录名包括字符字符（2）登录名是一个保留名，如）登录名是一个保留名，如sa或或public或是已存在的登录名或是已存在的登录名（3）登录名不能为）登录名不能为NULL或一个空字符串或一个空字符串举例举例exec sp_addlogin FirstUser,123456,学生学籍数据库学生学籍数据库, us_englishsp_droplogin 功能功能删除登录账号，禁止其访问

9、删除登录账号，禁止其访问SQL Server格式格式sp_droplogin 登录名登录名说明说明不能删除系统管理员不能删除系统管理员sa以及当前连接到以及当前连接到SQL Server 的登录的登录举例举例sp_droplogin FirstUser 注：注：sp_addlogin和和sp_droplogin只能用在只能用在SQL Server 认证模式下认证模式下第11页/共63页12sp_grantlogin 功能功能 将将Windows用户或用户组设定为用户或用户组设定为SQL Server 登录者登录者格式格式 sp_grantlogin 登录名登录名说明说明 登录名需用登录名需用“

10、域域用户名用户名”或或“BUILTIN用户组名用户组名”给出给出举例举例将将Windows用户用户“a1”设定为设定为SQL Server 登录者登录者 exec sp_grantlogin S4033a1 续前表续前表第12页/共63页13sp_denyloginsp_denylogin 功能功能拒绝拒绝WindowsWindows用户或用户组连接到用户或用户组连接到SQL ServerSQL Server格式格式sp_denyloginsp_denylogin 登录名登录名 说明说明登录名需用登录名需用“域域 用户名用户名”或或“BUILTINBUILTIN用户组名用户组名”给出给出举例举

11、例例：拒绝例：拒绝WindowsWindows用户用户S4033a1S4033a1登录到登录到SQL ServerSQL Serverexec exec sp_denyloginsp_denylogin S4033a1 S4033a1 sp_revokelogisp_revokelogin n 功能功能废除废除WindowsWindows用户或用户组在用户或用户组在SQL ServerSQL Server上的登录权上的登录权格式格式sp_revokeloginsp_revokelogin 登录名登录名 说明说明若若WindowsWindows用户用户a1a1属于组属于组Group1Group1

12、，则只要则只要Group1Group1具有登录具有登录SQL Server SQL Server 的权限，即使执行了的权限，即使执行了sp_revokeloginsp_revokelogin，用户用户a1a1仍可登录到仍可登录到SQL ServerSQL Server。若用户若用户a1a1的登录权未被废除，即使的登录权未被废除，即使Group1Group1的登录权被废除，的登录权被废除，则该用户仍能登录则该用户仍能登录SQL ServerSQL Server。举例举例废除废除WindowsWindows用户用户S4033a1S4033a1登录到登录到SQL Server SQL Server

13、的访问权的访问权exec exec sp_revokeloginsp_revokelogin S4033a1 S4033a1sp_granloginsp_granlogin 和和 sp_revokeloginsp_revokelogin 只能用于只能用于WindowsWindows认证模式下，设定认证模式下，设定WindowsWindows用户用户或用户组账号，不能对或用户组账号，不能对SQL ServerSQL Server认证模式下的登录账号进行设定认证模式下的登录账号进行设定 续前表续前表第13页/共63页14sp_helplogins 功能功能显示显示SQL Server所有登录者的信

14、息，包括每个数据库中与该所有登录者的信息，包括每个数据库中与该登录者相对应的用户名登录者相对应的用户名格式格式 sp_helplogins 登录名登录名说明说明若未指定若未指定“登录名登录名”，则显示当前数据库中所有登录者的信，则显示当前数据库中所有登录者的信息（包括息（包括Windows登录者）登录者）举例举例 显示登录者显示登录者a1的登录信息的登录信息,sp_helplogins FirstUser 续前表续前表第14页/共63页15 数据库级安全性 数据库用户 用来指出哪一个用户可以访问哪一个数据库 在一个数据库中，用户账号唯一标识一个用户 用户对数据的访问权限以及对数据库对象的所有关

15、系都是通过用户账号来控制。 数据库中的用户账号与登录账号是两个不同的概念9.3 SQL Server用户标识和鉴别用户标识和鉴别第15页/共63页16 guest用户允许其以该用户账号访问数据库允许其以该用户账号访问数据库登录用户是否合法登录用户是否合法的数据库用户账号的数据库用户账号允许登录用户以允许登录用户以guest 用户来访问数据库用户来访问数据库是否有是否有guest用户用户该登录用户被拒绝该登录用户被拒绝有有有有无无无无第16页/共63页17 利用SQL Server企业管理器创建新数据库用户第17页/共63页18 在创建和维护SQL Server登录账号时，先将该登录账号映射为一

16、个数据库用户 用户账号总是基用户账号总是基于数据库的，故于数据库的，故两个不同数据库两个不同数据库中可以有两个相中可以有两个相同的用户账号同的用户账号 第18页/共63页19利用系统存储过程创建和管理新数据库用户sp_grantdbaccess 功能功能为为SQL Server登录者或登录者或Windows用户或用户组建立一用户或用户组建立一个相匹配的数据库用户账号个相匹配的数据库用户账号格式格式sp_grantdbaccess ,说明说明：SQL Server 登录名或登录名或Windows用户或用户用户或用户组。若用组。若用Windows用户或用户组，则必须给出用户或用户组，则必须给出Wi

17、ndows主机名或主机名或Windows网络域名。网络域名。登录账号或登录账号或Windows用户或用户组必须存在用户或用户组必须存在：登录账号相对应的数据库用户名。：登录账号相对应的数据库用户名。当前数据库中没有该数据库用户名，若不给出该参数当前数据库中没有该数据库用户名，若不给出该参数值，则值，则SQL Server 把登录名作为默认的缺省用户名把登录名作为默认的缺省用户名举例举例将用户将用户FirstUser映射到当前数据中，其用户名为映射到当前数据中，其用户名为FirstDBUserexec sp_grantdbaccess FirstUser,FirstDBUsersp_helpus

18、er 功能功能显示当前数据库的指定用户信息显示当前数据库的指定用户信息格式格式sp_helpuser 说明说明若不指出参数，则显示所有用户信息若不指出参数，则显示所有用户信息举例举例exec sp_helpuser第19页/共63页20sp_revokedbaccess 功能功能删除数据库用户删除数据库用户格式格式sp_revokedbaccess 说明说明将数据库用户从当前数据库中删除，其对应的登录者则将数据库用户从当前数据库中删除，其对应的登录者则无法使用该数据库无法使用该数据库若被删除的数据库用户在当前数据库中拥有任一对象，若被删除的数据库用户在当前数据库中拥有任一对象，如：表、视图、存

19、储过程，则无法删除，只有在删除其如：表、视图、存储过程，则无法删除，只有在删除其所拥有的对象后，才可以将数据库用户删除所拥有的对象后，才可以将数据库用户删除举例举例删除删除“学生学籍数据库学生学籍数据库”的用户的用户FirstDBUserExec sp_revokedbaccess FirstDBUser续前表续前表第20页/共63页21 数据库对象级安全性-权限和角色 将登录账户映射为数据库用户账户后，该用户可以访问该数据 其访问数据库的权限取决于其所属的角色以及所授予的权限 权限 规定该用户能够对哪些数据库对象执行哪（些）种操作，以及能够访问和修改哪些数据 角色 管理员可将某些用户设置为某

20、一角色，只对角色进行权限设置，便可实现对所有具有该角色的用户的权限的设置，减少了管理员的工作量 如 缺省情况下，一个新数据库用户的权限具有public 角色 public 是一个特殊的数据库角色，含在每个数据库中，包括 master、msdb、tempdb、model 和所有用户数据库 每个数据库用户都属于它，并且无法除去该角色 public 角色可以自动获取数据库用户的所有默认权限9.4 角色与权限角色与权限第21页/共63页22权限分类对象权限和语句权限对象权限 针对表、视图、存储过程决定了能对表、视图、存储过程执行哪些操作，包括UPDATE、DELETE、INSERT、EXECUTE 语

21、句权限 用户是否具有权限来执行某一语句这些语句通常是一些具有管理性的操作，如创建数据库、表、存储过程等第22页/共63页23对象权限对象对象操作类型操作类型表表SELECT、INSERT、UPDATE、DELETE、REFERENCES 视图视图SELECT、UPDATE、INSERT、DELETE 存储过程存储过程EXECUTE 列列SELECT、UPDATE n语句权限语句权限CREATE DATABASE创建数据库创建数据库 CREATE TABLE 创建表创建表CREATE VIEW 创建视图创建视图CREATE RULE 创建规则创建规则CREATE DEFAULT 创建默认值创建默

22、认值CREATE PROCEDURE 创建存储过程创建存储过程BACKUP DATABASE 备份数据库备份数据库BACKUP LOG 备份日志备份日志REFERENCES表表示允许别的表的所示允许别的表的所有者引用本表的列有者引用本表的列作为外键约束作为外键约束 第23页/共63页24利用企业管理器管理权限 面向单一用户的权限设置 第24页/共63页25面向数据库对象的权限设置 第25页/共63页26使用GRANT、REVOKE和DENY三种命令来管理权限GRANT ：把权限授予某一用户，使该用户对该对象可以执行操作或运行某些语句将语句权限授予用户将语句权限授予用户将对象权限授予用户将对象权

23、限授予用户GRANT ALL | ,nTO ,nGRANT ALL|,n(,) ON | ON (,n)| ON | ON TO ,n WITH GRANT OPTION ALL表示表示授予该用授予该用户所有的户所有的对象权限对象权限WITH GRANT OPTION 表示该权限的被表示该权限的被授予者可以向其它用户授予这些权限授予者可以向其它用户授予这些权限第26页/共63页27 REVOKE：取消用户对某一对象或语句的权限（经过GRANT 语句授予的），不允许该用户执行针对数据库对象的某些操作或不允许其运行某些语句 DENY ：禁止用户对某一对象或语句的权限，明确禁止其对某一对象执行某些操

24、作或运行某些语句 REVOKE 和 DENY 语法格式与GRANT 语法格式相同第27页/共63页28 例：使用GRANT命令将表对象权限授予用户-将对于职员表的查询、增删改的操作权限授予用户FirstDBUserGRANT SELECT,INSERT,UPDATE,DELETEON 职员表TO FirstDBUser 执行效果第28页/共63页29 例：使用GRANT命令将列权限授予用户-将查询办公室表和修改Address列的权限授予用户FirstDBUserGRANT SELECT,UPDATE(Address)ON 办公室TO FirstDBUser第29页/共63页30 执行效果第30

25、页/共63页31用角色统一授予和管理权限服务器角色固定服务器角色名固定服务器角色名 角色的权限角色的权限 Sysadmin 可在可在SQL Server 中执行任何操作中执行任何操作ServeradminSQL Server服务器范围内的配置服务器范围内的配置setupadmin增加、删除连接服务器，并执行某些系统存储过程增加、删除连接服务器，并执行某些系统存储过程Securityadmin管理管理数据库登录管理管理数据库登录processadmin管理管理SQL Server进程进程dbcreator创建数据库并修改数据库创建数据库并修改数据库diskadmin管理磁盘文件管理磁盘文件Bul

26、kadmin执行执行BULK INSERT（大容量插入）操作大容量插入）操作第31页/共63页32 使用企业管理器添加成员到固定服务器角色中使用企业管理器添加成员到固定服务器角色中 查看服务器角色权限查看服务器角色权限sysadmin角色的所有权角色的所有权限限第32页/共63页33 使用存储过程管理服务器角色的成员使用存储过程管理服务器角色的成员 sp_addsrvrolemember 功功能能将某一登录者加入到服务器角色中成为该角色的成员将某一登录者加入到服务器角色中成为该角色的成员格格式式sp_addsrvrolemember 登录者名登录者名, 服务器角色服务器角色举举例例将登录者将登

27、录者FirstUser加入到加入到sysadmin 角色中角色中EXEC sp_addsrvrolemember FirstUser,sysadminsp_dropsrvrrolemember 功功能能将某一登录者从某一服务器角色中删除使之不再具有将某一登录者从某一服务器角色中删除使之不再具有该服务器角色所赋予的权限该服务器角色所赋予的权限格格式式sp_dropsrvrolemember 登录者名登录者名,服务器角色服务器角色举举例例EXEC sp_dropsrvrolemember FirstUser, sysadmin第33页/共63页34 数据库角色 固定数据库角色固定数据库角色 SQL

28、 Server预定义的、具有所有管理和访问数据库的权限预定义的、具有所有管理和访问数据库的权限 SQL Server管理者不能对其所具有的权限进行任何修改管理者不能对其所具有的权限进行任何修改 SQL Server中的每一个数据库中都有一组固定数据库角色中的每一个数据库中都有一组固定数据库角色 数据库角色数据库角色第34页/共63页35固定数据库角色名固定数据库角色名 角色的权限角色的权限 db_owner数据库的所有者，可以执行任何数据库管理工作，可以对数据数据库的所有者，可以执行任何数据库管理工作，可以对数据库内的任何对象进行任何操作，如删除、创建对象、将对象权库内的任何对象进行任何操作，

29、如删除、创建对象、将对象权限指定给其它用户。该角色包含下面各角色的所有权限限指定给其它用户。该角色包含下面各角色的所有权限db_accessadmin 可增加或删除可增加或删除Windows认证模式下认证模式下Windows用户或用户或Windows用户用户组登录者以及组登录者以及SQL Server用户用户db_datareader能且仅能对数据库中所有表执行能且仅能对数据库中所有表执行SELECT操作，以读取所有表操作，以读取所有表数据数据db_datawriter能对数据库中所有表执行能对数据库中所有表执行INSERT、UPDATE、DELETE操作，操作，但不能进行但不能进行SELEC

30、T操作操作db_addladmin可以新建、删除、修改数据库中任何对象可以新建、删除、修改数据库中任何对象db_securityadmin管理数据库内权限的管理数据库内权限的GRANT、DENY和和REVOKE，主要包括语主要包括语句和对象权限，也包括对角色权限的管理句和对象权限，也包括对角色权限的管理db_backupoperator可以备份数据库可以备份数据库db_denydatareader不能对数据库中任何表执行不能对数据库中任何表执行SELECT 操作操作db_denydatawriter不能对数据库中任何表执行不能对数据库中任何表执行UPDATE、DELETE和和INSERT 第3

31、5页/共63页36 使用企业管理器添加成员到固定数据库角色中使用企业管理器添加成员到固定数据库角色中 注：此时添加到数据库角色中的是数据库用户注：此时添加到数据库角色中的是数据库用户 第36页/共63页37 使用使用SQL Server SQL Server 企业管理器创建数据库角色企业管理器创建数据库角色注：注：“权限权限”按钮处于禁用状态，只有重新进入按钮处于禁用状态，只有重新进入”新建角色新建角色”对话框该按钮才可用。当新建对话框该按钮才可用。当新建数据库角色创建成功，可以通过以上步骤重新进入，此时数据库角色创建成功，可以通过以上步骤重新进入，此时“权限权限”为可用状态，单击按钮弹为可用

32、状态，单击按钮弹出出“数据库角色属性数据库角色属性”对话框，进行角色权限设置。对话框，进行角色权限设置。 第37页/共63页38 管理数据库角色所使用的系统存储过程管理数据库角色所使用的系统存储过程sp_addrole 功能功能创建新数据库角色创建新数据库角色 格式格式sp_addrole 数据库角色名数据库角色名 ,数据库角色的所有者数据库角色的所有者 举例举例在数据库学生学籍数据库中建立新的数据库角色在数据库学生学籍数据库中建立新的数据库角色SelectTablesp_addrole SelectTable sp_droprole 功能功能删除数据库中某一自定义的数据库角色删除数据库中某一

33、自定义的数据库角色 格式格式sp_droprole 数据库角色数据库角色说明说明不能删除固定数据库角色，如不能删除固定数据库角色，如db_ddladmin、 db_owner，缺缺省情况下是省情况下是dbo举例举例将学生学籍数据库的数据库角色将学生学籍数据库的数据库角色SelectTable删除删除sp_droprole SelectTablesp_helprole功能功能显示当前数据库所有的数据库角色的信息显示当前数据库所有的数据库角色的信息格式格式sp_helprole 数据库角色数据库角色说明说明显示数据库学生学籍数据库的所有数据库角色信息显示数据库学生学籍数据库的所有数据库角色信息举例

34、举例sp_helprole第38页/共63页39sp_addrolemember功能功能向数据库某一角色中添加数据库用户向数据库某一角色中添加数据库用户格式格式sp_addrolemember 数据库角色数据库角色,SQL Server 数据库用户数据库用户或或Windows用户或用户组用户或用户组说明说明用户用户FirstDBUser加入到角色加入到角色SelectTable中中举例举例sp_addrolemember SelectTable, FirstDBUsersp_droprolemember功能功能删除某一角色的成员删除某一角色的成员格式格式sp_droprolemember 数据

35、库角色数据库角色,数据库用户或数据库用户或Windows用户或用户组用户或用户组举例举例将用户将用户FirstDBUser从数据库角色中删除从数据库角色中删除sp_droprolemember SelectTable, FirstDBUsersp_helprolemember 功能功能显示某一数据库角色的所有成员显示某一数据库角色的所有成员格式格式sp_helprolemember 数据库角色数据库角色说明说明包括固定数据库角色。固定数据库角色不能是数据库角色包括固定数据库角色。固定数据库角色不能是数据库角色的成员，如不能将的成员，如不能将db_accessadmin 加入到自建的角色加入到自

36、建的角色SelectTable中中举例举例显示学生学籍数据库中所有角色的成员显示学生学籍数据库中所有角色的成员 sp_helprolemember 续前表续前表第39页/共63页40 Public数据库角色 一个特殊的数据库角色 数据库中的每个用户都属于 public 数据库角色第40页/共63页41 SQL Server 安全控制的其他手段 使用视图为用户提供局部数据 -列级安全控制视图列级安全控制视图 CREATE VIEW 教师基本信息教师基本信息_Stu AS SELECT 教师编号教师编号,姓名姓名,性别性别,职称职称,电子邮件电子邮件 FROM 教师基本信息教师基本信息 -行级安全

37、控制视图，只包含行级安全控制视图，只包含01班的成绩信息班的成绩信息 CREATE VIEW 成绩单成绩单_SpeClass AS SELECT S.姓名姓名,C.课程名课程名,SC.成绩成绩 FROM 学生成绩学生成绩 AS SC JOIN 学生基本信息学生基本信息 AS S ON(S.学号学号 = SC.学号学号) JOIN 课程基本信息课程基本信息 AS C ON(SC.课程号课程号=C.课程号课程号) WHERE left(S.学号学号,2)=01 -班号为学号的前班号为学号的前2个字个字符符 -使用视图屏蔽其他班学生成绩使用视图屏蔽其他班学生成绩第41页/共63页42 视图与权限结合

38、 将访问视图的权限授予给用户将访问视图的权限授予给用户,使该用户不具有访问视图所引用的基本使该用户不具有访问视图所引用的基本表的权限表的权限,但其仍可以从中查看相应的数据信息但其仍可以从中查看相应的数据信息 用户对表的整体结构不需要了解，只需要建立一个视图将该用户在该用户对表的整体结构不需要了解，只需要建立一个视图将该用户在该表中可以访问的列包含其内，并将表中可以访问的列包含其内，并将SELECT查询权限授予该用户，则查询权限授予该用户，则该用户可使用该用户可使用SELECT查询视图而获取他要查询视图而获取他要/可获取的信息可获取的信息第42页/共63页43使用存储过程作为安全机制 例如：创建

39、一个存储过程，包含查询例如：创建一个存储过程，包含查询“成绩单成绩单_SpeClass”的语句的语句-存储过程与视图的结合存储过程与视图的结合CREATE PROCEDURE成绩单成绩单_SpeClass_SP ASSELECT * FROM 成绩单成绩单_SpeClass 将存储过程的将存储过程的EXEC权限授予用户权限授予用户GRANT EXEC ON 成绩单成绩单_SpeClassTO FirstDBUser第43页/共63页44 实现步骤实现步骤 以以FirstUser身份登录连接身份登录连接SQL Server，执行视图执行视图“成绩单成绩单_SpeClass”的查询语句，系统报告其

40、无的查询语句，系统报告其无SELECT视图的权限视图的权限 重新以重新以sa或或Windows的的Administrator身份登录连接身份登录连接SQL Server，建立视图建立视图“成绩单成绩单_SpeClass”、存储过程存储过程“成绩单成绩单_SpeClass_Sp”及将存储过程的权限授予用户及将存储过程的权限授予用户FirstDBUser 再以再以FirstUser身份登录连接身份登录连接SQL Server，执行存储过程执行存储过程“成绩成绩单单_SpeClass_Sp”，得到视图的查询结果得到视图的查询结果 第44页/共63页45 并发控制概述 数据库中的数据因其具备共享性而可

41、以为多个用户或程序（事务）同时使用 可以被多个用户或程序使用的数据库称为多用户数据库 日常生活中例子 银行数据库系统 飞机订票数据库系统 若这多个事务一个个顺序执行，即在一个事务执行完成后，再执行下一个事务，称为串行操作 串行操作最大的问题是效率低 在单处理机系统中，事务轮流占用处理机来交叉运行，对于用户感觉上是同时在处理多个事务，从而部分地实现了并行操作 而在多处理机的系统中，每个事务可运行在不同的处理机上，能够做到真正地“并行”运行多个事务 目前多个事务在单处理机上交叉并行运行为最普遍的方式 在多个事务并发地对数据库中的同一个数据进行操作的过程中，若对并发操作不做任何控制，则会产生多种不一

42、致问题9.5 数据库的并发控制数据库的并发控制第45页/共63页46 例:并发操作中的不一致问题事务甲事务甲事务乙事务乙时间时间读余额读余额D为为1000读余额读余额D为为1000更新余额更新余额D=D-300t1t2t3t4更新余额更新余额D=D-200第46页/共63页47 并发操作的不一致性(3种) 丢失更新（Lost Update） 甲、乙两个事务读取同一个数据并进行更新，事务乙的更新结果破甲、乙两个事务读取同一个数据并进行更新，事务乙的更新结果破坏了事务甲的更新结果。坏了事务甲的更新结果。(上例上例) 污读（Dirty Read） 事务甲更新了数据事务甲更新了数据D，事务乙读了事务甲

43、更新后的数据事务乙读了事务甲更新后的数据D，事物甲因事物甲因为某种原因而被撤消，事务回滚修改无效，数据为某种原因而被撤消，事务回滚修改无效，数据D恢复原值，但事务恢复原值，但事务乙并不知道，所以其得到的数据与数据库中实际数据内容不一致，乙并不知道，所以其得到的数据与数据库中实际数据内容不一致，即事务乙读了即事务乙读了“脏脏”数据。数据。 不能重读（Unrepeatable Read） 事务甲读取数据事务甲读取数据D，接着事务乙也读取数据接着事务乙也读取数据D，并更新了数据并更新了数据D，事事务甲再次读取数据务甲再次读取数据D以进行校验，但得到的结果不同于其第一次读取以进行校验，但得到的结果不同

44、于其第一次读取的结果，即两次读取值不一致。的结果，即两次读取值不一致。第47页/共63页48事务甲事务甲事务乙事务乙时间时间读余额读余额D为为700读余额读余额X为为1000并更新余额并更新余额D=D-300t1t2t3t4更新余额更新余额D=D-200余额为余额为500因故回滚，余额因故回滚，余额D为为恢复为恢复为1000“脏脏”数据数据污读污读第48页/共63页49事务甲事务甲事务乙事务乙时间时间读余额读余额D为为1000并更新余额并更新余额D=D-200读余额读余额D为为1000t1t2t3读余额读余额D为为800两次读结果不一样两次读结果不一样不能重读不能重读第49页/共63页50 不

45、一致问题产生的原因 因为并发操作使事务之间的隔离性遭到破坏 事务在执行过程中受到其他事务的干扰，不能独占数据 并发控制的目的 在一定的并发程度的前提下，合理调度并发事务，避免并发事务之间相互干扰造成数据的不一致性 并发控制的主要方法 封锁（Locking）第50页/共63页51 封锁（Locking） 基本思想 甲事务要修改数据甲事务要修改数据D，在修改前必须锁定在修改前必须锁定D，使其他事务不能读取或修使其他事务不能读取或修改改D，直到甲事务修改完并写回数据，再解除封锁后方可访问数据直到甲事务修改完并写回数据，再解除封锁后方可访问数据D。这样就避免了丢失甲的修改。这样就避免了丢失甲的修改。

46、基本封锁类型 排它性封锁（称排它性封锁（称X封锁）封锁） 如果事务如果事务T建立对某个数据目标的排它性封锁，则只有事务建立对某个数据目标的排它性封锁，则只有事务T能对能对该数据读取或修改，其他任何事物均不能对该数据目标进行读取该数据读取或修改，其他任何事物均不能对该数据目标进行读取或修改或修改。 共享性封锁（称共享性封锁（称S封锁）封锁） 又称保护性封锁，事务又称保护性封锁，事务T对数据目标建立了共享性封锁，则事务对数据目标建立了共享性封锁，则事务T和其他事务均可对该数据目标读取，但其他事务不得对数据目标和其他事务均可对该数据目标读取，但其他事务不得对数据目标进行更新进行更新。第51页/共63

47、页52表示表示T的请求与的请求与T建立的封建立的封锁冲突，锁冲突，T的请求不能实现。的请求不能实现。T对数据目标建立的封锁对数据目标建立的封锁 T与与T的封锁相容，可的封锁相容，可以实现。以实现。SX-SX-NNYYNYYYYT对数据目标发对数据目标发出的封锁请求出的封锁请求 X：排它封锁。排它封锁。 S：共享封锁。共享封锁。 -：没有加锁。：没有加锁。n 共享性封锁和排它性封锁的相互作用可以用相容矩阵来表示共享性封锁和排它性封锁的相互作用可以用相容矩阵来表示第52页/共63页53 运用这两种封锁可以建立以下封锁协议 PX协议 任何事务任何事务T要对数据目标要对数据目标D进行修改，必须对进行修

48、改，必须对D进行进行X封锁，然封锁，然后读取并进行修改，如果不能得到这种封锁，必须等待直到取后读取并进行修改，如果不能得到这种封锁，必须等待直到取得封锁权才能对得封锁权才能对D进行操作。进行操作。 此协议可以防止更新丢失。此协议可以防止更新丢失。 PXC协议 在在PX协议基础上，进一步规定建立协议基础上，进一步规定建立X封锁的事务封锁的事务T要保留封锁要保留封锁权直到事务权直到事务T完全结束为止。可以防更新丢失，还可以防止因事完全结束为止。可以防更新丢失，还可以防止因事务务T的撤消而造成污读数据。的撤消而造成污读数据。 PS协议 事务事务T要读取数据要读取数据D必须对必须对D实现实现S封锁，然

49、后读取封锁，然后读取D，如果进一如果进一步企图更新步企图更新D时，时，S封锁升级为封锁升级为X封锁。可以防止更新丢失，又封锁。可以防止更新丢失，又可以提高并发程度。可以提高并发程度。 PSC协议 在在PS协议基础上，进一步规定建立协议基础上，进一步规定建立S封锁的事务封锁的事务T要保留封锁直要保留封锁直到事务到事务T完全结束为止。可以防止事务完全结束为止。可以防止事务T对数据对数据D有两次以上读有两次以上读取时出现数据不一致，同时又比取时出现数据不一致，同时又比PXC协议有较高的并发程度。协议有较高的并发程度。 第53页/共63页54 封锁的粒度（Granularity ） 封锁的数据的目标的

50、大小叫封锁的粒度，大到可以为整个数据文件或整个数据库，小到可以是单条记录或字段值。 封锁粒度愈细则并发程度愈高，但因封锁数目多而造成开销越大 封锁粒度愈粗则并发程度愈差，但因封锁数目少而进行的封锁检查少，则开销越小 确定封锁粒度和封锁时间的原则 安全的封锁应做到安全的封锁应做到 比最小的数据请求具有较多的数据单元比最小的数据请求具有较多的数据单元 安全的保持应做到安全的保持应做到 比最短请求时间具有较长时间的封锁比最短请求时间具有较长时间的封锁第54页/共63页55 死锁及消除方法 死锁：并发操作中的一种状态，即在多个事务并发执行的过程中，其中两个或多个并行的事务同时处于等待状态，其中每一个都在等待其他的事务释放封锁的状态。请求封锁请求封锁D1等待等待请求封锁请求封锁 D2t3t4等待等待等待等待等待等待事务事务A事务事务B时间时间封锁封锁 D2封锁封锁 D1t1t2第55页/共63页56死锁是可以避免的但因为代价太大，所以一般系统允许发生死锁只是在可能出现死锁的情况下，发现并消除死锁。诊断死锁是否发生的一般方法建立等待图利用超时机制来检