Skip navigation links
5.6.2 Adding User Accounts »
Section Navigation      [Toggle]

5.6.1. User Names and Passwords

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 5.5.2, “Privilege System Grant Tables”.

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 -u or --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.

    Warning

    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 work.

    You should never alter any of the tables in the mysql database in any manner whatsoever except by means of the procedure prescribed that is described in Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. Attempting to redefine MySQL's system tables in any other fashion results in undefined (and unsupported!) behavior.

  • MySQL user names can be up to 16 characters long. Changing the maximum length is not supported. If you try to change it, for example by changing the length of the User column in the mysql database tables, this will result in unpredictable behavior. (Altering privilege tables is not supported in any case.) Operating system user names might have a different maximum length. For example, Unix user names typically are limited to eight characters.

  • It is best to use only ASCII characters for user names and passwords.

  • The server uses MySQL passwords stored in the user table to authenticate client connections using MySQL built-in authentication. 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.

  • MySQL encrypts passwords stored in the user 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 PASSWORD() and ENCRYPT() functions in Section 11.12, “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.) Section 5.4.2.3, “Password Hashing in MySQL”, discusses password encryption further.

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.10.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 12.4.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: 

shell> mysql --user=monty --password=password db_name

If you prefer short options, the command looks like this: 

shell> mysql -u monty -ppassword db_name

There must be no space between the -p option and the following password value.

If you omit the password value following the --password or -p option on the command line, the client prompts for one.

Specifying a password on the command line should be considered insecure. See Section 5.4.2.2, “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”.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices