Bug #52404 Manual does not list what characters are allowed for passwords and user names
Submitted: 26 Mar 2010 23:03 Modified: 12 Jul 2010 16:50
Reporter: Roel Van de Paar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:Any OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[26 Mar 2010 23:03] Roel Van de Paar
Description:
http://dev.mysql.com/doc/refman/5.1/en/user-names.html

Does not list what special characters are allowed in passwords and user names.

How to repeat:
A small test shows that I could use many special characters correctly with MySQL 5.1.45 on Windows:

mysql> DROP USER test; CREATE USER test IDENTIFIED BY '/$#!%%$#&^%&^*&()(*^%%$#@!++{}{[[][]  ';
Query OK, 0 rows affected (0.00 sec)

C:\mysql5145\bin>mysql -utest -p"/$#!%%$#&^%&^*&()(*^%%$#@!++{}{[[][]  " -P5145
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71
[...]
Current user:           test@localhost

And logins with slightly altered sequences would not work (as should be the case):

C:\mysql5145\bin>mysql -utest -p"/$#!%%$#&^%&^*&()(*^%%$#@!++{}{[[][] " -P5145
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
C:\mysql5145\bin>mysql -utest -p"/$#!%%$#&^%&^*&()(*^%%$#@!++{}{[[][]   " -P5145
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
C:\mysql5145\bin>mysql -utest -p"$#!%%$#&^%&^*&()(*^%%$#@!++{}{[[][]  " -P5145
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

Interestingly, the following documentation page, albeit being about MySQL administrator, is mentioning something different (markup added):
http://dev.mysql.com/doc/administrator/en/mysql-administrator-user-administration-user-inf...

MySQL User: User name that is specified when connecting to the MySQL server. [...] **Using special characters or spaces for a user name is not recommended.**

Password: [...] **Again, don't use special characters or spaces for the password. It is, however, recommended that you use nontrivial passwords.**

Suggested fix:
List allowed characters for passwords and user names in manual. If OS dependent, specify the same.
[27 Mar 2010 6:44] Hartmut Holzgraefe
I think 'special' in the manual actually refers to non-ASCII characters.

Problem with them is that (AFAIR) currently encoding related configuration settings can only be retrieved and changed after authentication, so when setting the password from an UTF-8 context and then trying to log in from a system using ISO/Latin-1 the password hash will not match if there are non-ASCII characters like äöüßµ... in the input.
[1 Apr 2010 17:35] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Add this item to the page:

It is best to use only ASCII characters for user names and passwords 
because they are used in the authentication process before the
character set information for the client connection is established.
[3 Apr 2010 5:12] Roel Van de Paar
So *all* ASCII characters are allowed (http://en.wikipedia.org/wiki/ASCII)?
[3 Apr 2010 14:50] Paul DuBois
Hartmut's statement limits special to non-ASCII:

"I think 'special' in the manual actually refers to non-ASCII characters."

If you have information otherwise, please provide it and I'll update the manual.
[3 May 2010 13:10] Stefan Hinz
According to the previous comment, changing state to Need Doc Info.
[5 May 2010 15:15] Stefan Hinz
Sorry, must have forgotten to change status to Need Doc Info. Done now.
[20 Oct 2011 15:43] Paul DuBois
See also Bug#62301.

Revised the section to say:

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 the MYSQL_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 MySQL client programs support a --default-character-set
option that causes mysql_options() to be called as just described. In
addition, character set autodetection is supported as described in 
http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html.
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, utf16, and utf32, which
are not permitted as client character sets.