{question}
Does SingleStore have any criteria/restrictions for special characters in the user password?
{question}
{answer}
SingleStore software doesn't impose any criteria or restrictions with the user passwords. As far as the database is concerned, passwords can be as long as possible and as complex as we want. However, there are some caveats.
In some cases, the shell or a script will interpret the password before it's passed to the database, and if there are any special characters, they might not be handled properly. For example, if the password is #\@s2.Db#%sql6
and It has an escape character in it (\
). when we pass it via the command line, the shell would interpret that as it is, and it would result in access denied as below. You might have to escape the escape with another backslash (\
).
Let's create user with the password mentioned: #\@s2.Db#%sql6
mysql> CREATE USER 'singlestore'@'%' IDENTIFIED BY '#\@s2.Db#%sql6';
Query OK, 0 rows affected (0.00 sec)
We need to input the password when the command line prompts for it, which results in an access denied error as below
$ mysql -h ec2-1-3-11-5.compute-1.amazonaws.com -u singlestore -p
Enter password: -> Password typed is#\@s2.Db#%sql6
ERROR 1045 (28000): Access denied for user 'singlestore'@'10.0.1.1' (using password: YES)
Workaround to use escape character in this particular scenario is to escape the escape character as below,
mysql> CREATE USER 'singlestore'@'%' IDENTIFIED BY '#\\@s2.Db#%sql6';
Query OK, 0 rows affected (0.00 sec)
When we try to access now with #\@s2.Db#%sql6
, We can log in successfully.
$ mysql -h ec2-1-3-11-5.compute-1.amazonaws.com -u singlestore -p
Enter password: -> Password typed is#\@s2.Db#%sql6
Welcome to the MySQL monitor. Commands end with ; or \g.
Another concern now is, if you are trying to provide the password like below, it will fail because we have changed the password to include another backslash.
$ mysql -h ec2-1-3-11-5.compute-1.amazonaws.com -u singlestore -p#\@s2.Db#%sql6
ERROR 1045 (28000): Access denied for user 'singlestore'@'10.0.1.1' (using password: YES)
We have to use the actual password with (\\
) which was provided while creating the user,
$ mysql -h ec2-1-3-11-5.compute-1.amazonaws.com -u singlestore -p#\\@s2.Db#%sql6
Welcome to the MySQL monitor. Commands end with ; or \g.
The problem here is the way shell interprets the input(password). Our suggestion would be to avoid using the escape character in the password. If it's required to use the escape character, then follow the workaround with the caveats mentioned.
To learn about the SingleStore password policy, click here.
{answer}