第8章数据完整性和安全性

1、网络数据库技术与应用网络数据库技术与应用课件课件第第8章章西安航空职业技术学院王姝制作第8章 数据完整性和安全性n81 基本概念n82 几种常见的约束n83 触发器及其创建n84 实例教学触发器应用n85 数据安全性n86 事务、锁和批n87 小结n随着网络的发展，电子商务、网上银行、在线课堂等都需要后台数据库的支持，从而使网络数据库的保护问题更加重要。数据库的保护有多方面的内容，概括起来主要是保护后台数据库系统的完整性和安全性，使之具有随时可正常运行的功能，防止机密数据的泄漏。n数据库的完整性是指为了防止数据库中存在不合语义的数据，防止错误信息的输入与输出造成无效操作或错误结果。数据库的安全

2、性是为了防止恶意地破坏和非法地存取、使用。安全性措施的防范对象是非法用户和非法操作，完整性措施的防范对象是不合语义的数据。81 基本概念n811 约束n为维护数据库的完整性，DBMS必须提供一种机制来检查数据库的数据，看其是否满足语义规定的条件，我们将这些加在数据库数据之上的语义约束条件称为数据库完整性约束条件，简称约束，它们与数据一起存入数据库中。n设计数据库表时，可对表中的一个字段或多个字段的组合设置约束条件，让SQL Server检查该字段的输入值是否符合这个约束条件。约束条件分为表级约束和字段级约束两种。表级约束是一个表中几个字段的约束，字段级约束则是对表中一个字段的约束。812 数据

3、完整性n根据数据完整性措施所作用的数据库对象和范围不同，可以将数据完整性分为以下几种：n1、实体完整性n又称为行完整性。它把表中的每行都看作一个实体，要求所有行都具有惟一标识。在SQL Server中，可以通过建立PRIMARY KEY约束、UNIQUE约束等措施来实施实体完整性。例如，对“客户信息表”，客户编号就可以作为主键，每个客户的编号能够惟一地确定该客户对应的记录信息，那么在输入数据时，则不能有相同客户编号的记录存在，通过对客户编号这一字段建立主键约束，可实现“客户信息表”的实体完整性。n2、域完整性n域完整性又称为列完整性。它要求表中指定列的数据具有正确的数据类型、数据格式和有效的数

4、据范围。域完整性可以通过制定相应的规则、FOREIGN KEY约束、CHECK约束、默认值对象等方法实现。如对于“学生成绩表”，学生的某门课程的学分应在10分以内，为了对学分这一数据项输入的数据进行限制，可以定义该表的学分字段的约束条件。n例例8-1 定义“学生成绩表”以及学分字段的约束条件nCREATE TABLE 学生成绩表n（ 课程号 CHAR（6） NOT NULL，n 课程名 CHAR（18） NOT NULL，n 学分 TINYINT CHECK（学分=0 and 学分=10）NULL,n PRIMARY KEY(课程号) n）n3、参照完整性n又称为引用完整性。它保证主表（被参照

5、表）中的数据与从表（参照表）中数据的一致性。在SQL Server中，它通过主键约束PRIMARY KEY和外键FOREIGN KEY 约束实现。在数据库管理系统中，保证数据库的完整性是非常重要的。参照完整性确保键值在所有表中一致。n在被参照的表中，当其主键值被其他表参照时，该行记录既不能被删除，也不允许被改变。 返回82 几种常见的约束n 建立和使用约束的目的就是为了保证数据的完整性。约束作为SQL Server强制实行的应用规则，它能够限制用户存放到表中的数据的格式和可允许的值。n 约束作为数据库定义的一部分，在CREATE TABLE语句中申明。所以约束往往又被称作申明完整性约束。当表被

6、删除时，附属于该表的所有约束定义也将被删除。n1、主键约束（PRIMARY KEY）nPRIMARY KEY即前面提到的主键，用它保证表中每条记录的惟一性。设计一个数据库表时，可用一个字段或多个字段（最多16个字段）的组合作为这个表的主键。用单个字段作为主键时，使用字段约束；用字段组合作为主键时，则使用表级约束。n每个表只能有一个主键。虽然一个表也可以不设主键，但最好还是设置一个主键。如果不在主键字段中输入数据，或输入的数据在前面已经输入过，则这条记录将被拒绝。n2、外键约束（FOREIGN KEY）nFOREIGN KEY即前面提到的外键。外键字段与其他表中的主键字段或具有惟一性的字段相对应

