Home arrow MySQL arrow Page 5 - MySQL User Account Management

12.2.4 Changing Account Passwords - MySQL

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).

  1. MySQL User Account Management
  2. 12.2.2 The Grant Tables
  3. 12.2.3 Granting and Revoking Privileges
  4. The REVOKE Statement
  5. 12.2.4 Changing Account Passwords
  6. 12.2.5 Specifying Resource Limits
By: Sams Publishing
Rating: starstarstarstarstar / 20
July 20, 2006

print this article



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' =

    Any nonanonymous client can change its own password by omitting the FOR clause:

  • 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'

Be certain that you want to do this, however. It isn't a good idea to have accounts without passwords.

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: