MySQL数据库技术与项目应用课件 项目7 维护网上商城系统数据库安全

模块概览维护网上商城系统数据库安全MySQL数据库技术与项目应用07引言/FOREWORD随着信息化、网络化水平的不断提升，重要数据信息的安全受到越来越大的威胁，而大量的重要数据往往都存放在数据库系统中。如何保护数据库，有效防范信息泄露和篡改成为重要的安全保障目标。MySQL提供了用户认证、授权、事务和锁等机制实现和维护数据的安全，以避免用户恶意攻击或者越权访问数据库中的数据对象，并能根据不同用户分配相应的访问数据库对象及数据的权限。目录/Contents0103数据库用户权限管理使用锁保证事务并发的安全性02使用事务保证数据操作的安全性任务1

数据库用户权限管理MySQL是一个多用户数据库管理系统，具有功能强大的访问控制体系。本任务详细介绍了MySQL数据库用户及用户权限管理的实现，以防止不合法的使用所造成的数据泄露、更改和破坏。任务场景用户与权限数据库的安全性是指只允许合法用户进行其权限范围内的数据库相关操作，保护数据库以防止任何不合法的使用所造成的数据泄露、更改或破坏。数据库安全性措施主要涉及：用户认证问题访问权限问题MySQL用户root用户root用户是超级管理员，拥有操作MySQL数据库的所有权限。普通用户普通用户仅拥有数据库管理员赋予它的权限。用户与权限在安装MySQL时，会自动安装名为mysql的数据库。mysql数据库中的user表记录了允许连接到服务器的账号信息和一些全局级的权限信息，主要分为7个类别：账号列安全连接列身份验证密码策略列资源控制列权限列用户特征数据列用户与权限user表所属类别属性名数据类型是否为空默认值字段说明账号列Hostchar(255)NO

主机地址Userchar(32)NO

用户名安全连接列ssl_typeenum('','ANY','X509','SPECIFIED')NO

保存安全类型，值为X509时表示使用该证书ssl_cipherblobNO安全加密连接的特定密码x509_issuerblobNO由CA签发有效的X509证书x509_subjectblobNO保存含主题有效的X509证书身份验证和密码策略列pluginchar(64)NO

用户验证插件。默认值为caching_sha2_passwordauthentication_stringtextYES

登录密码password_expiredenum('N','Y')NON密码过期时间用户与权限所属类别属性名数据类型是否为空默认值字段说明身份验证和密码策略列password_last_changedtimestampYES

最后修改密码时间password_lifetimesmallintunsignedYES

密码有效期Password_reuse_historysmallintunsignedYES

是否允许重用历史密码Password_reuse_timesmallintunsignedYES

密码重用时间限期Password_require_currentenum('N','Y')YES

修改密码时是否需要提供当前密码account_lockedenum('N','Y')NON用户账号锁定或解锁状态资源控制列max_questionsintunsignedNO0每小时执行查询最大次数max_updatesintunsignedNO0每小时执行更新最大次数max_connectionsintunsignedNO0每小时执行连接最大次数max_user_connectionsintunsignedNO0单个用户同时建立连接的最大数用户与权限所属类别属性名数据类型是否为空默认值字段说明权限列Select_privenum('N','Y')NON查询数据权限Insert_privenum('N','Y')NON插入数据权限Update_privenum('N','Y')NON修改现有数据权限Delete_privenum('N','Y')NON删除现有数据权限Create_privenum('N','Y')NON创建数据库和表权限Drop_privenum('N','Y')NON删除数据库和表权限Reload_privenum('N','Y')NON执行刷新和重新加载MySQL日志、权限、主机、查询和表权限Shutdown_privenum('N','Y')NON关闭MySQL服务器。将此权限提供给root账户之外的任何用户时，都应当非常谨慎Process_privenum('N','Y')NON是否可以通过SHOWPROCESSLIST命令查看其他用户的进程用户与权限所属类别属性名数据类型是否为空默认值字段说明权限列Create_role_privenum('N','Y')NON创建角色权限Drop_role_privenum('N','Y')NON删除角色权限Json列User_attributesjsonYES

用户特征用户与权限Host和User两列共同组成了复合主键区分MySQL中的账户，当Host的值为“%”表示对所有主机开放权限，值为localhost时，表示只允许该用户在本机登录。在身份验证列中，authentication_string保存使用plugin插件算法对密码进行加密运算后的字符串。凡是以_priv结尾的列均为权限列，一共有29个权限列，限于篇幅这里仅列出了11项，其余的请详见MySQL文档，权限列中存储用户的全局权限，且数据类型均为ENUM型（枚举型），其取值只有N和Y两种，N表示没有权限，为保证数据库安全性，默认时权限都为N，管理者可根据实际需要为用户赋予相应的权限。用户管理账户管理包括创建用户、删除用户、密码管理等。要实现对用户账户的管理，必须有相应的操作权限。创建用户CREATEUSER[IFNOTEXISTS]

账户名1[用户身份验证选项1][,账户名2[用户身份验证选项2]]...DEFAULTROLE角色名1[,角色名2...][WITH资源控制选项][密码管理选项|账户锁定选项]

用户身份验证选项：指明身份验证的插件和密码。这里仅列举两种主要格式。

角色名：MySQL8.0新增的角色管理，以方便更好的管理用户的权限。

密码管理选项：用于密码过期设置。

账户锁定选项：默认情况下创建的新用户都为解锁状态，可以设置ACCOUNTLOCK锁定用户。

资源控制选项：用于设定资源控制列的相关值。可选值有四种。用户管理【例7.1】创建名为user1的用户。【例7.2】创建名为user2的用户，只能在本机登录，密码为“123456“。【例7.3】创建名为user3和user4的用户，密码分别为“user333”和“user444”，其中user3只能从本地登录，user4可以从任意地址登录。

mysql>CREATEUSER'user2'@'localhost'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.02sec)mysql>CREATEUSER'user1';QueryOK,0rowsaffected(0.01sec)mysql>CREATEUSER'user3'@'localhost'IDENTIFIEDBY'user333',->'user4'@'%'IDENTIFIEDBY'user444';QueryOK,0rowsaffected(0.01sec)用户管理【例7.4】创建名为user5的用户，使用插件为“mysql_native_password”。【例7.5】创建名为user6的用户，设置密码过期时间为30天。【例7.6】创建名为user7的用户，设置该用户一小时内最多连接服务器5次。mysql>CREATEUSER'user6'@'localhost'IDENTIFIEDBY'user666'->PASSWORDEXPIREINTERVAL30DAY;QueryOK,0rowsaffected(0.01sec)mysql>CREATEUSER'user5'@'localhost'->IDENTIFIEDWITH'mysql_native_password'BY'123456';QueryOK,0rowsaffected(0.01sec)mysql>CREATEUSER'user7'@'localhost'IDENTIFIEDBY'user777'->WITHMAX_CONNECTIONS_PER_HOUR5;QueryOK,0rowsaffected(0.05sec)用户管理修改用户密码MySQL修改密码的方式主要有ALTERUSER语句、SETPASSWORD语句和外部工具mysqladmin三种。使用ALTERUSER语句修改用户密码【例7.7】修改用户user1的密码为“123456”。【例7.8】修改当前用户密码为“123456”。ALTERUSER账户名IDENTIFIEDBY'新密码';mysql>ALTERUSER'user1'@'%'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.00sec)mysql>ALTERUSERuser()IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.00sec)用户管理使用SETPASSWORD语句修改用户密码【例7.9】修改用户user1的密码为queen。

SETPASSWORD[FOR账户名]='新密码';mysql>SETPASSWORDFOR'user1'@'%'='queen';QueryOK,0rowsaffected(0.00sec)学习提示：使用SETPASSWORD语句的修改操作有可能会记录到服务器的操作日志或客户端的历史文件中，有密码泄露风险，通常不建议使用。用户管理使用mysqladmin命令修改用户密码。【例7.10】修改用户user2的密码为“1234”。mysqladmin–u用户名[-h主机地址]–ppassword新密码mysqladmin–uuser2–ppassword123456用户管理修改用户名称使用RENAMEUSER语句可以对用户进行重命名。【例7.11】修改用户user1和user2的名称分别为lily和jack，lily可从任意主机登录。RENAMEUSER旧账户名1TO新账户名2[,旧账户名1TO新账户名2]…;mysql>RENAMEUSER'user1'@'%'to'lily'@'%',->'user2'@'localhost'to'Jack'@'localhost';QueryOK,0rowsaffected(0.01sec)用户管理修改用户除修改密码和用户名外，MySQL可使用ALTERUSER语句修改用户的资源限制、账户锁定状态、密码策略等属性。【例7.12】锁定账户'jack'@'localhost’。【例7.13】为账户'lily'@'%'添加资源控制，每小时该用户查询数据库次数不超过100次。。ALTERUSER[IFEXISTS]

