> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Settings for query permissions.

# 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`, [`DELETE`](/reference/statements/delete), [`UPDATE`](/reference/statements/update), `ALTER TABLE ... DELETE`, `ALTER TABLE ... UPDATE`.
3. Change settings query: `SET`, `USE`.
4. [DDL](https://en.wikipedia.org/wiki/Data_definition_language) queries: `CREATE`, `ALTER`, `RENAME`, `EXCHANGE`, `ATTACH`, `DETACH`, `DROP`, `TRUNCATE`.
5. Access management queries: [`GRANT`](/reference/statements/grant), [`REVOKE`](/reference/statements/revoke), and `CREATE`, `ALTER` and `DROP` of users, roles, row policies, masking policies, quotas and settings profiles. See [access control and account management](/concepts/features/security/access-rights).
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:

<h2 id="readonly">
  readonly
</h2>

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](/reference/settings/session-settings/other#readonly); 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.

<Note>
  Over the [HTTP interface](/concepts/features/interfaces/http), 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](/reference/statements/create/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](/concepts/features/configuration/settings/constraints-on-settings), which also
  recommends against making the `readonly` setting itself
  [changeable in read-only mode](/concepts/features/configuration/settings/constraints-on-settings#readonly-changeable-in-readonly).
</Note>

<h2 id="allow_ddl">
  allow\_ddl
</h2>

Allows or denies [DDL](https://en.wikipedia.org/wiki/Data_definition_language) 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

<Note>
  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.
</Note>

<Info>
  **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`, except for
  `KILL QUERY WHERE query_id = '<id>'`, which cancels your own query with that id without reading that
  table. 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.
</Info>

<h2 id="other-relevant-settings">
  Other relevant settings
</h2>

* [`allow_introspection_functions`](/reference/settings/session-settings/allow#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`](/reference/settings/session-settings/allow#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.