数据库技术及应用 课件 第8章 数据保护技术

DatabaseTechnology&Applications数据库技术及应用数据库完整性实施数据完整性的必要性正确性（correctness）数据语法的正确性如数值型数据中只能含有数字而不能含有字母有效性（valid）数据是否属于所定义域的有效范围如年龄不能是负数，在0到150之间。相容性（consistency）同一事实的两个数据应当一致如数据库中同时有年龄和出生年份这两个数据，则年龄应该等于当前年份-出生年份防止各类错误数据进入数据库！DBMS完整性控制的功能定义提供完整性约束条件的定义机制，确定要遵从的数据规则检查检查用户发出的操作请求是否违背完整性约束条件处理如果发现用户操作请求与完整性约束条件不符，需要采取一定的动作实体完整性规则的定义通过定义或者修改表结构的时候定义PRIMARYKEY来实现。定义PRIMARYKEY的子句在CREATETABLE命令中的格式为：PRIMARYKEY（<列名序列>）；//一个关系只能有一个PRIMARYKEY，键值非空且唯一实体完整性约束控制的实现【例8.1】新建学生S表（学号，姓名，年龄，身份证号），并定义实体完整性。CREATETABLES(SNOCHAR（8）PRIMARYKEY，/*在属性列级别上定义主键*/SNameCHAR(10),Sageint，SecurityNOCHAR(18));实体完整性约束控制的实现【例8.1】新建学生S表（学号，姓名，年龄，身份证号），并定义实体完整性。CREATETABLES(SNOCHAR（8），SNameCHAR(10),Sageint，SecurityNOCHAR(18),PRIMARYKEY（SNO))；/*在关系级别上定义主键*/实体完整性约束控制的实现【例8.2】新建课程C表（课程号，课程名，学分），并定义实体完整性。CREATETABLEC(CNOCHAR(8),CNameCHAR(10),HourNUMERIC(3),PRIMARYKEY(CNO));/*在关系级别上定义主键*/实体完整性约束控制的实现【例8.3】数据插入违反实体完整性而失败的例子往S表插入两条数据：INSERTINTOSVALUES('S001','小明',19,’200006’);/*插入成功*/INSERTINTOSVALUES('S001','小红',20,’300456’);/*插入失败*/INSERTINTOSVALUES('S002','小红',20,’300456’);/*插入成功*/UPDATESSETSNO='S001'WHERESNAME='小红'/*更新失败*/同样学号的记录已存在同样学号的记录已存在参照完整性规则的定义参照完整性（外键）定义的子句在CREATETABLE命令中一般格式为：FOREIGNKEY（<列名序列>）REFERENCES关系名<目标关系名>|（<列名序列>）

[ONDELETE<ACTION>][ONUPDATE<ACTION>]违反参照完整性的处理方法操作对象相关操作INSERTDELETEUPDATE

被参照表不需要检查根据参照表中外键定义的ONDELETE…（用户显示定义的方式，提供四种：CASCADE、NOACTION

