| Bug #84027 | Issues with renaming root user. | ||
|---|---|---|---|
| Submitted: | 1 Dec 2016 11:42 | Modified: | 20 Oct 2017 19:02 |
| Reporter: | Kapil Singla | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S2 (Serious) |
| Version: | 8.0.0-dmr | OS: | Debian (Debian GNU/Linux 8.2 (jessie)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | rename, rename command, user rename | ||
[1 Dec 2016 11:54]
Kapil Singla
I have tested all these steps in MySQL 5.7.16 and it all works well. However, MySQL 8.0.0-dmr is an issue.
[1 Dec 2016 21:54]
MySQL Verification Team
Thank you for the bug report. Different behavior between 5.7 and 8.0:
c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18 Source distribution PULL: 2016-NOV-20
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > rename user 'root'@'localhost' to 'master'@'localhost';
Query OK, 0 rows affected (0.10 sec)
mysql 5.7 > use test
Database changed
mysql 5.7 > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test_db |
+--------------------+
6 rows in set (0.00 sec)
c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.1-dmr Source distribution PULL: 2016-NOV-20
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 8.0 > rename user 'root'@'localhost' to 'master'@'localhost';
Query OK, 0 rows affected (0.06 sec)
mysql 8.0 > use test
Database changed
mysql 8.0 > show databases;
ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist
[19 May 2017 10:19]
Kapil Singla
Is there any progress on this now?
[19 May 2017 11:06]
Ståle Deraas
Posted by developer: Hi Kapil, We have a plan for how to handle it, and this will be fixed before 8.0 goes GA. Do you have a special concern or need around this issue, just so I can understand more about your interest?
[20 Oct 2017 19:02]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.4 release, and here's the changelog entry: After the MySQL root user account was renamed, a query that accessed an INFORMATION_SCHEMA view returned an error stating that the user specified as the definer does not exist. To avoid this error, a new system user account, 'mysql.infoschema'@'localhost', is now the DEFINER for INFORMATION_SCHEMA views. Reference manual update: https://dev.mysql.com/doc/refman/8.0/en/reserved-users.html
Description: when I rename the root user (root@localhost) to any other name. It chooses NOT to work as expected. For Example, If I rename 'root' user as 'master', it is not able to run 'show databases;' command. This command is not the only command it is not able to run, many others don't work for the same error. I renamed: ---------- mysql> rename user 'root'@'localhost' to 'master'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) user is still shown as root, even after rename. This is similar to what happens in 5.7 as well. ----------------------------------------------------------------------------------------------- mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) Command doesn't work: ---------------------- mysql> show databases; ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist Rename the user back to root and it starts working. --------------------------------------------------- mysql> rename user 'master'@'localhost' to 'root'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) How to repeat: root@<server>:/usr/local/mysql-8.0.0/bin# /usr/local/mysql-8.0.0/bin/mysql -u master -p --socket=/usr/local/mysql-8.0.0/data/mysqld_8000.sock --port=8000 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 8.0.0-dmr MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | gagent | localhost | | lagent | localhost | | magent | localhost | | master | localhost | | mysql.sys | localhost | +-----------+-----------+ 5 rows in set (0.00 sec) mysql> select user(); +------------------+ | user() | +------------------+ | master@localhost | +------------------+ 1 row in set (0.00 sec) mysql> show databases; ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist mysql> mysql> rename user 'master'@'localhost' to 'root'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) user still shows 'master', session level stuff but not consistent with ACID --------------------------------------------------------------------------- mysql> select user(); +------------------+ | user() | +------------------+ | master@localhost | +------------------+ 1 row in set (0.00 sec) mysql> exit Bye root@lon-dbserver-6:/usr/local/mysql-8.0.0/bin# /usr/local/mysql-8.0.0/bin/mysql -u root -p --socket=/usr/local/mysql-8.0.0/data/mysqld_8000.sock --port=8000 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 8.0.0-dmr MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select user, host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | gagent | localhost | | lagent | localhost | | magent | localhost | | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 5 rows in set (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> rename user 'root'@'localhost' to 'master'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> show databases; ERROR 1449 (HY000): The user specified as a definer ('root'@'localhost') does not exist mysql> mysql> rename user 'master'@'localhost' to 'root'@'localhost'; Query OK, 0 rows affected (0.02 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> On a side note, there is 1 more observation that mysql.proc table is missing: ----------------------------------------------------------------------------- mysql> desc mysql.proc -> ; ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist I ran mysql_upgrade to see if mysql.proc comes back, but it doesn't. Suggested fix: Expectation: ------------ I should be able to rename root user using 'rename' command and it should all work after rename.