MySQL数据库技术与项目应用教程电子教案 项目七 维护系统数据库安全

备课纸适用班级：P.课题项目七维护网上商城系统数据库安全课时4+4课时教学目的会在数据库中创建、删除用户和角色会对数据库中的权限进行授予、查看和收回操作了解事务的基本原理，会使用事务控制程序的执行了解事务的4种隔离级别了解锁在事务并发中的作用及其应用重点维护数据库中的用户和角色数据库的权限管理事务的隔离级别难点为用户授予合适的权限如何防止数据的脏读、幻读现象锁保证事务并发的安全性教具多媒体+机房课型新授作业课后习题课后项目实战教学过程课时分配教学方法教学内容课程引入随着信息化网络化水平的不断提升，重要数据信息的安全越来越受到威胁1.数据安全性的意义？2.如何防范信息泄露和篡改？任务1数据库用户权限管理知识场景：MySQL是一个多用户数据库管理系统，具有功能强大的访问控制体系。本任务详细介绍中MySQL数据库用户权限管理的实现，以防止不合法的使用所造成的数据泄露、更改和破坏。备课纸适用班级：P.带着问题开本任务主要讨论如下问题。展学习（1）数据库安全级别有哪些（2）如何进行用户管理（3）怎样为用户设置权限（4）如何创建和管理角色用户与权限数据库的安全性是指只允许合法用户进行其权限范围内的数据库相关操作。数据库安全性措施主要有如下两个方面：用户认证访问权限1.MySQL数据库中的用户MySQL用户主要包括root用户和普通用户。root用户是超级管理员，拥有操作MySQL数据库的所有权限。如root用户的权限包括创建用户、删除用户和修改普通用户的密码等管理权限。普通用户仅拥有创建该用户时赋予它的权限。数据库中包含了6个用于管理MySQL中权限的表。分别阐述user：是顶层的，是全局的权限db：是数据库层级的权限host：是数据库层级的权限table_priv：是表层级权限columns_priv：是列层级权限procs_priv：定义在存储过程上的权限学习提示user表是权限表中最为重要的表，它记录了允许连接到服务器的账号信息和一些全局级的权限信息备课纸适用班级：P.用户账户管理创建用户删除用户密码管理学习提示要实现对用户账户的管理，必须有相应的操作权限查看当前MySQL服务器中有哪些用户。操作演示结果分析host值为“localhost”表示允许从本机登录值为“%”表示允许从任意主机登录1.创建用户（1）用CREATEUSER语句创建用户CREATEUSER[IFNOTEXISTS]账户名1[用户身份验证选项1][,账户名2[用户身份验证选项2]]...DEFAULTROLE角色名1[,角色名2...][WITH资源控制选项][密码管理选项|账户锁定选项](1)用户身份验证选项：指明身份验证的插件和密码。这里仅列举两种主要格式IDENTIFIEDBY'密码'|IDENTIFIEDWITH'插件名称'BY'密码'除MySQL8.0提供的默认插件为caching_sha2_password外，低客户端通常还使用mysql_native_password等插件。(2)角色名：MySQL8.0新增的角色管理，以方便更好的管理用户的权限。实例讲解【例7.1】创建名为user1的用户。CREATEUSER'user1'备课纸适用班级：P.【例7.2】创建名为user2的用户，只能在本机登录，密码为“123456“。mysql>CREATEUSER'user2'@'localhost'IDENTIFIEDBY'123456';host值设置为localhost，表示用户“user2“只能在本机登录。明文密码串”123456“使用caching_sha2_password插件算法进行了加密处理。课堂练习创建名为user3和user4的用户，密码分别为user333和user444，其中user3可以从本地主机登录，user4可以从任意主机登录。学生演示mysql>CREATEUSER'user3'@'localhost'IDENTIFIEDBY'user333',->'user4'@'%'IDENTIFIEDBY'user444';学习提示CREATEUSER语句可以同时创建多个用户。新用户可以没有初始密码。课堂练习分别使用这些用户登录到MySQL服务器，观察这些用户访问服务器的状态。【例7.4】创建名为user5的用户，使用插件为“mysql_native_password”。mysql>CREATEUSER'user5'@'localhost'->IDENTIFIEDWITH'mysql_native_password'BY'123456';执行成功后，使用SELECT查询mysql.user表。mysql>SELECThost,user,authentication_string,plugin->FROMmysql.userWHEREuser='user5'\G;***************************1.row***************************host:localhostuser:user5authentication_string:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9plugin:mysql_native_password1rowinset(0.00sec)结果分析从查询结果看，本例中使用插件算法为mysql_native_password，其加密后对应的authentication_string列保存的值与caching_sha2_password插件算法的值不同。备课纸适用班级：P.实例讲解【例7.5】创建名为user6的用户，设置密码过期时间为30天。mysql>CREATEUSER'user6'@'localhost'IDENTIFIEDBY'user666'->PASSWORDEXPIREINTERVAL30DAY;学习提示密码到期后，用户登录成功执行任何SQL操作前，都需要重置用户密码，否则系统会提示“在执行此语句之前，必须使用ALTERUSER语句重置密码”的错误提示2.修改用户密码用户密码是正确登录MySQL服务器的凭据，为保证数据库的安全性，用户需要经常修改密码，以防止密码泄露。（1）使用ALTERUSER语句修改用户密码ALTERUSER账户名IDENTIFIEDBY'新密码';实例讲解【例7.7】修改用户user1的密码为“123456”。mysql>ALTERUSER'user1'@'%'IDENTIFIEDBY'123456';（2）使用SET语句修改用户密码SETPASSWORD[FOR账户名]='新密码';由于SET语句没有对密码加密的功能，因此在使用SET语句进行密码修改时，必须使用PASSWORD()函数对明文密码进行哈希运算。（3）使用mysqladmin命令修改用户密码mysqladmin–u用户名[-h主机地址]–ppassword新密码实例讲解【例7.9】修改用户use2的密码为“1234”mysqladmin–uuser2–ppassword1234学习提示mysqladmin工具存放在MySQL的安装目录的bin文件夹下。备课纸适用班级：P.3.修改用户名称RENAMEUSER旧账户名1TO新账户名2[,旧账户名1TO新账户名2]…;【例7.11】修改用户user1和user2的名称分别为lily和jack，lily可从任意主机登录。mysql>RENAMEUSER'user1'@'%'to'lily'@'%',->'user2'@'localhost'to'Jack'@'localhost';4.修改用户除修改密码和用户名外，MySQL可使用ALTERUSER语句修改用户的资源限制、账户锁定状态、密码策略等属性。语法格式如下。ALTERUSER[IFEXISTS]账户名1[用户身份验证选项1][,账户名2[用户身份验证选项2]]...DEFAULTROLE角色名1[,角色名2...][WITH资源控制选项][密码管理选项|账户锁定选项]【例7.12】锁定账户'jack'@'localhost'。mysql>ALTERUSER'jack'@'localhost'ACCOUNTLOCK;5.删除用户使用DROPUSER删除用户实例讲解【例7.14】删除用户user6和user7。DROPUSERuser6@localhost,user7@localhost;学习提示删除用户时，必须拥有数据库的全局CREATEUSER权限或DELETE权限。使用DELETE删除用户后，要用FLUSHPRIVILEGES语句重新加载用户权限。权限管理权限是指登录到MySQL服务器的用户，能够对数据库对象执行何种操作的规则集合，在MySQL启动时，服务器会将数据库中的各种权限信息读入到内存，以确定用户可进行的操作。备课纸适用班级：P.1.MySQL中的权限类型精讲（1）全局层级：使用ON*.*语法赋予权限。（2）数据库层级：使用ONdb_name.*语法赋予权限。（3）表层级：使用ONdb_name.tbl_name语法赋予权限。（4）列层级：使用SELECT(col1,col2…)、INSERT(col1，col2…)和UPDATE(col1,col2…)语法授予权限。（5）存储过程、函数级：使用executeonprocedure

