Automate patch 2021.1 includes major changes to application user authentication and the interactions with MySQL. Below we describe what changed and what Automate administrators need to know.
What changed?
Previously each user in Automate had a matching user in MySQL with the same password. This allowed for integrations and administrators to authenticate to the database directly for maintenance, reporting, and other tasks with a single set of credentials.
To improve security, starting in 2021.1 application passwords are now salted hashes of the actual user password (like other modern authentication systems do). As a result, the password the user logs in with is no longer the password that MySQL authentication happens with (there is still a matching database account for every application account). Thus, authentication breaks for any integration using an Automate user account to authenticate to MySQL.
What do Automate administrators need to do?
Before you upgrade to 2021.1 create a list of everything that accesses MySQL (such as dashboards, reporting software, PowerShell scripts, etc.) and the accounts that are being used. Once you have your list there are two options: alter the accounts before the patch, or create new accounts post-patch.
Option 1: Alter the existing accounts
Note: For this method to work the account needs to already be MySQL-only — if it exists in the application then it will need to be deleted and recreated or to have its ID number altered (which will prevent login).
In MySQL, a user account has three parts; a username, a password, and a host. Without diving into the finer points of MySQL authentication, the important thing to note is that if the host string of the account in MySQL is not “%” or “localhost” then the upgrade won’t delete or impact the account. To adjust your existing accounts, use the following SQL statement (adjusted for your network):
update mysql.user set host = '<hostname>' where user = '<username>' and host = '<% or localhost>';
FLUSH PRIVILEGES;To view your accounts the following statement can be used:
select * from mysql.user;Option 2: Creating new accounts
If your integrations are using an application account, or if you want to take the opportunity to do some user maintenance, the following steps will cover how to create a new user account.
First, create a MySQL account:
CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>';Please note that MySQL doesn’t have any native defenses against a brute force attack; please use a strong password and a restrictive host specifier.
Next, it’s time for permissions. There are two possible use cases: an administrative (“root”) account, or a read-only reporting account. We’ll look at both.
For an administrator, we need to run the following commands:
GRANT ALL PRIVILEGES ON * . * TO '<username>'@'<host>' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO '<username>'@'<host>' WITH GRANT OPTION;
FLUSH PRIVILEGES;The above grants all permissions on all databases to the specified user. For a read-only reporting user we’d use these commands:
GRANT SELECT, SHOW VIEW ON labtech . * TO '<username>'@'<host>' ;
FLUSH PRIVILEGES;Please note that the permissions granted are only on the labtech database. This is a least privilege best practice, but if your integration needs access to MySQL data then the command above can be modified for the performance_schema and information_schema databases as well.
What about the negative ID number?
The official documentation from Connectwise advises partners to configure a placeholder account inside the application. Ultimately, since Connectwise controls the application this would be prudent — changing the host prevents the account from being deleted now, but there’s no guarantee that it will work indefinitely (but it still is a best practice anyway!). This ultimately comes down to partner preference; here at Automation Theory, we like to keep the application blissfully unaware of anything at the database layer (as much as possible). However, if partners are looking to future-proof their deployments, the place holder application user account is advisable. The command to create the entry is below. If you’re using our anti-injection safety measure for users be sure to update the NewTickets value; otherwise the default of 0 is fine.
insert into users (userid, `name`, NewTickets) select min(userid) -1, "<username>", 0 from users;We hope this information has been helpful for you. Here at Automation Theory we’re certified MySQL DBAs focusing exclusively on the Connectwise Automate application. Feel free to contact us for all your database configuration needs.
