MySQL数据库教程课件

第10章数据安全学习目标掌握用户和数据权限的管理方法。掌握备份和恢复数据的方法。了解日志文件。10.1用户和数据权限管理为了保证数据库的安全性与完整性，MySQL要求只有拥有相应权限的用户可以访问数据库中相应的对象，执行相应的合法操作，用户应对他们需要的数据具有适当的访问权。10.1.1MySQL的权限系统1．权限系统的工作过程当用户登录到MySQL时，MySQL的权限系统通过下面两个阶段进行认证。（1）连接核实阶段对登录到MySQL的用户进行身份认证，判断该用户是否属于合法用户。若是合法用户，则通过认证；若是不合法用户，则拒绝连接。（2）请求核实阶段用户登录成功之后，MySQL进入请求核实阶段。针对该用户的每一个请求，MySQL都会检查它要执行什么操作，以及用户是否有权限来执行这些操作。10.1.1MySQL的权限系统2．权限表（1）user表：存储可以登录MySQL的所有用户的信息。（2）db表：存储用户对数据库的操作权限，决定了用户能操作哪些数据库。（3）tables_priv表：存储用户对数据表的操作权限，决定了用户能操作哪些数据表。（4）columns_priv表：存储用户对数据表中字段的操作权限，决定了用户能操作哪些字段。（5）procs_priv表：存储用户对存储过程和存储函数的操作权限，决定了用户是否能够创建、修改、删除或执行存储过程和存储函数。10.1.2用户管理1．添加用户2．删除用户3．修改用户名4．修改用户密码1．添加用户添加用户的语句：CREATEUSER用户名1[IDENTIFIEDBY"密码1"]

[,用户名2[IDENTIFIEDBY"密码2"]…];（1）用户名：格式为user_name@host_name，其中user_name是用户名，host_name是主机名。如果未指定主机名，默认是“%”，表示一组主机。（2）IDENTIFIEDBY子句：指定密码，密码必须用英文单引号或双引号引起来。

添加用户举例【例10-1】添加用户test1，主机名是localhost，密码为123。

CREATEUSERtest1@localhostIDENTIFIEDBY"123";【例10-2】添加用户test2，密码为456；添加用户test3，密码为111；添加用户test4，密码为666，主机名均为localhost。CREATEUSERtest2@localhostIDENTIFIEDBY"456",test3@localhostIDENTIFIEDBY"111",test4@localhostIDENTIFIEDBY"666";2．删除用户删除用户的语句：DROPUSER用户名1[,用户名2…];【例10-3】删除用户test4。DROPUSERtest4@localhost;3．修改用户名修改用户名的语句：RENAMEUSER原用户名1TO新用户名1[,原用户名2TO新用户名2…];【例10-4】将用户test3的名称修改为test。RENAMEUSERtest3@localhostTOtest@localhost;4．修改用户密码修改用户密码的语句：SETPASSWORD[FOR用户名]="新密码";省略FOR子句时，表示修改当前用户的密码。

【例10-5】将用户test的密码修改为123456。SETPASSWORDFORtest@localhost="123456";10.1.3权限管理新用户拥有的权限很少，只被允许进行不需要权限的操作。合理的权限设置可以保证数据库的安全。MySQL中的主要权限名称及说明。10.1.3权限管理1．授予权限2．查看权限3．转移权限4．收回权限1．授予权限授予权限的语句：GRANT权限1[(字段列表1)][,权限2[(字段列表2)]…]ON[目标]{表名|*|*.*|数据库名.*}TO用户1[,用户2…][WITHGRANTOPTION];（1）权限：指定权限名称，如INSERT、DELETE等。（2）字段列表：指定权限作用于哪些字段，字段之间用英文逗号隔开。默认为所有字段。（3）目标：可以是TABLE、FUNCTION或PROCEDURE。（4）表名：表示权限作用于指定的数据表（5）*.*：表示权限作用于所有数据库和所有数据表。（6）*：如果未指定当前数据库，含义与*.*相同，否则权限作用于当前数据库。（7）数据库名.*：表示权限作用于指定数据库中的所有数据表。（8）WITHGRANTOPTION：含义是可以将自己的权限授予其他用户。授予权限举例授予权限的语句：GRANT权限1[(字段列表1)][,权限2[(字段列表2)]…]ON[目标]{表名|*|*.*|数据库名.*}TO用户1[,用户2…][WITHGRANTOPTION];【例10-6】使用GRANT语句将数据库scoredb中学生表student的SELECT权限，以及姓名sname和性别sex字段的UPDATE权限授予用户test2。GRANTSELECT,UPDATE(sname,sex)ONscoredb.studentTOtest2@localhost;

