Installing Database Permissioner #
- Download plugin from the URL in the email
- Extract the download.
- Import the plugin DLL (System –> Solutions –> Plugin Manager –> Advanced –> Manage Plugins –> Add Plugin).
- Activate the plugin.
- Restart the Database Agent.
- Log out and back into the Control Center.
- Open the plugin tab in the Dashboard (System -> Configuration -> Dashboard -> Config -> Integration -> Database Permissioner).
Using Database Permissioner #
How permissions work in ConnectWise Automate #
Permissions are based on a hash table (generated per user) that contains a table name, and a comma separated list of MySQL privileges. As a result, a user can only have one privilege set per table. With this restriction, and the possibility to configure conflicting permission sets, Database Permissioner has two behaviors to be aware of.
- Super Admin users are granted all permissions on any tables defined in the plugin.
- In the permission list (evaluated as displayed in the GUI), the last matching permission is applied (previous assignments are overwritten)
Thus, if a user class is assigned SELECT permissions to a table, and subsequently an individual user in that class is assigned ALL permissions, the user will have ALL permissions. Please be mindful of the ordering of permissions to ensure no unwanted access is allowed.
Assigning Permissions #
To add a custom permission:
- Enter the name of the table
- Enter the desired permissions
- Enter the User Class ID or a User ID (leave the unused value set to 0)
- Click the “Add Permission” button. The table of existing permissions above should update with the new permission.
- Restart the Database Agent (this applies the permissions).
Removing Permissions #
To remove a custom permission:
- Select the row of the permission to be removed.
- Right-click, and choose “Delete” in the context menu.
- Select “Yes” in the pop-up box to confirm the deletion.
Note: The delete operation is row based. Right-clicking in a single cell will not trigger the delete process.
Troubleshooting Permission Assignment #
To assist in the debugging of permission assignment the plugin logs the assigned permissions to it’s log file each time it runs. For on-premise servers the log file can be found in the following location:
Also, any privileged MySQL account can view the user-level permissions on a table. The query is as follows:
select group_concat(user,": ",table_priv, '\r\n') FROM mysql.tables_priv WHERE DB LIKE 'labtech' AND Table_name LIKE '<custom_table_name>';
For any partners without direct access to the database these commands are able to be run from a script (included in the plugin download):