Creating users and roles in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/sre/user-management/index.md)

ClickHouse supports access control management based on [RBAC](https://en.wikipedia.org/wiki/Role-based_access_control) approach.

ClickHouse access entities:

- [User account](#user-account-management)
- [Role](#role-management)
- [Row Policy](#row-policy-management)
- [Settings Profile](#settings-profiles-management)
- [Quota](#quotas-management)

You can configure access entities using:

- SQL-driven workflow.

  You need to [enable](#enabling-access-control) this functionality.
- Server [configuration files](/docs/operations/configuration-files) `users.xml` and `config.xml`.

We recommend using SQL-driven workflow. Both of the configuration methods work simultaneously, so if you use the server configuration files for managing accounts and access rights, you can smoothly switch to SQL-driven workflow.

Note

You can't manage the same access entity by both configuration methods simultaneously.

Note

If you're looking to manage ClickHouse Cloud console users, please refer to this [page](/docs/cloud/security/manage-cloud-users)

To see all users, roles, profiles, etc. and all their grants use [`SHOW ACCESS`](/docs/sql-reference/statements/show#show-access) statement.

## Overview[​](#access-control-usage "Direct link to Overview")

By default, the ClickHouse server provides the `default` user account which isn't allowed using SQL-driven access control and account management but has all the rights and permissions. The `default` user account is used in any cases when the username isn't defined, for example, at login from client or in distributed queries. In distributed query processing a default user account is used, if the configuration of the server or cluster doesn't specify the [user and password](/docs/engines/table-engines/special/distributed) properties.

If you just started using ClickHouse, consider the following scenario:

1. [Enable](#enabling-access-control) SQL-driven access control and account management for the `default` user.
2. Log in to the `default` user account and create all the required users. Don't forget to create an administrator account (`GRANT ALL ON *.* TO admin_user_account WITH GRANT OPTION`).
3. [Restrict permissions](/docs/operations/settings/permissions-for-queries) for the `default` user and disable SQL-driven access control and account management for it.

### Properties of current solution[​](#access-control-properties "Direct link to Properties of current solution")

- You can grant permissions for databases and tables even if they don't exist.
- If a table is deleted, all the privileges that correspond to this table aren't revoked. This means that even if you create a new table with the same name later, all the privileges remain valid. To revoke privileges corresponding to the deleted table, you need to execute, for example, the `REVOKE ALL PRIVILEGES ON db.table FROM ALL` query.
- There are no lifetime settings for privileges.

### User account[​](#user-account-management "Direct link to User account")

A user account is an access entity that allows to authorize someone in ClickHouse. A user account contains:

- Identification information.
- [Privileges](/docs/sql-reference/statements/grant#privileges) that define the scope of queries the user can execute.
- Hosts are allowed to connect to the ClickHouse server.
- Assigned and default roles.
- Settings with their constraints applied by default at user login.
- Assigned settings profiles.

Privileges can be granted to a user account by the [GRANT](/docs/sql-reference/statements/grant) query or by assigning [roles](#role-management). To revoke privileges from a user, ClickHouse provides the [REVOKE](/docs/sql-reference/statements/revoke) query. To list privileges for a user, use the [SHOW GRANTS](/docs/sql-reference/statements/show#show-grants) statement.

Management queries:

- [CREATE USER](/docs/sql-reference/statements/create/user)
- [ALTER USER](/docs/sql-reference/statements/alter/user)
- [DROP USER](/docs/sql-reference/statements/drop)
- [SHOW CREATE USER](/docs/sql-reference/statements/show#show-create-user)
- [SHOW USERS](/docs/sql-reference/statements/show#show-users)

### Settings applying[​](#access-control-settings-applying "Direct link to Settings applying")

Settings can be configured differently: for a user account, in its granted roles and in settings profiles. At user login, if a setting is configured for different access entities, the value and constraints of this setting are applied as follows (from higher to lower priority):

1. User account settings.
2. The settings for the default roles of the user account. If a setting is configured in some roles, then order of the setting application is undefined.
3. The settings from settings profiles assigned to a user or to its default roles. If a setting is configured in some profiles, then order of setting application is undefined.
4. Settings applied to the entire server by default or from the [default profile](/docs/operations/server-configuration-parameters/settings#default_profile).

### Role[​](#role-management "Direct link to Role")

A role is a container for access entities that can be granted to a user account.

A role contains:

- [Privileges](/docs/sql-reference/statements/grant#privileges)
- Settings and constraints
- List of assigned roles

Management queries:

- [CREATE ROLE](/docs/sql-reference/statements/create/role)
- [ALTER ROLE](/docs/sql-reference/statements/alter/role)
- [DROP ROLE](/docs/sql-reference/statements/drop#drop-role)
- [SET ROLE](/docs/sql-reference/statements/set-role)
- [SET DEFAULT ROLE](/docs/sql-reference/statements/set-role)
- [SHOW CREATE ROLE](/docs/sql-reference/statements/show#show-create-role)
- [SHOW ROLES](/docs/sql-reference/statements/show#show-roles)

Privileges can be granted to a role by the [GRANT](/docs/sql-reference/statements/grant) query. To revoke privileges from a role ClickHouse provides the [REVOKE](/docs/sql-reference/statements/revoke) query.

#### Row policy[​](#row-policy-management "Direct link to Row policy")

Row policy is a filter that defines which of the rows are available to a user or a role. Row policy contains filters for one particular table, as well as a list of roles and/or users which should use this row policy.

Note

Row policies makes sense only if you have readonly access. If you can modify table or copy partitions between tables, it defeats the restrictions of row policies.

Management queries:

- [CREATE ROW POLICY](/docs/sql-reference/statements/create/row-policy)
- [ALTER ROW POLICY](/docs/sql-reference/statements/alter/row-policy)
- [DROP ROW POLICY](/docs/sql-reference/statements/drop#drop-row-policy)
- [SHOW CREATE ROW POLICY](/docs/sql-reference/statements/show#show-create-row-policy)
- [SHOW POLICIES](/docs/sql-reference/statements/show#show-policies)

### Settings profile[​](#settings-profiles-management "Direct link to Settings profile")

Settings profile is a collection of [settings](/docs/operations/settings). Settings profile contains settings and constraints, as well as a list of roles and/or users to which this profile is applied.

Management queries:

- [CREATE SETTINGS PROFILE](/docs/sql-reference/statements/create/settings-profile)
- [ALTER SETTINGS PROFILE](/docs/sql-reference/statements/alter/settings-profile)
- [DROP SETTINGS PROFILE](/docs/sql-reference/statements/drop#drop-settings-profile)
- [SHOW CREATE SETTINGS PROFILE](/docs/sql-reference/statements/show#show-create-settings-profile)
- [SHOW PROFILES](/docs/sql-reference/statements/show#show-profiles)

### Quota[​](#quotas-management "Direct link to Quota")

Quota limits resource usage. See [Quotas](/docs/operations/quotas).

Quota contains a set of limits for some durations, as well as a list of roles and/or users which should use this quota.

Management queries: