mysql5.6user_mysql5.6新建的user为啥会看到没有授权过的db？

数据库版本

Server version: 5.6.22-log MySQL Community Server (GPL)

root用户登录后，新建db,叫test2

mysql> create database test2 ;

Query OK, 1 row affected (0.00 sec)

新建用户usr_test2;

GRANT ALL PRIVILEGES ON test2.* TO 'usr_test2'@'%' IDENTIFIED BY 'test123' ;

flush privileges;

使用新建的用户usr_test2登录后，查看database发现多出来两个db,

其中的test和test_mqm没有授权给usr_test2啊，咋回事？看来值得深究一下原因

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

| test2 |

| test_mqm |

+--------------------+

4 rows in set (0.00 sec)

一。首先普及下mysql的权限知识

GRANT命令用来建立新用户，指定用户口令并增加用户权限。其格式如下：

mysql> GRANT ON

-> TO [IDENTIFIED BY ""]

-> [WITH GRANT OPTION];

是一个用逗号分隔的你想要赋予的MySQL用户权限的列表。你可以指定的权限可以分为三种类型：

数据库/数据表/数据列权限：

Alter: 修改已存在的数据表(例如增加/删除列)和索引。

Create: 建立新的数据库或数据表。

Delete: 删除表的记录。

Drop: 删除数据表或数据库。

INDEX: 建立或删除索引。

Insert: 增加表的记录。

Select: 显示/搜索表的记录。

Update: 修改表中已存在的记录。

全局管理MySQL用户权限：

file: 在MySQL服务器上读写文件。

PROCESS: 显示或杀死属于其它用户的服务线程。

RELOAD: 重载访问控制表，刷新日志等。

SHUTDOWN: 关闭MySQL服务。

特别的权限：

ALL: 允许做任何事(和root一样)。

USAGE: 只允许登录--其它什么也不允许做。

二。普及一下MySQL授权表

MySQL的授权系统通常是通过MySQL数据库中的四个表来实现的(老版本mysql还有host表，mysql5.6已经没有了)，

这些表有user、db、tables_priv和columns_priv。

这些表的用途各有不同，但是有一点是一致的，那就是都能够检验用户要做的事情是否为被允许的。

这4个表的字段都可分解为两类，一类为作用域字段，一类为权限字段。

作用域字段用来标识主机、用户或者数据库;而权限字段则用来确定对于给定主机、用户或者数据库来说，哪些动作是允许的。

user表——该表决定是否允许用户连接到服务器。如果允许连接，权限字段则为该用户的全局权限。

db表——用于决定哪些用户可以从哪些主机访问哪些数据库。包含在db表中的权限适用于这个表标识的数据库。

tables_priv表——该表与db表相似，不同之处是它用于表而不是数据库。这个表还包含一个其他字段类型，

包括timestamp和grantor两个字段，用于存储时间戳和授权方。

columns_priv——该表作用几乎与db和tables_priv表一样，不同之处是它提供的是针对某些表的特定列的权限。

这个表也多出了一个字段类型，即其他字段，包括了一个timestamp列，用于存放时间戳。

三、MySQL服务器的访问控制

对用户的访问进行控制，就是通过查询这些授权表:

User表

Db表

Tables_priv表

Columns_priv表

此外，一旦连接到了服务器，一个用户可以使用两种类型的请求:

管理请求(shutdown,reload，file,super,process等)

数据库相关的请求(select,insert，delete，update等)

当用户提交管理请求时，服务器只需查看user表，这是因为user表是唯一包含与管理工作有关的权限的一个表。

然而，当用户提交数据库请求时，要查看的表就要更多了。

user表中的权限是全局性，在这个表中授予用户的权限对服务器上的所有数据库都有效，这些权限可以被认为是超级用户权限。

db包含的权限是特定于数据库的。

因此，让user表内所有的权限保持为“N”不失为一个明智的选择。

有了上面的理论知识，我们就可以逐层的追查问题产生的原因了

因为我的用户usr_test2是新建的，并且授权都没问题，推测不是user表的问题，直接核查db表

mysql> select * from db where db='test' \G

*************************** 1. row ***************************

Host: %

Db: test

User:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: N

Execute_priv: N

Event_priv: Y

Trigger_priv: Y

*************************** 2. row ***************************

Host: %

Db: test

User: rabbit

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

2 rows in set (0.00 sec)

发现第一条有问题，user为空字符

删除该条记录后，刷新权限，发现已经看不到test数据库了，但还是多个test_mqm数据库

mysql> delete from db where db='test' and user =''\G

Query OK, 1 row affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| test2 |

| test_mqm |

+--------------------+

3 rows in set (0.00 sec)

继续核查db表

mysql> select * from db \G

*************************** 1. row ***************************

Host: %

Db: test5

User: usr_test5

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

*************************** 2. row ***************************

Host: %

Db: test\_%

User:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: N

Execute_priv: N

Event_priv: Y

Trigger_priv: Y

*************************** 3. row ***************************

Host: %

Db: test

User: rabbit

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

*************************** 4. row ***************************

Host: %

Db: test_mqm

User: rabbit

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

*************************** 5. row ***************************

Host: %

Db: test2

User: usr_test2

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

5 rows in set (0.00 sec)

果然第2条记录中的有问题，

*************************** 2. row ***************************

Host: %

Db: test\_%

User:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: N

Execute_priv: N

Event_priv: Y

Trigger_priv: Y

这说明把所有"test_"开头的db授权给了所有用户,因此原因找到了，删除db表里的这条记录即可。

mysql> delete from db where user='' ;

Query OK, 1 row affected (0.00 sec)

mysql> commit ;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

用usr_test2重新登录，查询db，显示正常，至此问题解决

[root@vm_mysql1 ~]# mysql -uusr_test2 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 490875

Server version: 5.6.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| test2 |

+--------------------+

2 rows in set (0.00 sec)