After applying the Connectwise Automate 2021.1 patch, several partners reported different issues, unrelated to the database authentication changes. The symptoms varied from plugins not functioning to certain SQL queries not working, to issues with custom tables, all the way to major application issues. The cause of these issues can be traced back to permission changes.
Database Permissions and Connectwise Automate
Automate is unique as an application, as it is tightly coupled to its MySQL database, and it’s not a traditional three-tier application. As a result of this, the application permissions closely mirror database permissions (and that’s why each application user has a matching database user). When looking at the User Class Manager interface, some of the column titles are MySQL permissions, and others are synonymous with MySQL permissions (create is insert, and read is select).
What Changed in 2021.1?
In previous versions of Automate, any user of Super Admin user class had the DBA role in MySQL (also known as all privileges, or super). This meant that a Super Admin could access any database table, perform any operation, and customize the schema in any way.
In the 2021.1 patch, most of the MySQL administrative privileges were removed from the Super Admin user class (except for the ability to grant permissions). Thus, three things happened:
- Any queries that required permissions beyond select, insert, update, or delete on the default application tables broke.
- Plugins that did not implement the IPermissions interface for their tables malfunctioned.
- Any schema modifications (stored procedures, triggers, etc.) created with an application won’t run now, since the user that created them doesn’t have the proper privileges now.
Please note that if you have followed certain community guides in the past your server might severely malfunction when applying patch 2021.1. Here at Automation Theory our guide in the forums for preventing duplicate MAC addresses and our blog post for preventing rouge user creation might prove to be problematic as they are trigger-based. We will be editing our content appropriately, but other community guides might not get such maintenance.
How can the breaking changes be corrected?
The first step is to have a working privileged MySQL user that is not an application account. Please see our blog post here for proper instructions.
Next, any schema customizations (triggers, stored procedures, etc. — but not tables) must be recreated with the new account. The exact steps will vary, but in general, it will involve showing the create statement, dropping the existing entity, and then running the create statement again
Tables must have the correct permissions applied to them — but there’s a catch: the Database Agent service resets the table permissions when it restarts, and it adds a permissions entry for each user for each table. As a temporary workaround, a script could be used to run the grant statement. Here at Automation Theory, we’ve created a plugin that will allow custom tables to be permissioned with the native application process.
We’d like to remind our readers that we do offer Automate application patching as a service in the event you’d like to have a DBA on standby when applying the 2021.1 patch.
We hope this information has been helpful to you, and we wish you the best of luck navigating these changes!
