# Manage Managed ClickHouse® users and roles


Create Managed ClickHouse® users and roles and grant them specific privileges to efficiently control or restrict access to your service.

## Manage users

### Add a user

Create a user account for your service in one of two ways.

To create a user using a hash and salt, run:

```sql
CREATE USER username IDENTIFIED WITH sha256_hash BY 'hash' SALT 'salt';
```

To create a user using a password directly, run:

```sql
CREATE USER username IDENTIFIED BY 'password';
```

Users' password digests are stored securely in ZooKeeper, out of reach of service
users. The password digests and salts are stored in backup files so they can be
recovered.

### Configure user settings

Configure user settings to control resource usage and query behavior. You can set limits
at the user level (applying to all queries by that user) or configure per-query constraints.

#### Set user resource limits

Configure user settings, for example, to restrict resource use per user. This can help you:

- Prevent single users from monopolizing CPU threads and starving other users.
- Ensure fair distribution of system resources among all active users.
- Maintain system stability by preventing service overload.

1. Create a user named `limited_user`.

    ```sql
    CREATE USER limited_user IDENTIFIED WITH SHA256_PASSWORD BY 'PASSWORD';
    ```

1. Set resource limit `max_threads = 1` for this user.

    ```sql
    ALTER USER limited_user SETTINGS max_threads = 1;
    ```

1. Grant read-only access to the `default` database to `limited_user`.

    ```sql
    GRANT SELECT ON default.* TO limited_user;
    ```

    > [!NOTE]
    > On Managed ClickHouse®, data privileges cannot be granted globally with `ON *.*`. Grant
    > them per database instead, repeating the statement for each database the user needs.
    > Access management privileges such as `CREATE USER` are global and use `ON *.*`.


1. Log in as `limited_user` to test the configuration.

    ```bash
    clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user limited_user --password PASSWORD --secure
    ```

    Replace `PASSWORD` with the password you set above. `$CH_HOST` and `$CH_PORT` are the
    variables exported in
    [Connect to Managed ClickHouse® with clickhouse-client](/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/).

1. Verify the resource limit setting.

    ```sql
    SHOW SETTINGS LIKE 'max_threads';
    ```

    This displays the `max_threads` setting with a value of `1` for the `limited_user`.

#### Set per-query resource limits

Constrain resources on a per-query basis for specific users by setting query-level limits
in their user profile.

1. Create a user with per-query memory and execution time limits.

    ```sql
    CREATE USER query_limited_user IDENTIFIED WITH SHA256_PASSWORD BY 'PASSWORD'
    SETTINGS max_memory_usage = 1000000000, max_execution_time = 30;
    ```

1. Grant appropriate permissions.

    ```sql
    GRANT SELECT ON default.* TO query_limited_user;
    ```

1. Test the per-query limits by logging in and running a query.

    ```bash
    clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user query_limited_user --password PASSWORD --secure
    ```

1. Verify the query-level settings.

    ```sql
    SHOW SETTINGS LIKE '%max_%';
    ```

**Common per-query settings:**

- `max_memory_usage`: Maximum memory per query (in bytes)
- `max_execution_time`: Maximum query execution time (in seconds)
- `max_rows_to_read`: Maximum rows a query can examine
- `max_result_rows`: Maximum rows in query result

## Manage roles and privileges

Apart from the platform roles that carry the privileges of the main service user
(`aiven_admin_role` and `aiven_readonly_role`), Managed ClickHouse has no predefined roles.
All roles you create are custom roles you design for your purposes by granting specific
privileges to particular roles.
For example: you can create a role that allows only reading a single database, table, or
column; or you can create another role that allows only inserting data, not deleting it.

ClickHouse® supports a **Role Based Access Control** model and allows
you to configure access privileges by using SQL statements. You can rely on
[the command-line interface](/product/dbaas/service-specific/clickhouse/how-to/connect-with-clickhouse-cli/).

The upstream ClickHouse documentation includes
[detailed documentation for access rights](https://clickhouse.com/docs/concepts/features/security/access-rights).

### Create a role

To create a role named **auditor**, run the following command:

```sql
CREATE ROLE auditor;
```

Find more information on creating roles in the
[upstream ClickHouse documentation](https://clickhouse.com/docs/reference/statements/create/role).

### Grant privileges

You can grant privileges both to specific roles and to individual
users. The grants can be also granular, targeting specific databases,
tables, columns, or rows.

> [!IMPORTANT]
> You cannot grant additional privileges to the main service user. Exoscale may grant privileges
> to the main service user during maintenance updates when adding new features for the service.

> [!NOTE]
> A user you create does not start with the privileges the platform grants to the main service
> user (`avnadmin`). By default, `avnadmin` can already read from the `S3` and `URL` sources, and
> it is the only user that can create a database in SQL. Grant the external-source privileges
> explicitly to your own users, for example
> `GRANT CREATE TEMPORARY TABLE, S3, URL ON *.* TO username`. See
> [Run federated queries](/product/dbaas/service-specific/clickhouse/how-to/run-federated-queries/).

As an example, the following request grants the `auditor` role privileges
to select data from the `transactions` database:

```sql
GRANT SELECT ON transactions.* TO auditor;
```

You can limit the grant to a specified table:

```sql
GRANT SELECT ON transactions.expenses TO auditor;
```

Or to particular columns of a table:

```sql
GRANT SELECT(Date, Title, Amount) ON transactions.expenses TO auditor
```

To grant several roles to several users at once, first make sure they all exist:

```sql
CREATE ROLE external;
CREATE USER mary_anderson IDENTIFIED WITH sha256_password BY 'Password1?';
CREATE USER james_miller IDENTIFIED WITH sha256_password BY 'Password2?';
```

Then run:

```sql
GRANT auditor, external TO mary_anderson, james_miller;
```

To allow a role, such as `auditor`, to create new users:

```sql
GRANT CREATE USER ON *.* TO auditor
```

There are a variety of privileges that you can grant. Find the full list in the
[upstream ClickHouse documentation](https://clickhouse.com/docs/reference/statements/grant#privileges).

> [!NOTE]
> You can grant privileges to a table that does not yet exist.

> [!NOTE]
> Users can grant privileges according to their privileges. If the user
> lacks the required privileges for a requested operation, they receive a
> `Not enough privileges` exception.

> [!WARNING]
> Privileges are not revoked when a table or database is removed. They
> continue to be active for any new table or database that is created with
> the same name.

Find all details on how the GRANT statement is supported in ClickHouse
in the
[upstream ClickHouse documentation](https://clickhouse.com/docs/reference/statements/grant).

### Set roles

A single user can be assigned different roles, either individually or
simultaneously.

```sql
SET ROLE auditor;
```

You can also specify a role to be activated by default when the user
logs in:

```sql
SET DEFAULT ROLE auditor, external TO mary_anderson, james_miller;
```

### Delete a role

If you no longer need a role, you can remove it:

```sql
DROP ROLE auditor;
```

### Revoke privileges

Remove all or specific privileges from users or roles:

```sql
REVOKE SELECT ON transactions.expenses FROM mary_anderson;
```

Revoke all privileges to a table or database simultaneously:

```sql
REVOKE ALL PRIVILEGES ON transactions.expenses FROM external;
```

See the ClickHouse documentation [for more information on revoking
privileges](https://clickhouse.com/docs/reference/statements/revoke).

### Check privileges

Run the following commands to see all available grants, users, and
roles:

```sql
SHOW GRANTS;
```

```sql
SHOW USERS;
```

```sql
SHOW ROLES;
```