Last week, we began our discussion of MySQL database security. This week, we continue that discussion with user account management. The second of several parts, this article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).
As discussed earlier, you can specify a password for an account by including an IDENTIFIED BY clause in a GRANT statement. If the account is new, the clause assigns its initial password. If the account already exists, the clause changes its password.
To change an existing account's password without changing any of its privileges, you have two options:
Use the SET PASSWORD statement, specifying the account name and the new password. For example, to set the password for jim on the local host to NewPass, use this statement:
SET PASSWORD FOR 'jim'@'localhost' =
PASSWORD('NewPass');
Any nonanonymous client can change its own password by omitting the FOR clause:
SET PASSWORD = PASSWORD('NewPass');
Use GRANT with the USAGE privilege specifier at the global level and an IDENTIFIED BY clause:
GRANT USAGE ON *.* TO 'jim'@'localhost'
IDENTIFIED BY 'NewPass';
USAGE means no privileges, so the statement changes the password without granting any privileges.
Note that with SET PASSWORD, you use PASSWORD() to encrypt the password, whereas with GRANT, you do not use it.
To allow a user to connect without specifying a password, change the password to the empty string. However, you cannot revoke the password this way with REVOKE. Instead, use either of the following statements:
SET PASSWORD FOR 'jim'@'localhost' = '';
GRANT USAGE ON *.* TO 'jim'@'localhost'
IDENTIFIED BY '';
Be certain that you want to do this, however. It isn't a good idea to have accounts without passwords.
/ 20 
