Security

Protecting MySQL Under Windows

The way of proceeding under Windows is very similar to that under Unix/Linux. However, the default configuration under Windows is even more insecure, for which reason a bit more work is necessary.

Furthermore, using the commands mysql and mysqladmin is more complex (in particular, when you wish to pass parameters). The simplest way of proceeding is first to open a command window (START |PROGRAMS |ACCESSORIES |COMMAND PROMPT) and there use cd to make the bin\ directory of the MySQL installation the current directory. (Hint: You will save some typing if you move the directory via Drag&Drop from Explorer into the command window.)

root Password for Local Access: With the following command you can secure root for the local computer with a password (where instead of xxx you provide the password of your choice). This example assumes that MySQL was installed in the directory Q:\Programs\mysql.

C:\ [mysql_dir]... \bin> mysql -u root Welcome to the MySQL monitor. mysql> SET PASSWORD FOR root@localhost = PASSWORD('xxx'); Query OK, 0 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)

Instead of executing SET PASSWORD in mysql, it must also be possible simply to execute the command mysqladmin -u root -h localhost password xxx in order to change the password for root. However, it appears that this command does not always function reliably under Windows, and often, instead of changing the root password for localhost, changes the root password for access by any number of other computers.

No root Access from External Computers: The following commands prevent root access from external computers.

Q:\ [mysql dir]... \bin> mysql -u root -p Enter password: xxx Welcome to the MySQL monitor. mysql> USE mysql; mysql> DELETE FROM user WHERE user='root' AND host='%'; Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)

Fewer Privileges for Local Users: In the default setting, local users have almost unrestricted rights, even if they do not register as root. The following command deletes all privileges for nonroot access.

mysql> REVOKE ALL ON *.* FROM ''@localhost; Query OK, 1 row affected (0.00 sec) mysql> REVOKE GRANT OPTION ON *.* FROM ''@localhost; Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)

No Local Access Without a Password:

Local users can still access universally visible databases (for example, those whose name begins with test_).

If it is not your wish that anyone with access to the local computer be able to register without a password, you can do without the above command and instead simply forbid local access without a password.

. There is another reason to recommend this course of action: The entry User = '' and Host = 'localhost' is often unsuspectingly given preference to other entries in the user table (e.g., those with User = 'aname' and User = 'a name' and Host = '%').

By deleting the entry, you avoid possible confusion:

mysql> DELETE FROM user WHERE user='' AND host='localhost'; Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)

From now on, administrative tasks are possible only as root under localhost. Therefore, you must use the commands mysql and mysqladmin with the options -u root and -p.

No Access from External Computers Without a Password: In the default setting, anyone can register with MySQL from an external computer. This access comes with no privileges, but nonetheless it should be prevented.

Here are the necessary commands:

mysql> DELETE FROM user WHERE host='%' AND user=''; Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)

WinMySQLadmin Users: If you specified a user name and password at the initial launch of WinMySQLadmin, then under some conditions, the program has an additional user with unrestricted privileges (like those of root). This is particularly the case for older versions of WinMySQLadmin. In the following it is assumed that the user name is namexy.

User namexy is secured by a password, but this password is stored in plain text in the file Windows\my.ini. Anyone who is permitted to work at the computer and knows a bit about MySQL can easily obtain unrestricted access to MySQL.

You have three options for closing this security loophole:

. Delete the user namexyz. . Delete the password line in Windows\my.ini. . Restrict the privileges of this user.

The first two of these options are not ideal, because the program WinMySQLadmin can then be used only with severe restrictions. A workable compromise consists in setting the privileges of namexy in such a way that the most important administrative tasks are possible, but alteration of data is forbidden.

mysql> REVOKE INSERT, UPDATE, DELETE, DROP, FILE, ALTER > ON *.* FROM namexyz@localhost; Query OK, 1 row affected (0.00 sec) mysql> REVOKE GRANT OPTION ON *.* FROM namexyz@localhost; Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 row s affected (0.01 sec)