MemSQL allows addition/removal of user accounts via the GRANT and DROP USER commands. By default, MemSQL is configured to support a root user with no password.
If you see an error like:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (user password: YES)
then you probably have a default password configured in your global my.cnf file. The client refers to this file for default parameters to use when connecting to the database, so if you have a line resembling:
password = XXXXXX
then the client will default to connecting to MemSQL with that password. MemSQL comes installed with a root user and no password, so to resolve this issue you have two simple workarounds:
- Remove this line from my.cnf
- Specify --password='' as an argument to the connection command: mysql -u root --password='' -h 127.0.0.1 -P 3306 --prompt="memsql> "
You can change the root user’s password by running
GRANT ALL ON *.* TO root@% IDENTIFIED BY 'password' WITH GRANT OPTION
Currently, MemSQL does not support granular permissions. Instead, the user authentication mechanism is simply a tool to control username/password logins to the database. Once logged in, a user has full permissions. Users can be specified with usernames, hostnames, and passwords.
MemSQL supports only the following GRANT syntax:
GRANT ALL ON *.* TO user_specification [, user_specification] ... WITH GRANT OPTION user_specification: user [ IDENTIFIED BY [PASSWORD] 'password' ]
This statement creates a user account with all privileges.
Drop User Syntax¶
MemSQL supports the following DROP USER syntax:
DROP USER user [, user] ...
This statement removes one or more user accounts.
MemSQL doesn’t expose the users table directly. Instead, you can view grants and permissions by querying information_schema.user_privileges.