Managing MySQL User Accounts

4.5 MySQL User Account Management

This section describes how to set up accounts for clients of your MySQL server. It discusses the following topics:

• The meaning of account names and passwords as used in MySQL and how that compares to names and passwords used by your operating system

• How to set up new accounts and remove existing accounts

• How to change passwords

• Guidelines for using passwords securely

• How to use secure connections with SSL

4.5.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 can't 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 will be able to connect successfully to the server.

• MySQL usernames can be up to 16 characters long. Operating system usernames might have 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. 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.4.5, "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 the GRANT and REVOKE 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 4.4.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 4.5.6, "Keeping Your Password Secure." To avoid this, 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

Then the client program will print a prompt and wait 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 call 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.