> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Access control and account management > Describes access control and account management in ClickHouse Cloud 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](/concepts/features/configuration/server-config/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. You can't manage the same access entity by both configuration methods simultaneously. If you're looking to manage ClickHouse Cloud console users, please refer to this [page](/products/cloud/guides/security/cloud-access-management/manage-cloud-users) To see all users, roles, profiles, etc. and all their grants use [`SHOW ACCESS`](/reference/statements/show#show-access) statement.

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](/reference/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](/concepts/features/configuration/settings/permissions-for-queries) for the `default` user and disable SQL-driven access control and account management for it.

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

A user account is an access entity that allows to authorize someone in ClickHouse. A user account contains: * Identification information. * [Privileges](/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](/reference/statements/grant) query or by assigning [roles](#role-management). To revoke privileges from a user, ClickHouse provides the [REVOKE](/reference/statements/revoke) query. To list privileges for a user, use the [SHOW GRANTS](/reference/statements/show#show-grants) statement. Management queries: * [CREATE USER](/reference/statements/create/user) * [ALTER USER](/reference/statements/alter/user) * [DROP USER](/reference/statements/drop) * [SHOW CREATE USER](/reference/statements/show#show-create-user) * [SHOW USERS](/reference/statements/show#show-users)

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](/reference/settings/server-settings/settings#default_profile).

Role

A role is a container for access entities that can be granted to a user account. A role contains: * [Privileges](/reference/statements/grant#privileges) * Settings and constraints * List of assigned roles Management queries: * [CREATE ROLE](/reference/statements/create/role) * [ALTER ROLE](/reference/statements/alter/role) * [DROP ROLE](/reference/statements/drop#drop-role) * [SET ROLE](/reference/statements/set-role) * [SET DEFAULT ROLE](/reference/statements/set-role) * [SHOW CREATE ROLE](/reference/statements/show#show-create-role) * [SHOW ROLES](/reference/statements/show#show-roles) Privileges can be granted to a role by the [GRANT](/reference/statements/grant) query. To revoke privileges from a role ClickHouse provides the [REVOKE](/reference/statements/revoke) query.

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. 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](/reference/statements/create/row-policy) * [ALTER ROW POLICY](/reference/statements/alter/row-policy) * [DROP ROW POLICY](/reference/statements/drop#drop-row-policy) * [SHOW CREATE ROW POLICY](/reference/statements/show#show-create-row-policy) * [SHOW POLICIES](/reference/statements/show#show-policies)

Settings profile

Settings profile is a collection of [settings](/reference/settings/index). 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](/reference/statements/create/settings-profile) * [ALTER SETTINGS PROFILE](/reference/statements/alter/settings-profile) * [DROP SETTINGS PROFILE](/reference/statements/drop#drop-settings-profile) * [SHOW CREATE SETTINGS PROFILE](/reference/statements/show#show-create-settings-profile) * [SHOW PROFILES](/reference/statements/show#show-profiles)

Quota

Quota limits resource usage. See [Quotas](/concepts/features/configuration/server-config/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: * [CREATE QUOTA](/reference/statements/create/quota) * [ALTER QUOTA](/reference/statements/alter/quota) * [DROP QUOTA](/reference/statements/drop#drop-quota) * [SHOW CREATE QUOTA](/reference/statements/show#show-create-quota) * [SHOW QUOTA](/reference/statements/show#show-quota) * [SHOW QUOTAS](/reference/statements/show#show-quotas)

Enabling SQL-driven access control and account management

* Setup a directory for configuration storage. ClickHouse stores access entity configurations in the folder set in the [access\_control\_path](/reference/settings/server-settings/settings#access_control_path) server configuration parameter. * Enable SQL-driven access control and account management for at least one user account. By default, SQL-driven access control and account management is disabled for all users. You need to configure at least one user in the `users.xml` configuration file and set the values of the [`access_management`](/concepts/features/configuration/settings/settings-users#access_management-user-setting), `named_collection_control`, `show_named_collections`, and `show_named_collections_secrets` settings to 1.

Defining SQL users and roles

If you're working in ClickHouse Cloud, please see [Cloud access management](/products/cloud/reference/security/console-roles). This article shows the basics of defining SQL users and roles and applying those privileges and permissions to databases, tables, rows, and columns.

Enabling SQL user mode

1. Enable SQL user mode in the `users.xml` file under the `` user: ```xml theme={null} 1 1 1 1 ``` The `default` user is the only user that gets created with a fresh install, and is also the account used for internode communications, by default. In production, it is recommended to disable this user once the inter-node communication has been configured with a SQL admin user and internode communications have been set with ``, cluster credentials, and/or internode HTTP and transport protocol credentials since the `default` account is used for internode communication. 2. Restart the nodes to apply the changes. 3. Start the ClickHouse client: ```sql theme={null} clickhouse-client --user default --password ```

Defining users

1. Create a SQL administrator account: ```sql theme={null} CREATE USER clickhouse_admin IDENTIFIED BY 'password'; ``` 2. Grant the new user full administrative rights ```sql theme={null} GRANT ALL ON *.* TO clickhouse_admin WITH GRANT OPTION; ```

Alter permissions

This article is intended to provide you with a better understanding of how to define permissions, and how permissions work when using `ALTER` statements for privileged users. The `ALTER` statements are divided into several categories, some of which are hierarchical and some of which aren't and must be explicitly defined. **Example DB, table and user configuration** 1. With an admin user, create a sample user ```sql theme={null} CREATE USER my_user IDENTIFIED BY 'password'; ``` 2. Create sample database ```sql theme={null} CREATE DATABASE my_db; ``` 3. Create a sample table ```sql theme={null} CREATE TABLE my_db.my_table (id UInt64, column1 String) ENGINE = MergeTree() ORDER BY id; ``` 4. Create a sample admin user to grant/revoke privileges ```sql theme={null} CREATE USER my_alter_admin IDENTIFIED BY 'password'; ``` To grant or revoke permissions, the admin user must have the `WITH GRANT OPTION` privilege. For example: ```sql theme={null} GRANT ALTER ON my_db.* WITH GRANT OPTION ``` To `GRANT` or `REVOKE` privileges, the user must have those privileges themselves first. **Granting or Revoking Privileges** The `ALTER` hierarchy: ```response theme={null} ├── ALTER (only for table and view)/ │ ├── ALTER TABLE/ │ │ ├── ALTER UPDATE │ │ ├── ALTER DELETE │ │ ├── ALTER COLUMN/ │ │ │ ├── ALTER ADD COLUMN │ │ │ ├── ALTER DROP COLUMN │ │ │ ├── ALTER MODIFY COLUMN │ │ │ ├── ALTER COMMENT COLUMN │ │ │ ├── ALTER CLEAR COLUMN │ │ │ └── ALTER RENAME COLUMN │ │ ├── ALTER INDEX/ │ │ │ ├── ALTER ORDER BY │ │ │ ├── ALTER SAMPLE BY │ │ │ ├── ALTER ADD INDEX │ │ │ ├── ALTER DROP INDEX │ │ │ ├── ALTER MATERIALIZE INDEX │ │ │ └── ALTER CLEAR INDEX │ │ ├── ALTER CONSTRAINT/ │ │ │ ├── ALTER ADD CONSTRAINT │ │ │ └── ALTER DROP CONSTRAINT │ │ ├── ALTER TTL/ │ │ │ └── ALTER MATERIALIZE TTL │ │ ├── ALTER SETTINGS │ │ ├── ALTER MOVE PARTITION │ │ ├── ALTER FETCH PARTITION │ │ └── ALTER FREEZE PARTITION │ └── ALTER LIVE VIEW/ │ ├── ALTER LIVE VIEW REFRESH │ └── ALTER LIVE VIEW MODIFY QUERY ├── ALTER DATABASE ├── ALTER USER ├── ALTER ROLE ├── ALTER QUOTA ├── ALTER [ROW] POLICY └── ALTER [SETTINGS] PROFILE ``` 1. Granting `ALTER` Privileges to a User or Role Using an `GRANT ALTER on *.* TO my_user` will only affect top-level `ALTER TABLE` and `ALTER VIEW` , other `ALTER` statements must be individually granted or revoked. for example, granting basic `ALTER` privilege: ```sql theme={null} GRANT ALTER ON my_db.my_table TO my_user; ``` Resulting set of privileges: ```sql theme={null} SHOW GRANTS FOR my_user; ``` ```response theme={null} SHOW GRANTS FOR my_user Query id: 706befbc-525e-4ec1-a1a2-ba2508cc09e3 ┌─GRANTS FOR my_user───────────────────────────────────────────┐ │ GRANT ALTER TABLE, ALTER VIEW ON my_db.my_table TO my_user │ └──────────────────────────────────────────────────────────────┘ ``` This will grant all permissions under `ALTER TABLE` and `ALTER VIEW` from the example above, however, it won't grant certain other `ALTER` permissions such as `ALTER ROW POLICY` (Refer back to the hierarchy and you will see that `ALTER ROW POLICY` isn't a child of `ALTER TABLE` or `ALTER VIEW`). Those must be explicitly granted or revoked. If only a subset of `ALTER` permissions is needed then each can be granted separately, if there are sub-privileges to that permission then those would be automatically granted also. For example: ```sql theme={null} GRANT ALTER COLUMN ON my_db.my_table TO my_user; ``` Grants would be set as: ```sql theme={null} SHOW GRANTS FOR my_user; ``` ```response theme={null} SHOW GRANTS FOR my_user Query id: 47b3d03f-46ac-4385-91ec-41119010e4e2 ┌─GRANTS FOR my_user────────────────────────────────┐ │ GRANT ALTER COLUMN ON default.my_table TO my_user │ └───────────────────────────────────────────────────┘ 1 row in set. Elapsed: 0.004 sec. ``` This also gives the following sub-privileges: ```sql theme={null} ALTER ADD COLUMN ALTER DROP COLUMN ALTER MODIFY COLUMN ALTER COMMENT COLUMN ALTER CLEAR COLUMN ALTER RENAME COLUMN ``` 2. Revoking `ALTER` privileges from Users and Roles The `REVOKE` statement works similarly to the `GRANT` statement. If a user/role was granted a sub-privilege, you can either revoke that sub-privilege directly or revoke the higher-level privilege it inherits from. For example, if the user was granted `ALTER ADD COLUMN` ```sql theme={null} GRANT ALTER ADD COLUMN ON my_db.my_table TO my_user; ``` ```response theme={null} GRANT ALTER ADD COLUMN ON my_db.my_table TO my_user Query id: 61fe0fdc-1442-4cd6-b2f3-e8f2a853c739 Ok. 0 rows in set. Elapsed: 0.002 sec. ``` ```sql theme={null} SHOW GRANTS FOR my_user; ``` ```response theme={null} SHOW GRANTS FOR my_user Query id: 27791226-a18f-46c8-b2b4-a9e64baeb683 ┌─GRANTS FOR my_user──────────────────────────────────┐ │ GRANT ALTER ADD COLUMN ON my_db.my_table TO my_user │ └─────────────────────────────────────────────────────┘ ``` A privilege can be revoked individually: ```sql theme={null} REVOKE ALTER ADD COLUMN ON my_db.my_table FROM my_user; ``` Or can be revoked from any of the upper levels (revoke all of the COLUMN sub privileges): ```response theme={null} REVOKE ALTER COLUMN ON my_db.my_table FROM my_user; ``` ```response theme={null} REVOKE ALTER COLUMN ON my_db.my_table FROM my_user Query id: b882ba1b-90fb-45b9-b10f-3cda251e2ccc Ok. 0 rows in set. Elapsed: 0.002 sec. ``` ```sql theme={null} SHOW GRANTS FOR my_user; ``` ```response theme={null} SHOW GRANTS FOR my_user Query id: e7d341de-de65-490b-852c-fa8bb8991174 Ok. 0 rows in set. Elapsed: 0.003 sec. ``` **Additional** The privileges must be granted by a user that not only has the `WITH GRANT OPTION` but also has the privileges themselves. 1. To grant an admin user the privilege and also allow them to administer a set of privileges Below is an example: ```sql theme={null} GRANT SELECT, ALTER COLUMN ON my_db.my_table TO my_alter_admin WITH GRANT OPTION; ``` Now the user can grant or revoke `ALTER COLUMN` and all sub-privileges. **Testing** 1. Add the `SELECT` privilege ```sql theme={null} GRANT SELECT ON my_db.my_table TO my_user; ``` 2. Add the add column privilege to the user ```sql theme={null} GRANT ADD COLUMN ON my_db.my_table TO my_user; ``` 3. Log in with the restricted user ```bash theme={null} clickhouse-client --user my_user --password password --port 9000 --host ``` 4. Test adding a column ```sql theme={null} ALTER TABLE my_db.my_table ADD COLUMN column2 String; ``` ```response theme={null} ALTER TABLE my_db.my_table ADD COLUMN `column2` String Query id: d5d6bfa1-b80c-4d9f-8dcd-d13e7bd401a5 Ok. 0 rows in set. Elapsed: 0.010 sec. ``` ```sql theme={null} DESCRIBE my_db.my_table; ``` ```response theme={null} DESCRIBE TABLE my_db.my_table Query id: ab9cb2d0-5b1a-42e1-bc9c-c7ff351cb272 ┌─name────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ id │ UInt64 │ │ │ │ │ │ │ column1 │ String │ │ │ │ │ │ │ column2 │ String │ │ │ │ │ │ └─────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ ``` 4. Test deleting a column ```sql theme={null} ALTER TABLE my_db.my_table DROP COLUMN column2; ``` ```response theme={null} ALTER TABLE my_db.my_table DROP COLUMN column2 Query id: 50ad5f6b-f64b-4c96-8f5f-ace87cea6c47 0 rows in set. Elapsed: 0.004 sec. Received exception from server (version 22.5.1): Code: 497. DB::Exception: Received from chnode1.marsnet.local:9440. DB::Exception: my_user: Not enough privileges. To execute this query it's necessary to have grant ALTER DROP COLUMN(column2) ON my_db.my_table. (ACCESS_DENIED) ``` 5. Testing the alter admin by granting the permission ```sql theme={null} GRANT SELECT, ALTER COLUMN ON my_db.my_table TO my_alter_admin WITH GRANT OPTION; ``` 6. Log in with the alter admin user ```bash theme={null} clickhouse-client --user my_alter_admin --password password --port 9000 --host ``` 7. Grant a sub-privilege ```sql theme={null} GRANT ALTER ADD COLUMN ON my_db.my_table TO my_user; ``` ```response theme={null} GRANT ALTER ADD COLUMN ON my_db.my_table TO my_user Query id: 1c7622fa-9df1-4c54-9fc3-f984c716aeba Ok. ``` 8. Test granting a privilege that the alter admin user doesn't have isn't a sub privilege of the grants for the admin user. ```sql theme={null} GRANT ALTER UPDATE ON my_db.my_table TO my_user; ``` ```response theme={null} GRANT ALTER UPDATE ON my_db.my_table TO my_user Query id: 191690dc-55a6-4625-8fee-abc3d14a5545 0 rows in set. Elapsed: 0.004 sec. Received exception from server (version 22.5.1): Code: 497. DB::Exception: Received from chnode1.marsnet.local:9440. DB::Exception: my_alter_admin: Not enough privileges. To execute this query it's necessary to have grant ALTER UPDATE ON my_db.my_table WITH GRANT OPTION. (ACCESS_DENIED) ``` **Summary** The ALTER privileges are hierarchical for `ALTER` with tables and views but not for other `ALTER` statements. The permissions can be set in granular level or by grouping of permissions and also revoked similarly. The user granting or revoking must have `WITH GRANT OPTION` to set privileges on users, including the acting user themselves, and must have the privilege already. The acting user can't revoke their own privileges if they don't have the grant option privilege themselves.