MySQL Bugs: #17187: root@localhost has no access to the database

Bug #17187	root@localhost has no access to the database
Submitted:	7 Feb 2006 11:38	Modified:	23 Feb 2006 9:50
Reporter:	Yves Pouplard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S1 (Critical)
Version:	1.1.7	OS:	Windows (Windows XP Pro French)
Assigned to:		CPU Architecture:	Any

Description:
This problem seems to concern both MySQL 5.0.13 up to 5.0.18 (in fact, the tool for configuring the database that is launched at the end of setup of MySQL Server) and MySQL Administrator 1.1.7 , and happens on new installs :

a) the tool for configuring the database does not allow to change the security settings for root (mainly its password); trying to changes it hangs the installation.

b) when opening MySQL Administrator 1.1.7, `root`@`localhost` (including the back quotes) is not allowed to SELECT on the USER (or USERS ?) table, and consequently it is not possible to add a new user (nor to change anything to root itself).

c) since MySQL Administrator 1.1.7, I have noticed that only INFORMATION_SCHEMA and TEST schematas are visible -- MYSQL itself has disappeared. It seems a good thing (for security reasons), but may it be related with the troubles?

Anyway, this happens only on new installs (on "clean" machines); as I made update after update on my own machine until now, I had noticed nothing until I had to put MySQL on another machine :-)

How to repeat:
a) Take a clean machine, with Windows XP Professional French, SP1 or older (nobody here has a SP2 that I could "play" with);
b) Install MySQL essential 5.0.18;
c) Try to change the root security settings;
d) Install MySQL Administrator 1.1.7;
e) Open it as root :-)

Thank you for the bug report. At first glance looks to me this isn't a 
MySQL Administrator bug, instead an installer issue since the setup
of the root privileges've failed. Could you please try to find the cause
which provoked the installer issue (a firewall for example) if there is
a message error in the log if the service was started.

Thanks in advance.

I am not sure for the client's machine, but when I reproduced the trouble on my own machine (after a full reinstall of Windows XP Pro French SP1 to be sure I had a clean machine), I had no firewall and no antivirus (but I have no Internet access, too, so I plan to keep the machine in ths state).

Hi!

I was unable to reproduce this bug using a clean install of XP w/o SP and MySQL 5.0.18

MySQL Server Instance Configuration Wizard (1)

Attachment: Bugs #17187 - MySQL Server Instance Configuration Wizard (1).jpg (image/pjpeg, text), 46.84 KiB.

MySQL Server Instance Configuration Wizard (2)

Attachment: Bugs #17187 - MySQL Server Instance Configuration Wizard (2).jpg (image/pjpeg, text), 38.10 KiB.

My Windows version (No firewall or antivirus inside)

Attachment: Bugs #17187 - Windows version (No firewall inside).jpg (image/pjpeg, text), 33.09 KiB.

The client's machine doesn't have Vista Inspirat, but has an older version than SP1 (as I had before). I am currently in Win XP Pro SP1, French version, I suppose it might linked to the fact it is French (non French versions of Windows are very difficult to find, so I can't make extensive tests).

There is no trace of the trouble in Windows logfiles, and MySQL service runs OK, so it seems to be only related to the Instance Configuration Wizard, either launched while installing MySQL Database Server itself or launched alone, later.

The trouble is the same inside MySQL Administrator 1.1.7, where the "mysql" instance is hidden, while it was visible before (when I made updates of versions rather of new full installs).

Hope this helps.

MySQL Administrator (1) - Login window is OK

Attachment: Bugs #17187 - MySQL Administrator (1) - Login window is OK.jpg (image/pjpeg, text), 30.35 KiB.

MySQL Administrator (2) - Instance mysql is hidden

Attachment: Bugs #17187 - MySQL Administrator (2) - Instance mysql is hidden.jpg (image/pjpeg, text), 28.85 KiB.

MySQL Administrator (3) - Clicking on User Administration menu

Attachment: Bugs #17187 - MySQL Administrator (3) - Clicking on User Administration menu.jpg (image/pjpeg, text), 18.90 KiB.

MySQL Administrator (4) - No user can be seen or added

Attachment: Bugs #17187 - MySQL Administrator (4) - No user can be seen or added.jpg (image/pjpeg, text), 45.52 KiB.

MySQL Administrator (5) - No schema can be added

Attachment: Bugs #17187 - MySQL Administrator (5) - No schema can be added.jpg (image/pjpeg, text), 15.86 KiB.

Hi everyone.

I think I have partially solved the problem, with the following operations:

1) Disable GRANT TABLES (in MySQL Administrator, Startup variables, Security panel) + restart mysqld;

2) Initial state:
mysql> select * from user;
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User    | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root    | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
| %         | support | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost | support | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
3 rows in set (0.00 sec)

3) After cloning root@localhost in root@%:
mysql> select * from user;
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User    | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| %         | root    | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost | root    | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
| %         | support | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost | support | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
4 rows in set (0.00 sec)

4) Adding privileges to root@localhost :
update user set Select_priv = 'Y', 
		Insert_priv = 'Y',
		Update_priv = 'Y',
		Delete_priv = 'Y',
		Create_priv = 'Y',
		Drop_priv = 'Y',
		Reload_priv = 'Y',
		Shutdown_priv = 'Y',
		Process_priv = 'Y',
		File_priv = 'Y',
		Grant_priv = 'Y',
		References_priv = 'Y',
		Index_priv = 'Y',
		Alter_priv = 'Y',
		Show_db_priv = 'Y',
		Super_priv = 'Y',
		Create_tmp_table_priv = 'Y',
		Lock_tables_priv = 'Y',
		Execute_priv = 'Y',
		Repl_slave_priv = 'Y',
		Repl_client_priv = 'Y',
		Create_view_priv = 'Y',
		Show_view_priv = 'Y',
		Create_routine_priv = 'Y',
		Alter_routine_priv = 'Y',
		Create_user_priv = 'Y'
where host = 'localhost' and user = 'root';

5) Control:
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User    | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| %         | root    | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost | root    | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                |          |            |             |              |             0 |           0 |               0 |                    0 |
| %         | support | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost | support | *C16E48BACA752CB06D77545CD1C69AEFBAF3A8CD | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+---------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
4 rows in set (0.00 sec)

6) Enable GRANT TABLES + restart mysqld;

Now, users and schemas can be added within MySQL Administrator (1.1.8).

Nevertheless, one problem remains: root is not allowed to create Backup procedures in MySQL Administrator (1.1.8).

7) Changed: Options, General Options: Store Passwords (method = Obscured): the trouble remains.

Any idea, someone? Anyway, I'm working on the case.

Best regards.

OK, I have found the way:
1) disable the "grant tables" in MySQL Administrator;
2) restart the mysql service;
3) go to the console interface;
4) use mysql;
5) create (if missing) user = 'root' on host = '%' AND user = 'root' on host = 'localhost' in the table named "user";
6) turn all privileges to 'Y' for these 2 records;
7) close the console, re-enable the "grant tables" in MySQL Administrator;
8) restart the mysql service;
9) Then all is OK.

Well, perhaps this is a bit tricky for end-users, so I'm still thinking to the case, I will lte you knw my future ideas if any.

Thanks to all :-)