账户名1[用户身份验证选项1][,账户名2[用户身份验证选项2]]...DEFAULTROLE角色名1[,角色名2...][WITH资源控制选项][密码管理选项|账户锁定选项]mysql>ALTERUSER'jack'@'localhost'ACCOUNTLOCK;QueryOK,0rowsaffected(0.00sec)mysql>ALTERUSER'lily'@'%'WITHMAX_QUERIES_PER_HOUR100;QueryOK,0rowsaffected(0.00sec)用户管理删除用户使用DROPUSER语句可以删除一个或多个用户。【例7.14】删除用户user6和user7。DROPUSER[IFEXISTS]账户名1[,账户名2][,…];DROPUSERuser6@localhost,user7@localhost;学习提示：删除用户时，必须拥有数据库的全局CREATEUSER权限或DELETE权限。权限管理权限是指登录到MySQL服务器的用户，能够对数据库对象执行何种操作的规则集合。为了保证数据的安全性，数据库管理员要根据不同层级的用户进行权限分配，以限制各用户只能在所拥有的权限范围内进行数据访问。mysql数据库中的权限表和权限类型表名权限层级说明格式语法user全局级保存用户被授予的全局权限ON*.*db数据库级保存用户被授予的数据库权限ON数据库名.*tables_priv表级保存用户被授予的表权限ON数据库名.表名columns_priv列级保存用户被授予的列权限ON数据库名.表名（列名1[,列名2…]）procs_priv函数级保存用户被授予的存储过程和存储函数的权限EXECUTEON存储过程名|存储函数名proxies_priv代理保存用户被授予的代理权限PROXYON账户名1TO账户名2权限管理MySQL中的各种权限权限类别权限名称user表中的列权限级别说明数据操作权限SELECTSelect_priv全局、数据库、表、列查询数据INSERTInsert_priv全局、数据库、表、列插入数据UPDATEUpdate_priv全局、数据库、表、列更新数据DELETEDelete_priv全局、数据库、表删除数据SHOWVIEWShow_view_priv全局、数据库、表查看视图结构定义权限CREATECreate_priv全局、数据库、表创建数据库或表权限CREATEVIEWCreate_view_priv全局、数据库创建视图权限CREATEROUTINECreate_routine_priv全局、数据库创建存储过程权限CREATEROLECreate_role_priv全局、数据库创建角色的权限ALTERAlter_priv全局、数据库、表修改数据库或表等权限DROPDrop_priv全局、数据库、表删除数据库或表权限INDEXIndex_priv全局、数据库、表用索引查询表TRIGGERTrigger_priv全局、数据库、表创建和管理触发器的权限EVENTEvent_priv全局、数据库创建和管理事件权限EXECUTEExecute_priv全局、数据库执行存储过程或存储函数权限REFERENCESReferences_priv全局、数据库、表、列创建外键权限管理权限ALL[PRIVILEGES]Super_priv全局超级权限CREATEUSERCreate_user_priv全局创建用户GRANTOPTIONGrant_priv全局、数据库、表、存储过、代理允许授予用户的权限PROXYProxy_pric

与代理的用户权限相同LOCKTABLESLock_tables_priv全局、数据库确是否可以使用LOCKTABLES命令阻止对表的访问和修改SHUTDOWNShutdown_priv全局关闭MySQL服务器权限。将该权限提供给root账户之外的用户时，都应当非常谨慎。FILEFile_priv全局加载服务器主机上的文件权限管理分配权限是给特定的用户授予对象的访问权限。MySQL中使用GRANT来分配权限。【例7.15】授予用户'lily'@'%'对数据库onlinedb所有表有SELECT、INSERT、UPDATE和DELETE的权限。GRANT权限类型1[(列列表)][,权限类型1[(列列表)]][,…n]ON{*|*.*|数据库名.*|数据库名.表名} TO账号名1[用户身份验证选项1][,账户名2[用户身份验证选项2]] [WITHGRANTOPTION]

权限类型：表示可赋予或收回的用户权限，如表7-2。

