MySQL Bugs: #3205: Securing Root Access Improperly Documented for Win32

Bug #3205	Securing Root Access Improperly Documented for Win32
Submitted:	17 Mar 2004 10:59	Modified:	9 May 2004 4:34
Reporter:	Clint Slakor	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
What I Did
==========
- Installed a fresh copy of MySQL 4.0.18 server & client tools on Windows XP SP1.
- Followed 2.4.1 Windows Post-Installation Procedures for removing the anonymous user account that has all privileges as well as assigning a password to the root "accounts":

C:\> C:\mysql\bin\mysql -u root mysql
mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
C:\> C:\mysql\bin\mysqladmin -u root password "newpwd"

What I Expected To Happen
=========================
- host='localhost' user='' would be removed
- root access would be secured with the password I specified

What Actually Happened
======================
- The anonymous privileged user was successfully removed.
- The record in table mysql.user where host = 'localhost' and user = 'root' was updated so that the password field was set with the password I specified to mysqladmin (encrypted).
- The record in table mysql.user where host = '%' and user = 'root' was unchanged.

Implications
============
By following the manual's instructions in section 2.4.1, the administrator succesfully secures root from the localhost ONLY.  One can still connect from any other host as root without supplying a password and have full access if only the manual's instructions are followed (tested) since the user record where host='%' and user='root' is left unchanged.  Since connecting as root from the localhost requires a password, the administrator may falsely assume root access is secured.

How to repeat:
1. Do a default install of MySQL 4.0.18 on any Win32 platform.

2. Follow the instructions in section 2.4.1 of the manual to "assign a password to the root accounts".

3. Verify a password is required to connect as root on the localhost by executing: mysql -u root -p and supply the configured password.

4. Note the IP of the machine running the server.

5. Go to any other host.

6. mysql -h {IP of server} -u root

7. Enjoy full access.

Suggested fix:
Suggested Documentation Fix
===========================
Alter the suggested process in 2.4.1 to read as follows:

C:\> C:\mysql\bin\mysql -u root mysql
mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> SET PASSWORD FOR ROOT = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'ROOT'@'%' = PASSWORD('newpwd');
mysql> FLUSH PRIVILEGES;
mysql> QUIT

Further Recommendations
=======================
To further secure the server, I recommend deleting the mysql.user record where host='%' and user = ''.  While this account has no priviledges, it can still connect and consume resources without any authentication necessary.  One can accomplish this by modifying the first DELETE statement above:

DELETE FROM user WHERE User = '';

I apologize for any misinterpretations on my part.

Regards,

Clint Kennedy, BSCS
djslakor@hotmail.com

The manual says:

C:\> C:\mysql\bin\mysql -u root mysql
mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
C:\> C:\mysql\bin\mysqladmin -u root password "newpwd"
C:\> C:\mysql\bin\mysqladmin -u root -h host_name password "newpwd"

It appears that you missed the last step?

See:

http://www.mysql.com/doc/en/Windows_post-installation.html

Thank you for responding.

No, I did not miss the last step.  -h host_name refers to the SERVER HOST (as is documented one paragraph down in the manual), not the host entry you wish to alter in the mysql.user table.

Example:

mysqladmin -u root -h % password "test"
mysqladmin: connect to server at % failed
error: 'Unknown MySQL Server Host '%' (11001)'
Check that mysqld is running on % and that the port is 3306.
You can check this by doing 'telnet % 3306'

So, again I submit the same problem / recommended documentation change.

Regards,

Clint Kennedy, BSCS

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

-h host_name does indeed refer to the server host.
That's because that's what you're supposed to enter
on the command line, not -h % as you are using.
When you connect that way, the server authenticates
you using the root@% user table entry, and changes
the password for that entry.

Fix status; I set it incorrectly.