Creating users and roles in ClickHouse

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

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

```
SHOW GRANTS FOR my_user;
```

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

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

```
REVOKE ALTER COLUMN ON my_db.my_table FROM my_user;
```

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

```
SHOW GRANTS FOR my_user;
```

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

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

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

2. Add the add column privilege to the user

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

3. Log in with the restricted user

```
clickhouse-client --user my_user --password password --port 9000 --host <your_clickhouse_host>
```

4. Test adding a column

```
ALTER TABLE my_db.my_table ADD COLUMN column2 String;
```

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

```
DESCRIBE my_db.my_table;
```

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

```
ALTER TABLE my_db.my_table DROP COLUMN column2;
```

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

```
GRANT SELECT, ALTER COLUMN ON my_db.my_table TO my_alter_admin WITH GRANT OPTION;
```

6. Log in with the alter admin user

```
clickhouse-client --user my_alter_admin --password password --port 9000 --host <my_clickhouse_host>
```

7. Grant a sub-privilege

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

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

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

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

- [Overview](#access-control-usage)
  - [Properties of current solution](#access-control-properties)- [User account](#user-account-management)- [Settings applying](#access-control-settings-applying)- [Role](#role-management)- [Settings profile](#settings-profiles-management)- [Quota](#quotas-management)- [Enabling SQL-driven access control and account management](#enabling-access-control)- [Defining SQL users and roles](#defining-sql-users-and-roles)
    - [Enabling SQL user mode](#enabling-sql-user-mode)- [Defining users](#defining-users)- [Alter permissions](#alter-permissions)

Was this page helpful?