MySQL数据库原理与应用(微课版)ch13MySQL权限与安全管理

第13章

MySQL权限与安全管理本章概述MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限。MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。用户管理包括管理用户账户、权限等。本章学习目标了解什么是权限表；掌握权限表的用法；掌握账户管理的方法；掌握权限管理的方法；掌握访问控制的用法；熟练运用本章知识来新建MySQL用户。目录01020304权限管理账户管理访问控制权限表05提升安全性06管理角色07综合管理用户权限本章小结08权限表MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由mysql_install_db脚本初始化。存储账户权限信息表主要有user、db、host、tables_priv、columns_priv和procs_priv。存储权限的数据表权限表user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。例如，一个用户在user表中被授予了DELETE权限，则该用户可以删除MySQL服务器上所有数据库中的任何记录。MySQL8.0中user表有42个字段，这些字段可以分为4类，分别是用户列、权限列、安全列和资源控制列。user表权限表db表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。db表比较常用。db表权限表tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。tables_priv表和columns_priv表的结构分别如表所示。tables_priv表和columns_priv表表表tables_priv表结构字段名数据类型默认值HostCHAR(60)

DbCHAR(64)

UserCHAR(16)

Table_nameCHAR(64)

GrantorCHAR(77)

TimestamptimestampCURRENT_TIMESTAMPTable_privset('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','CreateView','ShowView','Trigger')

Column_privset('Select','Insert','Update','References')权限表tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。tables_priv表和columns_priv表的结构分别如表所示。tables_priv表和columns_priv表表表

columns_priv表结构字段名数据类型默认值HostCHAR(60)

DbCHAR(64)

UserCHAR(16)

Table_nameCHAR(64)

Column_nameCHAR(64)

TimstamptimestampCURRENT_TIMESTAMPColumn_privset('Select','Insert','Update','References')权限表procs_priv表可以对存储过程和存储函数设置操作权限。procs_priv表结构如表所示。procs_priv表表

procs_priv表结构字段名数据类型默认值HostCHAR(60)

DbCHAR(64)

UserCHAR(16)

Routine_nameCHAR(64)

Routine_typeenum('FUNCTION','PROCEDURE')NULLGrantorCHAR(77)

Proc_privset('Excute','AlterRoutine','Grant')

TimstamptimestampCURRENT_TIMESTAMP目录权限管理账户管理访问控制权限表提升安全性管理角色综合管理用户权限本章小结0102030405060708权限表使用root用户登录到本地MySQL服务器的mysql库，命令如下：mysql-hlocalhost-uroot-P3307-p登录和退出MySQL服务器权限表执行CREATEUSER或GRANT语句时，服务器会修改相应的用户授权表，添加或者修改用户及其权限。CREATEUSER语句的基本语法格式如下：CREATEUSERuser_specification[,user_specification]…User_specification:user@host[IDENTIFIEDBY[PASSWORD]'password'|IDENTIFIEDWITHauth_plugin[AS'auth_string']]新建普通用户权限表使用CREATEUSER创建一个用户，用户名是buaalandy，密码是mypass，主机名是localhost，命令如下：CREATEUSER'buaalandy'@'localhost'IDENTIFIEDBY'mypass';新建普通用户权限表使用INSERT语句创建新用户的基本语法格式如下：INSERTINTOMYSQL.user(Host,User,authentication_string)VALUES('host','username',MD5('password'));Host、User、authentication_string分别为user表中的主机、用户名称和密码字段；MD5()函数为密码加密函数。直接操作MySQL用户表权限表在MySQL数据库中，可以使用DROPUSER语句删除用户，也可以直接通过DELETE从MySQL.user表中删除对应的记录来删除用户。DROPUSER语句语法如下：DROPUSERuser[,user];删除普通用户权限表DELETE语句基本语法格式如下：DELETEFROMMySQL.userWHEREhost='hostname'anduser='username'host和user为user表中的两个字段，两个字段的组合确定所要删除的账户记录。使用DELETE语句删除用户权限表root用户拥有很高的权限，不仅可以修改自己的密码，还可以修改其他用户的密码。root用户登录MySQL服务器后，可以通过SET语句修改MySQL.user表，以及通过UPDATE语句修改用户的密码。创建用户user1，命令如下：CREATEUSER'user1'@'localhost'IDENTIFIEDBY'my123';root用户修改普通用户密码目录权限管理账户管理访问控制权限表提升安全性管理角色综合管理用户权限本章小结0104050607080302权限管理账户权限信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。MySQL的各种权限权限管理授权：授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授予权限。授予的权限可以分为多个层级：1.全局层级：全局权限适用于一个给定服务器中的所有数据库。这些权限存储在MySQL.user表中。GRANTALLON*.*和REVOKEALLON*.*只授予和撤销全局权限。2.数据库层级：数据库权限适用于一个给定数据库中的所有目标。这些权限存储在MySQL.db和MySQL.host表中。GRANTALLONdb_name.和REVOKEALLONdb_name.*只授予和撤销数据库权限。3.表层级：表权限适用于一个给定表中的所有列。这些权限存储在MySQL.talbes_priv表中。GRANTALLONdb_name.tbl_name和REVOKEALLONdb_name.tbl_name只授予和撤销表权限。4.列层级：列权限适用于一个给定表中的单一列。这些权限存储在MySQL.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。5.子程序层级：CREATEROUTINE、ALTERROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。MySQL的各种权限权限管理SHOWGRANTS语句可以显示指定用户的权限信息，使用SHOWGRANT查看账户信息的基本语法格式如下：SHOWGRANTSFOR'user'@'host';其中，user表示登录用户的名称，host表示登录的主机名称或者IP地址。在使用该语句时，要确保指定的用户名和主机名都要用单引号引起来，并使用“@”符号将两个名字分隔开。例如：SHOWGRANTSFOR'user2'@'localhost'\G;收回权限目录权限管理账户管理访问控制权限表提升安全性管理角色综合管理用户权限本章小结0105060708020403访问控制当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝连接，即客户端用户连接请求中会提供用户名称、主机地址名和密码，MySQL使用user表中的3个字段（Host、User和authentication_string）执行身份检查，服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。一、连接核实阶段访问控制建立了连接之后，服务器进入访问控制的阶段2。对在此连接上的每个请求，服务器检查用户要执行的操作，然后检查是否有足够的权限来执行它。这正是在授权表中的权限列发挥作用的地方。这些权限可以来自user、db、host、tables_priv或columns_priv表。二、请求核实阶段目录权限管理账户管理访问控制权限表提升安全性管理角色综合管理用户权限本章小结0106070802030504提升安全性MySQL8.0支持多种AES256加密模式，通过更大的密钥长度和不同的块模式增强了高级加密标准。例如，通过加密函数AES_ENCODE()和解密函数AES_DECODE()来提高安全强度。AES_ENCODE()的语法格式如下：AES_ENCODE(str,pswd_str)//str为需要加密的字符串，参数pswd_str是密钥AES_DECODE()的语法格式如下：AES_ENCODE(str,pswd_str)//str为需要解密的字符串，参数pswd_str是密钥提升安全性MySQL8.0允许数据库管理员手动设置账户密码过期时间。任何密码超期的账户想要连接服务端时都必须更改密码。通过设置default_password_lifetime参数可以设置账户过期时间。首先查看系统中账户的过期时间。输入语句如下：SELECTuser,host,password_last_changed,password_lifetime,password_expiredFROMmysql.user\G;提升安全性MySQL新增了“安全模式”的安装形式，从而可以避免用户的数据被泄漏。用户通过以下方式来提升MySQL安装的安全性：（1）为root账户设置密码。（2）移除能从本地主机以外的地址访问数据库的root账户。（3）移除匿名账户。（4）移除test数据库，该数据库默认可被任意用户甚至匿名账户访问。目录权限管理账户管理访问控制权限表提升安全性管理角色综合管理用户权限本章小结0107080203040605管理角色创建角色，执行语句如下：CREATEROLErole_tt;#创建角色给角色授予权限，执行语句如下：GRANTSELECTONdb.*to'role_tt';#给角色role_tt授予查询权限创建用户user3，执行语句如下：CREATEUSER'user3'@'%'identifiedby'123456';为用户user3赋予角色role_tt，执行语句如下：GRANT'role_tt'TO'user3'@'%';给角色role_tt删除insert权限，执行语句如下：REVOKEINSERTONdb.*FROM'role_tt';查看默认角色