7、，其值必须在所引用的表中存在，而且所引用的表必须存放在同一数据库中。n如果在外键字段中输入一个非NULL值，但该值在所引用的表中并不存在。则这条记录也会被拒绝，因为这样将破坏两表之间的关联性。外键字段本身的值不要求是惟一的。n3、其他约束n除主键约束和外键约束外，常见的约束还有以下几种：n（1）NULL与NOT NULLn若在一个字段中允许不输入数据，则可以将该字段定义为NULL；如果在一个字段中必须输入数据，则应当将该字段定义为NOT NULL。n一个字段中出现NULL值意味着用户还没有为该字段输入值，NULL值既不等价于数据值型数据中的0，也不等价于字符型数据中的空串。n（2）UNIQUE

8、n如果一个字段值不允许重复，则应当对该字段添加UNIQUE约束。与主键不同的是，在UNIQUE字段中允许出现NULL值，但为保持惟一性，最多只能出现一次NULL值。n（3）CHECKnCHECK约束用于检查一个字段或整个表的输入值是否满足指定的检查条件。在表中插入或修改记录时，如果不符合这个检查条件，则这条记录将被拒绝。n（4）DEFAULTnDEFAULT约束用于指定一个字段的默认值，当尚未在该字段中输入数据时，该字段中将自动填入这个默认值。若对一个字段添加了NOT NULL约束，又没有设置DEFAULT约束，就必须在该字段中输入一个非NULL值，否则将会出错。 返回83 触发器及其创建n1

9、、触发器的功能n触发器是一种特殊类型的存储过程，与表紧密相连。当用户修改表中的数据时，触发器将自动执行，使用触发器可以实现多个表间数据的一致性。一般可以使用触发器完成如下功能：n（1）级联修改数据库中相关的表。如在产品信息数据库中有两个表：客户表cust_table 和订单表order_table。订单表order_table中包含订单信息和有业务往来的客户信息。如果出于某一需要删除了客户表cust_table中的某一客户信息，那么在订单表order_table中所有与该客户有关的记录都应该做相应的调整。n（2）执行比检查约束更为复杂的约束操作。在触发器中可以书写更加复杂的T-SQL语句，如可

10、以引用多个表，并使用IFELSE等语句做更加复杂的检查。n（3）拒绝或回滚违反引用完整性的操作。检查对数据表的操作是否违反引用完整性，并选择相应的操作。n（4）比较表修改前后数据之间的差别，并根据差别采取相应的操作。如：如果规定每次工资的变动幅度不能超过40%，使用触发器可以将修改后的表数据和修改前的表数据进行比较，如果超过40%的限制，可以回滚该修改操作。 n2、触发器类型和触发操作n触发器可以分为AFTER触发器和INSTEAD OF 触发器两种。nAFTER触发器：这种类型的触发器在数据变动（INSERT、UPDATE、DELETE操作）完成以后才被激发。一般情况下，对表数据的操作有插入

11、、修改、删除，因而维护数据的触发器也可分为三种类型：INSERT、UPDATE和DELETE。同一个表中可以使用多个触发器，即使同一类型的触发器，也可使用多个。它对变动的数据进行检查，如果发现错误，将拒绝或回滚变动的数据。nINSTEAD OF触发器：INSTEAD OF 触发器是SQL Server 2000中新增的功能。这种类型的触发器将数据变动以前被激发，并取代变动数据的操作（INSERT、UPDATE、DELETE操作），转而去执行触发器定义的操作。n在建立触发器时，还必须指定触发操作： INSERT、UPDATE、DELETE操作。至少要指定一种，也可以同时指定多种。在同一个数据表中

12、可以创建多个AFTER触发器，但是在表或视图上，每个INSERT、UPDATE、DELETE语句最多可以定义一个INSTEAD OF触发器。n3、创建触发器n方式一方式一 利用SQL命令创建触发器n用SQL命令创建触发器的语法格式如下：nCREATE TRIGGER trigger_name nON tableview WITH ENCRYPTIONnFORAFTERINSTEAD OFDELETE,INSERT,UPDATEnNOT FOR REPLICATION */nASn Sql_statementN nn方式二方式二 用企业管理器创建触发器n用企业管理器建立触发器的步骤如下：n（1）

13、登录到SQL Server服务器后，展开服务器组，然后展开服务器。n（2）展开将定义触发器的表所属的数据库，然后单击“表”文件夹。n（3）选择将在其上创建触发器的表，用鼠标右击，出现快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项.n（4）在“名称”对应的下拉列表框中单击“新建”，在“文本”框中输入触发器文本。若要检查语法，单击“检查语法”按钮。 返回84 实例教学触发器应用n下面在客户表cust_table中创建一个AFTER INSERT触发器，当有新客户加入时，给出欢迎信息。n在“定义触发器的界面”中的“文本”框中输入如下SQL代码：CREATE TRIGGER new_cus

