Assigning Account Passwords
602
Counter resets do not affect the
MAX_USER_CONNECTIONS
limit.
All counts begin at zero when the server starts; counts are not carried over through a restart.
For the
MAX_USER_CONNECTIONS
limit, an edge case can occur if the account currently has open the
maximum number of connections permitted to it: A disconnect followed quickly by a connect can result
in an error (
ER_TOO_MANY_USER_CONNECTIONS
[2923]
or
ER_USER_LIMIT_REACHED
[2925]
) if the
server has not fully processed the disconnect by the time the connect occurs. When the server finishes
disconnect processing, another connection will once more be permitted.
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.
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';
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('mypass');
MySQL stores passwords in the
user
table in the
mysql
database. 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('mypass');
The
old_passwords
[480]
system variable value determines the hashing method used by
PASSWORD()
[956]
. If you specify the password using that function and
SET PASSWORD
rejects
the password as not being in the correct format, it may be necessary to set
old_passwords
[480]
to change the hashing method. For descriptions of the permitted values, see
Section 5.1.4, “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';
To assign a password from the command line, use the
mysqladmin
command:
shell>
mysqladmin -u user_name -h host_name password "newpwd"
The account for which this command sets 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.
During authentication when a client connects to the server, MySQL treats the password in the
user
table as an encrypted hash value (the value that
PASSWORD()
[956]
would return for the password).
When assigning a password to an account, it is important to store an encrypted value, not the plaintext
password. Use the following guidelines:
• When you assign a password using
CREATE USER
,
GRANT
with an
IDENTIFIED BY
clause, or the
mysqladmin password
command, they encrypt the password for you. Specify the literal plaintext
password:
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