、SETNULL和SETDEFAULT（系统默认的方式NOACTION）根据参照表中外键定义的ONUPDATE…（用户显示定义的方式，提供四种：CASCADE、NOACTION、SETNULL和SETDEFAULT（系统默认的方式NOACTION）参照表违反则拒绝执行不需要检查违反则拒绝执行参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。建立选修SC表（SNO，CNO，Grade）并定义主键和建立参照关系：CREATETABLESC(SNOCHAR(8),CNOCHAR(10),GradeNUMERIC(3),PRIMARYKEY(SNO,CNO),FOREIGNKEY(SNO)REFERENCESS(SNO)ONDELETECASCADE,FOREIGNKEY(CNO)REFERENCESC(CNO)ONUPDATENOACTION);参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（1）往表中插入以下数据数据：INSERTINTOSVALUES('A001','李红',18);INSERTINTOSVALUES('A003','陈诚',18);INSERTINTOCVALUES('C001','C语言');INSERTINTOSCVALUES('A001','C001',95);INSERTINTOSCVALUES('A003','C001',85);参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（2）往SC表（参照关系）中插入数据：INSERTINTOSCVALUES('A001','C002',95);由于C表（被参照关系）中无课程C002，违反了参照完整性，所以C表的更新操作失败，系统拒绝插入。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（3）更新SC表（参照关系）中学号为“A001”，课程号为“C001”的记录：UPDATESCSETSNO='A002'WHERESNO='A001'ANDCNO='C001';由于S表中无学生“A002”，所以SC表的更新操作失败，系统拒绝更新。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（4）更新S表（被参照关系）中学号为“A001”的记录：UPDATESSETSNO='A002'WHERESNO='A001';在S表更新学生“A001”的学号时，在SC表中还存在着学生“A001”的选课信息，由于没有定义外键SNO的更新操作，ONUPDATE子句默认为NOACTION，故系统拒绝更新。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（5）更新C表（被参照关系）中课程号为“C001”的记录：UPDATECSETCNO='C002'WHERECNO='C001';在C表更新课程“C001”时，在SC表中还存在着课程“C001”的选课信息，且ONUPDATE子句中定义的是NOACTION，故系统拒绝更新。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（6）删除S表（被参照关系）中学号为“A001”的记录：DELETEFROMSWHERESNO='A001';在S表中删除学生“A001”时，在SC表中还存在着学生“A001”的信息，所以S表的删除操作违反了参照完整性。由于定义了外键SNO的删除操作为CASCADE，所以系统除了删除S表中学生“A001”的记录之外，还会删除SC表中学生“A001”选修的记录。参照完整性约束控制的实现【例8.4】新建选修SC表（学号，课程号，成绩），并定义实体完整性和参照完整性。（7）删除C表（被参照关系）中课程号为“C001”的记录：DELETEFROMCWHERECNO='C001';在C表删除课程“C001”时，在SC表中还存在着课程“C001”的信息，所以C表的删除操作违反了参照完整性。由于没有定义外键CNO上的删除操作，故默认为ONDELETENOACTION，所以系统拒绝删除课程表记录。用户自定义完整性约束NOTNULL非空DEFAULT

默认值UNIQUE

唯一值CHECK