列列表：表示权限作用在哪些列上，列名间由逗号隔开，默认时表示作用于整张表。

ON子句：指出所授权限的范围。

WITHGRANTOPTION：表示在授权时可以将该用户的权限转移给其他用户。mysql>GRANTSELECT,INSERT,UPDATE,DELETEONonlinedb.*TO'lily'@'%';QueryOK,0rowsaffected(0.04sec)权限管理使用SHOWGRANTS语句查看用户权限【例7.16】查看用户'lily'@'%'的权限。SHOWGRANTS[FOR账户名];mysql>SHOWGRANTSFOR'lily'@'%';++|Grantsforlily@%|++|GRANTUSAGEON*.*TO`lily`@`%`||GRANTSELECT,INSERT,UPDATE,DELETEON`onlinedb`.*TO`lily`@`%`|++2rowsinset(0.01sec)权限管理【例7.17】授予用户'jack'@'localhost'对数据库onlinedb在goods表中gname、gprice、gimage三列数据有UPDATE的权限。【例7.18】授予用户'jack'@'localhost',对数据库onlinedb中名为“proc_getGoodsPage”存储过程的执行权限。mysql>GRANTUPDATE(gname,gprice,gimage)->ONonlinedb.goodsTO'jack'@'localhost';QueryOK,0rowsaffected(0.06sec)mysql>GRANTEXECUTEONPROCEDUREonlinedb.proc_getGoodsPageTO'jack'@'localhost';QueryOK,0rowsaffected(0.05sec)权限管理收回权限收回权限是指取消某个用户的特定权限。【例7.19】收回用户'jack'@'localhost'对数据库onlinedb中名为“proc_getGoodsPage”存储过程的执行权限。REVOKEpriv_type[(column_list)][,priv_type[(column_list)]][,…]ON{table|*|*.*|database.*|database.table} FROMuser[,user]priv_type：表示用户的权限类型；column_list：表示权限作用在哪些列上，列名间有逗号隔开，缺省时表示作用整张表。ON子句：指出回收权限的范围；database.table：表示用户的权限范围，即只能在指定的数据库和表上使用权限；user：表示用户的账户，同CREATEUSER语句中user的释译。mysql>REVOKEEXECUTEONPROCEDUREc_getGoodsPage->FROM'jack'@'localhost';QueryOK,0rowsaffected(0.05sec)权限管理当要回收用户的所有权限时，只需要在REVOKE中增加ALLPRIVILEGES关键字，其语法格式如下。【例7.20】收回用户'jack'@'localhost'的所有权限。【例7.21】刷新用户权限。

REVOKEALLPRIVILEGES,GRANTOPTIONFROM账户名1[,账户名2][,…]mysql>REVOKEALLPRIVILEGES,GRANTOPTIONFROM'jack'@'localhost';QueryOK,0rowsaffected(0.00sec)mysql>FLUSHPRIVILEGES;QueryOK,0rowsaffected(0.00sec)权限管理使用Navicat图形工具管理用户和权限【例7.22】使用Navicat图形界面添加用户，账户名为'test'@'localhost'，密码为123456。权限管理【例7.23】使用图形界面为用户'test'@'localhost'设置读取onlinedb数据库的权限，并对user表有INSERT的权限。角色管理MySQL8.0+提供了对角色的支持。MySQL角色是权限的集合，像用户账户一样，可以授予和回收角色权限。对角色的操作主要包括创建角色、授予和回收角色权限、设置角色活动状态及删除角色。创建角色【例7.24】创建app_developer、app_read和app_write三个角色。CREATEROLE[IFNOTEXISTS]角色名1[,角色名2]...mysql>CREATEROLE'app_developer','app_read','app_write';QueryOK,0rowsaffected(0.00sec)角色管理授予和回收角色权限角色权限的授予和回收与用户账户权限的分配完全相同。【例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';mysql>SHOWGRANTSFORapp_developer;++|Grantsforapp_developer@%

|++|GRANTUSAGEON*.*TO`app_developer`@`%`

||GRANTALLPRIVILEGESON`onlinedb`.*TO`app_developer`@`%`|++2rowsinset(0.00sec)角色管理【例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'mysql>SHOWGRANTSFOR'lily'@'%';++|Grantsforlily@%|++|GRANTUSAGEON*.*TO`lily`@`%`||GRANTSELECT,INSERT,UPDATE,DELETEON`onlinedb`.*TO`lily`@`%`||GRANT`app_developer`@`%`TO`lily`@`%`|++3rowsinset(0.01sec)激活角色MySQL提供的SETDEFAULTROLE的语句可以激活角色。其语法格式如下。【例7.27】激活app_developer角色作为用户jack和lily的默认角色

