Securing Accounts


There are a few general security principles that apply to the management of user accounts in MySQL. We will look at these next .

Setting the Password for the Root Account

When you install MySQL, the root password is not set by default . You absolutely must set this password before using MySQL in anything other than a purely experimental environment. Without the root password set, anyone can log in and do anything he wants to your data. In virtually all cases, this is a very bad thing. If you have not done so already, set this password immediately .

Deleting Anonymous Accounts

When you install MySQL on Windows, it automatically creates some accounts for you. On Linux, this happens when you run the mysql_install_db script. Two of these accounts are anonymous; they represent the account you get when you don't specify a username. One has a host value of localhost and the other % (any other host, so effectively any remote connection). These accounts have no passwords set by default.

You can probably already see where we're going with this, but we strongly recommend that you delete these accounts. You can do this as shown here:

 
 delete from user where User=''; delete from db where User=''; 

You will need to follow this with a FLUSH PRIVILEGES statement to flush the grant tables.

The second reason to do this is that these accounts can cause confusion when regular users try to log in. If you create an account for, let's say username laura at any host ( % ), then when laura tries to connect from localhost , the MySQL server looks for matching entries in the user table. It has laura@% and (anonymous)@localhost . Because MySQL matches the most specific hostname first, the matching row is (anonymous)@localhost . Note that although laura has supplied a username, this doesn't matter! The anonymous accounts don't require a username. This anonymous account is likely to have a different password from laura 's account (by default, the password is blank, meaning the user should not supply one). This means that when laura tries to log in with her username and password from localhost , she will get an Access Denied error for no obvious reason.

Again, the best way to avoid this problem is to delete these accounts and forget about them.

Dangerous Privileges

MySQL has a very fine-grained privilege system, as we discussed in Chapter 11. You must be very careful about to whom you grant some of these privileges. The specific ones to be most careful of are FILE , PROCESS , and WITH GRANT OPTION .

The FILE privilege allows users to LOAD DATA INFILE . This can be manipulated to load in files from the server (such as the password file /etc/passwd) or even database data files, effectively circumventing the privilege system.

The PROCESS privilege allows users to SHOW PROCESSLIST . This reveals the queries being executed at any given time, which may reveal confidential information about one user to another.

The WITH GRANT OPTION privilege allows a user to share his privileges with others. As long as you know this and understand the consequences, you can grant this privilege cautiously.

Passwords and Encryption

MySQL user passwords are encrypted. Before version 4.1, you could use the encrypted password as stored to log in. This has now been fixed and the password and login mechanism have been made more secure.

If you are writing an application that stores (non-MySQL) usernames and passwords, we recommend that you use something other than the PASSWORD() function to encrypt them. We recommend use of MD5() or ENCRYPT() instead. See Chapter 8, "Using MySQL Built-In Functions with SELECT," for a further discussion of these functions.



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net