User Names and Passwords
MySQL stores accounts in the user
table of the MariaDB
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 , "Privilege System Grant Tables". MariaDB 5.6 supports authentication plugins, so it is possible that an account authenticates using some external authentication method. See , "Pluggable Authentication".
There are several distinctions between the way user names and passwords are used by MariaDB and the way they are used by your operating system:
- User names, as used by MariaDB for authentication purposes, have nothing to do with user names (login names) as used by Windows or Unix. On Unix, most MariaDB clients by default try to log in using the current Unix user name as the MariaDB 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 MariaDB 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 MariaDB user names, may be of a different maximum length. For example, Unix user names typically are limited to eight characters.Warning
The limit on MariaDB user name length is hard-coded in the MariaDB servers and clients, and trying to circumvent it by modifying the definitions of the tables in the
MariaDB
database does not work.You should never alter any of the tables in the
MariaDB
database in any manner whatsoever except by means of the procedure that is described in , "mysql_upgrade - Check Tables for MariaDB Upgrade". Attempting to redefine MySQL's system tables in any other fashion results in undefined (and unsupported!) behavior. - The server uses MariaDB passwords stored in the
user
table to authenticate client connections using MariaDB native authentication (against passwords stored in themysql.user
table). 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 MariaDB 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 MariaDB server. - MySQL encrypts passwords stored in the
user
table using its own algorithm. This encryption is the same as that implemented by thePASSWORD()
SQL function but differs from that used during the Unix login process. Unix password encryption is the same as that implemented by theENCRYPT()
SQL function. See the descriptions of thePASSWORD()
andENCRYPT()
functions in , "Encryption and Compression Functions".
From version 4.1 on, MariaDB 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
MariaDB
database is captured. (In earlier versions, even though passwords are stored in encrypted form in theuser
table, knowledge of the encrypted password value could be used to connect to the MariaDB server.) , "Password Hashing in MySQL", discusses password encryption further. - 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 the
mysql_options()
C API function with theMYSQL_SET_CHARSET_NAME
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 MariaDB client programs support a
--default-character-set
option that causesmysql_options()
to be called as just described. In addition, character set autodetection is supported as described in , "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 tomysql_options()
that can be used instead. Check the connector documentation.The preceding notes do not apply for
ucs2
,utf16
, andutf32
, which 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 , "Securing the Initial MariaDB Accounts", which also discusses how to assign passwords to them. Thereafter, you normally set up, modify, and remove MariaDB accounts using statements such as CREATE USER
, GRANT
, and REVOKE
. See , "Account Management Statements".
When you connect to a MariaDB 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 , "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 , "Connecting to the MariaDB Server".