MySQL stores accounts in the
user table of the
mysql system 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”.
There are several distinctions between the way user names and passwords are used by MySQL and 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
--useroption. This means that anyone can attempt to connect to the server using any user name, so 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 may be of a different maximum length. For example, Unix user names typically are limited to eight characters.Warning
The limit on MySQL user name length is hard-coded in MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the
mysqldatabase does not work.
You should never alter the structure of tables in the
mysqldatabase in any manner whatsoever except by means of the procedure that is described in Section 4.4.8, “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 is free to ignore rows that become malformed as a result of such modifications.
To authenticate client connections that use MySQL built-in authentication, the server uses MySQL passwords stored in the
usertable. These passwords are distinct from 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.
MySQL encrypts passwords stored in the
usertable 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
mysqldatabase is captured. (In earlier versions, even though passwords are stored in encrypted form in the
usertable, 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 encryption further.
If the user name and password contain only ASCII characters, it is possible to connect to the server regardless of character set settings. To connect when the user name or password contain non-ASCII characters, the client should call the
mysql_options()C API function with the
MYSQL_SET_CHARSET_NAMEoption 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-setoption that causes
mysql_options()to be called as just described. 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
ucs2, which is not permitted as a client character set.
The MySQL installation process populates the grant tables with an
initial account or accounts. The names and access privileges for
these accounts are described in
Section 2.12.4, “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”.
To connect to a MySQL server with a command-line client, specify user name and password options 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
If you omit the password value following the
option on the command line (as just shown), the client prompts for
one. Alternatively, the password can be specified on the command
mysql --user=monty --password=shell>
mysql -u monty -p
If you use the
-p option, there must be
no space between
-p and the
following password value.
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. See Section 4.2.6, “Using Option Files”.
For additional information about specifying user names, passwords, and other connection parameters, see Section 4.2.2, “Connecting to the MySQL Server”.