MySQL数据库原理与应用项目化教程（微课版） 课件 (含思政) 项目11-数据库的安全管理

项目十一数据库的安全管理

MySQL提供了一套完整的安全性机制保证数据的安全性：要防止普通用户随意访问，另外，如果发生软硬件故障、自然灾害和操作失误等意外情况时，要能恢复数据，尽可能地挽回或减少数据的损失。本项目学习MySQL8.0的用户管理、权限管理、数据的备份与还原等操作。知识目标：识记系统数据库mysql中user、db、tables_priv等权限表的作用。识记创建用户、修改用户密码、删除用户语句的语法。识记查看、授予、收回用户权限语句的语法。识记mysqldump命令备份数据库的语法。能力目标：能用语句创建用户、修改用户密码、删除用户。能用语句查看、授予、收回用户权限。能选择一个、多个或所有数据库进行备份并还原。任务11.1任务11.2用户管理权限管理任务11.3数据的备份与还原

任务11.1用户管理针MySQL主要包含两种用户：root用户和普通用户。root用户是系统安装时自带的，为超级管理员，拥有软件提供的一切权限，普通用户只拥有创建后赋予它的权限。通过root用户完成查看用户、创建用户、修改用户密码、删除用户等操作。【任务描述】用户管理11.1【相关知识】11.1.311.1.111.1.2

修改用户密码查看用户

创建用户11.1.4

删除用户用户管理11.1查看用户并没有直接的SQL语句，而要通过系统数据库mysql的user表，直接用SELECT*FROMuser来查看有哪些用户，会列出数据库所有用户及其权限。user表有几十个字段，大致分为4类：用户列，权限列、安全列和资源控制列，user表的主键是Host和User列的组合。用户登录服务器时，服务器会根据user表中Host、User、authentication_string、account_locked（分别存储主机名、用户名、密码、帐户锁定状态）这几个字段的值进行判断是否接受用户的登录。说明：

MySQL5.7版本开始user表的password字段改为了authentication_stringMySQL8.0版本开始移除了加密函数password()。查看用户11.1.1创建用户用CREATEUSER语句。语法格式：CREATEUSER'用户名'@'主机名'

[IDENTIFIED[WITH身份验证加密规则]BY'密码']；说明：用户名：登录数据库服务器使用的用户名。主机名：表示允许用户从哪台机器登陆，本机登陆填'localhost'，远程登陆则填'%'

IDENTIFIEDBY子句用来设置用户登录密码。身份验证加密规则：可选择caching_sha2_password或mysql_native_password，如果省略WITH子句，默认为配置文件my.ini中参数default_authentication_plugin的值。创建用户11.1.21.使用ALTERUSER语句

语法格式：ALTERUSER'用户名'@'主机名'[IDENTIFIEDBY'密码']；

2.使用SETPASSWORD命令

语法格式：

SETPASSWORD[FOR'用户名'@'主机名']='新密码';说明：FOR子句用来指定修改密码的帐户，如果省略表示设置当前用户密码。普通用户可以用设置当前用户密码的方式修改自己的密码。修改用户密码11.1.3删除用户帐号用DROPUSER

语句。

语法格式：

DROPUSER'用户名'@'主机名'[,…];说明：一次可以删除多个用户帐号，用户帐号之间用逗号隔开。删除用户11.1.4【任务实施】查看所有用户的主机名、用户名、密码及账户锁定状态。USEmysql

SELECTHost,User,authentication_string,account_lockedFROMuser;用户管理11.1图11.1查看所有用户的主机名、用户名、密码及账户锁定状态【任务实施】2．创建一个新用户：用户名为“zhang”，密码为“z123”，只允许本机登陆。CREATEUSER'zhang'@'localhost'IDENTIFIEDBY'z123';用户管理11.1图11.2创建“zhang”用户并查看【任务实施】3.创建一个新用户：用户名为“wang”，密码为“w123”，允许其从其他电脑远程登陆。

CREATEUSER'wang'@'%'IDENTIFIEDBY'w123';用户管理11.1图11.3创建“wang”用户并查看【任务实施】重开一个cmd窗口，试着以“wang”用户连接MySQL服务器，结果如图11.4所示。用户管理11.1图11.4“wang”用户连接MySQL服务器【任务实施】4.修改“wang”用户的密码，新密码为“123456”。（1）通过root用户修改ALTERUSER'wang'@'%'IDENTIFIEDBY'123456';或者SETPASSWORDFOR'wang'@'%'='123456';（2）用户自己修改SETPASSWORD='123456';用户管理11.1【任务实施】用户管理11.1图11.5修改“wang”用户密码【任务实施】5.删除“wang”、“zhang”这二个用户。

DROPUSER'wang'@'%','zhang'@'localhost';用户管理11.1图11.6删除“wang”用户、“zhang”用户思政小贴士【数据泄密案例，结合骚扰电话、诈骗电话的生活体验】重视信息泄露和信息安全问题，树立正确的职业道德和职业操守加强责任感11.1用户管理任务11.2权限管理新创建的用户可以连接服务器，但不具备访问数据库的实质权限。

通过给指定用户授权使其能访问“学生成绩管理”数据库的数据，还可以根据需要随时收回权限。【任务描述】权限管理11.2【相关知识】11.2.311.2.111.2.2

收回权限

查看权限

授予权限权限管理11.2用户权限存储在mysql数据库的user、db、tables_priv、columns_priv、procs_priv等权限表中。user表存储全局权限，全局权限对任何数据库有效；db表存储特定数据库的权限；tables_priv表、columns_priv表和procs_priv表分别存储特定表、特定列和特定存储过程及存储函数的权限。用户通过身份验证成功连接MySQL服务器后，服务器要对用户进行操作权限验证，权限验证按照user表、db表，tables_priv表，columns_priv表的顺序进行。查看权限11.2.1MySQL提供的常用权限请参见教材中的表11-1。通过SELECT语句查询权限表，可以查看用户不同级别的权限。MySQL提供了SHOWGRANTS语句可以方便地查看某个用户所拥有的各级权限，语法格式如下：SHOWGRANTS[FOR'用户名'@'主机名'];说明：如果省略FOR子句，表示当前用户查看自己的权限。通过FOR子句，root用户可以查看指定用户的权限。查看权限11.2.1授予用户权限使用

GRANT语句，语法格式如下：

GRANT权限[(列名列表)]ON库名.表名

TO'用户名'@'主机名'[,…]

[WITHwith-option[withoption]…];说明：权限可以是多个，用逗号隔开，如果是全部权限，可以使用allprivileges，简写为all。列名列表可选，表示权限作用于哪些列上，没有此选项则表示权限作用于整个表上。如果授予的权限对任何数据库有效，则“库名.表名”要写成“*.*”；如果权限对指定数据库的所有表都有效，则“库名.表名”要写成“库名.*”。

可以把权限一次授予多个用户，用户帐号之间用逗号隔开。WITH子句指定授权选项，最常用的是WITHGRANTOPTION，表示允许被授权用户将此权限授予其他用户

。授予权限11.2.2收回用户权限使用REVOKE语句，语法格式如下：REVOKE权限[(列名列表)]ON库名.表名

FROM'用户名'@'主机名'[,…]可以把一个用户的权限用一条语句全部收回，语法格式如下：REVOKEALL,GRANTOPTIONFROM'用户名'@'主机名'[,…];说明：MySQL的权限不能级联收回。收回某个用户的全部权限后，用户权限回到刚创建时状态，除了登录连接服务器，几乎没有什么权限。收回权限11.2.3【任务实施】1.查看二个新建用户的权限（帐号分别为‘zhang’@’localhost’、‘wang’@’localhost’）。（1）root用户查看普通用户权限SHOWGRANTSFOR’wang’@’localhost’;权限管理11.2图11.7root用户查看wang用户的权限【任务实施】（2）zhang用户查看自己的权限再开一个cmd窗口，以zhang用户身份连接服务器，输入以下代码：SHOWGRANTS;权限管理11.2图11.8zhang用户查看自己的权限【任务实施】2.授予“zhang”用户查询及修改studb数据库中所有表数据的权限，并允许其将此权限授予其他用户。

GRANTSELECT,UPDATEONstudb.*TO'zhang'@'localhost'WITHGRANTOPTION;权限管理11.2图11.9给zhang用户授权并查看权限【任务实施】3.通过“zhang”用户给“wang”用户授予查看stuinfo表的权限。GRANTSELECTONstudb.stuinfoTO'wang'@'localhost';权限管理11.2图11.10zhang用户给wang用户授权【任务实施】4.授予用户“zhang”在studb数据库中创建表的权限。GRANTCREATEONstudb.*TO'zhang'@'localhost';权限管理11.2图11.11给zhang用户再次授权并查看权限【任务实施】5.收回“zhang”、“wang”二个用户所有权限。