满足表达式的取值用户自定义完整性约束【例8.5】DEFAULT约束使用实例CREATETABLEEMPL(DnoNUMERIC(2),/*Dno为部门号*/EnoCHAR(8)UNIQUENOTNULL,/*属性Eno取值唯一,而且不能为空*/SalaryNUMERIC(10)DEFAULT8000);/*属性Salary具默认值约束*/用户自定义完整性约束【例8.6】UNIQUE约束使用实例CREATETABLEDEPT(DEPNONUMERIC(2),DnameCHAR(8)UNIQUENOTNULL,/*Dname为候选键*/LocationCHAR(10),PRIMARYKEY(DEPNO));/*在关系级别上定义主键*/用户自定义完整性约束【例8.7】CHECK约束使用实例——在S表关系中限定Gender只能取“male”或“female”：ALTERTABLESADDCOLUMNGenderCHAR(1)CHECK(GenderIN('M','F'));用户自定义完整性约束【例8.8】CHECK约束使用实例——在S表中加入CHECK约束S1——限制学生年龄为18到25岁：ALTERTABLESADDCONSTRAINTS1CHECK(SageBETWEEN18AND25);数据库安全性保护安全性控制的一般模型安全性保护的措施用户身份鉴别基于访问控制技术审计技术用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（1）创建用户student1，密码是student@123：CREATEUSERstudent1WITHPASSWORD"student@123";用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（2）将用户student1的登录密码由student@123修改为abcd@123：ALTERUSERstudent1IDENTIFIEDBY'abcd@123'REPLACE'student@123';是系统提供的最外层安全保护措施，其方法是每个用户在系统中必须有一个标志自己身份的标识符，用以和其它用户相区别。用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（3）使用视图PG_USER来查看当前系统中的用户列表：SELECT*FROMpg_user;用户身份鉴别【例8.9】在EMS数据库内，添加、修改和删除用户的例子（4）删除用户student1：DROPUSERstudent1CASCADE;基于访问控制技术访问控制（accesscontrol）就是一个数据库用户访问数据库资源权限的一种规定和管理。是数据库安全保护的主体技术。数据库用户类型角色机制授权回收数据库用户类型数据库用户管理员用户初始用户系统管理员普通用户数据库用户管理【例8.10】添加各类用户的例子（1）在EMS数据库内，创建系统管理员用户admin，密码为admin@123：CREATEUSERadminWITHSYSADMINPASSWORD"admin@123";数据库用户管理【例8.10】添加各类用户的例子（2）创建普通用户student1，密码是student1@123：CREATEUSERstudent1WITHPASSWORD"student1@123";数据库用户管理【例8.10】添加各类用户的例子（3）创建安全管理员security1，密码是security1@123：CREATEUSERsecurity1WITHCREATEROLEPASSWORD"security1@123";数据库用户管理【例8.10】添加各类用户的例子（4）使用视图pg_user来查看当前系统中的用户列表：SELECT*FROMpg_user;数据库用户管理【例8.10】添加各类用户的例子（5）使用视图pg_roles来查看当前系统中的角色列表：SELECT*FROMpg_roles;数据库用户管理【例8.10】添加各类用户的例子（6）使用系统表pg_authid来查看用户属性：SELECT*FROMpg_authidWHERErolsystemadmin='t’;/*该用户是管理员用户*/SELECT*FROMpg_authidWHERErolcreaterole='t’;/*该用户拥有创建角色权限*/数据库用户管理（7）删除student1用户：DROPUSERstudent1;角色机制角色(Role)是一类具有相同数据权限的用户(User)的集合。角色机制角色创建：GRANTROLE<角色名>;角色授权：GRANT<权限>[，<权限>]…ON<数据对象类型><数据对象名>TO<角色名>[，<角色>]…角色机制将角色授予其它用户：GRANT<角色名>[，<角色>]…TO<用户名>[，<用户名>]…[WITHGRANTOPTION]；将角色授予其它角色：GRANT<角色名1>TO<角色名2>；角色机制角色收回：REVOKE<权限>[，<权限>]…ON<数据对象类型><数据对象名>FROM<角色名>[，<角色>]…角色机制授权语句：GRANT{<权限1>，<权限2>…|ALL}[ON<数据对象类型><数据对象名>]TO{<用户/角色>[，用户/角色]…|PUBLIC}[WITHGRANTOPTION]/*表示获得权限的用户可以将其获得的权限继续授权给其它用户*/授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（1）创建老师角色Teacher，密码为teacher@123；创建学生用户Raul，密码为raul@123；创建学生用户White，密码为white@123；创建学生用户Mary，密码为mary@123：CREATEROLETeacherWITHPASSWORD"teacher@123";CREATEUSERRaulWITHPASSWORD"raul@123";CREATEUSERWhiteWITHPASSWORD"white@123";CREATEUSERMaryWITHPASSWORD"mary@123";授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（2）将对“学生”表的查询、更新和插入权授予角色Teacher：GRANTSELECT,UPDATE,INSERTONTABLE学生TOTeacher;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（3）将具有上述权限的角色授予Raul，White和Mary：GRANTTeacherTORaul,White,Mary;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（4）将Teacher赋予White的权限收回：REVOKETeacherFROMWhite;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（5）增加Teacher在“学生”表上的DELETE权限：GRANTDELETEONTABLE学生TOTeacher;授权语句【例8.11】使用角色机制完成将权限授予用户，由此可以看到角色机制可以使自主授权的执行更加方便和灵活。（6）收回Teacher在“学生”表上的UPDATE权限：REVOKEUPDATEONTABLE学生FROMTeacher;授权语句【例8.12】将“学生”表的SELECT权力和对其中学号的UPDATE权授予用户Tom，Lily：（1）创建用户Tom，Lily：CREATEUSERTomWITHPASSWORD"Tom@1234";CREATEUSERLilyWITHPASSWORD"Lily@1234";授权语句【例8.12】将“学生”表的SELECT权力和对其中学号的UPDATE权授予用户Tom，Lily：（2）此时查看系统角色表，发现存在Tom、Lily，说明创建用户时系统自动了创建同名角色：SELECTrolnameFROMpg_roles;授权语句【例8.12】将“学生”表的SELECT权力和对其中学号的UPDATE权授予用户Tom，Lily：（3）将“学生”表的SELECT权和学号的UPDATE权授予用户Tom：GRANTSELECT,UPDATE(学号)ONTABLE学生TOTomWITHGRANTOPTION;授权语句【例8.12】将“学生”表的SELECT权力和对其中学号的UPDATE权授予用户Tom，Lily：（4）以用户Tom的身份登录系统，即新建一个连接，用户名为Tom，右图，将权限授予Lily：GRANTSELECT,UPDATE(学号)ONTABLE学生TOLily;授权语句【例8.12】将“学生”表的SELECT权力和对其中学号的UPDATE权授予用户Tom，Lily：（5）由于DBMS在创建用户的时候，也同时创建了Tom，Lily的同名角色，实际上我们也可以直接在第（3）步之后用GRANTTomTOLily;授权语句【例8.13】将“学生”表的INSERT，UPDATE权限授予角色Teacher3，同时允许Teacher3将这两个权限再授予角色Teacher4：（1）创建不带有LOGIN属性的组角色Teacher3，带有LOGIN属性的登录角色Teacher4：CREATEROLETeacher3WITHPASSWORD"teacher3@123";CREATEROLETeacher4WITHPASSWORD"teacher4@123"LOGIN;授权语句【例8.13】将“学生”表的INSERT，UPDATE权限授予角色Teacher3，同时允许Teacher3将这两个权限再授予角色Teacher4：（2）查看系统用户表，SELECTusenameFROMpg_user;发现只存在Teacher4，这是因为创建角色Teacher3时没有分配LOGIN权限，所以没有创建同名用户。授权语句【例8.13】将“学生”表的INSERT，UPDATE权限授予角色Teacher3，同时允许Teacher3将这两个权限再授予角色Teacher4：（3）将“学生”表的INSERT，UPDATE权限授予用户Teacher3：GRANTINSERT,UPDATEONTABLE学生TOTeacher3WITHGRANTOPTION;授权语句【例8.13】将“学生”表的INSERT，UPDATE权限授予角色Teacher3，同时允许Teacher3将这两个权限再授予角色Teacher4：（4）将角色Teacher3的权限转授给角色Teacher4：GRANTTeacher3TOTeacher4;授权语句【例8.13】将“学生”表的INSERT，UPDATE权限授予角色Teacher3，同时允许Teacher3将这两个权限再授予角色Teacher4：（4）将角色Teacher3的权限转授给角色Teacher4：GRANTTeacher3TOTeacher4;授权语句【例8.13】将“学生”表的INSERT，UPDATE权限授予角色Teacher3，同时允许Teacher3将这两个权限再授予角色Teacher4：（5）打开客户端DockerDesktop，按下CLI按钮，打开openGauss数据库的命令行界面；输入su命令切换到omm用户，输入gsql命令切换到数据库EMS：输入\du命令，可看到角色Teacher4属于组角色Teacher3回收语句用户A将某权限授予用户B，则用户A也可以在它认为必要时将权限从B中回收，收回权限的语句称为回收语句，其具体形式如下：REVOKE{|<权限>ALL}[ON<数据对象类型><数据对象名>]FROM{<用户1>,<用户2>，…|PUBLIC}[CASCADE|RESTRICT]；/*CASCADE表示回收权限时会引起级联（连锁）回收，而RESTRICT则关系式不存在连锁回收时才能收回权限，否则拒绝回收。*/授权语句【例8.14】回收权限（1）从组角色Teacher3手中收回“学生”表上的插入和修改权，并且是级联收回。REVOKEINSERT,UPDATEONTABLE学生FROMTeacher3CASCADE;授权语句【例8.14】回收权限（2）从用户Lily手上收回用户Tom赋予的权限。REVOKETomFROMLily;审计（audit）技术操作类型，如查、增、删、改操作终端标识和操作用户标识操作日期和时间操作对象，如基本表、视图、元组或属性值将用于安全目的的数据库日志称为审计追踪（audittrail）审计追踪依赖于对数据库进行更新（插入、删除和修改）的日志，它主要包括下述内容：事务与事务管理数据库管理的重要课题如何保证数据库操作的并发执行的正确性如何保证数据库在系统发生故障时能从故障中恢复事务的概念例如：A账户给B账户转账，金额为N元。这个事务包含两个动作：

A账户-N B账户+N思考：若这个事务执行了一半会怎样？？？事务（Transaction）是DBMS的基本执行单位之一，事务是由用户定义的一个数据操作的有限序列，这个操作序列具有“要么全做，要么全不做”（allornothing）的特性。解决多用户共享数据导致的数据异常情况事务短事务一条更新（插入、删除或修改）的操作语句。长事务对数据库进行一系列操作的一个完整的用户程序，通常是一组SQL语句的序列。SQL事务机制⑴事务开始语句：BEGINTRANSACTION⑵事务提交语句：COMMIT[TRANSACTION]⑶事务回滚语句：ROLLBACK[TRANSACTION]授权语句【例8.15】新建银行数据库和对应的“账户”表，模拟两个账户转账，转账失败则事务回滚。（1）新建银行数据库，并在银行数据库中新建“账户”表：CREATEDATABASEBANK;CREATETABLE账户(

银行卡号VARCHAR(23)PRIMARYKEY,

身份证号VARCHAR(18)UNIQUENOTNULL,

姓名VARCHAR(12)NOTNULL,

余额NUMERICNOTNULLCHECK(余额>=0));授权语句【例8.15】新建银行数据库和对应的“账户”表，模拟两个账户转账，转账失败则事务回滚。（1）插入相关信息：INSERTINTO账户VALUES('6217001820020000000',,'李红',2500.5);INSERTINTO账户VALUES('6217001820020000001',,'刘佳',4001);INSERTINTO账户VALUES('6217001820020000002',,'高兴',1400);授权语句【例8.15】新建银行数据库和对应的“账户”表，模拟两个账户转账，转账失败则事务回滚。（2）李红转账给一个不存在的银行账号，转账失败，事务回滚：DO$$BEGIN UPDATE账户SET余额=余额-1000WHERE银行卡号='6217001820020000000'; IFEXISTS(SELECT1FROM账户WHERE银行卡号='6217001820020000003')THEN UPDATE账户SET余额=余额+1000WHERE银行卡号='6217001820020000003'; ELSE ROLLBACK; ENDIF;END$$;事务的性质原子性（Atomicity）：一个事务中的所有操作要么全执行，要么全不做，是一个不可分割的整体。一致性（Consistency）：事务执行应当使得数据库由一种一致性状态迁移到另一种新的一致性状态。隔离性（Isolation）：在多事务并发执行的结果如同在单用户环境下逐一执行事务的结果一致。持久性（Durability）：对一个已经提交的事务，事务对数据库的更新应永久地保存。串行与并发执行串行并发执行同时并发执行(多CPU)交叉或分时并发执行(单CPU)并发引起的不一致问题——丢失更新丢失更新（LostUpdate）是指两个事务T1和T2从数据库读取同一数据并进行更新，事务T2提交的更新结果破坏了事务T1提交的更新结果，导致了事务T1的更新被丢失。又称为写-写冲突（Write-WriteConflict）。并发引起的不一致问题——读“脏”数据读“脏”数据是由于一个事务读取了另一个事务尚未提交的数据所引起，因而称之为读-写冲突（read-writeconflict）。并发引起的不一致问题——不可重复读取不可重复读（Non-repeatableRead）是指当事务T1读取数据a的值为a’后，事务T2也对a进行读更新得到a’’，当T1再读取a进行校验时，发现前后两次读取值发生了变化，导致校验失败。封锁封锁的类型排它锁（eXclusiveLocks）又称为写锁或X锁。其含义是：事务T获得了数据对象A的X锁之后，T可以对A进行读写，而其它事务只有等到T事务提交之后释放了对A的X锁之后，才能对A进行封锁和读写操作。T2T1XSS×√X××nolock√√共享锁（SharingLocks）又称为读锁或S锁。其含义是：事务T获得了数据A上的S锁之后，T可以读A但不能写A；同时其它事务可以对A加S锁但不能加X锁，即共享锁允许多个不同的事务可以同时读A，但在事务T释放A上的S锁之前，其它事务（包括T本身）都不能写A。封锁协议封锁协议（LockingProtocol）是约定的一些加锁的规则，如何时申请锁、持锁的时间、何时释放锁等。对封锁方式规定不一样，就形成了不同的封锁协议一级封锁协议二级封锁协议三级封锁协议一级封锁协议对事务T要修改的数据加X锁，直到事务结束时才释放。防止丢失更新tT1T201Xlockquantity02Read

quantity=1003quantityquantity-4Xlockquantity04Writequantity=6Wait05CommitWait06UnXlockquantityWait07GetXlockquantity08Read

quantity=609quantityquantity-310Writequantity=311Commit12UnXlockquantity二级封锁协议一级封锁协议+“事务T对要读取的数据加S锁，读完后即释放S锁”。既防止丢失更新，又防止读“脏数据”tT1T201Xlockquantity

02Readquantity=100

03Writequantity=0

04

Slockquantity05ROLLBACK（quantity=100）Wait

06UnXlockquantityWait07

GetSlockquantity08

Readquantity=100三级封锁协议一级封锁协议+“事务T对要读取的数据加S锁，