6.3.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 Section 13.7.1.1, “CREATE USER Syntax”, Section 13.7.1.3, “GRANT Syntax”, and Section 13.7.1.6, “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: