MySQL 数据库 User表权限以及用户授权详解
MySQL 常用权限操作
1）本机登陆mysql： mysql -u root -p （-p一定要有）；改变数据库： use mysql;
给 MySQL 设置初始密码
UPDATE user SET password=PASSWORD("new-password") WHERE user='root';
MySQL 忘记密码重置
/etc/init.d/mysql stop # 先停止已有的mysql进程
mysqld_safe --skip-grant-tables &
mysql -u root mysql
UPDATE user SET password=PASSWORD("new-password") WHERE user='root';
FLUSH PRIVILEGES;
grant 权限名(所有的权限用all) on 库名(*全部).表名(*全部) to '用户名'@'%'(%表示所有的IP，可以只些一个IP) identified by "密码";
2）授权所有主机： grant all privileges on *.* to 'testUser'@'%' identified by 'password' with grant option; flush privileges;
3）授权指定主机： grant all privileges on *.* to 'testUser'@'192.168.22.250' identified by 'password' with grant option; flush privileges;
4）授权本地主机： grant all privileges on *.* to root@localhost identified by 'password' with grant option; flush privileges;
5）授权指定数据库： grant all privileges on testDB.* to 'testUser'@localhost identified by 'password' with grant option; flush privileges;
6）授权指定操作权限： grant select, insert, update, delete, create, drop on testDB.* to testUser@localhost identified by 'password' with grant option; flush privileges;
7) 进mysql库查看host为%的数据是否添加： use mysql; select host, user, password, Delete_priv, Grant_priv, Execute_priv from user;
8) 只读权限dump数据库： mysqldump -h 172.192.1.12 -uroot -p123456 --single-transaction your_db > your_db_bk.sql
9) 通过MySQL端口远程连接mysql： mysql -h 122.128.10.114 -P 31206 -uroot -pyg123456 // -P mysql在/etc/mysql/my.cnf 配置文件配置的端口，-p 密码
更多请参见米扑博客：Ubuntu安装配置MySQL
MySQL 查看用户授予的权限
在MySQL中，如何查看一个用户被授予了那些权限呢？
授予用户的权限可能分全局层级权限、数据库层级权限、表层级别权限、列层级别权限、子程序层级权限。
1、全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。
GRANT ALL ON *.* 和 REVOKE ALL ON *.* 只授予和撤销全局权限。
2、数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。
GRANT ALL ON db_name.* 和 REVOKE ALL ON db_name.* 只授予和撤销数据库权限。
3、表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。
GRANT ALL ON db_name.tbl_name 和 REVOKE ALL ON db_name.tbl_name 只授予和撤销表权限。
4、列层级
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。
当使用REVOKE时，您必须指定与被授权列相同的列。
5、子程序层级
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。
这些权限可以被授予为全局层级和数据库层级。
而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
测试示例
登录MySQL，选择 test 数据库，因为 grant 授权不针对数据库、表，所以没必要进入 mysql库、选择 user 表，那只是方便查询新添加的用户
mysql -uroot -p123456
use test;
create table test_tbl(name varchar(50), age int) charset=utf8; // 创建一个表供测试用
1、全局权限示例
创建一个测试账号 mimvp_test，授予全局层级的权限 SELECT、INSERT
MariaDB [test]> grant select,insert on *.* to 'mimvp_test'@'%' identified by 'test_pwd'; Query OK, 0 rows affected (0.046 sec) MariaDB [test]> flush privileges; Query OK, 0 rows affected (0.000 sec)
用下面两种方式查询授予 mimvp_test 的权限：
方式1：show grants for mimvp_test;
MariaDB [test]> show grants for mimvp_test; +--------------------------------------------------------------------------------------------------------------------+ | Grants for mimvp_test@% | +--------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO 'mimvp_test'@'%' IDENTIFIED BY PASSWORD '*06B6D5CE7537C6BFC2F51E16F85600CFD52500C2' | +--------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)
方式2：select * from mysql.user where user='mimvp_test'\G;
MariaDB [test]> select * from mysql.user where user='mimvp_test'\G;
*************************** 1. row ***************************
Host: %
User: mimvp_test
Password: *06B6D5CE7537C6BFC2F51E16F85600CFD52500C2
Select_priv: Y
Insert_priv: Y
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
Delete_history_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
is_role: N
default_role:
max_statement_time: 0.000000
1 row in set (0.000 sec)
由上可见，test 库的新用户 mimvp_test 只有 SELECT、INSERT 两个权限，因为 grant 授权时就只给了 SELECT、INSERT 这两个权限
2、数据库级权限示例
创建一个测试账号 mimvp_test，授予数据库级（test库）的权限 SELECT、INSERT、UPDATE、DELETE
MariaDB [test]> drop user mimvp_test;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> grant select,insert,update,delete on test.* to 'mimvp_test'@'%' identified by 'test_pwd';
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> select * from mysql.user where user='mimvp_test'\G;
*************************** 1. row ***************************
Host: %
User: mimvp_test
Password: *06B6D5CE7537C6BFC2F51E16F85600CFD52500C2
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
Delete_history_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
is_role: N
default_role:
max_statement_time: 0.000000
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [test]> select * from mysql.db where user='mimvp_test'\G;
*************************** 1. row ***************************
Host: %
Db: test
User: mimvp_test
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
Delete_history_priv: N
1 row in set (0.000 sec)
ERROR: No query specified
由上可见
drop user mimvp_test; // 删除步骤1创建的全局权限的用户名 mimvp_test
select * from mysql.user where user='mimvp_test'\G; // 查询全局权限用户，发现为空
select * from mysql.db where user='mimvp_test'\G; // 查询指定数据库权限，发现才有此用户名
查询授予 mimvp_test 的权限：
MariaDB [test]> show grants for mimvp_test; +-----------------------------------------------------------------------------------------------------------+ | Grants for mimvp_test@% | +-----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mimvp_test'@'%' IDENTIFIED BY PASSWORD '*06B6D5CE7537C6BFC2F51E16F85600CFD52500C2' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `MyDB`.* TO 'mimvp_test'@'%' | +-----------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)
3、数据表级权限示例
创建一个测试账号 mimvp_test，授予数据表级（test库，test_tbl表）的权限
MariaDB [test]> drop user mimvp_test; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> flush privileges; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant all on test.test_tbl to 'mimvp_test'@'%' identified by 'test_pwd'; Query OK, 0 rows affected (0.001 sec)
查询授予 mimvp_test 的权限：
MariaDB [test]> show grants for mimvp_test; +-----------------------------------------------------------------------------------------------------------+ | Grants for mimvp_test@% | +-----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mimvp_test'@'%' IDENTIFIED BY PASSWORD '*06B6D5CE7537C6BFC2F51E16F85600CFD52500C2' | | GRANT ALL PRIVILEGES ON `test`.`test_tbl` TO 'mimvp_test'@'%' | +-----------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)
从 tables_priv 表查询权限
MariaDB [test]> select * from mysql.tables_priv\G;
*************************** 1. row ***************************
Host: %
Db: test
User: mimvp_test
Table_name: test_tbl
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger,Delete versioning rows
Column_priv:
1 row in set (0.000 sec)
ERROR: No query specified
由上可见，此时 mimvp_test 用户只拥有 test.test_tbl 表的全部权限
4、列层级权限示例
创建一个测试账号 mimvp_test，授予列层级（test库，test_tbl表，test_tbl(name, age)）的权限
MariaDB [test]> drop user mimvp_test; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> flush privileges; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> grant select (name, age) on test.test_tbl to 'mimvp_test'@'%' identified by 'test_pwd'; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> flush privileges; Query OK, 0 rows affected (0.001 sec)
查询授予 mimvp_test 的权限：
MariaDB [test]> show grants for mimvp_test; +-----------------------------------------------------------------------------------------------------------+ | Grants for mimvp_test@% | +-----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mimvp_test'@'%' IDENTIFIED BY PASSWORD '*06B6D5CE7537C6BFC2F51E16F85600CFD52500C2' | | GRANT SELECT (name, age) ON `test`.`test_tbl` TO 'mimvp_test'@'%' | +-----------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)
从 columns_priv 表查询权限
MariaDB [test]> select * from mysql.columns_priv; +------+------+------------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +------+------+------------+------------+-------------+---------------------+-------------+ | % | test | mimvp_test | test_tbl | name | 0000-00-00 00:00:00 | Select | | % | test | mimvp_test | test_tbl | age | 0000-00-00 00:00:00 | Select | +------+------+------------+------------+-------------+---------------------+-------------+ 2 rows in set (0.000 sec)
由上可见，此时 mimvp_test 用户只拥有 test.columns_priv 表的指定字段权限
5、子程序级权限示例
创建一个测试账号 mimvp_test，授予子程序级（test库，test_tbl表，proc_test）的权限
MariaDB [test]> drop procedure if exists proc_test;
Query OK, 0 rows affected (0.004 sec)
MariaDB [test]> delimiter //
MariaDB [test]> create procedure proc_test()
-> BEGIN
-> select * from test.test_tbl;
-> END //
Query OK, 0 rows affected (0.004 sec)
MariaDB [test]> delimiter ;
MariaDB [test]> grant execute on procedure test.proc_test to 'mimvp_test'@'%' identified by 'test_pwd';
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
这里，重点提醒，创建执行过程，必须严格按照上面格式，包括 delimiter ; 的空格、分号都不要搞创新，我已经帮你们填过坑啦
查询授予 mimvp_test 的权限：
MariaDB [test]> show grants for mimvp_test; +-----------------------------------------------------------------------------------------------------------+ | Grants for mimvp_test@% | +-----------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mimvp_test'@'%' IDENTIFIED BY PASSWORD '*06B6D5CE7537C6BFC2F51E16F85600CFD52500C2' | | GRANT EXECUTE ON PROCEDURE `test`.`proc_test` TO 'mimvp_test'@'%' | +-----------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)
从 procs_priv 表查询权限
MariaDB [test]> select * from mysql.procs_priv where User='mimvp_test'; +------+------+------------+--------------+--------------+----------------+-----------+---------------------+ | Host | Db | User | Routine_name | Routine_type | Grantor | Proc_priv | Timestamp | +------+------+------------+--------------+--------------+----------------+-----------+---------------------+ | % | test | mimvp_test | proc_test | PROCEDURE | root@localhost | Execute | 0000-00-00 00:00:00 | +------+------+------------+--------------+--------------+----------------+-----------+---------------------+ 1 row in set (0.000 sec)
由上可见，此时 mimvp_test 用户只拥有 test.procs_priv 表的子程序权限
总结
如果需要查看用户被授予的权限，就需要从这五个层级来查看被授予的权限。
从上到下或从小到上，逐一检查各个层级被授予的权限。
MySQL User表权限字段说明全介绍
1、登录MySQL数据库
mysql -h 127.0.0.1 -uroot -p'123456'
use mysql;
desc user;
2、查看 user 表结构
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| Delete_history_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+
47 rows in set (0.002 sec)
3、user 表字段介绍
Select_priv。确定用户是否可以通过SELECT命令选择数据。
Insert_priv。确定用户是否可以通过INSERT命令插入数据。
Update_priv。确定用户是否可以通过UPDATE命令修改现有数据。
Delete_priv。确定用户是否可以通过DELETE命令删除现有数据。
Create_priv。确定用户是否可以创建新的数据库和表。
Drop_priv。确定用户是否可以删除现有数据库和表。
Reload_priv。确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表。
Shutdown_priv。确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时，都应当非常谨慎。
Process_priv。确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程。
File_priv。确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令。
Grant_priv。确定用户是否可以将已经授予给该用户自己的权限再授予其他用户。例如，如果用户可以插入、查询和删除foo数据库中的信息，并且授予了GRANT权限，则该用户就可以将其任何或全部权限授予系统中的任何其他用户。
References_priv。目前只是某些未来功能的占位符；现在没有作用。
Index_priv。确定用户是否可以创建和删除表索引。
Alter_priv。确定用户是否可以重命名和修改表结构。
Show_db_priv。确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库。可以考虑对所有用户禁用这个权限，除非有特别不可抗拒的原因。
Super_priv。确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令。
Create_tmp_table_priv。确定用户是否可以创建临时表。
Lock_tables_priv。确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改。
Execute_priv。确定用户是否可以执行存储过程。此权限只在MySQL 5.0及更高版本中有意义。
Repl_slave_priv。确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信。
Repl_client_priv。确定用户是否可以确定复制从服务器和主服务器的位置。
Create_view_priv。确定用户是否可以创建视图。此权限只在MySQL 5.0及更高版本中有意义。关于视图的更多信息，参见第34章。
Show_view_priv。确定用户是否可以查看视图或了解视图如何执行。此权限只在MySQL 5.0及更高版本中有意义。关于视图的更多信息，参见第34章。
Create_routine_priv。确定用户是否可以更改或放弃存储过程和函数。此权限是在MySQL 5.0中引入的。
Alter_routine_priv。确定用户是否可以修改或删除存储函数及函数。此权限是在MySQL 5.0中引入的。
Create_user_priv。确定用户是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户。
Event_priv。确定用户能否创建、修改和删除事件。这个权限是MySQL 5.1.6新增的。
Trigger_priv。确定用户能否创建和删除触发器，这个权限是MySQL 5.1.6新增的
MySQL 数据库user表host字段的%问题 （重要）
MySQL 数据库中，使用 select user(); 查看当前用户
select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+
MySQL 中host字段的%与localhost不是谁包括谁的问题，是由模糊到精确进行顺序排列、匹配的：
当用户从客户端请求登陆时，MySQL将授权表中的条目与客户端所提供的条目进行比较，包括用户的主机 host、用户名 user、密码 password
一、字段说明
user 授权表中的Host字段是可以使用通配符（% 表示任意本机和外机）作为模式进行匹配的，如 test.example.com, %.example.com, %.com和% 都可以匹配test.example.com这个主机。
user 授权表中的User字段不允许使用模式匹配，但是可以有一个空字符的用户名代表匿名用户，并且空字符串可以匹配所有的用户名，就像通配符一样。
当user表中的Host和User有多个值可以匹配客户端提供的主机和用户名时，MySQL将user表读入内存，并且按照一定规则排序，按照排序规则读取到的第一个匹配客户端用户名和主机名的条目对客户端进行身份验证。
二、排序规则
对于Host字段，按照匹配的精确程度进行排序，越精确的排序越前，例如当匹配test.example.com这个主机时, %.example.com比%.com更精确，而test.example.com比%.example.com更精确。
对于User字段，非空的字符串用户名比空字符串匹配的用户名排序更靠前。
User和Host字段都有多个匹配值，MySQL使用主机名（Host字段）排序最前的条目，在主机名字段相同时再选取用户名排序更前的条目。
因此，如果User和Host字段都有多个匹配值，主机名最精确匹配的条目被用户对用户进行认证。
这是一般的匹配规则，但是今天遇到了%不能在本地登录的问题。
首先，查看本机当前登录的用户
使用 select user(); 查看当前用户
1、这是本地root登录查看的user表：
root@localhost
2、这是远程用xiao用户登录：
Xiao@27.19.69.141
接着，验证登录匹配顺序
本地（localhost）用xiao用户登录，使用命令：“mysql -uxiao” // 可以登录，匿名匹配第5行
本地（localhost）用xiao用户登录，使用命令：“mysql -uxiao -p**************” // 不可以登录，精确匹配
MySQL官方有说 %不包括localhost，实际测试验证结果：%是包括 -h 为空、localhost、127.0.0.1，以及 -h 内网ip、外网ip地址的连接
%是匹配所有host的主机的，但是MySQL的user表中匹配host字段是从具体到模糊的，所以如果host字段有精确的，如 localhost或127.0.0.1存在（精确的排列在前），就不会去匹配%这个用户了。
但是为何本地 mysql -uxiao 可以直接登录，而mysql -uxiao -p******却登录不了?
使用xiao在本机登录数据时，不指定-h参数，则默认为localhost主机登录，而在MySQL中host字段有两个匹配的条目：@'%' 和 ''@'localhost'（localhost 优先）
本地 mysql -uxiao可以直接登录，不指定-h参数，且不指定密码认证，则默认为localhost主机登录，依次匹配第1、2、3、4、5行，第5行匹配成功，可以登录
匿名用户能够匹配的原因上面说过，空字符串可以匹配所有的用户名，就像通配符一样，不指定密码认证时，Host主机名排序更靠前，用户名次之
而mysql -uxiao -p******却登录不了，因为若包含了密码，则根据MySQL密码认证时的排序规则，第一个条目的用户名排序更前（匹配root），其次才是host字段主机名更精确排序靠前（匹配localhost），于是依次匹配第1行，user = root（优先），host = localhost （优先），第1行的password字段不为空，需要输入密码，因此登录不了。
解决的办法，是指定 host 字段为MySQL 外网ip地址（或内网ip地址），不要省略 -h ，否则默认用了 localhost 匹配。
因此，只有使用匿名用户（user 为空）的空密码（密码为空）才能登录进数据库，第5、6行，才会出现上述的情况。
实际上，上面的匹配规则才是问题的关键，米扑博客推荐删除 user 为空、host 为hostname的用户记录，例如：
# 说明:
# 本机连接, 是不指定 -h , -h localhost , -h 127.0.0.1 三种连接方式, 在数据库本机上验证测试
# 外机连接, 是指定包含了本机连接的三种连接方式，以及多了两条 -h 内网ip 和 -h 外网ip，例如 -h 148.10.16.47 五种连接方式, 在另一台外网ip服务器上测试验证
#
#
# 登录 mysql 查看默认用户, mysql默认没有密码，输入mysql直接登录
# mysql
# use mysql;
# select host, user, password, Delete_priv, Grant_priv from user;
#
# 删除主机host为本机
# delete from user where host = 'mimvp-bj';
#
# 删除 user 为空的
# delete from user where user = '';
MySQL 用户表 user 实例
本示例的MySQL 版本：Server version: 10.3.8-MariaDB MariaDB Server
1、删除 user 为空，host 为 hostname 的记录后，查询结果如下
MariaDB [mysql]> select host, user, password, Grant_priv, Select_priv, Delete_priv, Drop_priv from user; +-----------+-----------------+-------------------------------------------+------------+-------------+-------------+-----------+ | host | user | password | Grant_priv | Select_priv | Delete_priv | Drop_priv | +-----------+-----------------+-------------------------------------------+------------+-------------+-------------+-----------+ | localhost | root | *931ABC1235A0918F147E617ABC5E75126DFA8A35 | Y | Y | Y | Y | | 127.0.0.1 | root | *936DFA817ABC12757E653A1918F1ABC1235A045E | Y | Y | Y | Y | | ::1 | root | *936DFA8A1918F1ABC1235A045E757E617ABC1235 | Y | Y | Y | Y | | localhost | mimvp | *5CB7DEE46567282426EA1918E0B6E35D6B3F503E | Y | Y | Y | Y | | % | mimvp_rd | *574E0CB7DEB6B3F503EE38242EE46A19182895D6 | Y | Y | N | N | | % | mimvp_rd_only | *53F503E74E0CB7A1918E672289B6E382426E5D6B | Y | Y | N | N | +-----------+-----------------+-------------------------------------------+------------+-------------+-------------+-----------+ 6 rows in set (0.001 sec)
2、多种查询方式对比 %、localhost、127.0.0.1
## root mysql -P 3306 -uroot -p'12345678' # ok mysql -h 127.0.0.1 -P 3306 -uroot -p'12345678' # ok mysql -h localhost -P 3306 -uroot -p'12345678' # ok mysql -h 192.172.10.16 -P 3306 -uroot -p'12345678' # fail ERROR 1045 (28000): Access denied for user 'root'@'192.172.10.16' (using password: YES) mysql -h 186.10.26.57 -P 3306 -uroot -p'12345678' # fail ERROR 1045 (28000): Access denied for user 'root'@'186.10.26.57' (using password: YES) ## mimvp mysql -P 3306 -umimvp -p'12345678' # ok mysql -h 127.0.0.1 -P 3306 -umimvp -p'12345678' # ok mysql -h localhost -P 3306 -umimvp -p'12345678' # ok mysql -h 192.172.10.16 -P 3306 -umimvp -p'12345678' # fail ERROR 1045 (28000): Access denied for user 'mimvp'@'192.172.10.16' (using password: YES) mysql -h 186.10.26.57 -P 3306 -umimvp -p'12345678' # fail ERROR 1045 (28000): Access denied for user 'mimvp'@'186.10.26.57' (using password: YES) ## mimvp_rd mysql -P 3306 -u'mimvp_rd' -p'12345678' # ok mysql -h 127.0.0.1 -P 3306 -u'mimvp_rd' -p'12345678' # ok mysql -h localhost -P 3306 -u'mimvp_rd' -p'12345678' # ok mysql -h 192.172.10.16 -P 3306 -u'mimvp_rd' -p'12345678'# ok mysql -h 186.10.26.57 -P 3306 -u'mimvp_rd' -p'12345678' # ok
以上可见：
root 和 mimvp 用户，都属于 localhost，只能本机连接的三种连接方式；
mimvp_rd 属于 % 模糊匹配，不仅支持本机三种连接方式，还支持 -h 内网ip 和 -h 外网ip 两种连接方式
最后，也验证了上面我说的：
MySQL官方有说 %不包括localhost，实际测试验证结果：%是包括 -h 为空、localhost、127.0.0.1，以及 -h 内网ip、外网ip地址的连接
划重点：若配置了模糊 % 匹配，但外网ip无法访问MySQL数据库，请逐个检查如下：
1）/etc/my.cnf.d/server.cnf 服务器端配置文件里（未删除干净的也要检查）是否已注释掉：#bind-address=0.0.0.0
2）本机防火墙是否禁用了自定义的数据库端口号，如 3306、3456
3）在阿里云、腾讯云、百度云等服务器上配置的，一定记得配置安全规则，开放数据库端口号（TCP协议）绝大部分原因忘了这一条
MySQL 增加用户并赋予、删除、查看各种权限
MySQL 数据库下存储的都是权限表，重要的有user表、db表、host表、tables_priv表、columns_priv表、procs_priv表
MariaDB [mimvp_dbname]> use mysql; Database changed MariaDB [mysql]> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | column_stats | | columns_priv | | db | | event | | func | | general_log | | gtid_slave_pos | | help_category | | help_keyword | | help_relation | | help_topic | | host | | index_stats | | innodb_index_stats | | innodb_table_stats | | plugin | | proc | | procs_priv | | proxies_priv | | roles_mapping | | servers | | slow_log | | table_stats | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | transaction_registry | | user | +---------------------------+ 31 rows in set (0.000 sec)
1、user表：有用户列、权限列、安全列、资源控制列。
1）用户列包括：
host、user、password 表示主机、用户名、密码。
2）权限列
包括select_priv、insert_priv等以priv结尾的字段。决定了用户的权限。这些字段值只有y和n，y表示该权限可以用到所有数据库上；n表示该权限不能用到所有数据库上。可以用grant语句为用户赋予一些权限，也可以用update语句更新。如：grant_priv字段表示是否拥有grant权限；shutdown_priv字段表示是否拥有停止mysql服务的权限；super_priv字段表示是否拥有超级权限；execute_priv字段表示是否拥有execute权限，拥有该权限可以执行存储过程和函数。
3）安全列：
分别是ssl_type、ssl_cipher、x509_issuer、x509_subject。ssl用于加密；x509标准可以用来标识用户。通常标准的发行版不支持ssl，读者可以使用show variables like 'have_openssl'语句查看是否具有ssl功能。如果have_openssl的取值为disabled，则不支持ssl加密功能。
4）资源控制列：
max_questions、max_updates分别规定每小时可以允许执行多少次查询和更新、max_connections规定每小时可以建立多少连接；max_user_connctions规定单个用户可以同时具有的连接数。默认值都为0表示没有限制。
2、db表和host表
db表中存储了某个用户对一个数据库的权限。db表比较常用，而host表很少会用到。有用户列和权限列。
1）分别是host、db、user。分别表示主机名、数据库名和用户名。host表的用户列有两个字段，分别是host和db，分别表示主机名和数据库名。
2）host表是db表的扩展。如果db表中找不到host字段的值，就需要到host表中去寻找。但是host表很少用到，通常db表的设置已经满足要求了。
3）权限列：db表相比host表多了create_routine_priv字段和alter_routine_priv字段。决定用户是否具有创建和修改存储过程的权限。user表中的权限是针对所有数据库。如果user表中select_priv字段值为y，那么该用户可以查询所有数据库的表；如果为某用户只设置查询test表的权限，那么user表的select_priv字段的取值为n。而这个这个权限则记录在db表中取值是y。用户先根据user表的内容获取权限然后根据db表内容获取权限。
3、tables_priv表和columns_priv表
tables_priv表可以对单个表进行权限设置，columns_priv表可以对单个数据列进行权限设置。
1）tables_priv表包含8个字段分别是host、db、user、table_name、table_priv、column_priv、tiemstamp和grantor。前4个字段分别表示主机名、数据库名、用户名和表名。权限包括selecct、insert、update、delete、create、drop、grant、references、index、alter。column_priv权限包括select、insert、update、references。timestamp表示修改权限的时间。grantor表示权限是谁设置的。
2）columns_priv表包括7个字段，分别是host、db、user、table_name、column_name、column_priv、tiemstamp。这里多出了column_name字段，表示可以对哪些数据列进行操作。
注：数据库先判断user表中值是否为y，如果user表值是y，就不需要检查后面的表。如果user表值为n，则依次检查db表、tables_priv表和columns_priv表。
4、procs_priv表
此表可以对存储过程和存储函数进行权限设置。包含8个字段分别是host、db、user、routine_name、routine_type、proc_priv、timestamp和grantor。routine_name字段表示存储过程或函数的名称。routine_type字段表示类型。该字段有两个取值分别是function和procedure。proc_priv字段表示拥有的权限，分别为3类，分别是execute、alter routine和grant。timestamp字段存储更新的时间；grantor字段存储权限是谁设置的。
权限管理
权限名称 对应user表中的列 权限范围
create create_priv 数据库、表或索引
drop drop_priv 数据库或表
grant option grant_priv 数据库、表、存储过程或函数
references references_priv 数据库或表
alter alter_priv 修改表
delete delete_priv 删除表
index index_priv 用索引查询表
insert insert_priv 插入表
select select_priv 查询表
update update_priv 更新表
create view create_view_priv 创建视图
show view show_view_priv 查看视图
alter routine alter_routine_priv 修改存储过程或存储函数
create routine create_routine_priv 创建存储过程或存储函数
execute routine execute_priv 执行存储过程或存储函数
file file_priv 加载服务器主机上的文件
create temporary tables create_tmp_table_priv 创建临时表
lock tables lock_tables_priv 锁定表
create user create_user_priv 创建用户
process process_priv 服务器管理
reload reload_priv 重新加载权限表
replication client repl_client_priv 服务器管理
replication slave repl_slave_priv 服务器管理
show databases show_db_priv 查看数据库
shutdown shutdown_priv 关闭服务器
super super_priv 超级权限
用户管理
新建普通用户
可以用create user语句、mysql.user表中添加用户、还可以用grant语句新建用户
1、create user语句：
格式：create user user1 [identified by [password] 'password'][,user2 [identified by [password]'password']]...
例：create user 'test1'@'localhost' identified by 'test1';//如果密码是普通字符串就不用加password参数
2、insert语句来新建普通用户，直接将用户信息添加到mysql.user表
格式：insert into mysql.user(host,user,password) values('hostname','username',PASSWORD('password'));
注：要有对该表的insert权限才行。一般只要添加host、user、password这三个字段就行，其它字段取值为默认值。如果某个字段没有默认值。这个语句将不能执行。要将没有默认值的字段也设置值。通常ssl_cipher、x509_issuer、x509_subject没有默认值，所以插入时要设置值。
格式：insert into mysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values('hostname','username',PASSWORD('password'),'','','');
执行完后用flush privileges;使用户生效。但是执行flush命令需要reload权限。
3、grant语句来新建普通用户
格式：grant priv_type [(column_list)] on database.table to user [identified by [password]'password'][,user[identified by [password]'password']...[WITH with_option[with_option]...]
注：WITH后面可以带有一个或多个参数，如：
grant option:被授权的用户可以将这些权限赋予给别的用户；这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。
max_queries_per_hour count:设置每个小时可以允许执行count次查询；
max_updates_per_hour count:设置每个小时可以允许执行count次更新；
max_connections_per_hour count:设置每个小时可以建立count连接；
max_user_connections count:设置单个用户可以同时具有的count个连接数；
例：grant select on testdb.* to dba@localhost with grant option;
mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名1@用户地址 identified by '连接口令',用户名2@用户地址 identified by '连接口令';
权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。 --注：privileges可以省略
当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。
用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用"%"表示从任何地址连接。
如：mysql>grant select,insert,update,delete on *.* to 'user1'@'%' Identified by "123"
给来自所有地址的用户user1分配可对所有数据库所有表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123。
注：grant可以修改用户的密码和权限。
删除普通用户
1)、删除普通用户要有drop user权限格式：drop user user1[,user2]...;
注：user参数是由用户名和主机名组成。
例：drop user 'test2'@'localhost';
2）、delete语句将用户信息从mysql.user表中删除
delete from mysql.user where host='hostname' and user='username';//因为host和user两个字段都是主键，所以要两个字段才能确定一条记录。
修改密码
1、root用户修改自己密码
a、用mysqladmin命令修改密码，格式：mysqladmin -u username -p password "new_password";
注：这里的password为关键字不是指旧密码而且新密码必须用双引号括起来。
b、用update语句如：update mysql.user set password=password("new_password") where user="root" and host="localhost";
c、set语句，格式：set password=password("new_password");
2、root用户修改普通用户密码
a、set password for 'username'@'hostname'=password("new_password");
b、用update语句如：update mysql.user set password=password("new_password") where user="root" and host="localhost";
c、grant语句，格式：grant select on *.* to 'test3'@'localhost' identified by 'mytest3';
3、普通用户修改密码
1、set password=password('new_password');注：普通用户不能用mysqladmin命令，因为没有super权限
root用户密码丢失解决办法
1）、使用--skip-grant-tables选项使服务器停止权限判断，任何用户都可以访问数据库。该选项跟在mysql服务命令后面，
windows中用mysqld或mysqld-nt启动mysql服务，也可以用net start mysql启动mysql服务。如：
msyqld --skip-grant-tables或mysqld-nt --skip-grant-tables或net start mysql --skip-grant-tables
linux系统中用mysqld_safe启动mysql服务，也可以用/etc/init.d/mysql启动服务。如：
mysqld_safe --skip-grant-tables user=mysql或/etc/init.d/mysql start --mysqld --skip-grant-tables
2）、用上面的方法停止权限判断后用update语句来修改密码：
先用命令：mysql -u root登录
然后改密码update mysql.user set password=password('root') where user='root' and host='localhost';
最后flush privileges；
注：只能用update更新user表，不能用set语句
3、刷新系统权限表
mysql>flush privileges; 注意，授权后必须FLUSH PRIVILEGES;否则无法立即生效。
4、查看 MySQL 用户权限
查看当前用户（自己）权限：
show grants;
查看其他 MySQL 用户权限：
1）、select * from mysql.user;
2)、show grants for 'username'@'hostname';//可以查看权限的语句
5、收回授予权限
revoke priv_type [(column_list)]...on database.table from user [,user]...
注：column_list参数表示权限作用于哪些列上，没有该参数时作用于整个表上；user由用户名和主机名构成；
收回全部权限
格式：revoke all privileges,grant option from user[,user]...
参考推荐：
版权所有: 本文系米扑博客原创、转载、摘录，或修订后发表，最后更新于 2019-01-03 05:57:22
侵权处理: 本个人博客，不盈利，若侵犯了您的作品权，请联系博主删除，莫恶意，索钱财，感谢！