数据库原理与应用课件第9章MySQL数据库安全管理与保护机制应用

第九章MySQL数据库安全管理与保护机制应用9.1MySQL的安全设置9.2MySQL事务应用实例9.3MySQL数据库备份与恢复9.4MySQL日志9.5本章小结19.1MySQL的安全设置9.1.1MySQL权限表9.1.2用户管理9.1.3权限管理29.1.1MySQL权限表MySQL数据库管理系统是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定不同权限。默认情况下使用root用户，该用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户密码等管理权限。

在实际应用中，为了满足实际项目的需求，可以创建拥有不同权限的普通用户。MySQL在安装时会自动创建一个名为“mysql”的数据库，其中存储的都是用户权限表。用户登录以后，MySQL数据库管理系统会根据这些权限表的内容为每个用户赋予相应的权限。3user表是MySQL中最重要的一个权限表，用来记录允许连接到服务器的账号信息。需要注意的是，在user表里启用的所有权限都是全局级的，适用于所有数据库。user表中的字段大致可以分为四类：用户列、权限列、安全列和资源控制列，下面主要介绍这些字段的含义。41.用户列

用户列存储了用户连接MySQL数据库时需要输入的信息。MySQL用户列如表9.1所示。52.权限列

权限列的字段决定了用户的权限，用来描述在全局范围内允许对数据和数据库进行的操作。权限大致分为两大类，分别是高级管理权限和普通权限。高级管理权限主要对数据库进行管理，例如关闭服务的权限、超级权限和加载用户等；普通权限主要操作数据库，例如查询权限、修改权限等。63.安全列

安全列主要用来判断用户是否能够登录成功，user表中的安全列如表9.3所示74.资源控制列

资源控制列的字段用来限制用户使用的资源，user表中的资源控制列如表9.4所示89.1.2用户管理1.创建用户MySQL在安装时，会默认创建一个名为root的用户，该用户拥有超级权限，可以控制整个MySQL服务器。

在对MySQL的日常管理和操作中，为了避免有人恶意使用root用户控制数据库，通常创建一些具有适当权限的用户，尽可能地不用或少用root用户登录系统，以此来确保数据的安全访问。9MySQL提供三种方法创建用户：使用CREATEUSER语句创建用户、在mysql.user表中添加用户、使用GRANT语句创建用户。

（1）使用CREATEUSER语句创建用户

使用CREATEUSER语句创建MySQL用户，并设置相应的密码。其基本语法格式如下：CREATEUSER<用户>[IDENTIFIEDBY[PASSWORD]'password'][,用户[IDENTIFIEDBY[PASSWORD]'password']]10【例9-1】使用CREATEUSER创建一个用户，用户名是test1，密码是test1，主机名是localhost。SQL语句和执行过程如下：mysql>CREATEUSER'test1'@'localhost'IDENTIFIEDBY'test1';QueryOK,1rowsaffected(0.06sec)

结果显示，创建test1用户成功。11【例9-2】在MySQL中，使用password()函数获取密码的哈希值。

查看test1哈希值的SQL语句和执行过程如下：mysql>SELECTpassword('test1');+--------------------------------------------------------------------+|password('test1')|+--------------------------------------------------------------------+|*06C0BF5B64ECE2F648B5F048A71903906BA08E5C|+--------------------------------------------------------------------+1rowinset,1warning(0.00sec)“*06C0BF5B64ECE2F648B5F048A71903906BA08E5C”就是test1的哈希值。

下面使用密码的哈希值创建用户test1，SQL语句和执行过程如下：mysql>CREATEUSER'test1'@'localhost'IDENTIFIEDBYPASSWORD'*06C0BF5B64ECE2F648B5F048A71903906BA08E5C';QueryOK,0rowsaffected,1warning(0.00sec)执行成功后可使用密码“test1”登录。12

（2）使用INSERT语句新建用户当拥有对mysql.user表的INSERT权限后，可使用INSERT语句将用户的信息添加到mysql.user表中。通常INSERT语句只添加Host、User和authentication_string这3个字段的值。使用INSERT语句创建用户的代码如下：

INSERTINTOmysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('hostname','username',PASSWORD('password'),'','','');13【例9-3】使用INSERT语句创建名为test2的用户，主机名是localhost，密码是test2。SQL语句和执行过程如下：mysql>INSERTINTOmysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('localhost','test2',PASSWORD('test2'),'','','');QueryOK,1rowaffected,1warning(0.02sec)结果显示，新建用户成功。但是此时如果通过该账户登录MySQL服务器，还不能成功，因为test2用户还没有生效。可以使用FLUSH命令让用户生效，命令如下：FLUSHPRIVILEGES;使用FLUSH命令使MySQL刷新系统权限相关表，执行该命令需要RELOAD权限。14

