Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb
EPUB - 179.6Kb
HTML Download (TGZ) - 125.5Kb
HTML Download (Zip) - 135.6Kb


Security in MySQL  /  MySQL User Account Management  /  Assigning Account Passwords

5.5 Assigning Account Passwords

Required credentials for clients that connect to the MySQL server can include a password. This section describes how to assign passwords for MySQL accounts.

The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, see CREATE USER Syntax, GRANT Syntax, and SET PASSWORD Syntax.

MySQL stores passwords in the user table in the mysql database. Operations that assign or modify passwords are permitted only to users with the CREATE USER privilege, or, alternatively, privileges for the mysql database (INSERT privilege to create new accounts, UPDATE privilege to modify existing accounts). If the read_only system variable is enabled, use of account-modification statements such as CREATE USER or SET PASSWORD additionally requires the SUPER privilege.

MySQL hashes passwords stored in the mysql.user table to obfuscate them. For most statements described here, MySQL automatically hashes the password specified. An exception is SET PASSWORD ... = PASSWORD('auth_string'), for which you use the PASSWORD() function explicitly to hash the password. There are also syntaxes for CREATE USER, GRANT, and SET PASSWORD that permit hashed values to be specified literally; for details, see the descriptions of those statements.

To assign a password when you create a new account with CREATE USER, include an IDENTIFIED BY clause:

mysql> CREATE USER 'jeffrey'@'localhost'
    -> IDENTIFIED BY 'mypass';

For this CREATE USER syntax, MySQL automatically hashes the password before storing it in the mysql.user table.

To assign or change a password for an existing account, use one of the following methods:

  • Use SET PASSWORD with the PASSWORD() function:

    mysql> SET PASSWORD FOR
        -> 'jeffrey'@'localhost' = PASSWORD('mypass');
    

    If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:

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

    The PASSWORD() function hashes the password using the hashing method determined by the value of the old_passwords system variable value. If SET PASSWORD rejects the hashed password value returned by PASSWORD() as not being in the correct format, it may be necessary to change old_passwords to change the hashing method. For descriptions of the permitted values, see Server System Variables.

  • 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'@'localhost'
        -> IDENTIFIED BY 'mypass';
    

    For this GRANT syntax, MySQL automatically hashes the password before storing it in the mysql.user table.

  • To change an account password from the command line, use the mysqladmin command:

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

    For passwords changes made using mysqladmin, MySQL automatically hashes the password before storing it in the mysql.user table.

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


User Comments
Sign Up Login You must be logged in to post a comment.