User Management for Compose for MySQL

Compose for MySQL users are able to both create new users and grant those users permissions on the databases. To do so you will first have to acquire a local installation of MySQL and connect to the deployment through the command-line.
You can then create database users through a two step process:

create the user
grant the user permissions.

Creating a User

To create a new user, connect to your Compose for MySQL deployment with the mysql CLI, and use the CREATE USER command.

mysql> CREATE USER 'mickey'@'%' IDENTIFIED BY 'NotAnotherRodent';
Query OK, 0 rows affected (0.05 sec)

Specify a username; the host name part of the account name, if omitted, defaults to '%'. Use IDENTIFIED BY to specify a password.

Granting Permissions

The 'admin' user provided by default with a Compose for MySQL deployment has the GRANT OPTION privilege, which allows 'admin' to GRANT any of the permissions the 'admin' user has to any other user. As a result, when provisioning a new user, 'admin' to explicitly grant named privileges. 'admin' can't GRANT ALL ON because the 'admin' user does not have ALL. It will fail with the message:

mysql> GRANT ALL ON *.* to 'mickey'@'%' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

Instead you will have to enumerate the permissions available to 'admin' and specify which of those you would like to grant to your new user.

To grant named privileges, connect as the 'admin' user and run:
SHOW GRANTS;

+-------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'admin'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------------------------+
1 row in set (0.08 sec)

This will return the grants available to the 'admin' user, and then use any of those grants when applying grants to other users. For example:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'mickey'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)

To verify that the correct permissions have been granted, connect to your deployment using the new user and password and run the SHOW GRANTS; command.

Still Need Help?

If this article didn't solve things, summon a human and get some help!