Clickhouse: How to forbid user from querying a table such as a Kafka engine table?

[winter7]

As far as I know, each message can be read only once in kafka engine and manually querying a kafka engine table would cause messages not to be consumed by materialized_view.
I'm wondering how to forbid user from querying kafka engine table so that data accuracy will not be affected by misoperation.

[den-crane]

REVOKE SELECT ON mykafkatable FROM john;

https://clickhouse.tech/docs/en/operations/access-rights/#access-control
https://clickhouse.tech/docs/en/sql-reference/statements/revoke/
https://clickhouse.tech/docs/en/sql-reference/statements/grant/

[filimonov]

As far as I know, each message can be read only once in kafka engine and manually querying a kafka engine table would cause messages not to be consumed by materialized_view.
I'm wondering how to forbid user from querying kafka engine table so that data accuracy will not be affected by misoperation.

Maybe it's worth having that as a flag in Kafka table settings too? Smth like 'kafka_allow_selecting_data'?

Normally it's not the best idea to do selects from Kafka table (it should be consumed by MV), those select are mostly for debug (well it can theoreticlly be used in ETL-like processing, but AFAIK nobody do that this way)

[alexey-milovidov]

Maybe it's worth having that as a flag in Kafka table settings too? Smth like 'kafka_allow_selecting_data'?

Yes, it's reasonable, let's implement it.

[winter7]

Could you tell me how to create a user with grant option?

[den-crane]

Could you tell me how to create a user with grant option?

with grant option

https://clickhouse.tech/docs/en/sql-reference/statements/grant/#grant-privigele-syntax

grant all on *.* to bird with grant option

[winter7]

grant all on *.* to bird with grant option

I don't have any user with enough privileges to execute this query. I wonder how to create the first user with grant option.

Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: default: Not enough privileges. To execute this query it's necessary to have the grant ALL ON *.* WITH GRANT OPTION.

[den-crane]

@winter7

<?xml version="1.0" ?>
<yandex>
    <users>
        <default>
     <access_management>1</access_management>
        </default>
    </users>
</yandex>

https://clickhouse.tech/docs/en/operations/access-rights/#enabling-access-control

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 value of the access_management setting to 1.

[winter7]

When I tried to execute REVOKE SELECT ON mytable FROM john, I got

Code: 495. DB::Exception: Received from localhost:9000. DB::Exception: Cannot update User `john` in users.xml because this storage is readonly. (version 20.4.4.18 (official build))

even when users.xml file was in 666 mod.
Other users created by SQL in client can be granted/revoked properly. But I have this john user that has already been configured in users.xml instead of being newly created by SQL in client.

Since it has something to do with users.xml, what exactly should be configured in users.xml if I want to revoke a user's privilege? Maybe I can manually modify it.

[filimonov]

When I tried to execute REVOKE SELECT ON mytable FROM john, I got

Code: 495. DB::Exception: Received from localhost:9000. DB::Exception: Cannot update User `john` in users.xml because this storage is readonly. (version 20.4.4.18 (official build))

even when users.xml file was in 666 mod.
Other users created by SQL in client can be granted/revoked properly. But I have this john user that has already been configured in users.xml instead of being newly created by SQL in client.

Since it has something to do with users.xml, what exactly should be configured in users.xml if I want to revoke a user's privilege? Maybe I can manually modify it.

You need to create a user with CREATE USER statement to be able to revoke rights.

[winter7]

You need to create a user with CREATE USER statement to be able to revoke rights.

Can I just modify users.xml to revoke rights?