To assign or change a password for an existing account, one way is
to issue a SET PASSWORD
statement:
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('biscuit');
Only users such as root
that have 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'@'localhost' IDENTIFIED BY 'biscuit';
Passwords can 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 row that matches
user_name
in the
User
column and the client host from
which you connect in the Host
column.
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('localhost','jeffrey',PASSWORD('biscuit'));
mysql>FLUSH PRIVILEGES;
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 = PASSWORD('bagel')
->WHERE Host = 'localhost' AND User = 'francis';
mysql>FLUSH PRIVILEGES;
When you assign passwords using
GRANT
with an IDENTIFIED
BY
clause or with the mysqladmin
password command, they take care of encrypting the
password for you.
When you assign an account a nonempty password using
SET PASSWORD
,
INSERT
, or
UPDATE
, you must use the
PASSWORD()
function to encrypt the
password. 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('localhost','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
PASSWORD()
encryption differs
from Unix password encryption. See Section 5.6.1, “User Names and Passwords”.
User Comments
Add your own comment.