Which SQL Lab controls keep Superset users inside their intended access
A team that gave analysts SQL Lab access usually needs three things at once: stop accidental or deliberate writes, block functions that reach outside the data an analyst should see, and cap how much data a single query returns, all without taking away self-service exploration. The controls that do this sit in three places that must be designed together. The first is Superset itself, where per-database settings decide whether SQL Lab is exposed, whether data manipulation statements are allowed and which SQL functions are disallowed through DISALLOWED_SQL_FUNCTIONS. The second is the database account Superset connects with, which determines what a query can do even if Superset lets it through. The third is the network path between Superset and the database. Apache Superset is licensed under Apache-2.0 and has no paid edition, so the cost of these controls is your team's time and testing, not a license fee.
Treat the Superset settings as guardrails rather than proof. The Superset documentation describes these options and their defaults, and the Superset configuration documentation is the place to confirm the exact keys for your version. Function blocklists change between releases: the blocklist was extended in 6.0.0 and ClickHouse functions were disallowed by default in 4.1.2 and 5.0.0, and the release notes warn that a broader list can break queries that worked before. Read the UPDATING.md notes before each upgrade, then re-run your saved queries and dashboards in staging to find what broke.
Before relying on any of these controls, test them with a non-admin analyst account against a staging database. Confirm that a write statement is rejected in each database where SQL Lab is exposed, that a blocked function fails with a clear error, and that a large result is truncated at the configured row limit. Then confirm the same restrictions in the database account, since a function blocklist is not a substitute for read-only grants. Record the results and the Superset version, because the outcome can change after an upgrade. Ask your Superset specialist or partner to confirm enforcement paths in your environment and to document any gaps that remain.
Which SQL execution control fits which risk
This table compares five SQL execution controls in Apache Superset by the risk each one addresses, what it leaves open, and when to use it. Read each row as a separate layer, because no single control covers DML, data volume, function abuse and access together. Confirm exact setting names and defaults in the configuration documentation for your target version before writing any configuration.
| Control | Risk it addresses | What it does not stop | When to use it |
|---|---|---|---|
| Per-database DML permission | Users changing or deleting data through SQL Lab on a database that should be read-only | Read queries that expose sensitive columns, and DML sent by a role that has write rights at the warehouse level | Every production database connection where analysts should never write; pair with a read-only warehouse account |
| DISALLOWED_SQL_FUNCTIONS entries | Functions that read server metadata, call the network or expose environment details, such as ClickHouse hostName or currentDatabase | Functions not on the list, and data access that is already allowed by the database account | Engines where your team has reviewed the function catalog; test first because 6.0.0 extended the list and can block existing queries |
| SQL Lab row limits | Large result sets that load the warehouse or the browser and pull more data than a task needs | Aggregations or filters that scan the full table on the database side, and exports of the rows that do return | Shared warehouses with costly scans; confirm the setting and its default in the configuration docs for your version |
| Per-database SQL Lab exposure through roles | Users who can reach ad hoc SQL against a database they should only see through dashboards | Dashboard or chart users who can still see the underlying data, and users who hold roles that already grant SQL Lab access | Sensitive databases where only a small group needs free-form SQL; review which built-in and custom roles grant SQL Lab access |
| Query auditing | Lack of evidence about who ran what, which assessors and incident responders need | Any query from happening, and queries that bypass the Superset metadata trail, such as direct warehouse connections | Any regulated or sensitive environment; confirm which query history and logs your version stores and how long they persist |
Setting up per-database SQL Lab exposure step by step
This sequence turns SQL Lab from a shared query box into a set of per-database decisions, each with a recorded test result. Run it in staging first, then repeat the same checks in production before any user is moved onto the new roles.
Inventory databases and SQL Lab users
Start by listing every database connection in Superset's Data menu and, for each one, the team that needs SQL Lab access and the reason. Record the engine, the service account Superset uses to connect, and whether the account can write. Your data owner or platform lead signs off on the list, and the output is a table with one row per database and one named owner per row. Anything without a named owner is not ready for SQL Lab access.
Confirm the DML setting for each database
Open each database's connection settings and confirm that the option allowing DML (data modification statements such as INSERT, UPDATE, DELETE and DROP) is off. Keep it off unless a named owner has documented a reason to write through Superset. Then check that the warehouse account itself has no write privileges, because the UI setting is a control inside Superset and not a substitute for database permissions. Done means each row in the inventory shows DML off, with the check date and the reviewer's name.
Set the function blocklist for each engine
Review DISALLOWED_SQL_FUNCTIONS in superset_config.py and add entries for the engines in use. Superset 6.0.0 extended the default list for PostgreSQL, MySQL, SQLite, SQL Server and ClickHouse, which can block queries that previously ran, so check the UPDATING notes for your version before you deploy. Superset 4.1.2 and 5.0.0 disallow the ClickHouse functions version, currentDatabase and hostName by default. Test representative saved queries after the change, then record which functions were added and why.
Set row limits for SQL Lab results
Set the row limit that caps how many rows SQL Lab returns to the browser, and confirm the default that applies to each database. Also review the asynchronous query setting if your deployment uses workers, since long queries then run outside the web request. Ask your team to confirm the exact configuration keys in the Superset configuration documentation for your version. Verify by running an unfiltered select against a large table as the test user and confirming the result is truncated at the configured limit.
Restrict SQL Lab access through roles
Grant the SQL Lab permission only to roles that need it, rather than to Alpha or Admin by default. Map each database to the roles allowed to query it, using the database access permissions on each role. Confirm that a Gamma-based test user cannot open SQL Lab or query a database outside its role. Done means a role matrix exists that matches the inventory from step one, and the test user's attempts outside that matrix are refused.
Verify each control with a test user in staging
Sign in as a test user in staging for each role and run the same set of checks: a write statement such as an INSERT or DROP must be rejected, a blocked function such as one on the blocklist must return an error, a large select must be truncated, and a query against a database outside the role must be refused. Record the statement, the expected result, the actual result and the time for each check. Any failed check goes back to the step that owns it before you continue.
Record results for the change record
Gather the inventory, DML settings, blocklist diff, row limit values, role matrix and the test results into one change record, with the Superset version, the configuration file version and the names of the reviewers. Store it with your other change records so an auditor or the next upgrade can see what was tested and when. Repeat the same test set in production after deployment and log those results separately. Done means a reviewer who was not involved can reproduce every check from the record alone.
Where SQL Lab controls fail and what to add around them
SQL Lab settings limit what Superset will run, but they do not decide what the database account can read or how long evidence is kept. The cards below name four gaps that remain after per-database settings are in place, and the controls to add around each one.
Blocklists are never complete
DISALLOWED_SQL_FUNCTIONS is a list of function names, so it only blocks what someone thought to add for that engine. Superset 6.0.0 extended the list with more sensitive functions for several engines, and 4.1.2 and 5.0.0 blocked ClickHouse functions by default, which shows how the list changes between releases. Check the list against each engine your users query, and treat it as one layer. Pair it with a database account that lacks the privileges those functions would need, and review the list after every upgrade using the release notes.
DML switches are not database permissions
Turning off data modification in SQL Lab stops Superset from sending those statements, but it does not stop a connection string that holds write privileges from being used elsewhere. Any other tool, script or leaked credential on that account can still write. Create a read-only database user for each Superset connection, grant only the schemas and tables that role needs, and confirm with a test write that the account fails. Ask the database team who holds the credential and where it is stored.
Row limits do not cap total reads
A row limit caps one result set, but a user can run many queries, each under the limit, and assemble a full extract over time. Row limits therefore reduce accidental large pulls without bounding exposure. Compensate with query timeouts and rate controls at the database or proxy layer, and with restricted schemas so sensitive tables are not reachable in the first place. Confirm with the database team which accounts have the concurrency and scan limits you intend to enforce.
Network restrictions limit reachable hosts
SQL Lab can only reach the databases its connection settings allow, but the Superset host often sits on a network that reaches far more than that. Restrict outbound traffic from the Superset web and worker hosts to the specific database endpoints, ports and any required metadata services. Test from a Superset worker that a connection to an unapproved host fails. Document the allowed routes so the network team can review them with each new database connection.
Audit logs need retention and a reviewer
Query history is useful for investigations only if logs survive long enough and someone reads them. Decide which events go to the central log store, how long they are kept, and who can read them without Superset admin rights. Name a review owner, define what they look for, such as large result sets, repeated failed statements or queries against sensitive schemas, and record each review. Ask your security team to confirm retention periods against your own policy before go-live.
How the DISALLOWED_SQL_FUNCTIONS changes in 5.0 and 6.0 affect existing queries
Superset 4.1.2 and 5.0.0 disallow three ClickHouse functions by default: version, currentDatabase and hostName. These functions reveal server metadata, so blocking them limits what an SQL Lab user can learn about the ClickHouse server behind a connection. The restriction only applies to queries sent to ClickHouse connections, so PostgreSQL, MySQL and other engines are unaffected by this change.
- Affected engine: The default block covers ClickHouse only in these two releases.
- Affected functions:
version,currentDatabaseandhostNamefail when they appear in a query text. - Where it shows up: Ad hoc SQL Lab queries, saved queries, and virtual dataset SQL that calls these functions will return an error after the upgrade.
- Who is affected: Teams that use these functions to label environments, show the active database in a dashboard title, or check server versions in diagnostic queries.
- Workaround scope: Changes to the disallowed list are made through configuration, so confirm the current key name and format in the UPDATING notes before editing any setting.
Before upgrading from an earlier release, search your ClickHouse queries for these three names. Confirm the exact default list in the release notes for your target version.
Superset 6.0.0 extends DISALLOWED_SQL_FUNCTIONS with a larger set of functions that the project classes as more sensitive. The expanded list covers PostgreSQL, MySQL, SQLite, SQL Server and ClickHouse. Because the change is a blocklist expansion, a query that ran on 5.x can fail on 6.0.0 without any change to the query text, the data or the user's role.
- Engines covered: PostgreSQL, MySQL, SQLite, SQL Server and ClickHouse each have additional functions on the list.
- Existing queries: Saved queries, chart datasets and dashboard filters that call a newly blocked function will stop returning results.
- Failure pattern: Errors often appear only when a particular chart refreshes or a user opens a dashboard, so a quick spot check of SQL Lab may miss them.
- Engine-specific checks: A function that is harmless on one engine can be blocked on another, so test each connection type separately.
- Exact list: The precise function names for each engine are in the UPDATING notes, and you should confirm them against the release you deploy.
After upgrading to 6.0.0, check dashboards and scheduled reports against a staging copy with production-like data, not only SQL Lab, because reports and async queries run under different conditions.
Before any upgrade that changes the disallowed list, build an inventory of every SQL statement that Superset executes or stores. The UPDATING notes for each release name the functions involved, so use those names as search terms against your metadata. The goal is to find affected saved queries, virtual datasets, chart definitions and dashboards before users find them in production.
- Saved queries: Export or query the saved query records in the metadata database and search their SQL text for each listed function name.
- Virtual datasets: Search the SQL defined on virtual datasets, since every chart built on one inherits its query.
- Dashboards and charts: Map affected datasets to the charts and dashboards that use them, including filters and native dashboard controls.
- Scheduled reports and alerts: Check reports and alerts that run on a schedule, because they can fail silently after the upgrade.
- Staging verification: Load a staging copy of the metadata database, upgrade it, and open each affected dashboard and run each affected saved query.
Record the results with owners and a decision for each query, whether it is rewritten, moved to a different function or approved as an exception. Confirm the function lists in the UPDATING notes for every version between your current release and the target.
Why row limits and DML switches do not replace database privileges
Superset's SQL Lab settings decide what the application accepts from a user, but they do not decide what the database engine permits. A DML switch that is off for a database stops the statement in Superset before it is sent. A row limit caps what Superset returns to the browser. Neither one changes the privileges of the account Superset uses to connect, so any code path that reaches the engine with that account, including a misconfigured connection, a new feature or a SQL statement that slips past a pattern check, runs with whatever rights that account holds. Treat the database account as the enforced boundary and the Superset settings as a second layer on top of it.
Making the boundary real starts with the warehouse user. Create a dedicated read-only account for each sensitivity level, grant SELECT only on the schemas and tables the analysts need, and confirm that the account cannot create objects, alter tables or write to schemas used by other applications. Network rules close the remaining gap: restrict which hosts can open connections to the warehouse, so a copied credential is not usable from elsewhere. Scope the schemas in the connection itself, not only in the Superset role, because a role that hides a dataset in the interface does not stop a direct query against the engine.
Per-user impersonation is the strongest way to carry Superset's access model into the database, since the engine then applies its own grants to each person. Support differs by engine and by how the connection is configured, so confirm for each database whether impersonation is available, how the user identity is passed, and whether it works with your authentication setup before you rely on it. Where it is not available, document the shared service account as an accepted risk and compensate with the schema scoping and network rules above. The engine vendor's documentation and a test in staging are the sources to use for that confirmation.
Limits to confirm in your environment before relying on these controls
The SQL execution controls on this page depend on the Superset version you run, the defaults in your build and how your role model is configured. Each card below names one thing to check, what it affects and how to confirm it against the vendor documentation or a test in your own environment.
Version and blocklist changes
The DISALLOWED_SQL_FUNCTIONS list changes between releases, so a blocklist you tested on one version may differ on another. Superset 6.0.0 extended the list for several engines, and 4.1.2 and 5.0.0 added ClickHouse functions by default. Read the UPDATING notes for every version between your current release and your target, and confirm which functions your build blocks. Then run your saved charts and dashboards against the new list in staging, because a newly blocked function can break queries that worked before.
DML and row limit defaults
Confirm the default state of DML, the statements that change data, and the default row limit in the exact build you deploy. Check the value in your superset_config.py and in the database connection settings, not only in the documentation for another version. A default that was safe on one release can be permissive on the next, so record the effective values after every upgrade.
Function naming by engine
Each database engine names and resolves functions differently, so a blocklist entry that catches a function on PostgreSQL may miss an equivalent on MySQL, SQLite, SQL Server or ClickHouse. Build a test matrix per connected engine, run each blocked function name through SQL Lab, and confirm that the attempt is rejected. Include schema-qualified and case-varied spellings in the tests.
Per-database SQL Lab exposure
Decide whether SQL Lab access can be scoped per database in your role model, so that a user with access to one connection cannot open SQL Lab against another. Test this with a non-admin account that holds only the roles you plan to assign. Admin and Alpha roles often carry broader rights than expected, so confirm what each built-in role can reach before you rely on the scoping.
Audit log contents and storage
Find out what Superset records about executed queries, including the SQL text, the user, the database, the timestamp and the row count, and where those entries are stored. Ask whether the record lives in the metadata database, in application logs or in an external log pipeline, and who can read or delete it. An audit trail that rotates out or sits with the same administrators you are monitoring does not give you the evidence you need.
Custom roles and upgrade resets
Confirm whether a custom role that you edited, or a permission you changed on a built-in role, survives an upgrade. Superset's initialization step can reset edited roles, so a restriction applied through the UI may disappear after superset init runs. Keep SQL execution settings in version-controlled configuration where possible, and rerun your permission tests after every upgrade.
Questions teams ask before restricting SQL Lab
Can our own team restrict SQL Lab without a partner?
Yes, a team with Superset administration experience and access to its database administrators can make these changes itself. The work is configuration rather than code: deciding which databases allow DML, which functions go in DISALLOWED_SQL_FUNCTIONS, which roles can open SQL Lab, and what row limits apply. The harder part is inventory and testing. Your team needs to know which saved queries, charts and dashboards depend on functions or statements you are about to block. A partner is most useful when the number of databases and roles is large, or when the team lacks time to test each change against a realistic query library. Confirm your internal capacity against that list before deciding.
How much more effort is a restricted SQL Lab than a default install?
A default install gives users SQL Lab with few limits, so the restricted version adds work in three areas: policy decisions, configuration and verification. Policy decisions cover which users need ad hoc SQL at all. Configuration covers per-database settings, function blocklists and role changes. Verification means running representative queries as each role and confirming that allowed work succeeds and blocked work fails with a clear message. Effort grows with the number of exceptions you must support, not with the install itself. Ask any provider to split its quote into these three areas so you can see where the time goes.
What drives the effort of restricting SQL execution?
Four factors drive most of the effort:
- Number of databases, since each one has its own DML and SQL Lab settings and its own engine dialect.
- Number of engines, because function names and dangerous constructs differ between PostgreSQL, MySQL, SQLite, SQL Server and ClickHouse.
- Number of roles and users who need SQL Lab, and how many custom roles already exist.
- How much existing SQL is in saved queries, charts and dashboards that must keep working.
A single PostgreSQL database with one analyst group is a small task. Ten engines with dozens of roles and a large saved query library is a multi-stage project. Request a quote that states the count of each factor it assumes.
What do we need to provide for a SQL Lab restriction project?
Plan for three inputs from your side. Database administrators must confirm which accounts Superset uses, what privileges those accounts hold, and whether any user needs functions that will be blocked. Role owners must decide who keeps SQL Lab access and what each group may run. You also need a staging environment with a copy of the metadata database and representative data sources, so changes can be tested before production. Without staging, every blocked function becomes a production incident. Ask the provider to list these dependencies in writing before work starts.
How long does testing take for a function blocklist and query library?
Testing duration depends on the size of the function list and the query library, and no fixed figure applies to every environment. Testing is faster when the blocklist is short and most saved queries use standard syntax. It takes longer when the blocklist covers functions used in production dashboards, or when each database needs separate test passes. Ask for the test plan to name the query set, the roles tested, the databases covered and the number of test cycles. Confirm the expected duration with the provider against your own query inventory, since no researched figure is available for it.
Will a Superset upgrade break our SQL Lab restrictions or queries?
It can. Superset 6.0.0 extended DISALLOWED_SQL_FUNCTIONS with more sensitive functions for PostgreSQL, MySQL, SQLite, SQL Server and ClickHouse, and this can block queries that worked before. Superset 4.1.2 and 5.0.0 also disallow the ClickHouse functions version, currentDatabase and hostName by default. Read the breaking change notes in UPDATING.md for each version between your current release and the target, then test your restricted configuration in staging. Back up the metadata database before upgrading, and record which functions and settings you customized so you can reapply them.
Does restricting SQL Lab stop users from seeing sensitive data?
No. Restricting SQL execution limits what users can run, but it does not control what data they can see through charts, dashboards or allowed queries. Data access still depends on dataset permissions, row level security and least-privilege database accounts. Blocking DML and dangerous functions reduces the risk of data changes and some injection-style misuse, while function blocklists are not a complete defense. Treat SQL Lab controls as one layer in a broader access model and ask any provider how they test that layer against bypass attempts.
Can DeployOpen guarantee that restricted queries will never break?
No engagement can guarantee that. DeployOpen plans and implements restrictions in your infrastructure, documents the configuration and tests representative queries as each role before production. Work is scoped and agreed in writing before it starts, so the deliverables and test coverage are visible in advance. Future upgrades and new queries can still change behavior, which is why ongoing testing and staged upgrades are part of the plan. Ask for the test cases, the roles covered and the rollback steps in the written scope.
How DeployOpen scopes and delivers SQL Lab hardening
DeployOpen approaches SQL execution controls in Apache Superset as a written, fixed-scope piece of work inside infrastructure you control. We map which users reach SQL Lab, which databases allow DML, and which functions and row limits apply, then build and test those settings in staging before production.
How we scope
- Inventory every Superset database connection and record which ones currently allow DML and which users can reach SQL Lab.
- Agree in writing which databases stay read-only, which functions are added to DISALLOWED_SQL_FUNCTIONS, and which row limits apply.
- Confirm with your team the Superset version in use, since function block lists changed in 6.0.0 and existing queries may break.
How we build
- Apply per-database SQL Lab and DML settings in a staging copy, then record each change against the agreed scope.
- Test representative saved queries and dashboards under the new function block list to find breakage before users do.
- Confirm that database accounts behind each connection hold only the privileges the scope requires, matching the read-only design.
How we hand over
- Deliver a written configuration record covering DML flags, disallowed functions, row limits and the SQL Lab role assignments.
- Document how to review executed queries in your audit logs and who on your team reviews them and how often.
- Provide a test script your team can rerun after each Superset upgrade, following the project's upgrade steps and UPDATING.md notes.
How we support
- Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts.
- Ongoing support, upgrades and continuous testing are available as a retainer, including review of function block lists after each release.
- Ask any partner to state in the quote which items are one-time work and which recur under a retainer.
