Details
-
Improvement
-
Status: Done
-
Medium
-
Resolution: Fixed
-
None
-
None
-
None
Description
There seem to be some issues with the current implementation.
I have a situation like this:
8.0.4>show grants for [email protected]'%'; +---------------------------------------+ | Grants for [email protected]% | +---------------------------------------+ | GRANT USAGE ON *.* TO `developer`@`%` | | GRANT `tester`@`%` TO `developer`@`%` | +---------------------------------------+ 2 rows in set (0,00 sec) 8.0.4>select * from role_edges; +-----------+-----------+-----------+-----------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+-----------+-----------+-----------+-------------------+ | % | app_read | localhost | dev1 | N | | % | app_read | localhost | root | N | | % | tester | % | developer | N | | % | tester | localhost | plavi | N | +-----------+-----------+-----------+-----------+-------------------+ 4 rows in set (0,00 sec) 8.0.4>show grants for [email protected]'%'; +------------------------------------------+ | Grants for [email protected]% | +------------------------------------------+ | GRANT USAGE ON *.* TO `tester`@`%` | | GRANT INSERT ON `test`.* TO `tester`@`%` | +------------------------------------------+ 2 rows in set (0,00 sec)
Basically I want to concentrate here on the "developer" account which has "tester" role.
============
TESTCASE #1:
============
If I select only specific user I get his grants but not with the grants from his roles and pt-show-grants also creats empty roles which are not associated to this specific account.
./pt-show-grants --socket=/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock --user=plavi --password=test1234 --only=developer 8.0.4>select Host,User from mysql.user; +-----------+------------------+ | Host | User | +-----------+------------------+ | % | app_developer | | % | app_read | | % | app_write | | % | developer | | % | tester | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | plavi | | localhost | root | +-----------+------------------+ 10 rows in set (0,00 sec) 8.0.4>select * from mysql.role_edges; +-----------+-----------+---------+-----------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+-----------+---------+-----------+-------------------+ | % | tester | % | developer | N | +-----------+-----------+---------+-----------+-------------------+ 1 row in set (0,00 sec) 8.0.4>show grants for developer; +---------------------------------------+ | Grants for [email protected]% | +---------------------------------------+ | GRANT USAGE ON *.* TO `developer`@`%` | | GRANT `tester`@`%` TO `developer`@`%` | +---------------------------------------+ 2 rows in set (0,00 sec) 8.0.4>show grants for tester; +------------------------------------+ | Grants for [email protected]% | +------------------------------------+ | GRANT USAGE ON *.* TO `tester`@`%` | +------------------------------------+ 1 row in set (0,00 sec)
So as you can see "GRANT INSERT ON `test`.* TO `tester`@`%`" is missing since it did not pickup the grants from tester role which is assigned to developer.
pt-show-grants output:
-- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 8.0.4-rc-log at 2018-04-19 19:38:10 -- Roles CREATE ROLE IF NOT EXISTS `app_read`; CREATE ROLE IF NOT EXISTS `tester`; CREATE ROLE IF NOT EXISTS `app_developer`; CREATE ROLE IF NOT EXISTS `app_write`; -- End of roles listing -- Grants for 'developer'@'%' CREATE USER IF NOT EXISTS 'developer'@'%'; ALTER USER 'developer'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT; GRANT USAGE ON *.* TO `developer`@`%`; GRANT `tester`@`%` TO `developer`@`%`;
Also why is it creating roles which are not associated with "developer" account?
============
TESTCASE #2:
============
If I list a user and all his roles in --only=user,role1,role2 format I can get a good copy of grants, here's how it looks:
./pt-show-grants --socket=/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock --user=plavi --password=test1234 --only=developer,tester 8.0.4>select Host,User from mysql.user; +-----------+------------------+ | Host | User | +-----------+------------------+ | % | app_developer | | % | app_read | | % | app_write | | % | developer | | % | tester | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | plavi | | localhost | root | +-----------+------------------+ 10 rows in set (0,00 sec) 8.0.4>select * from mysql.role_edges; +-----------+-----------+---------+-----------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+-----------+---------+-----------+-------------------+ | % | tester | % | developer | N | +-----------+-----------+---------+-----------+-------------------+ 1 row in set (0,00 sec) 8.0.4>show grants for developer; +---------------------------------------+ | Grants for [email protected]% | +---------------------------------------+ | GRANT USAGE ON *.* TO `developer`@`%` | | GRANT `tester`@`%` TO `developer`@`%` | +---------------------------------------+ 2 rows in set (0,00 sec) 8.0.4>show grants for tester; +------------------------------------------+ | Grants for [email protected]% | +------------------------------------------+ | GRANT USAGE ON *.* TO `tester`@`%` | | GRANT INSERT ON `test`.* TO `tester`@`%` | +------------------------------------------+ 2 rows in set (0,00 sec)
pt-show-grants output for this:
-- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 8.0.4-rc-log at 2018-04-19 19:38:18 -- Roles CREATE ROLE IF NOT EXISTS `app_read`; CREATE ROLE IF NOT EXISTS `tester`; CREATE ROLE IF NOT EXISTS `app_developer`; CREATE ROLE IF NOT EXISTS `app_write`; -- End of roles listing -- Grants for 'developer'@'%' CREATE USER IF NOT EXISTS 'developer'@'%'; ALTER USER 'developer'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT; GRANT USAGE ON *.* TO `developer`@`%`; GRANT `tester`@`%` TO `developer`@`%`; -- Grants for 'tester'@'%' CREATE USER IF NOT EXISTS 'tester'@'%'; ALTER USER 'tester'@'%' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT; GRANT INSERT ON `test`.* TO `tester`@`%`; GRANT USAGE ON *.* TO `tester`@`%`;
============
TESTCASE #3:
============
If I list a user and roles in --only=user,role1,role2 format but use --drop statement then it also doesn't look good.
./pt-show-grants --socket=/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock --user=plavi --password=test1234 --only=developer,tester --drop 8.0.4>select * from mysql.role_edges; Empty set (0,00 sec) 8.0.4>select Host,User from mysql.user; +-----------+------------------+ | Host | User | +-----------+------------------+ | % | app_developer | | % | app_read | | % | app_write | | % | developer | | % | tester | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | plavi | | localhost | root | +-----------+------------------+ 10 rows in set (0,00 sec) 8.0.4>show grants for tester; +------------------------------------------+ | Grants for [email protected]% | +------------------------------------------+ | GRANT USAGE ON *.* TO `tester`@`%` | | GRANT INSERT ON `test`.* TO `tester`@`%` | +------------------------------------------+ 2 rows in set (0,00 sec) 8.0.4>show grants for developer; +---------------------------------------+ | Grants for [email protected]% | +---------------------------------------+ | GRANT USAGE ON *.* TO `developer`@`%` | +---------------------------------------+ 1 row in set (0,00 sec)
Notice that the "mysql.role_edges" table is empty now which is not the case in original and the tester role exists but it is not assigned to developer account.
pt-show-grants output:
-- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 8.0.4-rc-log at 2018-04-19 16:49:28 -- Roles CREATE ROLE IF NOT EXISTS `app_read`; CREATE ROLE IF NOT EXISTS `tester`; CREATE ROLE IF NOT EXISTS `app_developer`; CREATE ROLE IF NOT EXISTS `app_write`; -- End of roles listing DROP USER 'developer'@'%'; DELETE FROM `mysql`.`user` WHERE `User`='developer' AND `Host`='%'; -- Grants for 'developer'@'%' CREATE USER IF NOT EXISTS 'developer'@'%'; ALTER USER 'developer'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT; GRANT USAGE ON *.* TO `developer`@`%`; GRANT `tester`@`%` TO `developer`@`%`; DROP USER 'tester'@'%'; DELETE FROM `mysql`.`user` WHERE `User`='tester' AND `Host`='%'; -- Grants for 'tester'@'%' CREATE USER IF NOT EXISTS 'tester'@'%'; ALTER USER 'tester'@'%' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT; GRANT INSERT ON `test`.* TO `tester`@`%`; GRANT USAGE ON *.* TO `tester`@`%`; FLUSH PRIVILEGES;
Attachments
Issue Links
- created by
-
PT-1488 pt-show-grants support for MySQL 8.0
-
- Done
-