14、t /*新建触发器 */ON cust_tableAFTER INSERTASPRINT “欢迎您的加盟！”Go SET NOCOUNT ON /*关闭影响行数信息 */INSERT INTO cust_table(Cust_id, Cust_name, Credit_limit, Linkman, Phone)VALUES (1001, JCP Inc., 500000, 张言, 53412573)GOn用户在创建触发器以后的使用过程中，可能会发现使用触发器虽然可以保证数据引用的完整性，但却会影响系统性能；有时要将触发器升级为新版本，这都需要删除触发器,可以在图8.2所示的“定义触发器的界面

15、”中选定相应的触发器名称后，单击“删除”按钮，也可以用T-SQL语句删除。删除触发器的语句为：nDROP TRIGGER trigger_name,trigger_name2 返回85 数据安全性n851 用户安全管理n852 权限安全管理n853 使用 SQL Server管理用户n返回851 用户安全管理n所谓用户安全是指用户身份的合法性。用户身份的验证是通过用户标识与鉴别来实现的。登录账户也称为登录标识符（Login ID），它是控制访问SQL Server系统的账户。如果事先没有指定有效的登录账户，用户是不能连接到SQL Server的。n在SQL Server中，有两类登录账户：一类

16、是由SQL Server自身负责身份验证的登录账户；另一类是登录到SQL Server的Windows NT/2000网络账户，可以是组账户或用户账户，使用这些账户进行登录时，SQL Server将“相信”Windows NT/2000操作系统己经验证了的用户身份而不再进行身份验证。显然，在Windows98/Me操作系统下使用SQL Server桌面版时是不能使用第二类登录账户的。 返回 852 权限安全管理n建立连接后，用户必须使用特定的用户账户才能对数据库进行访问，才可以查看经授权可以查看的表和视图，只能执行经授权可以执行的存储过程和管理功能，将这种机制称为权限管理。定义一个用户的存取权

17、限就是要定义这个用户可以在哪些数据对象上进行哪些类型的操作，也称为授权。n当验证了用户的身份并允许其登录到SQL Server之后，在用户访问的每个数据库中都要求设置单独的用户账户。这样做的目的是为防止一个用户在连接到SQL Server之后，对服务器上的所有数据库进行访问。n每个数据库中的用户账户都用于为该数据库中的对象（表、视图和存储过程等）设置安全权限。无论是Windows NT账户、SQL Server用户账户、用户所属的Windows NT组，还是SQL Server登录账户，都可以在数据库中映射为不同的数据库用户账户。n用户获得对数据库的访问权限之后，SQL Server就可接收并

18、执行命令。用户在数据库中进行的所有活动，都是通过T-SQL语句传到SQL Server中的。当SQL Server连到这些语句时，将确定用户是否具有在数据库中执行该语句的权限。如果用户没有执行语句的权限，或者没有访问该语句所使用对象的权限，SQL Server将向用户返回一个权限错误。n 返回8.5.3 使用 SQL Server管理用户n安装SQL Server后，启动企业管理器，在树形窗格中依次展开服务器组、服务器和“安全性”，然后单击“登录”节点，就会在内容窗格中看到以下三个内置登录账户。（如果系统采用的是Windows98操作系统,只能看到系统内置的登录账户sa）n （1）域名Admi

19、nistrator：一个Windows NT用户账户，允许作为SQL Server登录账户使用。n（2）BUILTINAdministrators：一个Windows NT组账户，凡属于该组的用户账户都可作为SQL Server登录账户使用。n（3）sa是SQL Server系统管理员登录账户。n需要特别说明的是：在安装SQL Server之后，系统管理员登录账户sa并没有设置密码。在这种情况下，建议立即为 sa账户设置一个登录密码，以防止未经授权使用sa登录访问SQL Server。另外，虽然sa是内置的系统管理员登录账户，但最好不要在日常管理中使用sa进行登录。只有当其他系统管理员不可用或

20、忘记了密码，没有其他方法登录到SQL Server的情况下，才使用sa这个特殊的账户。n1、添加登录账户n在对SQL Server进行维护和管理时，通常需要添加一些登录账户。有了登录账户，才能连接到SQL Server。添加登录账户的任务可以使用企业管理器或T-SQL语句来完成。在此主要介绍用企业管理器对账户的操作。n启动企业管理器。n在“树”窗格中，展开服务器组，选择展开一个服务器。n展开“安全性”文件夹，单击“登录”节点。n右击内容窗格的空白处，从弹出的快捷菜单中选择“新建登录（L）”命令。n在弹出的“SQL Server属性对话框”中选择“常规”选项卡，在“名称”后的文本框中输入一个SQ

21、L Server登录账户名称。 n如果要在连接SQL Server时使用Windows身份验证模式，请选择“Windows NT 身份验证”选项，并在“域（M）”列表中选择一个Windows NT/2000域。n如果要在连接SQL Server时使用SQL Server身份验证模式，请选择“SQL Server身份验证”选项，并在“密码（P）”后的文本框中输入一个登录密码。n在“默认设置”区域中可以定义登录到SQL Server之后将连接到的数据库，默认值为系统数据库master。在“语言”后的列表中可设置显示给用户的信息所用的默认语言。n完成上述设定后，单击“确定”按钮，在弹出的确认密码对话

22、框中进一步确认密码后，关闭对话框。 n2、修改登录密码n对于一个已经存在的SQL Server登录账户，可以用企业管理器添加或更改相应的登录密码。操作过程如下：n（1）启动企业管理器。n（2）在“树”窗格中，展开服务器组，选择展开一个服务器。n（3）展开“安全性”文件夹，单击“登录”节点。n（4） 在内容窗口中，右击想要修改密码的那个登录账户，从弹出的快捷菜单中选择“属性”命令后。弹出“登录属性”对话窗口，可在“密码（P）”后的文本框中输入（或修改）登录密码。n（5）单击“确定”，当出现“密码确认”窗口时，再次输入密码，单击“确定”即可。n3、禁止登录账户n若要暂时禁止一个SQL Server

23、登录账户连接到SQL Server，只需修改该账户的登录密码就行了。若要暂时禁止一个使用Windows身份验证的登录账户（一个组或一个用户）连接到SQL Server，可以用企业管理器实现。操作如下：n（1）在“树”窗格中，展开服务器组，然后选择展开一个服务器。n（2）展开“安全性”文件夹，单击“登录”节点n（3）在内容窗口中，右击想要禁止的那个登录账户，从弹出的快捷菜单中选择“属性”命令后，弹出“登录属性”的对话窗口图。在该窗口中选中“拒绝访问（Deny access）”选项后，单击“确定”按钮即可。n4、删除登录账户n若要永久禁止使用一个登录账户（无论它使用哪种身份验证模式）连接到SQL

24、Server，就应该将该登录账户删除。在企业管理器中进行如下操作：n（1）在“树”窗格中，展开服务器组，选择展开一个服务器。n（2）展开“安全性”文件夹，单击“登录”节点n（3）在内容窗口中，右击想要删除的那个登录账户，从弹出的快捷菜单中选择“删除”命令或者按delete键，在弹出的确认对话框中单击“是”，确定对该登录账户的删除操作。n 返回86 事务、锁和批n861 事务的概念n事务是SQL Server中的一个重要概念，在数据库应用中普遍使用。一个事务作为一个整体来执行，它的关键在于：如果在执行过程中发生了错误，可以回退事务，使数据库恢复到事务开始之前的状态，就好像什么都没发生过一样，从而

25、有效地保证数据的完整性，实现数据库的并发访问。n事务具有以下几个重要属性：n原子性：一个事务对数据库所作的修改要么全部被执行，要么全部被取消。从保持数据状态上来讲，它是一个不可分割的整体。n一致性：事务所作的修改必须遵守数据库中的各种约束、规则的要求。n隔离性：如果有两个或者多个事务，这些事务必须按照一定的时间顺序排序执行，而不能在执行一个事务的同时又执行另一个事务。这可以通过锁来实现。n（4）永久性：事务一旦完成，它对数据库所进行的修改将被永久保留。另外，SQL Server每次启动时都会根据事务日志来修复可能存在的错误，回退所有未完成的事务。n862 事务的分类n事务可分为显式事务、隐式事

26、务、自动事务和分布式事务。n1、显式事务n显式事务就是由用户显式地指定具体的事务处理语句而定义的事务。显式事务由以下几个事务处理语句构成：n（1）BEGIN TRANSACTION语句n用于定义事务的开始。当开始一个事务后，就在内存中为这次操作开辟一个缓冲区。在实际应用中，事务往往是嵌套的，即在一个事务还没有处理完前又开始了另一个事务，这样高层的事务在提交自己的修改以前就可以判断低层的事务是否成功提交或被取消。通常BEGIN TRANSACTION返回一个整型值，表示该事务所在的嵌套层。最高层的事务将返回1。n（2）COMMIT TRANSACTION语句n标志着事务的结束，在事务中对数据库所

