In light of the recent security vulnerabilities, many Connectwise partners are starting to monitor Automate for the creation of user accounts in Automate (among other security layers). Like all parts of IT security, more is generally better, and prevention is better than cure. With regards to SQL injection of privileged accounts, we can handle this portion of Connectwise Automate security at the database layer.
Warning: This is not officially supported and might not be suitable for your environment. Please proceed at your own risk.
Important Note: Due to the changes in Automate Patch 2021.1 these changes need to be made with a non-application privileged MySQL account. Anyone who implemented this with an application super admin account previously should recreate it with a dedicated account as described here: https://automationtheory.org/connectwise-automate-mysql-user-changes-in-patch-2021-1/ Otherwise the changes in patch 2021.1 might lead to server malfunctions, as described here: https://automationtheory.org/breaking-database-permission-changes-automate-2021-1/
Triggers to the rescue
MySQL can be configured to execute custom checks for specific actions on specific tables. This is how the last modified auditing works. Our solution is going to leverage a field most partners don’t use and treat it as a pin number of sorts. Since there’s an existing trigger that runs before update on the users table we need to drop and recreate the trigger with our customizations.
Triggers can’t be modified via our Database Commander plugin, but the following SQL commands can be loaded into a script. Our first step is to drop the existing trigger.
-- Drop the existing trigger
drop trigger AuditUsersAdd;delimiter $$
create trigger labtech.AuditUsersAdd before insert on labtech.users
FOR EACH ROW
BEGIN
-- Existing CW actions for this trigger
SET NEW.Last_Date=now();
SET NEW.Last_User=User();
-- Action to abort if the New Tickets field doesn't match
if NEW.NewTickets != 12345 then -- CHANGE 12345 TO SOMETHING UNIQUE!!!
signal sqlstate '45000' SET MESSAGE_TEXT = 'Unauthorized user account';
end if;
-- Set NewTickets back to it's default:
SET NEW.NewTickets = 0;
END $$
delimiter ;The above SQL trigger will check each new user added for the new tickets value (must be numeric). If it doesn’t match the hardcoded value the MySQL statement to add the user will abort and return the error message (which won’t show at the application layer). When creating new users simply put in your hardcoded pin number and configure as you normally would.
It’s worth noting that this solution is not foolproof, nor is it enough to ensure 100% security. For added tamper protection the name of the trigger can be altered (although this might complicate patching in the future should Connectwise ever try to update it).
We hope that this guide has been helpful. Here at Automation Theory, we’re certified MySQL DBAs dedicated exclusively to the Connectwise Automate software stack. Be sure to check out our integrations and services.
