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

还是关于Mysql创建用户和角色出现的问题,创建的用户无法访问数据库
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 [email protected] WITH ADMIN OPTION;
GRANT SaleManagerRole TO [email protected] WITH ADMIN OPTION;
GRANT CustomerManagerRole TO [email protected] WITH ADMIN OPTION;

/* 给各部门职员分配权限。 */
GRANT PurchaseEmployeeRole TO [email protected];
GRANT SaleEmployeeRole TO [email protected];
GRANT CustomerEmployeeRole TO [email protected];

/* (4)回收角色或用户权限 */
/* 收回客户经理角色的销售信息查看权限 */
REVOKE SaleQueryRole FROM CustomerManagerRole;
/* 回收 Mike的客户部门职员权限 */
REVOKE CustomerEmployeeRole FROM [email protected];

/* (5)验证权限分配正确性 */
/* 以David用户名登录数据库,验证采购部门经理的权限。 */
用以上方法创建用户后,打开命令行,输入 mysql -uDavid -p111111进入数据库,输入 show databases;只有一个数据库名字叫做 information_schema,没有原来的数据库。 使用 select * from sales.part后提示 SELECT command denied to user 'Tom'@'localhost' for table 'orders' use sales;提示Access denied for user 'Tom'@'localhost' to database 'sales'。 输入 show grants for [email protected];后命令行输出
+--------------------------------------------------------------------+
| Grants for [email protected]                                           |
+--------------------------------------------------------------------+
| GRANT CREATE USER, CREATE ROLE ON *.* TO `Tom`@`localhost`         |
| GRANT `SaleManagerRole`@`%` TO `Tom`@`localhost` WITH ADMIN OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
有没有哪位大神帮忙解决一下!多谢了! 下面是数据库用户和表的截图 image.png image.png