授予权限举例下面分别利用test1和test2两个用户验证学生表student中的姓名sname和性别sex字段的SELECT和UPDATE权限。

（1）以用户test2登录MySQL进行验证。由于用户test2具有相应的权限，所以张函的性别sex字段值修改成功。授予权限举例下面分别利用test1和test2两个用户验证学生表student中的姓名sname和性别sex字段的SELECT和UPDATE权限。

（2）以用户test1登录MySQL进行验证。由于用户test1没有任何授权，选择数据库scoredb时就已经被拒绝访问，更不用说修改性别sex字段值了。C:\ProgramFiles\MySQL\MySQLServer8.0\bin>mysql-utest1-pEnterpassword:***WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis15Serverversion:8.0.30MySQLCommunityServer-GPLCopyright(c)2000,2022,Oracleand/oritsaffiliates.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>USEscoredb;ERROR1044(42000):Accessdeniedforuser'test1'@'localhost'todatabase'scoredb'授予权限举例授予权限的语句：GRANT权限1[(字段列表1)][,权限2[(字段列表2)]…]ON[目标]{表名|*|*.*|数据库名.*}TO用户1[,用户2…][WITHGRANTOPTION];【例10-7】使用GRANT语句授予用户test1对数据库scoredb中的教师表teacher的查询SELECT和插入INSERT权限。GRANTSELECT,INSERTONscoredb.teacherTOtest1@localhost;

授予权限举例授予权限的语句：GRANT权限1[(字段列表1)][,权限2[(字段列表2)]…]ON[目标]{表名|*|*.*|数据库名.*}TO用户1[,用户2…][WITHGRANTOPTION];【例10-8】使用GRANT语句将数据库scoredb的所有权限授予用户test。GRANTALLONscoredb.*TOtest@localhost;

