Creating users and roles in ClickHouse

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:

- [CREATE QUOTA](/docs/sql-reference/statements/create/quota)
- [ALTER QUOTA](/docs/sql-reference/statements/alter/quota)
- [DROP QUOTA](/docs/sql-reference/statements/drop#drop-quota)
- [SHOW CREATE QUOTA](/docs/sql-reference/statements/show#show-create-quota)
- [SHOW QUOTA](/docs/sql-reference/statements/show#show-quota)
- [SHOW QUOTAS](/docs/sql-reference/statements/show#show-quotas)

### Enabling SQL-driven access control and account management[​](#enabling-access-control "Direct link to 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](/docs/operations/server-configuration-parameters/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`](/docs/operations/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[​](#defining-sql-users-and-roles "Direct link to Defining SQL users and roles")

Tip

If you're working in ClickHouse Cloud, please see [Cloud access management](/docs/cloud/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[​](#enabling-sql-user-mode "Direct link to Enabling SQL user mode")

1. Enable SQL user mode in the `users.xml` file under the `<default>` user:

   ```
   <access_management>1</access_management>
   <named_collection_control>1</named_collection_control>
   <show_named_collections>1</show_named_collections>
   <show_named_collections_secrets>1</show_named_collections_secrets>
   ```

   Note

   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 `<secret>`, 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:

   ```
   clickhouse-client --user default --password <password>
   ```

### Defining users[​](#defining-users "Direct link to Defining users")

1. Create a SQL administrator account:

   ```
   CREATE USER clickhouse_admin IDENTIFIED BY 'password';
   ```
2. Grant the new user full administrative rights

   ```
   GRANT ALL ON *.* TO clickhouse_admin WITH GRANT OPTION;
   ```

## Alter permissions[​](#alter-permissions "Direct link to 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

```
CREATE USER my_user IDENTIFIED BY 'password';
```

2. Create sample database

```
CREATE DATABASE my_db;
```

3. Create a sample table

```
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

```
CREATE USER my_alter_admin IDENTIFIED BY 'password';
```

Note

To grant or revoke permissions, the admin user must have the `WITH GRANT OPTION` privilege.
For example:

```
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:

```
├── 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:

```
GRANT ALTER ON my_db.my_table TO my_user;
```

Resulting set of privileges:

```
SHOW GRANTS FOR  my_user;
```

```
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:

```
GRANT ALTER COLUMN ON my_db.my_table TO my_user;
```

Grants would be set as:

```
SHOW GRANTS FOR my_user;
```

```
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:

```
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`

```
GRANT ALTER ADD COLUMN ON my_db.my_table TO my_user;
```