27、作的修改在此时提交。n（3）ROLLBACK TRANSACTION语句n用于取消此次事务以来所有对数据源所做的操作。这是结束事务的一种方式，一般是因为在事务的执行过程中发生了错误，需要执行这条语句使数据库放弃在事务中所作的更改，数据恢复到事务开始之前的状态。执行ROLLBACK TRANSACTION后，本次事务将结束。n2、隐式事务n用户可以执行SET IMPLICIT_TRANSACTION ON 语句使SQL Server进入隐式事务处理模式。这时用户不必再执行BEGIN TRANSACTION 来启动事务处理，而只需使用COMMIT TRANSACTION或ROLLBACK TRAN

28、SACTION来结束事务。当用户执行了COMMIT TRANSACTION或ROLLBACK TRANSACTION之后，SQL Server就自动进入下一个事务，将这种事务称为隐式事务。n 3、自动事务模式n这是SQL Server默认的事务处理方式。当任何一条语句被成功执行以后，它对数据库所作的修改都将被自动提交，如果发生错误则自动回退。如果想取得更大的灵活性，可以用BEGIN TRANSACTION进入显式事务模式或者打开IMPLICIT_TRANSACTION进入隐式事务模式。这时，可以将若干条数据库操作语句组合成一个事务。n 4、分布式事务n前面所介绍的事务都是在一个服务器上的操作，

29、还有一种称为分布式事务的用户定义事务。分布式事务跨越两个或多个服务器，通过建立分布式事务并执行它，通过事务管理器负责协调事务管理，保证数据的完整性。主要的事务管理器是Microsoft分布式事务处理协调器MS DTC。对于应用程序，管理分布式事务很像管理本地事务。事务结束时，应用程序请求提交或回滚事务。不同的是，分布式提交必须由事务管理器管理，以尽量避免出现因网络故障而导致一个事务由某些资源成功提交，由另一些资源管理器回滚的情况。n863 锁n在实际应用中，可能会出现对数据库的并发操作。如多个用户同时对同一数据进行修改，或者查询正在被别的进程更新的数据，这都可能引起数据的混乱。为了有效地控制多

30、用户的并发操作，SQL Server引入了资源锁机制。n1、 锁的类型nSQL Server中有不同类型的锁，具体为：n（1）排它锁：它不允许别的事务读取或修改已被锁定的资源。n（2）共享锁：它允许别的事务读取已被锁定的资源，但不允许对锁定的资源进行修改操作。n（3）修改锁：在可能进行修改操作的开始阶段运用修改锁来锁定资源。在需要修改时，将修改锁转变为排它锁或共享锁。n（4）结构锁：在进行表的创建或者修改时，SQL Server使用结构修改锁。当编译或查询时，SQL Server使用结构稳定锁。n（5）意向锁：意向锁说明SQL Server有在该资源上获得共享锁或者排它锁的意向。意向锁可分为共

31、享意向锁、排它意向锁和共享排它意向锁三种。n2、锁的粒度n被锁定的资源单位称为锁的粒度（或称锁定粒度）。根据情况，SQL Server中的锁可以灵活运用在不同的资源层次上。锁定粒度由小到大可分为有以下几种：nRID行标识符：锁定表中的单行数据。n键值：具有索引的行数据。n页面：一个数据页面或者索引页面。n区域：一组连续的行数据页或索引页面。n表：整个表。n数据库：整个数据库。n 锁的粒度不同，系统的开销不同，锁定粒度与数据库访问并发度是一对矛盾，锁定粒度大，系统开销小，但并发度会降低；锁定粒度小，系统开销大，但可提高并发度。n在多用户环境中，当不同的用户分别锁定一资源，又都在等待对方首先释放该资源时，就会出现无限等待的情况，这称为死锁。为了减少死锁的发生，在设计程序时，应尽量减少事务对资源的锁定时间。nSQL Server定期查看并标识所有的锁定请求，如果在下一次查看过程中发现某请求仍然处于等待状态，SQL Server将会搜索系统中是否有死锁现象。如果存在死锁，则根据死锁的优先级别，先结束一个优先级较低的事务。除此之外，用户还可以设置LOCK_timeout来设置程序请求锁定的最长等待时间。如果一个锁定请求等待超过了最长等待时间，则该语句被自动取消。 n设置LOCK_timeout的语法格式为：nSET LOCK_timeouttimeout_periodn其中，tim