Skip to main content

Permissions for Queries

Queries in ClickHouse can be divided into several types:

  1. Read data queries: SELECT, SHOW, DESCRIBE, EXISTS.
  2. Write data queries: INSERT, OPTIMIZE.
  3. Change settings query: SET, USE.
  4. DDL queries: CREATE, ALTER, RENAME, ATTACH, DETACH, DROP TRUNCATE.
  5. KILL QUERY.

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

readonly​

Restricts permissions for read data, write data, and change settings queries.

When set to 1, allows:

  • All types of read queries (like SELECT and equivalent queries).
  • Queries that modify only session context (like USE).

When set to 2, allows the above plus:

  • SET and CREATE TEMPORARY TABLE

    Tip

    Queries like EXISTS, DESCRIBE, EXPLAIN, SHOW PROCESSLIST, etc are equivalent to SELECT, because they just do select from system tables.

Possible values:

  • 0 — Read, Write, and Change settings queries are allowed.
  • 1 — Only Read data queries are allowed.
  • 2 — Read data and Change settings queries are allowed.

Default value: 0

Note

After setting readonly = 1, the user can’t change readonly and allow_ddl settings in the current session.

When using the GET method in the HTTP interface, readonly = 1 is set automatically. To modify data, use the POST method.

Setting readonly = 1 prohibits the user from changing settings. There is a way to prohibit the user from changing only specific settings. Also there is 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.

Possible values:

  • 0 — DDL queries are not allowed.
  • 1 — DDL queries are allowed.

Default value: 1

Note

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

KILL QUERY

KILL QUERY can be performed with any combination of readonly and allow_ddl settings.