在前面的文章中 MYSQL用户权限管理（一）介绍了MYSQL用户和权限体系以及访问控制的两个阶段；
在本篇博文中接着上一篇的内容介绍MYSQL账户以及密码管理，会涉及到账户的创建、删除、授权等问题；
一、MySQL账户及密码管理
MySQL提供许多语句用来管理用户账号，这些语句可以用来管理包括登陆和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性，需要通过账户管理来保证。下面介绍四种用来管理账号密码的方式：
1.1 添加账户
您可以通过两种方式创建MySQL帐户：
（1）通过使用用于创建帐户和建立其权限的帐户管理语句，例如 CREATE USER和 GRANT。这些语句使服务器对基础授权表进行适当的修改。
（2）通过DML语句操作mysql权限表，如操作MySQL授权表INSERT， UPDATE或 DELETE（不推荐这样操作）。
首选方法是使用帐户管理语句，因为它们比直接操作授权表更简洁，更不容易出错。
root@localhost [(none)]>CREATE USER 'wjq'@'%' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.01 sec) root@localhost [(none)]>GRANT ALL PRIVILEGES ON *.* TO 'wjq'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) root@localhost [(none)]>CREATE USER 'seiang'@'localhost' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.01 sec) root@localhost [(none)]>GRANT select,insert ON *.* TO 'seiang'@'localhost'; Query OK, 0 rows affected (0.00 sec)
更改用户名
root@localhost [(none)]>rename user 'seiang'@'localhost' to 'seiang1'@'localhost'; Query OK, 0 rows affected (0.01 sec)
通过GRANT指令（只能用于添加新用户）
# 创建mytest用户并对此库下的所有表赋予所有权限; root@localhost [mysql]>grant all on *.* to 'mytest'@'%' identified by 'seiang'; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [mysql]>show warnings\G; *************************** 1. row *************************** Level: Warning Code: 1287 Message: Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. 1 row in set (0.00 sec)
同样，通过grant创建用户在MySQL 5.7中使用会被警告，是一个即将被移除的特性。推荐使用CREATE USER语句。
虽然介绍了好几种方法创建用户，但真正在使用中，最好按照规范使用CREATE USER创建用户，GRANT设置权限，ALTER USER更改密码，而不要直接将用户信息插入user表中，因为user表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了user表中的记录，则可能会对MySQL服务器造成很大的影响。
要查看帐户的权限，请使用 SHOW GRANTS：
root@localhost [(none)]>show grants for 'wjq'@'%'; +------------------------------------------------------------+ | Grants for wjq@% | +------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'wjq'@'%' WITH GRANT OPTION | +------------------------------------------------------------+ 1 row in set (0.00 sec)
要查看帐户的非特权属性，请使用 SHOW CREATE USER：
root@localhost [(none)]>show create user 'wjq'@'%'\G; *************************** 1. row *************************** CREATE USER for wjq@%: CREATE USER 'wjq'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK 1 row in set (0.00 sec)
1.2 删除账户
root@localhost [(none)]>drop user 'wjq'@'%'; Query OK, 0 rows affected (0.00 sec)
1.3 预留用户账户
root@localhost [(none)]>select user,host from mysql.user; +---------------+---------------+ | user | host | +---------------+---------------+ | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+---------------+ 3 rows in set (0.01 sec)
MySQL安装过程需要首先初始化。在初始化期间，MySQL会创建应被视为保留的用户帐户：
- ‘root’@’localhost：用于管理目的。此帐户具有所有权限，可以执行任何操作。
严格地说，此帐户名称不是保留的，因为某些安装会将root帐户重命名为 其他名称，以避免使用众所周知的名称公开具有高权限的帐户。- ‘mysql.sys’@’localhost’：作为 DEFINER对 sys架构对象。使用该 mysql.sys帐户可避免DBA重命名或删除root 帐户时出现的问题。此帐户已锁定，因此无法用于客户端连接。
- ‘mysql.session’@’localhost’：由插件内部使用以访问服务器。此帐户已锁定，因此无法用于客户端连接
1.4 设置账户资源限制
限制客户端使用MySQL服务器资源的一种方法是将全局 max_user_connections系统变量设置为非零值。这限制了任何给定帐户可以同时进行的连接数，但对连接后客户端可以执行的操作没有限制。此外，设置 max_user_connections无法管理个人帐户。MySQL管理员都对这两种控件都很感兴趣。
为了解决这些问题，MySQL允许使用这些服务器资源限制个人帐户：
帐户每小时可以发出的查询数
帐户每小时可以发布的更新次数
帐户每小时可以连接到服务器的次数
帐户与服务器同时连接的数量
客户端可以发出的任何语句都会针对查询限制进行计数，除非其结果是从查询缓存中提供的。只有修改数据库或表的语句才会计入更新限制。
要在帐户创建时为帐户建立资源限制，请使用该CREATE USER 语句。要修改现有帐户的限制，请使用 ALTER USER。提供一个 WITH子句，命名每个资源是有限的。每个限制的默认值为零（无限制）。例如，要创建可以访问customer数据库但只能以有限方式访问数据库的新帐户 ，请发出以下语句：
root@localhost [(none)]>CREATE USER 'wjq'@'%' IDENTIFIED BY 'password'
-> WITH MAX_QUERIES_PER_HOUR 20
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5
-> MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.00 sec)
限制类型不必全部在 WITH子句中命名，但命名的那些可以按任何顺序存在。每个每小时限制的值应该是一个表示每小时计数的整数。对于 MAX_USER_CONNECTIONS，限制是一个整数，表示帐户的最大同时连接数。如果此限制设置为零，则全局 max_user_connections系统变量值确定同时连接的数量。如果max_user_connections也为零，则帐户没有限制。
要修改现有帐户的限制，请使用 ALTER USER语句。以下语句将用户seiang查询限制更改为100：
root@localhost [(none)]>ALTER USER 'seiang'@'localhost' WITH MAX_QUERIES_PER_HOUR 100; Query OK, 0 rows affected (0.00 sec)
要删除限制，请将其值设置为零。
例如，要删除每小时可连接的次数限制，请使用以下语句：
root@localhost [(none)]>ALTER USER 'seiang'@'localhost' WITH MAX_QUERIES_PER_HOUR 0; Query OK, 0 rows affected (0.00 sec)
如前所述，帐户的同时连接限制由 MAX_USER_CONNECTIONS限制和 max_user_connections系统变量确定。假设全局max_user_connections值为10，并且三个帐户具有指定的个别资源限制，如下所示：
ALTER USER 'user1'@'localhost' WITH MAX_USER_CONNECTIONS 0; ALTER USER 'user2'@'localhost' WITH MAX_USER_CONNECTIONS 5; ALTER USER 'user3'@'localhost' WITH MAX_USER_CONNECTIONS 20;
user1连接限制为10（全局 max_user_connections值），因为它的MAX_USER_CONNECTIONS限制为零。user2并且分别user3具有5和20的连接限制，因为它们具有非零MAX_USER_CONNECTIONS限制
针对于数据库在user表与该帐户对应的表行中存储帐户的资源限制的列为max_questions，max_updates和 max_connections列存储每小时限制和max_user_connections列存储MAX_USER_CONNECTIONS限制。
当任何帐户对其使用任何资源具有非零限制时，将进行资源使用计数。
在服务器运行时，它会计算每个帐户使用资源的次数。如果帐户在过去一小时内达到其连接数限制，则服务器将拒绝该帐户的其他连接，直到该小时为止。同样，如果帐户达到其查询或更新数量的限制，服务器将拒绝进一步的查询或更新，直到小时结束。在所有这些情况下，服务器都会发出相应的错误消息。
资源计数发生在每个帐户。可以为所有帐户全局重置当前每小时资源使用计数，也可以针对给定帐户单独重置当前每小时资源使用计数：
要将所有帐户的当前计数重置为零，请发出 FLUSH USER_RESOURCES声明。还可以通过重新加载授权表来重置计数（例如，使用FLUSH PRIVILEGES语句或mysqladmin reload命令）。
通过再次设置其任何限制，可以将个人帐户的计数重置为零。指定等于当前分配给帐户的值的限制值。
每小时计数器重置不会影响 MAX_USER_CONNECTIONS限制。
服务器启动时，所有计数从零开始。计数不会通过服务器重启而延续。
1.5 通过mysqladmin工具（只能改密码）
# 给root@localhost用户登录mysql设置密码为"seiang"; $ mysqladmin -u root -h localhost password "seiang"
# 修改root@localhost用户登录mysql数据库的密码; $ mysqladmin -u root -h localhost password "new passwd" -p "old passwd"
1.6 通过直接修改mysql.user表的用户记录
# MySQL 5.6
mysql> update mysql.user set password=PASSWORD('redhat') where user='root';
# MySQL 5.7
mysql> update mysql.user set authentication_string=PASSWORD('redhat') where user='root';
或
mysql> set password for 'root'@'localhost'=PASSWORD('redhat');
改完记得刷新内存中现有的表，另外这种形式在MySQL 5.7中使用会被警告，会告诉你这是一个即将被移除的特性。MySQL 5.7提供了新的更改密码的方式：ALTER USER语句。
mysql> use mysql mysql> alter user root@'localhost' identified by '123456';
二、MySQL管理员密码找回
1、关闭MySQL
$ service mysqld stop
2、在配置文件中[mysqld]字段添加skip-grant-tables指令，跳过授权表
$ cat /etc/my.cnf [mysqld]skip-grant-tables
3、给root用户登录mysql设置密码为redhat并以加密方式
mysql> use mysql;
mysql> update user set password=PASSWORD(‘redhat’) where user=’root’;
MySQL5.7修改密码
mysql> update mysql.user set authentication_string=PASSWORD(‘redhat’) where user=’root’;
三、MySQL权限管理
权限管理主要是对登录到MySQL的用户进行权限验证，所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能时证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT/INSERT/UPDATE和DELETE权限。
1）MySQL权限说明
账户权限信息被存储在MySQL数据库的几张权限表中，在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。其中GRANT和REVOKE语句所涉及的常用权限大致如下这些：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE等，还有一个特殊的proxy权限，是用来赋予某个用户具有给他人赋予权限的权限。
2）MySQL用户授权
授权就是为某个用户授予权限，合理的授权可以保证数据库的安全，MySQL中可以使用GRANT语句为用户授予权限。授权可以分为多个层次：
全局层级：全局权限适用于一个给定服务器中的所有数据库，这些权限存储在mysql.user表中。
数据库层级：数据库权限适用于一个给定数据库中的所有目标，这些权限存储在mysql.db表中。
表层级：表权限适用于一个给定表中的所有列，这些权限存储在mysql.tables_priv表中。
列层级：列权限使用于一个给定表中的单一列，这些权限存储在mysql.columns_priv表中。
子程序层级：CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在mysql.procs_priv表中。
PS：MySQL中必须拥有GRANT权限的用户才可以执行GRANT语句。
3.1 GRANT赋予用户权限
# 定义对已经存在的用户可以操作此库下的所有表及所有权限； Mysql> grant all privileges on DB_NAME.* to 'USERNAME'@'HOST'; # 创建tom用户并赋予select权限对此库下的所有表； mysql> grant select on DB_NAME.* to 'tom'@'localhost' identified by '1234'; # 定义tom用户赋予insert权限对db库下的xsb表； mysql> grant insert on db.xsb to 'tom'@'localhost'; # 定义tom用户赋予update权限对db库下的xsb表； mysql> grant update on db.xsb to 'tom'@'localhost'; # 定义tom用于赋予update权限对db库下的xsb表中的AGE字段； mysql> grant update(AGE) on db.xsb to 'tom'@'localhost'; # 定义tom用于赋予super权限在*.*上(super权限可以对全局变量更改)； mysql> grant super on *.* to 'tom'@'%'; # 通过GRANT语句中的USAGE权限，你可以创建账户而不授予任何权限；它可以将所有全局权限设为'N'，假定你将在以后将具体权限授予该账户； mysql> grant usage on *.* to 'tom'@'%';
all表示赋予用户全部权限（包含存储过程、存储函数等创建和执行）。当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%’表示从任何地址连接。而’连接口令’不能为空，否则创建失败。
3.2 REVOKE移除用户权限
# 移除tom用户对于db.xsb的权限; mysql> revoke all on db.xsb from 'tom'@'localhost'; # 刷新授权表; mysql> flush privileges; # SHOW查看用户的权限 mysql> show grants for 'USERNAME'@'HOST';
PS：使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号记录依然在user表中保存。
3.3 PROXY特殊权限
如果想让某个用户具有给他人赋予权限的能力，那么就需要proxy权限了。当你给一个用户赋予all权限之后，你查看mysql.user表会发现Grant_priv字段还是为N，表示其没有给他人赋予权限的权限。
我们可以查看一下系统默认的超级管理员权限：
mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)
可以看到其本身有PROXY权限，并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予权限的能力，就需要给此用户PROXY权限，如下：
mysql> grant all on *.* to 'test'@'%' identified by '123456'; mysql> GRANT PROXY ON ''@'' TO 'test'@'%' WITH GRANT OPTION;
3.4 数据库开发人员，创建表、索引、视图、存储过程、函数等权限授权
# grant创建、修改、删除MySQL数据表结构权限 grant create on testdb.* to developer@'192.168.0.%'; grant alter on testdb.* to developer@'192.168.0.%'; grant drop on testdb.* to developer@'192.168.0.%'; # grant操作MySQL外键权限 grant references on testdb.* to developer@'192.168.0.%'; # grant操作MySQL临时表权限。 grant create temporary tables on testdb.* to developer@'192.168.0.%'; # grant操作MySQL索引权限 grant index on testdb.* to developer@'192.168.0.%'; # grant操作MySQL视图、查看视图源代码 权限 grant create view on testdb.* to developer@'192.168.0.%';grant show view on testdb.* to developer@'192.168.0.%'; # grant操作MySQL存储过程、存储函数权限 grant create routine on testdb.* to developer@'192.168.0.%'; grant alter routine on testdb.* to developer@'192.168.0.%'; grant execute on testdb.* to developer@'192.168.0.%';
<参考>
https://dev.mysql.com/doc/refman/5.7/en/security.html
转载请注明原文链接：MySQL用户和权限管理(二)