（3）使用GRANT语句新建用户使用GRANT语句创建用户的基本语法形式如下:GRANTpriv_typeONdatabase.tableTOuser[IDENTIFIEDBY[PASSWORD]'password']

参数说明如表9.6所示15【例9-4】使用GRANT语句创建名为test3的用户，主机名为localhost，密码为test3，该用户对所有数据库的所有表都有SELECT权限。SQL语句和执行过程如下：mysql>GRANTSELECTON*.*TO'test3'@localhostIDENTIFIEDBY'test3';QueryOK,0rowsaffected,1warning(0.01sec)“*.*”表示所有数据库下的所有表。结果显示创建用户成功，test3用户对所有表都有查询（SELECT）权限。162.修改用户

在MySQL中，使用RENAMEUSER语句修改一个或多个已经存在的用户账号，语法格式如下：RENAMEUSER<旧用户>TO<新用户>其中，<旧用户>表示系统中已经存在的MySQL用户账号，<新用户>：表示新的MySQL用户账号。17【例9-5】使用RENAMEUSER语句将用户名test1修改为testUser1，主机是localhost。SQL语句和执行过程如下：mysql>RENAMEUSER'test1'@'localhost'TO'testUser1'@'localhost';QueryOK,0rowsaffected(0.03sec)在cmd命令行工具中，使用testUser1用户登录数据库服务器，如下所示：C:\Users\USER>mysql-hlocalhost-utestUser1-pEnterpassword:*****WelcometotheMySQLmonitor.Commandsendwith;or\g.183.删除用户

在MySQL数据库中，使用DROPUSER语句删除用户，也可以直接在mysql.user表中删除用户以及相关权限。（1）使用DROPUSER语句删除普通用户

使用DROPUSER语句删除用户的语法格式如下：DROPUSER<用户1>[,<用户2>]…其中，<用户>用来指定需要删除的用户账号。19【例9-6】使用DROPUSER语句删除用户'test1@'localhost'。SQL语句和执行过程如下：mysql>DROPUSER'test1'@'localhost';QueryOK,0rowsaffected(0.00sec)在cmd命令行工具中，使用test1用户登录数据库服务器，发现登录失败，说明用户已经删除，如下所示：C:\Users\USER>mysql-hlocalhost-utest1-pEnterpassword:****ERROR1045(28000):Accessdeniedforuser'test'@'localhost'(usingpassword:YES)20（2）使用DELETE语句删除普通用户

使用DELETE语句直接删除mysql.user表中相应的用户信息，但必须拥有mysql.user表的DELETE权限，其基本语法格式如下：DELETEFROMmysql.userWHEREHost='hostname'ANDUser='username';【例9-7】使用DELETE语句删除用户'test2'@'localhost'。SQL语句和执行过程如下所示：DELETEFROMmysql.userWHEREHost='localhost'ANDUser='test2';QueryOK,1rowsaffected(0.00sec)

结果显示删除成功，可使用SELETE语句查询mysql.user表，确定该用户是否已经删除。219.1.3权限管理1.查看用户权限

在MySQL中，可以通过查看mysql.user表中的数据记录来查看相应的用户权限，也可以使用SHOWGRANTS语句查询用户的权限。数据库下的user表中存储着用户的基本权限，可使用SELECT语句来查看，代码如下：SELECT*FROMmysql.user;要执行该语句，必须拥有对user表的查询权限。22【例9-8】创建testuser1用户并查询权限。SQL语句和执行过程如下：mysql>CREATEUSER'testuser1'@'localhost';QueryOK,0rowsaffected(0.00sec)mysql>SHOWGRANTSFOR'testuser1'@'localhost';+---------------------------------------------------------------+|Grantsfortestuser1@localhost|+---------------------------------------------------------------+|GRANTUSAGEON*.*TO'testuser1'@'localhost'|+----------------------------------------------------------------+1rowinset(0.00sec)其中，USAGEON*.*表示该用户对任何数据库和任何表都没有权限。23【例9-9】查询root用户的权限。SQL语句和执行过程如下：：mysql>SHOWGRANTSFOR'root'@'localhost';+------------------------------------------------------------------+|Grantsforroot@localhost|+----------------------------------------------------------------+|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'WITHGRANTOPTION||GRANTPROXYON''@''TO'root'@'localhost'WITHGRANTOPTION|+------------------------------------------------------------------+2rowsinset(0.00sec)242.MySQL用户授权

授权就是为某个用户赋予某些权限。例如，可以为新建的用户赋予查询所有数据库和表的权限。MySQL提供了GRANT语句来为用户设置权限。

在MySQL中，拥有GRANT权限的用户才可以执行GRANT语句，其语法格式如下：GRANTpriv_type[(column_list)]ONdatabase.tableTOuser[IDENTIFIEDBY[PASSWORD]'password'][,user[IDENTIFIEDBY[PASSWORD]'password']]...[WITHwith_option[with_option]...]253.权限类型说明（1）授予数据库权限时，<权限类型>的值，如表9.8所示26（2）授予表权限时，<权限类型>的值，如表9.9所示。27

（3）授予列权限时，<权限类型>的值只能指定为SELECT、INSERT和UPDATE，同时权限的后面需要加上列名列表column-list。

（4）授予用户权限时，<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是CREATEUSER或SHOWDATABASES，其中CREATEUSER表示授予用户可以创建和删除新用户的权限，授予用户可以使用SHOWDATABASES语句查看所有已有的数据库的定义的权限。28【例9-10】使用GRANT语句创建一个新的用户testUser，密码为testPwd。用户testUser对所有的数据有查询、插入权限，并授予GRANT权限。

SQL语句和执行过程如下：mysql>GRANTSELECT,INSERTON*.*->TO'testUser'@'localhost'->IDENTIFIEDBY'testPwd'->WITHGRANTOPTION;QueryOK,0rowsaffected,1warning(0.05sec)使用SHOWGRANTS语句查询用户testUser的权限，如下所示：mysql>SHOWGRANTSFOR'testUser'@'localhost';+-------------------------------------------------------------------------+|GrantsfortestUser@localhost|+-------------------------------------------------------------------------+|GRANTSELECT,INSERTON*.*TO'testUser'@'localhost'WITHGRANTOPTION|+-------------------------------------------------------------------------+1rowinset(0.00sec)结果显示，testUser对所有数据库的所有表有查询、插入权限，并可以将这些权限赋予给别的用户。294.删除用户权限

在MySQL中，可以使用REVOKE语句删除某个用户的某些权限（此用户不会被删除），在一定程度上可以保证系统的安全性。例如，如果数据库管理员觉得某个用户不应该拥有DELETE权限，那么就可以删除DELETE权限。

使用REVOKE语句删除权限的语法格式有两种形式。30（1）删除用户某些特定的权限，语法格式如下：REVOKEpriv_type[(column_list)]...ONdatabase.tableFROMuser[,user]...REVOKE语句中的参数与GRANT语句中的参数意义相同。其中：priv_type表示权限的类型；column_list表示权限作用于哪些列上，没有该参数时作用于整个表上；user由用户名和主机名构成，格式为“username'@'hostname'”。312）删除特定用户的所有权限，语法格式如下：REVOKEALLPRIVILEGES,GRANTOPTIONFROMuser[,user]...【例9-11】使用REVOKE语句取消用户testUser的插入权限。SQL语句和执行过程如下：mysql>REVOKEINSERTON*.*FROM'testUser'@'localhost';QueryOK,0rowsaffected(0.01sec)mysql>SHOWGRANTSFOR'testUser'@'localhost';+------------------------------------------------------------------------------------+|GrantsfortestUser@localhost|+-------------------------------------------------------------------------------------+|GRANTSELECTON*.*TO'testUser'@'localhost'WITHGRANTOPTION|+------------------------------------------------------------------------------------+1rowinset(0.00sec)结果显示，删除testUser用户的INSERT权限成功。329.2MySQL事务应用实例9.2.1实例一9.2.2实例二339.2.1实例一模拟在张三的账户减少500元后，李四的账户还未增加500元时，有其他事务访问数据库的场景。由于代码需要在两个窗口中执行，为了方便描述，将两个窗口分别简称为A窗口和B窗口。34

（1）首先使用CREATEDATABASE语句创建名为mybank的数据库，使用CREATETABLE语句创建数据表bank(customerName,currentMoney)，使用INSERT语句插入两行值(‘张三，1000.00’)和(‘李四，1.00’)，作为本实例的准备工作。35

（2）分别在A、B两个窗口开启事务36在A窗口中开启一个事务，并更新mybank数据库中bank表的数据，SQL语句和运行结果如下：mysql>USEmybank;Databasechangedmysql>BEGIN;QueryOK,0rowsaffected(0.00sec)mysql>UPDATEbankSETcurrentMoney=currentMoney-500WHEREcustomerName='张三';QueryOK,1rowaffected(0.05sec)Rowsmatched:1Changed:1Warnings:0

在B窗口中查询bank数据表中的数据，SQL语句和运行结果如下：mysql>SELECT*FROMmybank.bank;+-------------------+------------+|customerName|currentMoney|+------------------+------------+|张三|1000.00||李四|1.00|+------------------+-------------+2rowsinset(0.00sec)

（3）在A窗口中继续执行事务并提交事务，SQL语句和运行结果如下：mysql>UPDATEbankSETcurrentMoney=currentMoney+500WHEREcustomerName='李四';QueryOK,1rowaffected(0.05sec)Rowsmatched:1Changed:1Warnings:0mysql>COMMIT;QueryOK,0rowsaffected(0.07sec)37

（4）在B窗口中再次查询bank数据表的数据，SQL语句和运行结果如下：mysql>SELECT*FROMmybank.bank;+------------------+------------------+|customerName|currentMoney|+------------------+------------------+|张三|500.00||李四|501.00|+----------------+---------------------+2rowsinset(0.00sec)38

在A窗口中执行COMMIT提交事务后，对数据所做的更新将一起提交，其他事务会话读取到的是更新后的数据。从结果可以看出张三和李四的总账户余额和转账前保持一致，这样数据从一个一致性状态更新到另一个一致性状态。399.2.2实例二在实例一中，张三的账户余额已经减少到500元，如果再转出1000元，将会出现余额为负数，因此需要回滚到原始状态。40SQL语句和运行结果如下所示：mysql>BEGIN;QueryOK,0rowsaffected(0.00sec)mysql>UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='张三';QueryOK,1rowaffected(0.04sec)Rowsmatched:1Changed:1Warnings:0mysql>ROLLBACK;QueryOK,0rowsaffected(0.07sec)mysql>SELECT*FROMmybank.bank;+-------------------+------------------+|customerName|currentMoney|+------------------+------------------+|张三|500.00||李四|501.00|+------------------+------------------+2rowsinset(0.00sec)41从结果可以看出，执行事务回滚后，账户数据恢复到初始状态，即该事务执行之前的状态。在数据库操作中，为了有效保证并发读取数据的正确性，提出了事务的隔离级别。在上面实例一和二的演示中，事务的隔离级别为默认隔离级别。在MySQL中，事务的默认隔离级别是REPEATABLE-READ（可重读）隔离级别，即事务未结束时（未执行COMMIT或ROLLBACK），其它会话只能读取到未提交数据。42MySQL事务是一项非常消耗资源的功能，在使用过程中要当注意以下四点。（1）事务尽可能简短（2）事务中访问的数据量尽量最少（3）查询数据时尽量不要使用事务（4）在事务处理过程中尽量不要出现等待用户输入的操作439.3MySQL数据库备份与恢复9.3.1MySQL数据库备份9.3.2MySQL数据库恢复449.3.1MySQL数据库备份1.备份一个数据库

使用mysqldump命令备份一个数据库，其语法格式如下：mysqldump-uusername-pdbname[tbname...]>filename.sql【例9-12】使用root用户备份test数据库下的student表。打开命令行（cmd）窗口，输入备份命令和密码，运行过程如下：C:\Windows\system32>mysqldump-uroot-pteststudent>C:\student.sqlEnterpassword:****452.备份多个数据库

如果要使用mysqldump命令备份多个数据库，需要使用“--databases”参数。备份多个数据库的语法格式如下：mysqldump-uusername-P--databasesdbname1dbname2...>filename.sql加上“--databases”参数后，需指定至少一个数据库名称，多个数据库名称之间用空格隔开。【例9-13】使用root用户备份test数据库和mysql数据库。打开命令行（cmd）窗口，输入命令如下：

