Permissions for queries

Settings for query permissions.

Queries in ClickHouse can be divided into several types:

1. Read data queries: SELECT, SHOW, DESCRIBE, EXISTS.
2. Write data queries: INSERT, OPTIMIZE, DELETE, UPDATE, ALTER TABLE ... DELETE, ALTER TABLE ... UPDATE.
3. Change settings query: SET, USE.
4. DDL queries: CREATE, ALTER, RENAME, EXCHANGE, ATTACH, DETACH, DROP, TRUNCATE.
5. Access management queries: GRANT, REVOKE, and CREATE, ALTER and DROP of users, roles, row policies, masking policies, quotas and settings profiles. See access control and account management.
6. KILL QUERY.

ALTER TABLE ... DELETE and ALTER TABLE ... UPDATE change data rather than table metadata, which is why they are listed above as write data queries. They require the ALTER DELETE and ALTER UPDATE privileges, which are also the privileges the standalone DELETE and UPDATE statements require. Those privileges belong to the ALTER TABLE privilege group, so allow_ddl = 0 refuses all four statements on a persistent table.

The following settings regulate user permissions by the type of query:

readonly

Restricts which queries a session may run. The values of the setting, its default, and which settings each value lets you change are described in the settings reference; this section describes which classes of query each value permits.

When set to 1, allows queries such as:

• Read queries (like SELECT and equivalent queries).
• Queries that modify only session context (like USE).

When set to 2, allows the above plus SET, CREATE TEMPORARY TABLE and RESTORE. A RESTORE can create a table and load data into it, so readonly = 2 does not by itself stop a session from writing; readonly = 1 refuses it.

BACKUP is not restricted by readonly at any value: a session that has the privileges to back a table up can write a backup even at readonly = 1. Do not rely on readonly to prevent backups.

Most table functions need the CREATE TEMPORARY TABLE privilege, so a SELECT that reads from one is refused at readonly = 1 but not at readonly = 2. Some, such as numbers, are allowed in read-only mode.

For any value above 0, none of the following is permitted on a persistent table: write data queries (INSERT, OPTIMIZE, DELETE, UPDATE, ALTER TABLE ... DELETE, ALTER TABLE ... UPDATE) or DDL queries (CREATE, ALTER TABLE, ALTER VIEW, RENAME, EXCHANGE, ATTACH, DETACH, DROP, TRUNCATE TABLE). SYSTEM statements that require a privilege from the SYSTEM group, and CREATE, ALTER and DROP of users, roles, row policies, masking policies, quotas and settings profiles are not permitted either. Named collection management is an exception: readonly does not restrict CREATE NAMED COLLECTION, ALTER NAMED COLLECTION or DROP NAMED COLLECTION.

Granting a privilege with GRANT is refused too, but not every access management statement is: a local REVOKE and GRANT CURRENT GRANTS are not gated by readonly, so a read-only session can still revoke a privilege it holds with grant option and propagate its own grants to another user. Revoking a privilege ON CLUSTER is refused.

Temporary tables are exempt from both settings: a session that may create one may also ALTER it, insert into it and drop it.

Over the HTTP interface, a request whose method is not POST runs with readonly = 2 if the effective value would otherwise be 0. A stricter value already set by the user’s settings or by a settings profile is kept. PUT and DELETE are exempt when they reach a SQL-defined handler that accepts them, so such a request can modify data when the effective readonly is 0; otherwise use the POST method to modify data.

On a request raised this way, a readonly parameter in the query string is refused with Cannot modify 'readonly' setting in readonly mode unless it names the value the request already has.

There is a way to prohibit the user from changing only specific settings, and a way to allow changing only specific settings under readonly = 1 restrictions. For details see constraints on settings.

allow_ddl

Allows or denies DDL queries on databases, tables, views, dictionaries, user-defined functions, workloads, resources and SQL-defined handlers.

Possible values:

• 0 — Executing a query on a persistent object that needs any of the following privileges is blocked: CREATE DATABASE, DROP DATABASE, CREATE TABLE, CREATE VIEW, ALTER TABLE, ALTER VIEW, DROP TABLE, DROP VIEW, TRUNCATE, CREATE DICTIONARY, DROP DICTIONARY, CREATE FUNCTION, DROP FUNCTION, CREATE WORKLOAD, DROP WORKLOAD, CREATE RESOURCE, DROP RESOURCE, CREATE HANDLER, ALTER HANDLER, DROP HANDLER. RENAME, EXCHANGE, ATTACH and DETACH need these privileges too, so they are blocked as well, except that ALTER TABLE ... ATTACH PARTITION and ATTACH PART need only INSERT and are not blocked by this setting, although readonly blocks them on a persistent table. ATTACH PARTITION ... FROM needs ALTER DELETE as well and is blocked. Granting and revoking these privileges is not blocked.
• 1 — Nothing is blocked by this setting.

Default value: 1

You cannot run SET allow_ddl = 1 if allow_ddl = 0 for the current session.

allow_ddl does not restrict access management queries: GRANT, REVOKE and CREATE, ALTER and DROP of users, roles, row policies, masking policies, quotas and settings profiles are unaffected by it. CREATE TEMPORARY TABLE and named collection management are unaffected too, as are ALTER DATABASE ... MODIFY SETTING, ALTER DATABASE ... MODIFY COMMENT and UNDROP TABLE, which readonly does not restrict either. Inside a CREATE or ALTER of a user, a role or a settings profile, a SETTINGS allow_ddl = 1 clause is refused while the current session has allow_ddl = 0, and a SETTINGS allow_ddl = 0 clause is accepted. An embedded setting is checked against the session’s own settings constraints, which is also why SET allow_ddl = 1 is refused.

KILL QUERY

Killing your own queries does not require the KILL QUERY privilege, so it works with any combination of readonly and allow_ddl. It does require SELECT on system.processes. Killing a query that belongs to another user, and any KILL QUERY ... ON CLUSTER, require the KILL QUERY privilege, which readonly = 1 and readonly = 2 refuse.

Other relevant settings

• allow_introspection_functions is the third setting that takes part in the permission decision itself, together with readonly and allow_ddl. When it is disabled, executing an introspection function is blocked. Granting the INTROSPECTION privilege is not blocked.
• allow_non_metadata_alters is not a permission setting, but it restricts ALTER TABLE further: when it is disabled, a command that alters the table definition is refused on MergeTree-family tables if applying it would rewrite data on disk (DROP COLUMN, RENAME COLUMN, a MODIFY COLUMN type change, MODIFY TTL). CLEAR COLUMN, CLEAR INDEX and CLEAR PROJECTION are refused too, although they leave the definition unchanged. Statements that are themselves mutations, such as ALTER TABLE ... DELETE, ALTER TABLE ... UPDATE and ALTER TABLE ... MATERIALIZE INDEX, are not affected.