Set up SQL access for reports, dashboards and custom fields
Create a read-only MySQL user for your own SQL, enter it under Settings → SQL access, and fix the reasons the console refuses a user.
Set up SQL access for reports, dashboards and custom fields
Self-hosted only: This guide applies to self-hosted deployments. On cloud instances SQL access is not available; the menu entry is hidden and only the statements of the built-in report templates run.
SQL that you write yourself and SQL that the query builders generate runs only over a dedicated MySQL user. This covers:
- report widgets and report text parameters,
- SQL panels on dashboards, including the preview in the Panel Builder,
- SQL custom fields on the device page, including the preview in the Custom Field Builder.
You create that user in MySQL and enter it in the Web Console under Settings → SQL access (/settings/sql-access). Its MySQL grants decide which tables and columns such SQL can read. The query builders list exactly the tables and columns the user can read.
The built-in report templates do not need SQL access. Their unchanged statements run without it, also in duplicates, imports and catalog templates. A changed or new statement needs SQL access, and so does a duplicate whose statement a later release has changed.
Before you start
You need:
- A MySQL account with the right to create users, usually
root. The database user that the guided installer creates for NetLock has noCREATE USERright. - A Web Console account with the permission
settings_sql_access(the Administrator role has it). - The name of the NetLock database. The examples below use
netlock; replace it with yours.
Open a MySQL prompt as root:
-
Docker install. The NetLock images have no shell; open the prompt in the database container:
sudo docker exec -it mysql-container sh -c 'mysql -u root -p"$MYSQL_ROOT_PASSWORD" "$MYSQL_DATABASE"'If your container has a different name, list the names with
sudo docker ps --format '{{.Names}}'. The database name isMYSQL_DATABASEin/home/netlock/docker-compose.yml. -
Own MySQL server. Use any administration client and sign in as root.
Note: Install the NetLock RMM server with Docker configures NetLock itself with
"User": "root". The user for SQL access must still be a separate user: the console refuses the MySQL user that NetLock itself uses.
Step 1 — Create the MySQL user
Recommended: per-table grants without credential tables
The simplest way is to copy the statements shown on Settings → SQL access under Create the MySQL user. They are generated for the tables of this instance: one CREATE USER statement and one GRANT SELECT statement per table. Replace the password placeholder <strong password> and run them as root.
The equivalent manual way:
-
Create the user:
CREATE USER 'netlock_reports'@'%' IDENTIFIED BY '<strong password>'; -
Run this generator. Replace
netlockin theWHEREclause with your database name. It prints oneGRANTline per table:SET SESSION group_concat_max_len = 1000000; SELECT CONCAT('GRANT SELECT ', IF(SUM(c.COLUMN_NAME REGEXP 'password|secret|token|access_key|private_key|api_key|_encrypted$|community|credential|auth_|headers|http_body') > 0, CONCAT('(', GROUP_CONCAT(IF(c.COLUMN_NAME REGEXP 'password|secret|token|access_key|private_key|api_key|_encrypted$|community|credential|auth_|headers|http_body', NULL, CONCAT('`', c.COLUMN_NAME, '`')) ORDER BY c.ORDINAL_POSITION SEPARATOR ', '), ') '), ''), 'ON `', c.TABLE_SCHEMA, '`.`', c.TABLE_NAME, '` TO ''netlock_reports''@''%'';') AS grant_statement FROM information_schema.COLUMNS c JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_TYPE = 'BASE TABLE' WHERE c.TABLE_SCHEMA = 'netlock' AND c.TABLE_NAME NOT IN ('account_app_devices', 'account_app_push_settings', 'account_passkeys', 'account_sessions', 'accounts', 'agent_package_configurations', 'agent_package_guid_history', 'agent_package_installers', 'agent_protection_blocks', 'agent_protection_exemptions', 'agent_protection_offenders', 'agent_protection_statistics', 'agent_protection_volume', 'api_command_requests', 'api_idempotency_keys', 'api_request_log', 'api_tokens', 'api_webhooks', 'dashboard_shares', 'device_bitlocker_recovery_keys', 'device_upload_tokens', 'enduser_accounts', 'enduser_sessions', 'external_refs', 'integration_billing_maps', 'integration_connections', 'integration_jobs', 'integration_leases', 'integration_log', 'integration_mappings', 'integration_object_links', 'integration_state', 'integration_status_items', 'ip_whitelist_dynamic', 'microsoft_teams_notifications', 'ntfy_sh_notifications', 'policy_defaults', 'relay_admins', 'relay_session_admins', 'relay_sessions', 'settings', 'telegram_notifications', 'ticket_webhooks', 'webhook_notifications') GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME HAVING SUM(NOT c.COLUMN_NAME REGEXP 'password|secret|token|access_key|private_key|api_key|_encrypted$|community|credential|auth_|headers|http_body') > 0 ORDER BY c.TABLE_NAME; -
Copy the printed
GRANTlines and run them.
What the generator leaves out:
- The tables in the
NOT INlist hold sign-in data, keys and tokens, the credentials of notification channels, the request and command logs of the public API, relay sessions and the state of the agent protection. - In every other table, columns whose name contains
password,secret,token,access_key,private_key,api_key,community,credential,auth_,headersorhttp_body, or ends in_encrypted, are left out through a column grant — for exampledevices.access_key,devices.fcm_token, the mail passwords ofticket_departments, the SNMP community and v3 passwords ofsnmp_devicesand the request headers, body and credentials ofuptime_monitors. A query that selects such a column fails with a MySQL access error. A table in which every column is left out gets no grant. - A table grant (a line without a column list) also covers columns that a later update adds to that table.
Tables added by a later update are not readable automatically, and neither are columns added to a table that carries a column grant, such as devices.update_inventory_at (3.3.0.11). Run the generator again, or copy the statements from Settings → SQL access again, after an update. Existing grants are not affected by running them a second time.
Simple: the whole database
CREATE USER 'netlock_reports'@'%' IDENTIFIED BY '<strong password>';
GRANT SELECT ON `netlock`.* TO 'netlock_reports'@'%';This user can also read the credential tables and columns that the recommended variant leaves out, and every table added by later updates. Choose the variant that matches who edits reports, dashboards and custom fields on your instance.
If the database name contains an underscore, escape it in a database-level grant, for example `net\_lock`.*. An unescaped _ is a wildcard in MySQL; the console accepts it only if no other existing database matches the pattern.
Host part of the user
'%' is needed with Docker, because the Web Console and the server connect from other containers. With your own MySQL server, restrict the host to the machines that run the Web Console and the server, for example 'netlock_reports'@'10.0.0.5' (create one user per host or use a subnet pattern if they differ).
What the console accepts
The user may hold nothing but SELECT on the NetLock database — on the whole database, on single tables, or as column grants — plus USAGE. Do not grant anything else: no other privilege, no WITH GRANT OPTION, no roles, no privileges on *.* and no grants on other databases. A user without any grant on the NetLock database fails at connect with MySQL's Access denied … to database message.
Step 2 — Enter the user in the Web Console
- Open
Settings → SQL access. - Enter the
MySQL user name(for examplenetlock_reports) and thePassword. - Click
Save.
The console connects with the user and checks its grants. The status line at the top shows the result:
Active: SQL runs over the MySQL user netlock_reports.— your own SQL and the query builders are available.Off: the MySQL user netlock_reports was refused.followed by the reason — see Refusal reasons below.Not configured: your own SQL and the query builders are off.Scheduled reports with your own SQL show[SQL access not configured]in those widgets until a user is configured.
The connection uses the same server, port, database, TLS settings and extra connection parameters as NetLock's own connection; only user and password differ. It appears in MySQL with the application name netlock-report-sql. Each piece of SQL must be exactly one SELECT query (or WITH … SELECT). Queries run read-only and stop after 10 seconds. User variables such as @rownum := @rownum + 1 are allowed.
The password is stored encrypted with the local encryption key and is never shown again. When you save with the same user name and an empty password field, the stored password is kept; a different user name always needs its password.
Periodic re-check
The Web Console and the server check the grants again every 15 minutes. If the grants were widened in the meantime, SQL access switches off: a log entry is written and the page shows the refusal. While the user is refused, the check runs every minute, so SQL access returns shortly after you fix the grants.
Step 3 — Verify it worked
- The status line on
Settings → SQL accessreadsActive. - In the Report Builder, the Widget Editor offers the Visual Query Builder and the SQL field; the table list contains the tables the user can read.
- A dashboard SQL panel and a SQL custom field show data instead of the
SQL access not configurednotice.
Change the password
-
Change it in MySQL:
ALTER USER 'netlock_reports'@'%' IDENTIFIED BY '<new strong password>'; -
On
Settings → SQL access, enter the new password and clickSave.
Until the new password is saved, queries over the user fail and the next check reports a connection failure.
Remove SQL access
-
On
Settings → SQL access, clear the user name and clickSave. Confirm the dialog. The configuration is removed; the user stays in MySQL. -
Drop the user in MySQL:
DROP USER 'netlock_reports'@'%';
Refusal reasons
| Message | What to do |
|---|---|
The connection with this user failed: followed by the MySQL error | Check user name, password and host part of the user. Access denied … to database means the user has no grant on the NetLock database; run the GRANT statements of step 1. |
This is the MySQL user of NetLock itself. Create a separate user. | Create a separate user as described in step 1. |
The user holds privileges other than SELECT. | Revoke every other privilege (for example INSERT, UPDATE, EXECUTE, SHOW VIEW or dynamic *_ADMIN privileges). Proxy grants and partial revokes are refused with this message as well. Check with SHOW GRANTS FOR 'netlock_reports'@'%';. |
The user holds WITH GRANT OPTION. | Revoke the grant option, for example REVOKE GRANT OPTION ON `netlock`.* FROM 'netlock_reports'@'%'; (use the level it was granted on). |
The user has a role granted or active, or the MySQL server sets mandatory roles. | Revoke the role from the user and remove it from the default roles. When the server sets mandatory_roles, every session could activate those roles; SQL access needs a server without mandatory roles. |
The user can read databases other than the NetLock database. | Revoke grants on other databases and on database name patterns with %. Escape _ in the NetLock database name if another database matches the pattern. |
The user holds privileges on all databases (*.*). | Revoke the global privileges; grant SELECT on the NetLock database or its tables instead. |
The user cannot read any table of the NetLock database. | Run the GRANT statements of step 1. |
After fixing the grants, click Save again, or wait for the next check (every minute while the user is refused).
What happens without SQL access
- Own report widgets show
SQL access not configuredinstead of data; in generated output the widget shows[SQL access not configured]. Widgets of the built-in templates keep working. - Dashboard SQL panels are locked with a notice, also in the TV view. Catalog widget panels are not affected, and neither are SQL panels that were added from the shipped panel templates and still carry the template statement unchanged.
- SQL custom fields show
(SQL access not configured). - The Widget Editor, the Panel Builder and the Custom Field Builder show a notice with a link to
Settings → SQL accessand do not offer the query builder, the SQL field or the AI SQL assistant. - Saving or importing a report template that contains own SQL is refused.
- In the public API, creating or changing such a template, generating a report from it and scheduling it answer
403 sql_access_not_configured; see X.9.16.
Related
- Chapter 11 — Reports — the Report Builder, widgets and placeholders.
- Chapter 2 — Dashboard — the Panel Builder.
- Chapter 8.4 — Custom Fields — SQL Select fields.
- A.12 — Content defaults — the SQL access setting and the builders that use it.
Run a compliance report and schedule email delivery
Generate a report from a template on demand, then set up a weekly PDF email schedule.
Remote-control a device with the Relay App
Install the Relay App on the operator's machine, sign in with a relay API key and control a Windows, Linux or macOS device from its Screen Control tab: zoom, full screen, recording, the remote clipboard, pasting files, and anchored devices.