mysqldump-uroot-p--databasestestmysql>C:\testandmysql.sql执行完后，可以在C:\下面看到名为testandmysql.sql的文件，这个文件中存储着这两个数据库的信息。463.备份所有数据库mysqldump命令备份所有数据库的语法格式如下：mysqldump-uusername-P--all-databases>filename.sql使用“--all-databases”参数时，不需要指定数据库名称。【例9-14】使用root用户备份所有数据库。打开命令行（cmd）窗口，输入命令如下：mysqldump-uroot-p--all-databases>C:\all.sql执行完后，可以在C:\下面看到名为all.sql的文件，这个文件中存储着所有数据库的信息。479.3.2MySQL数据库恢复当数据丢失或意外损坏时，可以通过恢复已经备份的数据来尽量减少数据的丢失和破坏造成的损失。在MySQL中，可以使用mysql命令来恢复备份的数据。mysql命令可以执行备份文件中的CREATE语句和INSERT语句，即mysql命令可以通过CREATE语句来创建数据库和表，通过INSERT语句来插入备份的数据。mysql命令语法格式如下：mysql-uusername-P[dbname]<filename.sql48【例9-15】使用root用户恢复所有数据库。打开命令行（cmd）窗口，输入命令如下：mysql-uroot-p<C:\all.sql执行完后，MySQL数据库就已经恢复了all.sql文件中的所有数据库。需要注意的是，如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。因为，其对应的sql文件中含有CREATEDATABASE语句，可以通过该语句创建数据库。创建数据库之后，可以执行sql文件中的USE语句选择数据库，然后在数据库中创建表并且插入记录499.4MySQL日志9.4.1MySQL日志及分类9.4.2MySQL二进制日志9.4.3MySQL使用日志还原数据库509.4.1MySQL日志及分类

日志是数据库的重要组成部分，主要用来记录数据库的运行情况、日常操作和错误信息。

在MySQL中，日志可以分为二进制日志、错误日志、通用查询日志和慢查询日志。对于MySQL的管理工作而言，这些日志文件是不可缺少的。

在MySQL所支持的日志文件里，除了二进制日志文件外，其它日志文件都是文本文件。默认情况下，MySQL只会启动错误日志文件，而其它日志则需要手动启动。

使用日志文件有优点也有缺点。启动日志文件后，虽然可以对MySQL服务器性能进行维护，但是会降低MySQL的执行速度。日志文件还会占用大量的硬盘空间。

因此，是否启动日志文件以及启动何种类型的日志文件要根据具体的应用来决定。519.4.2MySQL二进制日志

二进制日志（BinaryLog）也可叫作变更日志（UpdateLog），是MySQL中非常重要的日志。

默认情况下，二进制日志功能是关闭的。查看二进制日志是否开启的命令格式如下：mysql>SHOWVARIABLESLIKE'log_bin';+------------------+-------+|Variable_name|Value|+------------------+-------+|log_bin|OFF|+-------------------+-------+1rowinset,1warning(0.02sec)

结果显示，二进制日志是关闭的。521.启动二进制日志文件通过在配置文件中添加log-bin选项来开启二进制日志，命令格式如下：[mysqld]log-bin=dir/[filename]其中，dir参数指定二进制文件的存储路径；filename参数指定二进制文件的文件名，其形式为filename.number。532.查看二进制日志文件（1）查看二进制日志文件列表查看MySQL中的二进制日志文件列表，命令及执行过程如下：mysql>SHOWbinarylogs;+----------------------------+-----------------------+|Log_name|File_size|+----------------------------+------------+------------+|hostname-bin.000001|177||hostname-bin.000002|154|+----------------------------+-------------+----------+2rowsinset(0.00sec)54

（2）查看当前正在写入的二进制日志文件查看当前MySQL中正在写入的二进制日志文件，命令及执行过程如下：mysql>SHOWmasterstatus;+----------+-------+----------+------------+---------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+----------+-------+----------+------------+--------------------+|hostname-bin.000002|154||||+----------+-------+----------+------------+--------------------+1rowinset(0.00sec)55

（3）查看二进制日志文件内容二进制日志使用二进制格式存储，不能直接打开查看。需使用mysqlbinlog命令，查看二进制日志，语法格式如下：mysqlbinlogfilename.number需要注意的是，mysqlbinlog命令只在当前文件夹下查找指定的二进制日志，因此需要在二进制日志所在的目录下运行该命令，否则将会找不到指定的二进制日志文件。56【例9-16】使用mysqlbinlog命令，查看C:\log目录下的mylog.000001文件。打开命令行（cmd）窗口，代码执行如下：C:\Users\11645>cdC:\logC:\log>mysqlbinlogmylog.000001/*!50530SET@@SESSION.PSEUDO