（1）收回zhang用户的所有权限

REVOKEALLPRIVILEGES,GRANTOPTIONFROM'zhang'@'localhost';权限管理11.2图11.12收回zhang用户的全部权限【任务实施】zhang用户权限全部收回了，但是通过查看wang用户的权限，证明授予wang用户的权限没有受到影响。权限管理11.2图11.13zhang授予wang的权限不受影响【任务实施】（2）收回wang用户的查看权限REVOKESELECTONstudb.stuinfoFROM'wang'@'localhost';权限管理11.2图11.14收回wang用户的权限并查看任务11.3数据的备份与还原数据的备份与还原操作不但可以避免因意外发生造成的数据损失，还可以实现数据库的迁移，因为数据库的迁移不能通过简单的复制（或剪切）、粘贴数据文件操作来实现。利用mysqldump工具导出“学生成绩管理”数据库的sql脚本文件实现数据的备份，通过执行该脚本文件实现数据还原。【任务描述】11.3数据的备份与还原【相关知识】21

还原数据

备份数据11.3数据的备份与还原mysqldump

工具支持一次备份单个数据库、多个数据库和所有数据库。备份一个数据库

语法格式如下：

mysqldump-uusername-pdbname[tbname1tbname2…]>backupname.sql说明：Username：执行备份的用户名。dbname：要备份的数据库的名称，tbname1、tbname2表示数据库中的表名。backupname.sql：备份导出的SQL脚本文件名，可以包含该文件所在路径，文件扩展名“sql”表示是SQL脚本文件。备份产生的SQL脚本文件中不包含创建数据库的语句。备份数据11.3.1(2)备份多个数据库语法格式如下：mysqldump-uusername-p--databasesdbname1dbname2….>backupname.sql说明：databases前面有2个“-”，“—databases”后面跟多个数据库名称，多个数据库名之间用空格分隔。备份产生的SQL脚本文件中包含了创建数据库的语句。备份数据11.3.1(3)

备份所有数据库语法格式如下：mysqldump-uusername-p--all-databases>backupname.sql说明：“--all-databases”表示备份所有数据库。备份产生的SQL脚本文件中包含了创建数据库的语句。备份数据11.3.1MySQL还原数据有两种常用的方式。

(1)使用mysql工具语法格式如下：mysql–uusername-p[dbname]<backupname.sql说明：dbname：要还原数据库的名称，只有还原一个数据库时需要提供。backupname.sql：需要还原的SQL脚本文件，如果不在当前路径下，要指定该文件所在路径。

由于只有在备份一个数据库时，导出的SQL脚本文件中没有创建数据库的语句，因此，在还原一个数据库的数据前，要确认该数据库已存在，如果不存在要先创建。还原数据11.3.2(2)使用source命令

source命令是mysql客户端程序提供的命令，语法格式如下：

sourcebackupname.sql说明：backupname.sql：需要还原的SQL脚本文件，如果不在当前路径下，要指定该文件所在路径。如果backupname.sql是单个数据库的备份文件，执行source命令前需要先用USE命令切换到需要还原的数据库。还原数据11.3.2思政小贴士【备份一个数据库的备份文件与多个数据库的备份文件的不同（一个有建库语句，一个没有），因此还原一个数据库和多个数据库的操作不同】学习要知其然知其所以然（知道是这样，也知道为什么是这样），知其然是知识，所以然是方法，学习方法比单纯的知识要重要的多。11.3数据的备份与还原【任务实施】备份studb数据库并还原。（1）备份数据

打开一个cmd窗口，在DOS提示符后输入命令：

mysqldump-uroot-pstudb>d:\backup\studb.sql11.3数据的备份与还原图11.15备份一个数据库（studb）【任务实施】（2）还原数据①用mysql工具

mysql-uroot-pstudb<d:\backup\studb.sql11.3数据的备份与还原图11.16用mysql工具还原studb数据库【任务实施】②用source命令启动mysql客户端程序并切换到studb数据库，输入下面的命令：sourced:\back