MySQL Workbench Alternatives – ClusterControl Database User Management

MySQL user and privilege management is very critical for authentication, authorization and accounting purposes. Since MySQL 8.0, there are now two types of database user privileges:

1. Static privileges – The common global, schema and administrative privileges like SELECT, ALTER, SUPER and USAGE, built into the server.
2. Dynamic privileges – New in MySQL 8.0. A component that can be registered and unregistered at runtime which provides better control over global privileges. For example, instead of assigning SUPER privilege only for configuration management purposes, that particular user is better be granted with SYSTEM_VARIABLES_ADMIN privilege only.

Creating a database schema with its respective user is the very initial step to start using MySQL as your database server. Most applications that use MySQL as the datastore require this task to be done before the application could work as intended. To use with an application, commonly a MySQL user is configured to have full privileges (ALL PRIVILEGES) on the schema level, meaning the database user used by the application has the freedom to perform any actions on the assigned database.

In this blog post, we are going to compare and contrast MySQL database user management features between MySQL Workbench and ClusterControl.

MySQL Workbench – Database User Management

For MySQL Workbench, you can find all the user management stuff under Administration -> Management -> User and Privileges. You should see a list of existing users on the left-side while on the right-side is the authentication and authorization configuration section for the selected user:

MySQL supports over 30 static privileges and it is not easy to understand and remember them all. MySQL Workbench has a number of preset administrative roles, which is very helpful when assigning sufficient privileges to a database user.  For example, if you would like to create a MySQL user specifically to perform backup activities using mysqldump, you may pick the BackupAdmin role and the related global privileges will be assigned to the user accordingly:

To create a new database user, click on the “Add Account” button and supply necessary information under the “Login” tab. You may add some more resource restrictions under the “Account Limit” tab. If the user is only for a database schema and not intended for any administrative role (strictly for application usage), you may skip the “Administrative Roles” tab and just configure the “Schema Privileges”. 

Under the “Schema Privileges” section, one can pick a database schema (or define the matching pattern) by clicking “Add Entry”. Then, press the “Select ALL” button to allow all rights (except GRANT OPTION) which is similar to “ALL PRIVILEGES” option statement:

A database user will not be created in the MySQL server until you have applied the changes, by clicking the “Apply” button.

ClusterControl – Database and Proxy User Management

ClusterControl database and user management is a bit more straightforward than what MySQL Workbench offers. While MySQL Workbench is more developer friendly, ClusterControl is focused more on what SysAdmins and DBAs are interested in, more like common administration stuff for those who are already familiar with MySQL roles and privileges.

To create a database user, go to Manage -> Schemas and Users -> Users -> Create New User. You will be presented with the following user creation wizard:

Creating a user in ClusterControl requires you to fill up all necessary fields in one page, unlike MySQL Workbench which involved a number of clicks to achieve similar results. ClusterControl also supports creating a user with “REQUIRE SSL” syntax, to enforce the particular user to access only via SSL encryption channel.

ClusterControl provides an aggregated view on all database users in a cluster, eliminating you to login to every individual server to look for a particular user:

A simple rollover on the privileges box reveals all privileges that have been assigned to this user. ClusterControl also provides a list of inactive users, user accounts that have not been used since the last server restart:

The above list gives us a clear summary of which users are worth to exist, allowing us to manage the user more efficiently. DBAs can then ask the developer whether the inactive database user is still necessary to be active, otherwise the user account can be locked or dropped.

If you are having a ProxySQL load balancer in between, you might know that ProxySQL has its own MySQL user management to allow it to be passed through it. There are a number of different settings and variables if compared to the common MySQL user configurations e.g, default hostgroup, default schema, transaction persistence, fast forward and many more. ClusterControl provides a graphical user interface in managing ProxySQL database users, improving the experience and efficiency of managing your proxy and database users at once:

When creating a new database user via ProxySQL management page, ClusterControl will automatically create the corresponding user on both ProxySQL and MySQL. However, when dropping a MySQL user from ProxySQL, the corresponding database user will remain on the MySQL server.

Advantages & Disadvantages

ClusterControl supports multiple database vendors so you will get a similar user experience dealing with other database servers. ClusterControl also supports creating a database user on multiple hosts at once, where it will make sure the created user exists on all database servers in the cluster. ClusterControl has a cleaner way when listing out user accounts, where you can see all necessary information right in the listing page. However, user management requires active subscription and is not available in the community edition. It does not support all platforms that MySQL can run, particularly only certain Linux distributions like CentOS, RHEL, Debian and Ubuntu.

The strongest advantage of MySQL Workbench is that it is free, and can be used together with schema management and administration. It’s built to be more friendly to developers and DBAs and has the advantage of being built and backed by the Oracle team, who owns and maintains MySQL server. It also provides much clearer guidance with description on most of the input fields, especially in the critical parts like authentication and privilege management. The preset administrative role is a neat way of granting a set of privileges to a user, based on the work the user must carry out on the server. On the down side, MySQL Workbench is not a cluster friendly tool since every management connection is tailored to one endpoint MySQL server. Thus, it doesn’t provide a centralized view of all users in the cluster. It also doesn’t support creating users with SSL enforcement.

Both of these tools do not support the new MySQL 8.0 dynamic privileges syntax e.g, BACKUP_ADMIN, BINLOG_ADMIN, SYSTEM_VARIABLES_ADMIN, etc.

The following table highlights notable features for both tools for easy comparison: