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 all types of queries except DDL queries.
  • allow_ddl β€” Restricts permissions for DDL queries.

KILL QUERY can be performed with any settings.

readonly​

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

See how the queries are divided into types above.

Possible values:

  • 0 β€” All queries are allowed.
  • 1 β€” Only read data queries are allowed.
  • 2 β€” Read data and change settings queries are allowed.

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 prohibit the user from changing all the settings. There is a way to prohibit the user from changing only specific settings, for details see constraints on settings.

Default value: 0

allow_ddl​

Allows or denies DDL queries.

See how the queries are divided into types above.

Possible values:

  • 0 β€” DDL queries are not allowed.
  • 1 β€” DDL queries are allowed.

You can’t execute SET allow_ddl = 1 if allow_ddl = 0 for the current session.

Default value: 1

Original article