NetLock RMMNetLock RMM Docs
IV — Administration

Content defaults

SQL access for Dashboards, Reports, and Custom Fields — the MySQL user that own SQL runs over — plus a pointer to Ticket System defaults.

Content defaults

Three content builders of the Console work with SQL: the Report Builder, the dashboard Panel Builder and the Custom Field Builder. Which parts of the MySQL schema they can read is decided by one setting, Settings → SQL access. The separate Ticket System Settings page is sometimes grouped with these, but works differently; it is covered at the end.

A.12.1 SQL access

Route: /settings/sql-access. Self-hosted only; on cloud instances the menu entry is hidden and the page states that SQL access is not available.

SQL that users write themselves and SQL that the query builders generate runs only as a dedicated MySQL user. The administrator creates that user in MySQL and enters user name and password on this page. The user's MySQL grants are the boundary of what such SQL can read; the query builders list exactly the tables and columns it can read.

The page shows:

  • the status: Active, Not configured, or Off with the reason the user was refused;
  • the MySQL user name and the Password field. The password is stored encrypted with the local encryption key and never shown again; leaving the field empty keeps the stored password while the user name is unchanged;
  • ready-to-copy CREATE USER and per-table GRANT SELECT statements generated for the tables of this instance, without the tables and columns that hold credentials, and a link to the how-to.

Saving with an empty user name removes the configuration after a confirmation; the user stays in MySQL.

The console accepts a user only if it holds nothing but SELECT on the NetLock database (whole database, single tables or column grants) plus USAGE. The check runs on save and again every 15 minutes in the Web Console and the server; if the grants are widened later, SQL access switches off until they are fixed. The step-by-step setup, the statements and the refusal reasons are in Set up SQL access for reports, dashboards and custom fields.

SQL access page with status line, user name and password fields, and the generated GRANT statements

Permission: settings_sql_access.

Note: After the update to 3.3.0.11, accounts and custom roles that held settings_reports_enabled or settings_dashboards_enabled hold settings_sql_access.

A.12.2 Dashboards

SQL panels on dashboards run as the SQL access user. This covers statements typed directly and statements generated by the Panel Builder's query builder, including the preview. Without SQL access they are locked with a notice, also in the TV view; catalog widget panels are not affected. Creating and changing SQL panels needs dashboard_sql_panels and assignment to every tenant. See Chapter 2.

A.12.3 Reports

Custom SQL in report widgets and text parameters runs as the SQL access user. The built-in report templates work without it as long as their statements stay unchanged, also in duplicates, imports and catalog templates. Without SQL access, widgets with custom SQL show SQL access not configured, and saving or importing a template with custom SQL is refused. See Chapter 11.3.

A.12.4 Custom Fields

Route: /settings/custom-fields.

The page holds the section Global values: the custom field values every device inherits unless a group, location, tenant or the device itself sets one (see Chapter 8.4). Custom fields with the SQL Select data source run as the SQL access user; without it they show (SQL access not configured) on the device page.

Permissions: settings_enabled, settings_custom_fields_enabled.

A.12.5 Ticket System defaults

The Ticket System has its own settings page at /settings/ticket-system with a different layout: module toggle, ticket number prefix, blocked file extensions, and Labels / Types / SLA tabs. It is documented with the rest of the ticket module in Chapter 10.2 and the sub-sections that follow.

To only enable the module, switch on Ticket System Enabled on the General tab at /settings/ticket-system.

A.12.6 Operational notes

  • Saved SQL is checked when it runs. Every custom statement runs as the SQL access user, so narrowing that user's grants takes effect at once for existing reports, panels and custom fields. A statement that reads a table or column the user can no longer read fails with MySQL's access error.
  • New tables after an update. With per-table grants, tables added by a later update are not readable until you run the GRANT statements again. Copy them from Settings → SQL access after an update.
  • Built-in report statements after an update. When a release changes a built-in statement, a duplicate made from the older version needs SQL access.

Permissions

PageFlags
SQL accesssettings_enabled, settings_sql_access
Custom Fields Settingssettings_enabled, settings_custom_fields_enabled