还是关于Mysql创建用户和角色出现的问题，创建的用户无法访问数据库

Question

use Sales;

SHOW GRANTS FOR 'David'@'localhost';
/* （1）创建用户 */
CREATE USER 'David' @'localhost' IDENTIFIED BY  '111111';
CREATE USER 'Tom' @'localhost' IDENTIFIED BY  '111111';
CREATE USER 'Kathy' @'localhost' IDENTIFIED BY  '111111';
GRANT CREATE ROLE ON *.* TO 'David'@'localhost';
GRANT CREATE ROLE ON *.* TO 'Tom'@'localhost';
GRANT CREATE ROLE ON *.* TO 'Kathy'@'localhost';
GRANT CREATE USER ON *.* TO 'David'@'localhost';
GRANT CREATE USER ON *.* TO 'Tom'@'localhost';
GRANT CREATE USER ON *.* TO 'Kathy'@'localhost';

CREATE USER 'Jefery' @'localhost' IDENTIFIED BY '111111';
CREATE USER 'Jane' @'localhost' IDENTIFIED BY '111111';
CREATE USER 'Mike' @'localhost' IDENTIFIED BY '111111';

/* （2）创建角色并分配权限 */
/* 为各个部门分别创建一个查询角色，并分配相应的查询权限 */
CREATE ROLE PurchaseQueryRole;
GRANT SELECT ON TABLE Sales.part TO PurchaseQueryRole;
GRANT SELECT ON TABLE Sales.supplier TO PurchaseQueryRole;
GRANT SELECT ON TABLE Sales.partsupp TO PurchaseQueryRole;

CREATE ROLE SaleQueryRole;
GRANT SELECT ON TABLE Sales.orders TO SaleQueryRole;
GRANT SELECT ON TABLE Sales.lineitem TO SaleQueryRole;

CREATE ROLE CustomerQueryRole;
GRANT SELECT ON TABLE Sales.customer TO CustomerQueryRole;
GRANT SELECT ON TABLE Sales.nation TO CustomerQueryRole;
GRANT SELECT ON TABLE Sales.region TO CustomerQueryRole;

/* 为各个部门分别创建一个职员角色，对本部门信息具有查看、插入权限 */
CREATE ROLE PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.part TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.supplier TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.partsupp TO PurchaseEmployeeRole;

CREATE ROLE SaleEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.orders TO SaleEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.lineitem TO SaleEmployeeRole;

CREATE ROLE CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.customer TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.nation TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON TABLE sales.region TO CustomerEmployeeRole;

/* 为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限,
对其他部门的信息具有查询权。经理有权给本部门职员分配权限。 */

CREATE ROLE PurchaseManagerRole;
GRANT CREATE ROLE ON TABLE Sales.* TO PurchaseManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.part TO PurchaseManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.partsupp TO PurchaseManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.supplier TO PurchaseManagerRole;
GRANT SaleQueryRole TO PurchaseManagerRole;
GRANT CustomerQueryRole TO PurchaseManagerRole;

CREATE ROLE SaleManagerRole;
GRANT CREATE ROLE ON TABLE *.* TO SaleManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.orders TO SaleManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.lineitem TO SaleManagerRole;
GRANT PurchaseQueryRole TO SaleManagerRole;
GRANT CustomerQueryRole TO SaleManagerRole;

CREATE ROLE CustomerManagerRole;
GRANT CREATE ROLE ON TABLE *.* TO CustomerManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.customer TO CustomerManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.region TO CustomerManagerRole;
GRANT ALL PRIVILEGES ON TABLE sales.nation TO CustomerManagerRole;
GRANT PurchaseQueryRole TO CustomerManagerRole;
GRANT SaleQueryRole TO CustomerManagerRole;

/* （3）给用户分配权限 */
/* 给各部门经理分配权限。 */
GRANT PurchaseManagerRole TO David@localhost WITH ADMIN OPTION;
GRANT SaleManagerRole TO Tom@localhost WITH ADMIN OPTION;
GRANT CustomerManagerRole TO Kathy@localhost WITH ADMIN OPTION;

/* 给各部门职员分配权限。 */
GRANT PurchaseEmployeeRole TO Jefery@localhost;
GRANT SaleEmployeeRole TO Jane@localhost;
GRANT CustomerEmployeeRole TO Mike@localhost;

/* （4）回收角色或用户权限 */
/* 收回客户经理角色的销售信息查看权限 */
REVOKE SaleQueryRole FROM CustomerManagerRole;
/* 回收 Mike的客户部门职员权限 */
REVOKE CustomerEmployeeRole FROM Mike@localhost;

/* （5）验证权限分配正确性 */
/* 以David用户名登录数据库，验证采购部门经理的权限。 */

1. 用以上方法创建用户后，打开命令行，输入mysql -uDavid -p111111进入数据库，输入show databases;只有一个数据库名字叫做information_schema，没有原来的数据库。
2. 使用select * from sales.part后提示SELECT command denied to user 'Tom'@'localhost' for table 'orders'
3. use sales;提示Access denied for user 'Tom'@'localhost' to database 'sales'。
4. 输入show grants for Tom@localhost;后命令行输出

+--------------------------------------------------------------------+
| Grants for Tom@localhost                                           |
+--------------------------------------------------------------------+
| GRANT CREATE USER, CREATE ROLE ON *.* TO `Tom`@`localhost`         |
| GRANT `SaleManagerRole`@`%` TO `Tom`@`localhost` WITH ADMIN OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

• 有没有哪位大神帮忙解决一下！多谢了！
• 下面是数据库用户和表的截图
• 

Answer 1

等待大神解答