2．查看权限查看权限的语句：SHOWGRANTSFOR用户名;【例10-9】使用SHOWGRANTS语句查看用户test1的权限信息。SHOWGRANTSFORtest1@localhost;mysql>SHOWGRANTSFORtest1@localhost;+--------------------------------------------------------------------+|Grantsfortest1@localhost|+--------------------------------------------------------------------+|GRANTUSAGEON*.*TO`test1`@`localhost`||GRANTSELECT,INSERTON`scoredb`.`teacher`TO`test1`@`localhost`|+--------------------------------------------------------------------+3．转移权限在GRANT语句中，将WITH子句指定为WITHGRANTOPTION，表示TO子句中所指定的所有用户都具有将自己所拥有的权限授予其他用户的权利。【例10-10】使用GRANT语句将数据库scoredb中所有表的CREATE、ALTER和DROP权限授予用户test1，并允许用户test1将这些权限授予其他用户。GRANTCREATE,ALTER,DROPONscoredb.*TOtest1@localhostWITHGRANTOPTION;获得GRANTOPTION权限后，用户test1就可以将CREATE、ALTER和DROP权限传递给其他用户。4．收回权限（1）收回用户的所有权限的语句REVOKEALLPRIVILEGES,GRANTOPTIONFROM用户1[,用户2…];【例10-11】使用REVOKE语句收回用户test1的所有权限。REVOKEALLPRIVILEGES,GRANTOPTIONFROMtest1@localhost;mysql>SHOWGRANTSFORtest1@localhost;+----------------------------------------------+|Grantsfortest1@localhost|+----------------------------------------------+|GRANTUSAGEON*.*TO`test1`@`localhost`|+----------------------------------------------+4．收回权限（2）收回特定的权限的语句REVOKE权限1[(字段列表1)][,权限2[(字段列表2)]…]ON[目标]{表名|*|*.*|数据库名.*}FROM用户1[,用户2…];【例10-12】使用REVOKE语句收回用户test2对数据库scoredb学生表student中性别sex字段的UPDATE权限。REVOKEUPDATE(sex)ONscoredb.studentFROMtest2@localhost;mysql>SHOWGRANTSFORtest2@localhost;+------------------------------------------------------------------------------+|Grantsfortest2@localhost|+------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO`test2`@`localhost`||GRANTSELECT,UPDATE(`sname`)ON`scoredb`.`student`TO`test2`@`localhost`|+------------------------------------------------------------------------------+10.2数据备份与数据恢复为了防止人为操作和自然灾害等导致数据丢失或损坏，需要定期对数据库进行备份，以便出现意外并造成数据库数据丢失或损坏时，可以使用备份的数据进行恢复，将不良影响和损失降到最低。10.2.1数据备份数据备份命令:Mysqldump功能：将数据库中的数据备份成一个文本文件，各个数据库及数据表的结构和数据都存储在该文本文件中。工作原理：先将需要备份的数据库及数据表的结构转换成相应的CREATE语句，然后将每张数据表中的数据转换成一条INSERT语句。以后在恢复数据时，直接使用这些CREATE和INSERT语句创建数据库及数据表并插入数据。使用要求：必须以Windows管理员身份打开命令提示符窗口，并且切换到MySQL安装目录下的bin文件夹。cdC:\ProgramFiles\MySQL\MySQLServer8.0\bin数据备份举例1．使用mysqldump命令备份数据表mysqldump-u用户名-p[密码]数据库名表名1[表名2…]>文件名.sql【例10-13】以用户root的身份使用mysqldump命令将数据库scoredb中的课程表course备份到D:\backup文件夹中，备份文件名为course.sql。mysqldump-uroot-pscoredbcourse>D:\backup\course.sql数据备份举例备份文件course.sql的主要内容数据备份举例2.使用mysqldump命令备份数据库mysqldump-u用户名-p[密码]--databases数据库名1[数据库名2…]>文件名.sql【例10-14】以用户root的身份使用mysqldump命令将数据库scoredb备份到D:\backup文件夹中，备份文件名为scoredb.sql。mysqldump-uroot-p--databasesscoredb>D:\backup\scoredb.sql数据备份举例备份文件scoredb.sql的部分内容。数据备份举例3．使用mysqldump命令备份全部数据库mysqldump-u用户名-p[密码]--all-databases>文件名.sql【例10-15】以用户root的身份使用mysqldump命令将全部数据库备份到D:\backup文件夹中，备份文件名为wholedb.sql。mysqldump-uroot-p--all-databases>D:\backup\wholedb.sql10.2.2数据恢复数据恢复命令：mysql-u用户名–p[数据库名]<文件名.sql注意：mysql命令和mysqldump命令一样，必须直接在Windows命令提示符下执行。数据恢复举例【例10-16】新建一个数据库testdb，以用户root的身份使用mysql命令将D:\backup\course.sql文件中备份的课程表course导入数据库testdb中。①在MySQL命令行使用CREATEDATABASE语句创建一个新数据库testdb。CREATEDATABASEtestdb;②在Windows命令提示符下使用mysql命令恢复课程表course。mysql-uroot-ptestdb<D:\backup\course.sql③在MySQL命令行使用SHOWTABLES语句查看数据库testdb中的表。USEtestdb;SHOWTABLES;需要在Windows命令提示符窗口和MySQL命令行窗口之间交替执行。mysql>USEtestdb;Databasechangedmysql>SHOWTABLES;+------------------+|Tables_in_testdb|+------------------+|course|+------------------+数据恢复举例【例10-17】删除已经创建好的数据库scoredb后，以用户root的身份使用mysql命令将D:\backup\scoredb.sql文件中备份的数据库scoredb恢复。①在MySQL命令行使用DROPDATABASE命令删除数据库scoredb。

DROPDATABASEscoredb;②在Windows命令提示符下使用mysql命令恢复数据库scoredb。mysql-uroot-p<D:\backup\scoredb.sql③在MySQL命令行使用SHOWDATABASES查看数据库。SHOWDATABASES;mysql>SHOWDATABASES;+--------------------+|Database|+--------------------+|information_schema||librarydb||mysql||performance_schema||scoredb||sys||testdb|+--------------------+10.3日志文件日志文件中记录了MySQL的运行情况、用户操作和错误信息等。10.3.1二进制日志文件二进制日志文件主要记录数据库的变化情况，包含所有修改了数据和潜在修改了数据的语句，以及每条语句的执行时间信息，但不包含没有修改任何数据的语句。二进制日志文件的主要作用是帮助用户最大程度地恢复数据，因为它记录了用户对数据库进行的所有修改。1．二进制日志文件的配置2．查看二进制日志文件列表3．暂停二进制日志功能4．删除二进制日志文件5．使用二进制日志文件恢复数据库1．二进制日志文件的配置（1）log_bin：值为ON，表明二进制日志功能已经开启。（2）log_bin_basename：日志文件的路径和文件名前缀，其中DESKTOP-AGMQ460是主机名。（3）log_bin_index：索引文件名。其内容为所有二进制日志文件的清单。（4）log_bin_trust_function_creators：变量的值表明是否允许用户创建可能导致不安全的函数。（5）log_bin_use_v1_row_events：二进制日志文件的版本信息。mysql>SHOWVARIABLESLIKE"LOG_BIN%";+-----------------+---------------------------------------------------------------------+|Variable_name|Value|+-----------------+---------------------------------------------------------------------+|log_bin|ON||log_bin_basename|C:\ProgramData\MySQL\MySQLServer8.0\Data\DESKTOP-AGMQ460-bin||log_bin_index|C:\ProgramData\MySQL\MySQLServer8.0\Data\DESKTOP-AGMQ460-bin.index||log_bin_trust_function_creators|OFF||log_bin_use_v1_row_events|OFF|+-----------------+---------------------------------------------------------------------+2．查看二进制日志文件列表查看二进制日志文件列表的语句：SHOWBINARYLOGS;mysql>SHOWBINARYLOGS;+----------------------------+-----------+-----------+|Log_name|File_size|Encrypted|+----------------------------+-----------+-----------+|DESKTOP-AGMQ460-bin.000001|214|No||DESKTOP-AGMQ460-bin.000002|214|No||DESKTOP-AGMQ460-bin.000003|214|No||DESKTOP-AGMQ460-bin.000004|7716|No||DESKTOP-AGMQ460-bin.000005|180|No||DESKTOP-AGMQ460-bin.000006|19711|No||DESKTOP-AGMQ460-bin.000007|31768|No||DESKTOP-AGMQ460-bin.000008|180|No||DESKTOP-AGMQ460-bin.000009|180|No||DESKTOP-AGMQ460-bin.000010|157|No|+----------------------------+-----------+-----------+3．暂停二进制日志功能暂停二进制日志功能的语句：SETSQL_LOG_BIN={0|1};（1）0：暂停二进制日志功能。（2）1：启动暂停的二进制日志功能。4．删除二进制日志文件（1）删除比指定编号小的所有二进制日志文件PURGE{MASTER|BINARY}LOGSTO"二进制日志文件名";【例10-18】删除所有编号小于000005的二进制日志文件。PURGEMASTERLOGSTO"DESKTOP-AGMQ460-bin.000005";mysql>SHOWBINARYLOGS;+----------------------------+-----------+-----------+|Log_name|File_size|Encrypted|+----------------------------+-----------+-----------+|DESKTOP-AGMQ460-bin.000005|180|No||DESKTOP-AGMQ460-bin.000006|19711|No||DESKTOP-AGMQ460-bin.000007|31768|No||DESKTOP-AGMQ460-bin.000008|180|No||DESKTOP-AGMQ460-bin.000009|180|No||DESKTOP-AGMQ460-bin.000010|157|No|+----------------------------+-----------+-----------+4．删除二进制日志文件（2）删除指定日期以前的所有二进制日志文件PURGE{MASTER|BINARY}LOGSBEFORE"日期和时间";【例10-19】删除2022年9月30日23:00:00之前的二进制日志文件。

PURGEMASTERLOGSBEFORE"2022-09-3023:00:00";4．删除二进制日志文件（3）删除所有二进制日志文件RESETMASTER;mysql>SHOWBINARYLOGS;+----------------------------+-----------+-----------+|Log_name|File_size|Encrypted|+----------------------------+-----------+-----------+|DESKTOP-AGMQ460-bin.000001|157|No|+----------------------------+-----------+-----------+5．使用二进制日志文件恢复数据库二进制日志文件虽然可以用来恢复MySQL数据库，但是其占用的存储空间非常大。因此，在备份MySQL数据库之后，应该删除备份之前的所有二进制日志文件。如果备份之后发生异常，造成数据库的数据损失，则可以通过备份之后产生的二进制日志文件进行还原。【例10-21】假定已经完成了数据库scoredb备份，备份文件为scoredb.sql，并且删除了所有的二进制日志文件。创建全新的二进制日志文件后，在课程表course中插入了一门新课程，在学生表student中删除了一名退学的学生。就在刚才，数据库崩溃了，请将数据库恢复。请模拟这个过程。10.3.2错误日志文件（1）在MySQL数据库中，错误日志功能默认是开启的，并且无法被关闭。默认情况下，MySQL会将启动和停止数据库的信息以及一些错误信息记录到错误日志文件。（2）错误日志文件名的形式为“主机名.err”，例如，DESKTOP-AGMQ460.err。（3）错误日志文件以文本文件形式存储，可以通过记事本程序查看。（4）错误日志文件可以直接删除10.3.3通用查询日志文件（1）通过查看通用查询日志文件，可以了解用户对MySQL数据库进行了哪些操作。（2）通用查询日志文件名的形式为“主机名.log”。例如，DESKTOP-AGMQ460.log。（3）通用查询日志文件以文本文件形式存储，可以通过记事本程序查看。（4）通用查询日志文件也可以直接删除。10.3.4慢查询日志文件（1）通过查看慢查询日志文件，可以找出执行时间较长、效率低下的查询语句，然后进行优化。（2）慢查询日志文件名的形式为“主机名-slow.log”。例如，DESKTOP-AGMQ460-slow.log。（3）慢查询日志文件以文本文件形式存储，可以通过记事本程序查看。（4）慢查询日志文件可以直接删除。10.4课堂案例：学生成绩管理数据库的数据安全1．用户管理（1）添加用户user1、user2和user3，主机名是localhost，密码均为123。CREATEUSERuser1@localhostIDENTIFIEDBY"123",user2@localhostIDENTIFIEDBY"123",user3@localhostIDENTIFIEDBY"123";10.4课堂案例：学生成绩管理数据库的数据安全1．用户管理（2）将用户user3的名称修改为user。RENAMEUSERuser3@localhostTOuser@localhost;（3）将用户user的密码修改为123456。SETPASSWORDFORuser@localhost="123456";（4）删除用户user。DROPUSERuser@localhost;10.4课堂案例：学生成绩管理数据库的数据安全2．权限管理（1）将数据库scoredb中课程表course的课程名称cname和学时hours字段的UPDATE权限授予用户user1。GRANTUPDATE(cname,hours)ONscoredb.courseTOuser1@localhost;（2）将数据库scoredb中选修成绩表score的INSERT和DELETE权限授予用户user1。GRANTINSERT,DELETEONscoredb.scoreTOuser1@localhost;（3）授予用户user2对数据库scoredb拥有所有操作权限，并允许用户user2将这些权限授予其他用户。GRANTALLONscoredb.*TOuser2@localhostWITHGRANTOPTION;10.4课堂案例：学生成绩管理数据库的数据安全2．权限管理（4）查看用户