Home arrow MySQL arrow Page 4 - Managing MySQL User Accounts

4.5.5 Assigning Account Passwords - MySQL

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss MySQL user account management. The fourth of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

  1. Managing MySQL User Accounts
  2. 4.5.2 Adding New User Accounts to MySQL
  3. 4.5.3 Removing User Accounts from MySQL
  4. 4.5.5 Assigning Account Passwords
  5. 4.5.6 Keeping Your Password Secure
  6. Setting Up SSL Certificates for MySQL
  7. SSL GRANT Options
By: Sams Publishing
Rating: starstarstarstarstar / 7
June 15, 2006

print this article



Passwords may be assigned from the command line by using the mysqladmin command:

shell> mysqladmin -u user_name -h host_name
password "newpwd"

The account for which this command resets the password is the one with a user table record that matches user_name in the User column and the client host from which you connect in the Host column.

Another way to assign a password to an account is to issue a SET PASSWORD statement:

mysql> SET PASSWORD FOR 'jeffrey'@'%' =

Only users such as root with update access to the mysql database can change the password for other users. If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:

mysql> SET PASSWORD = PASSWORD('biscuit');

You can also use a GRANT USAGE statement at the global level (ON *.*) to assign a password to an account without affecting the account's current privileges:

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%'
IDENTIFIED BY 'biscuit';

Although it is generally preferable to assign passwords using one of the preceding methods, you can also do so by modifying the user table directly:

  • To establish a password when creating a new account, provide a value for the Password column:

    shell> mysql -u root mysql
    mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey',PASSWORD

To change the password for an existing account, use UPDATE to set the Password column value:

shell> mysql -u root mysql
mysql> UPDATE user SET Password =
-> WHERE Host = '%' AND User = 'francis'; mysql> FLUSH PRIVILEGES;

When you assign an account a password using SET PASSWORD, INSERT, or UPDATE, you must use the PASSWORD() function to encrypt it. (The only exception is that you need not use PASSWORD() if the password is empty.) PASSWORD() is necessary because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to set passwords like this:

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey','biscuit'); mysql> FLUSH PRIVILEGES;

The result is that the literal value 'biscuit' is stored as the password in the user table, not the encrypted value. When jeffrey attempts to connect to the server using this password, the value is encrypted and compared to the value stored in the user table. However, the stored value is the literal string 'biscuit', so the comparison fails and the server rejects the connection:

shell> mysql -u jeffrey -pbiscuit test
Access denied

If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, they both take care of encrypting the password for you. The PASSWORD() function is unnecessary.

Note: PASSWORD() encryption is different from Unix password encryption. See Section 4.5.1, "MySQL Usernames and 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: