《数据库技术及应用（MySQL）》课件 第8章 数据库安全管理

MySQL数据库技术及应用第8章数据库安全管理MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同的用户指定允许的权限。日志也是MySQL数据库的重要组成部分，日志文件中记录着MySQL数据库运行期间发生的变化。为了防止人为操作和自然灾难引起数据丢失或破坏，需要定期地、制度化地对数据进行备份，所以备份和恢复是数据库安全管理中非常重要的内容。前言学习要点了解数据的安全控制机制掌握数据库安全控制的各种方法能够对MySQL数据库中的用户和权限进行管理能够对MySQL日志文件进行管理熟悉每种数据备份与数据恢复的使用第8章数据库安全管理学习内容8.1MySQL的安全性8.2用户管理8.3MySQL权限管理8.4MySQL日志管理8.5MySQL数据备份与恢复第8章数据库安全管理在MySQL数据库管理中，主要是通过访问控制和权限表来实现其安全性控制的。在服务器上运行MySQL时，提供了访问控制，以此确保MySQL服务器的安全访问，即不同的用户对各自需要的数据具有不同的访问权。而数据库管理员的职责就是当MySQL遭受非法入侵时拒绝访问，保证数据库的安全性和完整性。存储账户权限信息表主要有user、db、host、tables_priv、columns_priv和procs_priv。第8章数据库安全管理8.1MySQL的安全性8.1.1MySQL访问控制工作过程MySOL的访问控制可以分为两个阶段：连接核实阶段和请求核实阶段。1.连接核实阶段2.请求核实阶段第8章数据库安全管理8.1MySQL的安全性8.1.2MySQL权限表1.user表2.db表和host表3.tables_priv表和columns_priv表4.procs_priv表第8章数据库安全管理8.1MySQL的安全性8.1.2MySQL安全管理等级MySQL安全管理等级包括：操作系统安全性服务器安全性数据库安全性数据库对象安全性第8章数据库安全管理8.1MySQL的安全性学习内容8.1MySQL的安全性8.2用户管理8.3MySQL权限管理8.4MySQL日志管理8.5MySQL数据备份与恢复第8章数据库安全管理MySQL用户包括超级用户(root)和普通用户。8.2.1创建用户【例8-1】创建用户zhang，口令为1234；创建用户jia，口令为qwe；创建用户zhou，口令为x456；创建用户yang，口令为asd6。CREATEUSER'zhang'@'localhost'IDENTIFIEDBY'1234','jia'@'localhost'IDENTIFIEDBY'qwe','zhou'@'localhost'IDENTIFIEDBY'x456','yang'@'localhost'IDENTIFIEDBY'asd6';第8章数据库安全管理8.2用户管理8.2.2删除用户删除用户使用DROPUSER语句。使用DROPUSER语句，必须拥有mysql数据库的全局CREATEUSER权限或DELETE权限。语法格式如下。DROPUSER'username'@'host'[,…];【例8-2】删除用户yang。DROPUSER'yang'@'localhost';第8章数据库安全管理8.2用户管理8.2.3修改用户口令修改用户口令使用SETPASSWORD语句。语法格式如下。SETPASSWORDFORuser='password’;【例8-3】将用户zhang的口令修改为'123456'。SETPASSWORDFOR'zhang'@'localhost'='123456’;省略FOR子句，可以更改自己的口令。SETPASSWORD='123456';第8章数据库安全管理8.2用户管理学习内容8.1MySQL的安全性8.2用户管理8.3MySQL权限管理8.4MySQL日志管理8.5MySQL数据备份与恢复第8章数据库安全管理当成功创建用户账户后，还不能执行任何操作，需要为该用户授予适当的权限。可以使用SHOWGRANTSFOR语句来查询用户的权限。【例8-4】查询用户jia的权限。SHOWGRANTSFOR'jia'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限权限的授予使用GRANT语句。语法格式如下。GRANTpriv_type[(column_list)][,priv_type[(columnlist)]]…ON[object_type]priv_levelTOuser_specification[,user_specification]…[REQUIRE|NONE|ssl_option[[AND]ssl_option…|][WITHwith_option…]第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限1.授予列权限授予列权限时，priv_level的值只能是SELECT、INSERT和UPDATE，同时权限后面需要加上列名列表。【例8-5】授予用户zhang对数据库jxxx的表student，拥有列Sno和列Sname的SELECT权限。GRANTSELECT(sno,sname)ONjxxx.studentTO'zhang'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限2.授予表权限【例8-6】创建新用户zhu和ma，并设置对应的系统登录口令后，授予它们在jxxx数据库中的student表上的查询和更新的权限。--创建用户CREATEUSER'zhu'@'localhost'IDENTIFIEDBY'1234','ma'@'localhost'IDENTIFIEDBY'5678';--授权GRANTSELECT,UPDATEONjxxx.studentTO'zhu'@'localhost','ma'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限3.授予数据库权限【例8-7】授予用户ma在所有数据库中创建新表和删除表的权限。GRANTCREATE,DROPON*.*TO'ma'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限3.授予数据库权限【例8-8】授予用户zhang对所有数据库中所有表的查询和添加行的权限。GRANTSELECT,INSERTON*.*TO'zhang'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限3.授予数据库权限【例8-9】授予用户zhou对jxxx数据库执行所有数据库操作的权限。GRANTALLONjxxx.*TO'zhou'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限4.授予用户权限【例8-10】授予已存在用户zhou创建新用户的权限。GRANTCREATEUSERON*.*TO'zhou'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限4.授予用户权限【例8-11】通过user表来查询以上用户对所有数据库的权限。SELECTuser,select_priv,insert_priv,create_priv,drop_priv,create_user_privFROMmysql.user;第8章数据库安全管理8.3MySQL权限管理8.3.1授予权限5.权限的转移【例8-12】授予已存在用户ma在jxxx数据库中的student表上添加数据行和更新表的值的权限，并允许他将自身的权限授予给其他用户。GRANTINSERT,UPDATEONjxxx.studentTO'ma'@'localhost'WITHGRANTOPTION;第8章数据库安全管理8.3MySQL权限管理8.3.2撤销权限撤销用户的权限可以使用REVOKE语句。如果使用REVOKE语句，必须拥有mysql数据库的全局CREATEUSER权限或UPDATE权限。语法格式如下。REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]…ON[object_type]priv_levelFROMuser[,user]…或REVOKEALLPRIVILIEGES,GRANTOPTIONFROMuser[,user]…第8章数据库安全管理8.3MySQL权限管理8.3.2撤销权限【例8-13】收回用户zhang在jxxx数据库中的student表上的添加行的权限。REVOKEINSERTONjxxx.studentFROM'zhang'@'localhost';第8章数据库安全管理8.3MySQL权限管理8.3.2撤销权限【例8-14】可以通过tables_priv表，查询以上用户对student表的权限。SELECTdb,user,table_name,table_priv,column_privFROMmysql.tables_privWHEREtable_name='student';第8章数据库安全管理8.3MySQL权限管理学习内容8.1MySQL的安全性8.2用户管理8.3MySQL权限管理8.4MySQL日志管理8.5MySQL数据备份与恢复第8章数据库安全管理MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件(各自存储了不同类型的日志)，包括错误日志(log-err)、通用查询日志(log)、二进制日志(log-bin)及慢查询日志(log-slow-queries)。MySQL8.0又新增两种支持的日志：中继日志和数据定义语句日志。从日志当中可以查询到MySQL数据库的运行情况、用户操作、错误信息等，可以为MySQL管理和优化提供必要的信息。对于MySQL的管理工作而言，这些日志文件是不可缺少的。第8章数据库安全管理8.4MySQL日志管理8.4.1MySQL日志MySQL日志主要分为6类，使用这些日志文件，可以查看MySQL内部发生的事情。·二进制日志：记录所有更改数据的语句，可以用于数据复制。·错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。·通用查询日志：记录建立的客户端连接和执行的语句。·慢查询日志：记录所有执行时间超过long_query_time的所有查询或不适用索引的查询。·中继日志：记录复制时从主服务器收到的数据改变。·数据定义语句日志：记录数据定义语句执行的元数据操作。第8章数据库安全管理8.4MySQL日志管理8.4.2二进制日志1.启用和设置二进制日志二进制日志记录了所有用户对数据库数据的修改操作。MySQL数据库在默认情况下是开启二进制日志文件的。通过命令查看二进制日志是否开启的语句如下。SHOWVARIABLESLIKE'log_bin';第8章数据库安全管理8.4MySQL日志管理8.4.2二进制日志2.查看二进制日志SHOWBINARYLOGS语句可以查看当前的二进制日志文件个数及其文件名。查看二进制日志文件个数及文件名，命令如下。SHOWBINARYLOGS;第8章数据库安全管理8.4MySQL日志管理8.4.2二进制日志3.删除二进制日志(1)使用PURGEMASTERLOGS命令删除指定的日志文件。(2)使用RESETMASTER命令删除所有的二进制日志文件。第8章数据库安全管理8.4MySQL日志管理8.4.2二进制日志4.使用二进制日志还原数据库如果MySQL服务器启用了二进制日志，在数据库意外丢失数据时可以使用mysqlbinlog工具从指定的时间点开始(例如最后一次备份)直到现在或另一个指定的时间点的日志中恢复数据。要想从二进制日志恢复数据，需要知道当前二进制日志文件的路径和文件名，一般可以从配置文件(即f或者my.ini，文件名取决于MySQL服务器的操作系统)中找到路径。mysqlbinlog恢复数据的语法格式如下。mysqlbinlog[option]filename|mysql-uuser-ppass第8章数据库安全管理8.4MySQL日志管理8.4.3通用查询日志通用查询日志记录了用户的所有操作，包括启动和关闭MySQL服务，包括对数据库的增/删/查/改等信息，在并发操作大的环境下会产生大量的信息，从而导致不必要的磁盘I/O，会影响MySQL的性能。1.启用通用查询日志MySQL服务器在默认情况下，并没有开启通用查询日志。如果需要打开通用查询日志，可以通过修改f或者my.ini配置文件来启动通用查询日志。在[mysqld]组下加入log选项，语法格式如下。[mysqld]general-log=ONgeneral_log_file=[path/[filename]]第8章数据库安全管理8.4MySQL日志管理8.4.3通用查询日志2.查看通用查询日志通用查询日志记录了用户的所有操作。通过查看通用查询日志，可以了解用户对MySQL进行的操作。通用查询日志是以文本文件的形式存储在文件系统中的，在Windows操作系统下可以使用文本编辑器来查看，在Linux操作系统下可以使用vim工具或者gedit工具来查看。第8章数据库安全管理8.4MySQL日志管理8.4.3通用查询日志3.停止通用查询日志MySQL服务器停止通用查询日志功能有两种方法，一种是修改f或者my.ini文件，把[mysqld]组下的general_log值设置为OFF，修改保存后，再重启MySQL服务，即可生效。第二种方法是使用SET语句来设置，跟启动通用查询日志的操作类似。第8章数据库安全管理8.4MySQL日志管理8.4.3通用查询日志4.删除通用查询日志通用查询日志会记录用户的所有操作。如果数据的使用非常频繁，那么通用查询日志的数据量将会非常大，会占用服务器非常大的磁盘空间。数据管理员可以删除很长时间之前的查询日志，以保证服务器上的硬盘空间。(1)手动删除通用查询日志(2)使用mysqladmin命令直接删除通用查询日志第8章数据库安全管理8.4MySQL日志管理8.4.4慢查询日志慢查询日志是用来记录查询时长超过指定时间的日志。通过慢查询日志，可以一边查找出执行时间较长、执行效率较低的语句，一边进行优化。MySQL服务器在默认情况下，慢查询日志是关闭的。如果需要打开慢查询日志功能，有两种方法，第一种是通过修改f或者my.ini配置文件再重启MySQL服务来启动慢查询日志；第二种是通过SET语句来设置慢查询日志开关来启动慢查询日志功能。第8章数据库安全管理8.4MySQL日志管理8.4.4慢查询日志1.修改配置文件开启慢查询日志通过修改f或者my.ini文件，在里面设置选项，再重启MySQL服务，可以开启慢查询日志。可在[mysqld]组下设置long_query_time、slow-query-log和slow_query_log_file的值，具体形式如下：[mysqld]long_query_time=nslow-query-log=ONslow_query_log_file=[path/[filename]]第8章数据库安全管理8.4MySQL日志管理8.4.4慢查询日志2.使用SET语句开启慢查询日志除了修改配置文件，也支持通过SET语句修改慢查询日志相关的全局变量来开启慢查询日志。第8章数据库安全管理8.4MySQL日志管理8.4.4慢查询日志3.查看慢查询日志MySQL的慢查询日志是以文本形式存储的，可以直接使用文本编辑器查看。在慢查询日志中，记录着执行时间较长的查询语句，用户可以从慢查询日志中获取执行效率较低的查询语句，为查询优化提供重要的依据。第8章数据库安全管理8.4MySQL日志管理8.4.4慢查询日志4.停止慢查询日志MySQL服务器停止慢查询日志功能有两种方法：一种方法是修改f或者my.ini文件，把[mysqld]组下的slow_query_log值设置为OFF，修改保存后，再重启MySQL服务，即可生效；另一种方法是使用SET语句来设置。这两种方法跟启动慢查询日志的操作类似。第8章数据库安全管理8.4MySQL日志管理8.4.4慢查询日志5.删除慢查询日志如果慢查询日志文件过大，需要回收空间加以利用(或者其他原因)，可以删除慢查询日志文件。慢查询日志和通用查询日志的删除方法是一样的。一种就是手动删除慢查询日志，另一种就是使用mysqladmin命令来删除。(1)手动删除慢查询日志(2)使用mysqladmin命令直接删除慢查询日志第8章数据库安全管理8.4MySQL日志管理学习内容8.1MySQL的安全性8.2用户管理8.3MySQL权限管理8.4MySQL日志管理8.5MySQL数据备份与恢复第8章数据库安全管理在任何数据库环境中，总会有不确定的意外情况发生，比如意外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。数据备份的任务与意义就在于当灾难发生后通过备份的数据完整、快速、简捷、可靠地恢复原有系统。为了有效防止数据丢失，并将损失降到最低，并且保持关系完整性与统一性，用户应定期地、制度化地对MySQL数据库服务器做维护。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复，这样就尽可能地降低了意外原因导致的损失。备份和恢复是数据库管理中常用的操作，提供备份和恢复机制是一项重要的数据库管理工作。第8章数据库安全管理8.5MySQL数据备份与恢复8.5.1备份数据在MySQL数据库中，常用的备份数据的方法有两种，一种是使用SELECT…INTOOUTFILE语句导出表数据，一种是使用MYSQLDUMP命令备份数据。1.使用SELECT…INTOOUTFILE语句导出表数据语法格式如下。SELECTcolumnlistFROMtableWHEREconditionINTOOUTFILE'filename'[OPTIONS]第8章数据库安全管理8.5MySQL数据备份与恢复8.5.1备份数据2.使用MYSQLDUMP命令备份数据(1)备份表使用MYSQLDUMP命令可以备份一个数据库的一个表或多个表。语法格式如下：MYSQLDUMP-uusername-pdbname[tbname,[tbname…]]>filename.sql第8章数据库安全管理8.5MySQL数据备份与恢复8.5.1备份数据2.使用MYSQLDUMP命令备份数据(2)备份数据库使用MYSQLDUMP命令可以备份一个数据库的一个表或多个数据库。备份一个数据库的语法格式如下。MYSQLDUMP-uusername-pdbname>filename.sql第8章数据库安全管理8.5MySQL数据备份与恢复8.5.1备份数据2.使用MYSQLDUMP命令备份数据(3)备份整个数据库系统使用MYSQLDUMP命令可以备份整个数据库系统。语法格式如下。MYSQLDUMP-uusername-p--all-databases>filename.sql第8章数据库安全管理8.5MySQL数据备份与恢复8.5.2恢复数据在MySQL数据库中，常用的恢复数据的方法有两种，一种是使用LOADDATAINFILE语句导入表数据，一种是使用MYSQL命令恢复数据。1.使用LOADDATAINFILE语句导入表数据语法格式如下。LOADDATA[LOCAL]INFILEfilenameINTOTABLE'tablename'[OPTIONS][IGNOREnumberLINES]第8章数据库安全管理8.5MySQL数据备份与恢复8.5.2恢复数据在MySQL数据库中，常用的恢复数据的方法有两种，一种是使用LOADDATAINFILE语句导入表数据，一种是使用MYSQL命令恢复数据。2.使用MYSQL命令恢复数据使用MYSQL命令可以使用先前备份的文件恢复数据。语法格式如下：MYSQL-uusername-p[dbname]<filename.sql第8章数据库安全管理8.5MySQL数据备份与恢复8.5.3使用Workbench备份与恢复数据除了使用命令进行MySQL数据库的数据备份与恢复以外，还可以使用第三方工具更方便的进行数据备份与恢复，比如MySQLWorkbench。MySQLWorkbench工作空间的Adminis