。提示：设置全局变量activate_all_on_login为ON，也可以激活所有的角色。SETDEFAULTROLE{ALL|角色名[,用户名2]…}TO用户名1[,用户名2]…#激活角色mysql>SETDEFAULTROLE'app_developer'TO'jack'@'localhost','lily'@'%';任务2

使用事务保证数据操作的安全性通常情况下，每个查询的执行都是相互独立的，不必考虑哪个查询在前，哪个查询在后。实际应用中，较为复杂的业务逻辑通常都需要执行一组SQL语句，且这一组语句执行的数据结果存在一定的关联，语句组的执行要么都执行成功，要么什么都不做。为了控制语句组的执行过程，保证数据的一致性，MySQL使用事务机制。本任务在SQL程序基础上，详细讨论事务的基本原理和MySQL中事务的使用。任务场景事务概述事务是一组有着内在逻辑联系的SQL语句。支持事务的数据库系统要么正确执行事务里的所有SQL语句，要么把它们当作整体全部放弃，也就是说事务永远不会只完成一部分。事务可以由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。在事务中的操作，要么都执行，要么都不执行，这就是事务的目的，也是事务的重要特征。使用事务可以大大提高数据安全性和执行效率，因为在执行多条SQL命令的过程中不需要使用LOCK命令锁定整个数据表。事务概述事务必须同时满足4个特征。俗称为ACID标准。原子性（Atomicity）原子性是指数据库事务是不可分割的操作单位。一致性（Consistency）一致性是指事务将数据库从一种状态变成另一种一致的状态。在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。隔离性（Isolation）隔离性要求每个读写事务的对象与其他事务的操作对象能互相分离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。持久性（Durability）事务一旦提交，其结果就是永久性的，即使发生死机等故障，数据库也能将数据恢复。持久性（Durability）只能从事务本身的角度来保证结果的永久性，如事务提交后，所有的变化都是永久的，即使当数据库由于崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。事务的基本操作开启和提交事务MySQL中使用STARTTRANSACTION开启事务。事务手动提交使用COMMIT语句。mysql>STARTTRANSACTION;mysql>COMMIT;学习提示：MySQL默认所有为自动提交模式，由系统变量@@autocommit进行控制，值为1时，表示自动提交，值为0时则不自动提交。事务的基本操作【例7.28】手动提交事务应用示例。在onlinedb数据库中，当用户成功提交订单后（向orders表添加一条记录），用户积分按规则增加。（1）事务操作前，查看数据（2）开启事务，执行数据处理mysql>USEonlinedb;Databasechanged#查看uid为2的用户名和积分mysql>SELECTuname,ucreditFROMusersWHEREuid=2;+++|uname|ucredit|+++|蔡静|139|+++1rowinset(0.00sec)#开启事务mysql>STARTTRANSACTION;#uid为2的用户提交了订单，订单金额为125元mysql>INSERTINTOorders(uid,ocode,oamount)values(2,'O1232',125);#修改uid为2的用户积分，每10元积1分mysql>UPDATEusersSETucredit=ucredit+125/10WHEREuid=2;事务的基本操作（3）手动事务提交，查看数据#提交事物mysql>COMMIT;mysql>SELECTuname,ucreditFROMusersWHEREuid=2;+++|uname|ucredit|+++|蔡静|151|+++1rowinset(0.00sec)学习提示：MySQL中对象的创建、修改和删除操作都会隐式地执行事务的提交。如CREATEDATABASE、ALTERTABLE、DROPINDEX等。

