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

TABLE OF CONTENTS:
  1. MySQL User Account Management
  2. 12.2.2 The Grant Tables
  3. 12.2.3 Granting and Revoking Privileges
  4. 12.2.3.2 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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



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

blog comments powered by Disqus
   

MYSQL ARTICLES

- Xeround Releases Free Version of MySQL Cloud...
- Oracle Announces New MySQL Specialization
- Constant Contact Chooses SkySQL for MySQL Su...
- Revoke Statement in MySQL
- The Grant Statement in MySQL
- SuccessBricks Announces ClearDB Availability...
- Building a PHP ORM: Deploying a Blog
- TROSYS Launches Free MySQL Manager and Admin...
- Building an ORM in PHP: Domain Modeling
- Building an ORM in PHP
- MySQL Leads Open Source Market, Gets Cluster...
- Oracle Announces Milestone Release for MySQL
- How to Stop SQL Injection Attacks
- New Defragmentation Solution for SQL Server
- Comparison of MyISAM and InnoDB MySQL Databa...


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 10 - Follow our Sitemap

Dev Shed Tutorial Topics: