MySQL stores accounts in the
user table of the
mysql database. An account is defined in terms
of a user name and the client host or hosts from which the user
can connect to the server. The account may also have a password.
For information about account representation in the
user table, see
Section 6.2.2, “Privilege System Grant Tables”. MySQL 5.7
supports authentication plugins, so it is possible that an account
authenticates using some external authentication method. See
Section 6.3.8, “Pluggable Authentication”.
There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by your operating system:
User names, as used by MySQL for authentication purposes, have
nothing to do with user names (login names) as used by Windows
or Unix. On Unix, most MySQL clients by default try to log in
using the current Unix user name as the MySQL user name, but
that is for convenience only. The default can be overridden
easily, because client programs permit any user name to be
specified with a
--user option. Because this means that anyone
can attempt to connect to the server using any user name, you
cannot make a database secure in any way unless all MySQL
accounts have passwords. Anyone who specifies a user name for
an account that has no password is able to connect
successfully to the server.
MySQL user names can be up to 16 characters long. Operating system user names, because they are completely unrelated to MySQL user names, may be of a different maximum length. For example, Unix user names typically are limited to eight characters.
The limit on MySQL user name length is hard-coded in the
MySQL servers and clients, and trying to circumvent it by
modifying the definitions of the tables in the
mysql database does not
You should never alter any of the tables in the
mysql database in any manner whatsoever
except by means of the procedure that is described in
Section 4.4.6, “mysql_upgrade — Check and Upgrade MySQL Tables”. Attempting to redefine
MySQL's system tables in any other fashion results in
undefined (and unsupported!) behavior.
The server uses MySQL passwords stored in the
user table to authenticate client
connections using MySQL native authentication (against
passwords stored in the
These passwords have nothing to do with passwords for logging
in to your operating system. There is no necessary connection
between the “external” password you use to log in
to a Windows or Unix machine and the password you use to
access the MySQL server on that machine.
If the server authenticates a client using a plugin, the
authentication method that the plugin implements may or may
not use the password in the
user table. In
this case, it is possible that an external password is also
used to authenticate to the MySQL server.
MySQL encrypts passwords stored in the
table using its own algorithm. This encryption is the same as
that implemented by the
PASSWORD() SQL function but
differs from that used during the Unix login process. Unix
password encryption is the same as that implemented by the
ENCRYPT() SQL function. See the
descriptions of the
ENCRYPT() functions in
Section 12.13, “Encryption and Compression Functions”.
From version 4.1 on, MySQL employs a stronger authentication
method that has better password protection during the
connection process than in earlier versions. It is secure even
if TCP/IP packets are sniffed or the
database is captured. (In earlier versions, even though
passwords are stored in encrypted form in the
user table, knowledge of the encrypted
password value could be used to connect to the MySQL server.)
Section 188.8.131.52, “Password Hashing in MySQL”, discusses password
It is possible to connect to the server regardless of
character set settings if the user name and password contain
only ASCII characters. To connect when the user name or
password contain non-ASCII characters, the client should call
mysql_options() C API
function with the
option and appropriate character set name as arguments. This
causes authentication to take place using the specified
character set. Otherwise, authentication will fail unless the
server default character set is the same as the encoding in
the authentication defaults.
Standard MySQL client programs support a
--default-character-set option that causes
mysql_options() to be called
as just described. In addition, character set autodetection is
supported as described in
Section 10.1.4, “Connection Character Sets and Collations”. For programs that use a
connector that is not based on the C API, the connector may
provide an equivalent to
mysql_options() that can be
used instead. Check the connector documentation.
The preceding notes do not apply for
are not permitted as client character sets.
When you install MySQL, the grant tables are populated with an
initial set of accounts. The names and access privileges for these
accounts are described in Section 2.9.2, “Securing the Initial MySQL Accounts”,
which also discusses how to assign passwords to them. Thereafter,
you normally set up, modify, and remove MySQL accounts using
statements such as
Section 13.7.1, “Account Management Statements”.
When you connect to a MySQL server with a command-line client, specify the user name and password as necessary for the account that you want to use:
mysql --user=monty --password=
If you prefer short options, the command looks like this:
mysql -u monty -p
There must be no space between the
-p option and the following password value.
If you omit the
-p option on the command line, the client prompts
Specifying a password on the command line should be considered insecure. See Section 184.108.40.206, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.
For additional information about specifying user names, passwords, and other connection parameters, see Section 4.2.2, “Connecting to the MySQL Server”.