事务的基本操作回滚事务若不想提交事务时，可使用ROLLBACK回滚事务。【例7.29】回滚事务应用示例。在onlinedb数据库中，用户蔡静消费50分用于兑换礼品，并取消兑换。（1）开启事务，执行数据处理mysql>ROLLBACK;#开启事务mysql>STARTTRANSACTION;#修改uid为2的用户积分减去50mysql>UPDATEusersSETucredit=ucredit-50WHEREuid=2;#查看用户积分mysql>SELECTuname,ucreditFROMusersWHEREuid=2;+++|uname|ucredit|+++|蔡静|101|+++1rowinset(0.00sec)事务的基本操作（2）回滚事务，查看数据#回滚事务mysql>ROLLBACK;#查看用户积分mysql>SELECTuname,ucreditFROMusersWHEREuid=2;+++|uname|ucredit|+++|蔡静|151|+++1rowinset(0.00sec)事务的基本操作设置事务保存点使用ROLLBACK回滚事务时，事务中所有的操作都会被撤销。若只需撤销部分操作，可以在事务中增加事务保存点。设定了保存点的事务可以使用以下语句回滚到指定保存点。若想删除某个保存点，可使用RELEASE语句。SAVEPOINT保存点;ROLLBACKTOSAVEPOINT保存点;RELEASESAVEPOINT保存点;事务的基本操作【例7.30】事务保存点应用示例。（1）事务操作，并设置保存点save。#开启事务mysql>STARTTRANSACTION;#修改uid为2的用户积分减去50mysql>UPDATEusersSETucredit=ucredit-50WHEREuid=2;#创建保存点save1mysql>SAVEPOINTsave1;#修改uid为2的用户积分再减去10mysql>UPDATEusersSETucredit=ucredit-10WHEREuid=2;#查看用户积分mysql>SELECTuname,ucreditFROMusersWHEREuid=2;+++|uname|ucredit|+++|蔡静|91|+++1rowinset(0.00sec)事务的基本操作（2）回滚事务到保存点save#回滚事务mysql>ROLLBACKTOSAVEPOINTsave1;#查看用户积分mysql>SELECTuname,ucreditFROMusersWHEREuid=2;+++|uname|ucredit|+++|蔡静|101|+++1rowinset(0.00sec)学习提示：在一个事务中，可以设置多个保存点。当事务执行完成后，所有保存点都会自动被删除。事务的隔离级别数据库是多线程并发的，多个用户可通过线程执行不同的事务，共享同一个数据库资源，这就可能出现数据重复读、脏读或幻读等现象。为了防止这些现象的产生，MySQL通过设置事务的隔离级别来保证事务之间相互不受影响。4类隔离级别READUNCOMMITTED（未提交读）：读取未提交内容隔离级别，即所有事务都可以看到其他未提交事务的执行结果。READCOMMITTED（已提交读）：该隔离级别满足隔离的简单定义，即一个事务只能看见已经提交事务所做的改变。这种情况下，用户可以避免脏读。REPEATABLEREAD（可重复读）：可重复读隔离级别，是MySQL的默认事务隔离级别。它确保同一个事务的多个实例在并发读取数据时，会看到同样的数据行SERIALIZABLE（可序列化）：该级别是最高的隔离级别。它通过强制事务排序，使之不可能相互冲突，从而解决幻读、脏读和重复读的问题。事务的隔离级别四种隔离级别分别有可能产生的问题：隔离级别读数据一致性脏读不可重复读幻读READUNCOMMITTED（未提交读）最低级别，只能保证不读取物理上损坏的数据YYYREADCOMMITTED（已提交读）语句级NYYREPEATABLEREAD（可重复读）事务级NNYSERIALIZABLE（可序列化）最高级别，事务级NNN事务隔离级别应用实例事务隔离级别是由系统变量@@transaction_isolation进行管理。若需要设置或查看全局的事务隔离级别，可使用@@global.tranaction_isolation。【例7.31】查看系统变量@@session.transaction_isolation。mysql>SELECT@@session.transaction_isolation;++|@@session.transaction_isolation|++|REPEATABLE-READ

|++1rowinset(0.00sec)事务隔离级别应用实例【例7.32】修改当前会话的隔离级别为提交读（READ-COMMITTED，RC）。mysql>SET@@session.transaction_isolation='READ-COMMITTED'mysql>SELECT@@session.transaction_isolation;++|@@session.transaction_isolation|++|READ-COMMITTED|++1rowinset(0.00sec)事务隔离级别应用实例【例7.33】RC隔离级别下的不可重复读示例。打开两个MySQL的客户端，分别用这两个用户登录MySQL，其中登录用户为“root”的标记为事务A，登录用户为“lily”标记为事务B。事务A窗口，设置隔离级别为“提交读（READ-COMMITTED）”。事务A窗口中，开启事务，并读取cid为1的商品，显示gid、gname、gprice。事务B窗口，开启事务，并修改gid为1的商品价格为26，并提交事务。回到事务A窗口，再次查看数据。任务3

使用锁保证事务并发的安全性锁是计算机中用于协调多个进程或线程并发访问共享资源的机制。假若在同一时刻点，多个客户端对于同一个表执行更新或查询操作，有可能因为资源拥塞造成数据的不一致。为保证多用户在读写操作时数据的一致，需要使用锁对并发进行控制。本任务主要介绍锁的分类，并使用锁在不同的事务场景下控制数据操作的一致性。任务场景锁机制概述当多用户并发访问共享资源时，数据库需要合理控制资源的访问规则，锁就是用来实现这些访问规则的重要结构。锁是MySQL并发控制的主要技术方案之一。MySQL中锁的分类全局锁：对整个数据库实例加锁。表级锁：当锁定的数据粒度是一张表时，就为表级锁。行锁：当锁定的数据粒度是一行或多行时，称为行锁。MySQL中的锁类型行锁共享锁（S锁）排他锁（X锁）：表锁意向共享锁（IS锁）意向排他锁（IX锁）参数排他锁共享锁意向排他锁意向共享锁排他锁（X）NNNN共享锁（S）NYNY意向排他锁（IX）NNYY意向共享锁（IS）NYYYMySQL中锁的应用给记录行加锁当事务级别为未提交时，不加锁；在已提交读和可重复读的事务隔离下，数据读操作都不加锁，但插入、删除和修改都会加上排他锁（X），该级别以下的级别中读写不冲突；在可序列化事务隔离级别下，读写冲突，其中读加共享锁，而写则加排他锁。使用SELECT语句显式为记录行进行加锁SELECT语句[FORshare|FORupdate]

FORshare：表示为SELECT语句查询的结果数据行加上共享锁，此时其他事务可以读这些数据，但不能写。

FORupdate：表示为SELECT语句查询的结果数据行加上排他锁，此时其他事务既不能读也不能写这些数据。MySQL中锁的应用【例7.34】多用户并发时，共享锁使用示例。时间线事务A事务BT1STARTtransaction;#加共享锁SELECTgname,gpriceFROMgoodsWHEREgid=1FORshare;

T2

STARTtransaction;#查询数据SELECTgname,gpriceFROMgoodsWHEREgid=1;#成功#查询数据，并获取共享锁SELECTgname,gpriceFROMgoodsWHEREgid=1FORshare;#成功T3

UPDATEgoodsSETgprice=gprice*0.9WHEREgid=1;#阻塞MySQL中锁的应用【例7.35】多用户并发时，排它锁使用示例。时间线事务A事务BT1STARTtransaction;#加排他锁SELECTgname,gpriceFROMgoodsWHEREgid=1FORupdate;

T2

STARTtransaction;#查询数据SELECTgname,gpriceFROMgoodsWHEREgid=1;#成功#查询数据，并获取共享锁SELECTgname,gpriceFROMgoodsWHEREgid=1FORshare;#阻塞MySQL中锁的应用死锁的产生和处理若事务加锁后相互不兼容，则会出现死锁，也就是说假定事务A的执行需要事务B释放锁才能继续，而事务B的完成也需要事务A释放锁才能完成，这时就会产生死锁。【例7.36】多用户并发时，死锁示例。时间线事务A事务BT1STARTtransaction;#加共享锁SELECT*FROMgoodsFORshare;

T2

STARTtransaction;#加共享锁SELECT*FROMgoodsFORshare;T3UPDATEgoodsSETgprice=26WHEREgid=1;#阻塞

T4

UPDATEgoodsSETgprice=50WHEREgid=2;#阻塞项目实训实践任务（1）创建用户（2）授予用户权限（3）创建角色，为角色授权（4）

创建事务实践内容（1）使用SQL语句创建一个用户zhao,密码为123456。（2）使用SQL语句创建一个用户zhang，密码为123456。（3）使用SQL语句创建一个用户wang，密码是123456，同时授予该用户对数据onlinedb的表users上拥有SELECT权限。（6）使用SQL语句收回对用户wang在表users上的SELECT权限。（4）使用SQL语句修改用户名为zhang的登录密码，修改为zhang123456。（5）使用SQL语句创建两个角色userAdmin和goodsAdmin。（6）使用SQL语句将onlinedb的表users上的UPDA