5.8.1. MySQL Usernames and Passwords
A MySQL account is defined in terms of a username and the client
host or hosts from which the user can connect to the server. The
account also has a password. There are several distinctions
between the way usernames and passwords are used by MySQL and
the way they are used by your operating system:
Usernames, as used by MySQL for authentication purposes,
have nothing to do with usernames (login names) as used by
Windows or Unix. On Unix, most MySQL clients by default try
to log in using the current Unix username as the MySQL
username, but that is for convenience only. The default can
be overridden easily, because client programs allow any
username to be specified with a -u or
--user option. Because this means that
anyone can attempt to connect to the server using any
username, you cannot make a database secure in any way
unless all MySQL accounts have passwords. Anyone who
specifies a username for an account that has no password is
able to connect successfully to the server.
-
MySQL usernames can be up to a maximum of 16 characters
long. This limit 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 work.
Note: You should
never alter any of the tables in the
mysql database in any manner whatsoever
except by means of the procedure prescribed by MySQL AB that
is described in Section 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”. Attempting
to redefine MySQL's system tables in any other fashion
results in undefined (and unsupported!) behavior.
Operating system usernames are completely unrelated to MySQL
usernames and may even be of a different maximum length. For
example, Unix usernames typically are limited to eight
characters.
MySQL passwords have nothing to do with passwords for
logging in to your operating system. There is no necessary
connection between the 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 using its own algorithm. This
encryption is different from that used during the Unix login
process. MySQL password encryption is the same as that
implemented by the PASSWORD() SQL
function. Unix password encryption is the same as that
implemented by the ENCRYPT() SQL
function. See the descriptions of the
PASSWORD() and
ENCRYPT() functions in
Section 12.10.2, “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 mysql 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.)
When you install MySQL, the grant tables are populated with an
initial set of accounts. These accounts have names and access
privileges that are described in
Section 2.9.3, “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
GRANT and REVOKE. See
Section 13.5.1, “Account Management Statements”.
When you connect to a MySQL server with a command-line client,
you should specify the username and password for the account
that you want to use:
shell> mysql --user=monty --password=guess db_name
If you prefer short options, the command looks like this:
shell> mysql -u monty -pguess db_name
There must be no space between the
-p option and the following password value. See
Section 5.7.4, “Connecting to the MySQL Server”.
The preceding commands include the password value on the command
line, which can be a security risk. See
Section 5.8.6, “Keeping Your Password Secure”. To avoid this problem,
specify the --password or -p
option without any following password value:
shell> mysql --user=monty --password db_name
shell> mysql -u monty -p db_name
When the password option has no password value, the client
program prints a prompt and waits for you to enter the password.
(In these examples, db_name is
not interpreted as a password because it is
separated from the preceding password option by a space.)
On some systems, the library routine that MySQL uses to prompt
for a password automatically limits the password to eight
characters. That is a problem with the system library, not with
MySQL. Internally, MySQL doesn't have any limit for the length
of the password. To work around the problem, change your MySQL
password to a value that is eight or fewer characters long, or
put your password in an option file.
