pt-show-grants output unsupported syntax for users with DEFAULT ROLE
General
Escalation
General
Escalation
Description
pt-show-grants creates following ALTER USER syntax for users configured with “DEFAULT ROLE“ which is not supported supported syntax for mysql 8.0 or 8.4
Unsupported command: ALTER USER test_user@% IDENTIFIED WITH 'mysql_native_password' AS '*8A447777509932F0ED07ADB033562027D95A0F17' DEFAULT ROLE test_role@% REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT ROLE test_role@% REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK' at line 1
Ref MySQL Doc:
How to reproduce (8.0 & 8.4 both(:
Create roles and assign it as DEFAULT ROLE to user
Run the pt-show-grants to dump the grants
Fetch the role/user commands
Execute commands into MySQL instance
Wanted to understand specific reason why the CREATE USER & ALTER USER commands were seperated as highlighted here We dont run into this syntax error if both commands are merged together (as what we get from the output of show create user)
i.e. CREATE USER IF NOT EXISTS test_user@% IDENTIFIED WITH 'mysql_native_password' AS '*8A447777509932F0ED07ADB033562027D95A0F17' DEFAULT ROLE test_role@% REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
Other possible solution is to split the ALTER USER even further to get things working:
pt-show-grants creates following ALTER USER syntax for users configured with “DEFAULT ROLE“ which is not supported supported syntax for mysql 8.0 or 8.4
Unsupported command: ALTER USER
test_user@%IDENTIFIED WITH 'mysql_native_password' AS '*8A447777509932F0ED07ADB033562027D95A0F17' DEFAULT ROLEtest_role@%REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT ROLE
test_role@%REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK' at line 1Ref MySQL Doc:
How to reproduce (8.0 & 8.4 both(:
Create roles and assign it as DEFAULT ROLE to user
Run the pt-show-grants to dump the grants
Fetch the role/user commands
Execute commands into MySQL instance
Wanted to understand specific reason why the CREATE USER & ALTER USER commands were seperated as highlighted here
We dont run into this syntax error if both commands are merged together (as what we get from the output of
show create user)i.e.
CREATE USER IF NOT EXISTS test_user@% IDENTIFIED WITH 'mysql_native_password' AS '*8A447777509932F0ED07ADB033562027D95A0F17' DEFAULT ROLE test_role@% REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;Other possible solution is to split the ALTER USER even further to get things working: