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 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用户名登录数据库，验证采购部门经理的权限。 */用以上方法创建用户后，打开命令行，输入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 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)有没有哪位大神帮忙解决一下！多谢了！

下面是数据库用户和表的截图