或executeonfunction语法授予权限。MySQL中的各种权限：见表7-52.分配权限精讲分配权限是给特定的用户授予对象的访问权限。GRANT权限类型1[(列列表)][,权限类型1[(列列表)]][,…n]ON{*|*.*|数据库名.*|数据库名.表名} TO账号名1[用户身份验证选项1][,账户名2[用户身份验证选项2]] [WITHGRANTOPTION]备课纸适用班级：P.实例讲解【例7.15】授予用户lily@%对数据库onlinedb所有表有SELECT、INSERT、UPDATE和DELETE的权限。mysql>GRANTSELECT,INSERT,UPDATE,DELETEONonlinedb.*TO'lily'@'%';使用SHOWGRANTS语句可以查看用户权限。语法格式如下SHOWGRANTS[FOR账户名];实例讲解【例7.16】查看用户lily@%的权限实例讲解【例7.18】授予用户jack@localhost对数据库onlinedb中名为proc_getGoodsPage存储过程的执行权限mysql>GRANTEXECUTEONPROCEDUREonlinedb.proc_getGoodsPageTO'jack'@'localhost';学习提示除了使用GRANT给用户分配权限外，还可以使用UPDATE语句修改特定权限表，实现权限的分配3.收回权限收回权限就是取消某个用户的特定权限。REVOKE权限类型1[(列列表)][,权限类型1[(列列表)]][,…n]ON{*|*.*|数据库名.*|数据库名.表名} FROM用户账号1[,用户账号2…]实例讲解【例7.19】收回用户jack@localhost对数据库onlinedb中名为proc_getGoodsPage存储过程的执行权限REVOKEEXECUTEONPROCEDUREonlinedb.spGetgdNamesFROM‘Jack’@’localhost’;学习提示在使用GRANT授权或REVOKE收回权限后，都必须FLUSHPRIVILEGES语句，重新加载权限表，否则无法立即生效。备课纸适用班级：P.角色管理MySQL8.0+提供了对角色的支持。MySQL角色是权限的集合，像用户账户一样，可以授予和回收角色权限。可以将角色赋予指定账户，使该账户具备角色拥有的权限。通过角色管理，可以简化数据库中用户账户的权限授予和回收，提高管理效率。1.创建角色CREATEROLE[IFNOTEXISTS]角色名1[,角色名2]...【例7.24】创建app_developer、app_read和app_write三个角色。mysql>CREATEROLE'app_developer','app_read','app_write';说明角色创建成功后，会在mysql.user表中添加一行记录。角色名与用户账户一样包括角色名和主机地址，当不指定主机地址时，默认为“%”。创建角色时，也可以在指定角色名的同时指定主机地址，其方式与创建用户账号相同。2.授予和回收角色权限角色权限的授予和回收与用户账户权限的分配完全相同。【例7.25】为app_developer、app_read和app_write三个角色分配权限。#将onlinedb数据库的所有权限分配给角色app_developerGRANTALLONonlinedb.*TO'app_developer';#将onlinedb数据库上数据查询权限分配给角色app_readGRANTSELECTONonlinedb.*TO'app_read';#将onlinedb数据库上数据修改权限分配给角色app_readGRANTINSERT,UPDATE,DELETEONonlinedb.*TO'app_write';【例7.26】将用户jack和lily设定为app_developer角色，将用户user5和user6设定为app_read角色，将用户test设定为app_read和app_write角色。#指定用户为app_developer角色mysql>GRANT'app_developer'TO'jack'@'localhost','lily'@'%';#指定用户为app_read角色mysql>GRANT'app_read'TO'user5'@'localhost','user6'@'localhost';#指定用户为app_read和app_write角色mysql>GRANT'app_read','app_write'TO'test'@'localhost'激活角色：将用户指定为某一角色后，还需要激活才能使用setdefaultroleto用户名备课纸适用班级：P.任务2使用事务保证数据操作的安全性任务场景实际应用中，较为复杂的业务逻辑通常都需要执行一组SQL语句，，且这一组语句执行的数据结果存在一定的关联，语句组的执行要么都执行成功，要么什么都不做。带着问题开本任务主要讨论如下问题。展学习（1）事务工作过程怎样？（2）事务有哪些隔离级别？（3）MySQL中的事务如何工作？事务日志的工作过程精讲（1）定义：事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为ACID（原子性、一致性、和隔离性持久性）属性，只有这样才能成为一个事务：（2）事务的四个特性结合场景讲原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，解抽象概念要么全都不执行。一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如B树索引或双向链表），都必须是正确的。隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。备课纸适用班级：P.小结事务的事务日志的作用：就是记录设备，保证事务的持久性。即使服务器硬件、操作作用系统或MySQL自身出现故障，MySQL也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的位置。看图分析事务日志的工作过程事务的基本操作1.开启和提交事务mysql>STARTTRANSACTION;#开启事务mysql>COMMIT;#手动提交事务2.回滚事务mysql>ROLLBACK;实例【例7.27】手动提交事务应用示例。在onlinedb数据库中，当用户成功提交订单后（向orders表添加一条记录），用户积分按规则增加。演示#开启事务mysql>STARTTRANSACTION;#uid为2的用户提交了订单，订单金额为125元mysql>INSERTINTOorders(uid,ocode,oamount)values(2,'O1232',125);#修改uid为2的用户积分，每10元积1分mysql>UPDATEusersSETucredit=ucredit+125/10WHEREuid=2;#提交事物mysql>COMMIT;备课纸适用班级：P.【例7.28】创建存储过程，实现用户确认下单之后，需要删除该用户在购物车演示+精讲中的商品信息，并将其添加到订单表中，使用事务完成。CREATEPROCEDUREupAddOrders(idINT)BEGIN DECLAREodtotalINT; DECLAREodidINT; --指定事务的起始位置Loop_label:LOOP --启动事务 STARTTRANSACTION; --获取当前用户购物车中商品的数量 SELECTSUM(scNum)INTOodtotalFROMcartWHEREuID=id; --创建订单 INSERTINTOorders(uID,oTime,oTotal)VALUES(id,NOW(),odtotal); --如果创建失败，回滚 IFROW_COUNT()<1THEN ROLLBACK; LEAVEloop_label; ENDIF; --获取订单ID SETodid=LAST_INSERT_ID(); --将购物车中的商品添加到订单详细表中 INSERTINTOorder_items(oID,gdID,odNum) SELECTodid,gdID,scNum FROMcart WHEREuID=id; --如果添加失败，回滚 IFROW_COUNT()<1THEN ROLLBACK; LEAVEloop_label; ENDIF; --删除购物车中的商品 DELETEFROMcartWHEREuID=id; 备课纸适用班级：P.--如果删除失败回滚，否则提交 IFROW_COUNT()<1THEN ROLLBACK; LEAVEloop_label; ELSE COMMIT; ENDIF: ENDLOOP;END事务的隔离级别精讲数据库是多线程并发的，所以容易出现多个线程同时开启事务的情况。以防出现重复读、脏读或幻读的情况。对比各级别的数据访问情况实例演示事务的隔离级别应用实例【例7.30】查看系统变量@@session.transaction_isolationSELECT@@session.tx_isolation;设置事务的隔离级别SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVERL[READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE];实例讲解【例7.31】修改当前会话的隔离级别为提交读（READ-COMMITTED，RC）mysql>SET@@session.transaction_isolation='READ-COMMITTED'演示+精讲【例7.32】RC隔离级别下的不可重复读示例。（1）为了模拟事务隔离下数据读写可能出现的问题，除使用默认用户'root'@'%'外，另使用了本章任务1中创建的用户'lily'@'%'，该用户具有对onlinedb数据库的SELECT、INSERT、UPDATE权限备课纸适用班级：P.（2）打开两个MySQL的客户端，分别用这两个用户登录MySQL，其中登录用户为“root”的标记为事务A，登录用户为“lily”标记为事务B。演示+精讲（3）客户端A：开启事务，设置隔离级别为“提交读（READ-COMMITTED）”mysql>SET@@session.transaction_isolation='READ-COMMITTED';（4）客户端A：开启事务，并读取cid为1的商品客户端B：开启事务，修改商品客户端A：再次查看相同的信息备课纸适用班级：P.任务3使用锁保证事务并发的安全性引入锁是计算机中用于协调多个进程或线程并发访问共享资源的机制。假若在同一时刻点，多个客户端对于同一个表执行更新或查询操作，有可能因为资源拥塞造成数据的不一致。为保证多用户在读写操作时数据的一致，需要使用锁对并发进行控制。锁机制概述当多用户并发访问共享资源时，数据库需要合理控制资源的访问规则，锁就是用来实现这些访问规则的重要结构，是MySQL并发控制的主要技术方案之一锁的粒度主要分为表锁和行锁。表锁：粒度为表，逻辑简单，并发量十分低下，实际应用中较少行锁：粒度为行，由Innodb存储引擎实现，支持高并发。事务和锁不同的事务隔离级别下，不同的数据操作，其加锁也不相同。当事务级别为未提交时，不加锁；在已提交读和可重复读的事务隔离下，数据读操作都不加锁，但插入、删除和修改都会加上X锁，该级别以下的级别中读写不冲突；在可序列化事务隔离级别下，读写冲突，其中读加共享锁，而写则加排它锁。锁类型共享锁（S锁）：共享锁又称为读锁，是读取操作创建的锁实例讲解排他锁（X锁）：排他锁又称为写锁，一个事务获取了排他锁之后，允许对锁定范围内的数据执行写操作意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁（IS锁）和意向排他锁（IX锁）两类。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。“有意”表示事务想执行操作但还没有真正执行。锁和锁止键的关系，要么是相容的，要么是互斥的。锁a和锁b相容是指操作同样一组数据时，如果事务A获取了锁a，另一个事务B还可以获取锁b。锁a和锁b互斥是指操作同样一组数据时，如果事务A获取了锁a，另一个事务B在A释放锁a之前无法获取锁b。备课纸适用班级：P.MySQL中锁的应用除系统自动加锁外，还可以使用SELECT语句显式为记录行进行加锁，语法格式如下。SELECT语句[FORshare|FORupdate]FORshare：表示为SELECT语句查询的记录加共享锁，此时其他事务可以获取共享锁，但不能获取排他锁。FORupdate：表示为SELECT语句查询的记录加上排他锁，此时其他事务既不能获取这些记录的共享锁和排他锁。学习提示对于同一条SQL语句，其加锁机制除受隔离级别影响外，还受是否是主键、是否有索引、是否是唯一索引及SQL的查询计划有关。实例讲解【例7.33】多用户并发时，共享锁使用示例。备课纸适用班级：P.【例7.34】多用户并发时，排它锁使用示例。实例讲解死锁的产生和处理实际应用中，若事务加锁后相互不兼容，则会出现死锁，也就是说假定事务A的执行需要事务B释放锁才能继续，而事务B的完成也需要事务A释放锁才能完成，这时就会产生死锁。实例讲解【例7.35】多用户并发时，死锁示例。此时事务A和事务B的插入操作都需要另一方释放持有的共享锁，这时就会产生死锁。事务A和事务B都会处于等待中。InnoDB存储引擎为了防止死锁造成的事物长时间等待，设置自动检测死锁的机制，并通过设置系统变量innodb_lock_wait_timeout，当时间域值达到，则自动放锁，并回滚事务的所有操作。项目实战习题+实战用户权限（1）使用SQL语句创建一个用户zhao,密码为123456。（2）使用SQL语句创建一个用户zhang，密码为123456。（3）的表users上拥有SELECT权限。（4）使用SQL语句收回对用户在表users上的SELECT权限。（5）使用SQL语句修改用户名为zhang的登录密码，修改为zhang123456。（6）使用SQL语句创建两个角色userAdmin和goodsAdmin。（7）使用SQL语句将的表users上的UPDATE，SELECT和DELETE权限授予角色userAdmin，将表goods上的I