Requêtes courantes de gestion des accès

AutogéréSi vous utilisez une instance ClickHouse autogérée, consultez les utilisateurs et rôles SQL.

Cet article présente les bases de la définition des utilisateurs et des rôles SQL, ainsi que de l’application de ces privilèges et autorisations aux bases de données, tables, lignes et colonnes.

Utilisateur admin

Les services ClickHouse Cloud disposent d’un utilisateur admin, default, créé lors de la création du service. Le mot de passe est fourni à ce moment-là et peut être réinitialisé par les utilisateurs de ClickHouse Cloud disposant du rôle Admin. Lorsque vous ajoutez des utilisateurs SQL supplémentaires à votre service ClickHouse Cloud, ils doivent avoir un nom d’utilisateur SQL et un mot de passe. Si vous souhaitez leur accorder des privilèges de niveau administratif, attribuez aux nouveaux utilisateurs le rôle default_role. Par exemple, pour ajouter l’utilisateur clickhouse_admin :

CREATE USER IF NOT EXISTS clickhouse_admin
IDENTIFIED WITH sha256_password BY 'P!@ssword42!';

GRANT default_role TO clickhouse_admin;

Lorsque vous utilisez la SQL Console, vos instructions SQL ne sont pas exécutées en tant qu’utilisateur default. Elles sont exécutées à la place en tant qu’utilisateur nommé sql-console:${cloud_login_email}, où cloud_login_email correspond à l’adresse e-mail de l’utilisateur qui exécute actuellement la requête.Ces utilisateurs de la SQL Console générés automatiquement disposent du rôle default.

Authentification sans mot de passe

Deux rôles sont disponibles pour la SQL Console : sql_console_admin, avec des autorisations identiques à default_role, et sql_console_read_only, avec des autorisations en lecture seule. Les utilisateurs Admin se voient attribuer par défaut le rôle sql_console_admin, donc rien ne change pour eux. En revanche, le rôle sql_console_read_only permet d’accorder à des utilisateurs non Admin un accès en lecture seule ou un accès complet à n’importe quelle instance. Un administrateur doit configurer cet accès. Les rôles peuvent être ajustés à l’aide des commandes GRANT ou REVOKE afin de mieux répondre aux exigences propres à chaque instance, et toute modification apportée à ces rôles sera conservée.

Contrôle d’accès granulaire

Cette fonctionnalité de contrôle d’accès peut également être configurée manuellement avec une granularité par utilisateur. Avant d’attribuer les nouveaux rôles sql_console_* aux utilisateurs, il convient de créer des rôles de base de données propres aux utilisateurs de la SQL Console, correspondant à l’espace de noms sql-console-role:<email>. Par exemple :

CREATE ROLE OR REPLACE sql-console-role:<email>;
GRANT <some grants> TO sql-console-role:<email>;

Lorsqu’un rôle correspondant est détecté, il est attribué à l’utilisateur à la place des rôles prédéfinis. Cela permet de mettre en place des configurations de contrôle d’accès plus complexes, par exemple en créant des rôles tels que sql_console_sa_role et sql_console_pm_role, puis en les attribuant à des utilisateurs spécifiques. Par exemple :

CREATE ROLE OR REPLACE sql_console_sa_role;
GRANT <whatever level of access> TO sql_console_sa_role;
CREATE ROLE OR REPLACE sql_console_pm_role;
GRANT <whatever level of access> TO sql_console_pm_role;
CREATE ROLE OR REPLACE `sql-console-role:christoph@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:jake@clickhouse.com`;
CREATE ROLE OR REPLACE `sql-console-role:zach@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:christoph@clickhouse.com`;
GRANT sql_console_sa_role to `sql-console-role:jake@clickhouse.com`;
GRANT sql_console_pm_role to `sql-console-role:zach@clickhouse.com`;

Tester les privilèges d’administrateur

Déconnectez-vous de la session de l’utilisateur default, puis reconnectez-vous en tant qu’utilisateur clickhouse_admin. Toutes les opérations suivantes devraient réussir :

SHOW GRANTS FOR clickhouse_admin;

CREATE DATABASE db1

CREATE TABLE db1.table1 (id UInt64, column1 String) ENGINE = MergeTree() ORDER BY id;

INSERT INTO db1.table1 (id, column1) VALUES (1, 'abc');

SELECT * FROM db1.table1;

DROP TABLE db1.table1;

DROP DATABASE db1;

Utilisateurs non administrateurs

Les utilisateurs doivent disposer des privilèges nécessaires, et tous ne doivent pas être administrateurs. La suite de ce document présente des scénarios d’exemple ainsi que les rôles requis.

Préparation

Créez ces tables et ces utilisateurs pour les exemples.

Création d’une base de données, d’une table et de lignes d’exemple

Créer une base de données de test

CREATE DATABASE db1;

Créer une table

CREATE TABLE db1.table1 (
   id UInt64,
   column1 String,
   column2 String
)
ENGINE MergeTree
ORDER BY id;

Insérer des lignes d’exemple dans la table

INSERT INTO db1.table1
   (id, column1, column2)
VALUES
   (1, 'A', 'abc'),
   (2, 'A', 'def'),
   (3, 'B', 'abc'),
   (4, 'B', 'def');

Vérifier la table

Requête

SELECT *
FROM db1.table1

Réponse

Query id: 475015cc-6f51-4b20-bda2-3c9c41404e49

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
│  3 │ B       │ abc     │
│  4 │ B       │ def     │
└────┴─────────┴─────────┘

Créer `column_user`

Créez un utilisateur standard qui servira à démontrer la restriction de l’accès à certaines colonnes :

CREATE USER column_user IDENTIFIED BY 'password';

Créer `row_user`

Créez un utilisateur standard qui servira à démontrer la restriction de l’accès aux lignes contenant certaines valeurs :

CREATE USER row_user IDENTIFIED BY 'password';

Création de rôles

Avec cet ensemble d’exemples :

des rôles avec différents privilèges, au niveau des colonnes et des lignes, seront créés
des privilèges seront accordés aux rôles
des utilisateurs seront affectés à chaque rôle

Les rôles servent à définir des groupes d’utilisateurs disposant de privilèges spécifiques, au lieu de gérer chaque utilisateur individuellement.

Créer un rôle pour limiter les utilisateurs de ce rôle à la consultation de `column1` uniquement dans la base de données `db1` et la table `table1` :

CREATE ROLE column1_users;

Définir les privilèges pour autoriser l’accès à `column1`

GRANT SELECT(id, column1) ON db1.table1 TO column1_users;

Ajouter l’utilisateur `column_user` au rôle `column1_users`

GRANT column1_users TO column_user;

Créer un rôle pour limiter les utilisateurs de ce rôle à la consultation de certaines lignes uniquement, en l’occurrence celles contenant `A` dans `column1`

CREATE ROLE A_rows_users;

Ajouter l’utilisateur `row_user` au rôle `A_rows_users`

GRANT A_rows_users TO row_user;

Créer une politique pour autoriser l’accès uniquement aux lignes où `column1` a la valeur `A`

CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users;

Définir les privilèges sur la base de données et la table

GRANT SELECT(id, column1, column2) ON db1.table1 TO A_rows_users;

Accorder des permissions explicites aux autres rôles afin qu’ils conservent l’accès à toutes les lignes

CREATE ROW POLICY allow_other_users_filter 
ON db1.table1 FOR SELECT USING 1 TO clickhouse_admin, column1_users;

Lorsqu’une politique est attachée à une table, le système l’applique, et seuls les utilisateurs et rôles définis peuvent effectuer des opérations sur cette table ; toutes les autres opérations sont refusées. Pour éviter que la politique de lignes restrictive ne s’applique aux autres utilisateurs, une autre politique doit être définie afin de permettre aux autres utilisateurs et rôles de conserver un accès normal ou un autre type d’accès.

Vérification

Tester les privilèges de rôle avec un utilisateur dont l’accès est restreint à certaines colonnes

Connectez-vous au client ClickHouse avec l’utilisateur `clickhouse_admin`

clickhouse-client --user clickhouse_admin --password password

Vérifiez l’accès à la base de données, à la table et à toutes les lignes avec l’utilisateur admin.

SELECT *
FROM db1.table1

Query id: f5e906ea-10c6-45b0-b649-36334902d31d

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
│  3 │ B       │ abc     │
│  4 │ B       │ def     │
└────┴─────────┴─────────┘

Connectez-vous au client ClickHouse avec l’utilisateur `column_user`

clickhouse-client --user column_user --password password

Testez `SELECT` en utilisant toutes les colonnes

SELECT *
FROM db1.table1

Query id: 5576f4eb-7450-435c-a2d6-d6b49b7c4a23

0 rows in set. Elapsed: 0.006 sec.

Received exception from server (version 22.3.2):
Code: 497. DB::Exception: Received from localhost:9000. 
DB::Exception: column_user: Not enough privileges. 
To execute this query it's necessary to have grant 
SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)

L’accès est refusé, car toutes les colonnes sont demandées et l’utilisateur n’a accès qu’à id et column1

Vérifiez la requête `SELECT` avec uniquement les colonnes spécifiées et autorisées :

SELECT
    id,
    column1
FROM db1.table1

Query id: cef9a083-d5ce-42ff-9678-f08dc60d4bb9

┌─id─┬─column1─┐
│  1 │ A       │
│  2 │ A       │
│  3 │ B       │
│  4 │ B       │
└────┴─────────┘

Tester les privilèges du rôle avec un utilisateur soumis à une restriction au niveau des lignes

Connectez-vous au client ClickHouse avec `row_user`

clickhouse-client --user row_user --password password

Affichez les lignes disponibles

SELECT *
FROM db1.table1

Query id: a79a113c-1eca-4c3f-be6e-d034f9a220fb

┌─id─┬─column1─┬─column2─┐
│  1 │ A       │ abc     │
│  2 │ A       │ def     │
└────┴─────────┴─────────┘

Vérifiez que seules les deux lignes ci-dessus sont renvoyées ; les lignes pour lesquelles column1 vaut B doivent être exclues.

Modifier les utilisateurs et les rôles

Les utilisateurs peuvent se voir attribuer plusieurs rôles afin d’obtenir la combinaison de privilèges nécessaire. Lorsque plusieurs rôles sont utilisés, le système les combine pour déterminer les privilèges ; au final, les permissions des rôles sont cumulatives. Par exemple, si role1 autorise uniquement select sur column1 et que role2 autorise select sur column1 et column2, l’utilisateur aura accès aux deux colonnes.

À l’aide du compte Admin, créez un nouvel utilisateur avec des rôles par défaut afin de restreindre l’accès à la fois par ligne et par colonne

CREATE USER row_and_column_user IDENTIFIED BY 'password' DEFAULT ROLE A_rows_users;

Supprimez les privilèges précédents du rôle `A_rows_users`

REVOKE SELECT(id, column1, column2) ON db1.table1 FROM A_rows_users;

Autorisez le rôle `A_row_users` à sélectionner uniquement `column1`

GRANT SELECT(id, column1) ON db1.table1 TO A_rows_users;

Connectez-vous au client ClickHouse avec `row_and_column_user`

clickhouse-client --user row_and_column_user --password password;

Testez avec toutes les colonnes :

SELECT *
FROM db1.table1

Query id: 8cdf0ff5-e711-4cbe-bd28-3c02e52e8bc4

0 rows in set. Elapsed: 0.005 sec.

Received exception from server (version 22.3.2):
Code: 497. DB::Exception: Received from localhost:9000. 
DB::Exception: row_and_column_user: Not enough privileges. 
To execute this query it's necessary to have grant 
SELECT(id, column1, column2) ON db1.table1. (ACCESS_DENIED)

Testez avec les colonnes autorisées uniquement :

SELECT
    id,
    column1
FROM db1.table1

Query id: 5e30b490-507a-49e9-9778-8159799a6ed0

┌─id─┬─column1─┐
│  1 │ A       │
│  2 │ A       │
└────┴─────────┘

Dépannage

Il arrive que des privilèges se recoupent ou se combinent, produisant des résultats inattendus. Les commandes suivantes peuvent être utilisées pour cerner le problème à l’aide d’un compte administrateur.

Lister les grants et rôles d’un utilisateur

SHOW GRANTS FOR row_and_column_user

Query id: 6a73a3fe-2659-4aca-95c5-d012c138097b

┌─GRANTS FOR row_and_column_user───────────────────────────┐
│ GRANT A_rows_users, column1_users TO row_and_column_user │
└──────────────────────────────────────────────────────────┘

Lister les rôles dans ClickHouse

SHOW ROLES

Query id: 1e21440a-18d9-4e75-8f0e-66ec9b36470a

┌─name────────────┐
│ A_rows_users    │
│ column1_users   │
└─────────────────┘

Afficher les politiques

SHOW ROW POLICIES

Query id: f2c636e9-f955-4d79-8e80-af40ea227ebc

┌─name───────────────────────────────────┐
│ A_row_filter ON db1.table1             │
│ allow_other_users_filter ON db1.table1 │
└────────────────────────────────────────┘

Afficher la définition d’une politique et les privilèges actuels

SHOW CREATE ROW POLICY A_row_filter ON db1.table1

Query id: 0d3b5846-95c7-4e62-9cdd-91d82b14b80b

┌─CREATE ROW POLICY A_row_filter ON db1.table1────────────────────────────────────────────────┐
│ CREATE ROW POLICY A_row_filter ON db1.table1 FOR SELECT USING column1 = 'A' TO A_rows_users │
└─────────────────────────────────────────────────────────────────────────────────────────────┘

Exemple de commandes pour gérer les rôles, les politiques et les utilisateurs

Les commandes suivantes peuvent être utilisées pour :

supprimer des privilèges
supprimer des politiques
désattribuer des rôles à des utilisateurs
supprimer des utilisateurs et des rôles

Exécutez ces commandes en tant qu’utilisateur admin ou default

Retirer un privilège d’un rôle

REVOKE SELECT(column1, id) ON db1.table1 FROM A_rows_users;

Supprimer une politique

DROP ROW POLICY A_row_filter ON db1.table1;

Retirer un utilisateur d’un rôle

REVOKE A_rows_users FROM row_user;

Supprimer un rôle

DROP ROLE A_rows_users;

Supprimer un utilisateur

DROP USER row_user;

Résumé

Cet article a présenté les bases de la création d’utilisateurs et de rôles SQL, ainsi que les étapes pour définir et modifier les privilèges des utilisateurs et des rôles. Pour plus d’informations sur chacun de ces éléments, veuillez consulter nos guides utilisateur et notre documentation de référence.

​Utilisateur admin

​Authentification sans mot de passe

​Contrôle d’accès granulaire

​Tester les privilèges d’administrateur

​Utilisateurs non administrateurs

​Préparation

​Création d’une base de données, d’une table et de lignes d’exemple

Créer une base de données de test

Créer une table

Insérer des lignes d’exemple dans la table

Vérifier la table

Créer column_user

Créer row_user

​Création de rôles

Créer un rôle pour limiter les utilisateurs de ce rôle à la consultation de column1 uniquement dans la base de données db1 et la table table1 :

Définir les privilèges pour autoriser l’accès à column1

Ajouter l’utilisateur column_user au rôle column1_users

Créer un rôle pour limiter les utilisateurs de ce rôle à la consultation de certaines lignes uniquement, en l’occurrence celles contenant A dans column1

Ajouter l’utilisateur row_user au rôle A_rows_users

Créer une politique pour autoriser l’accès uniquement aux lignes où column1 a la valeur A

Définir les privilèges sur la base de données et la table

Accorder des permissions explicites aux autres rôles afin qu’ils conservent l’accès à toutes les lignes

​Vérification

​Tester les privilèges de rôle avec un utilisateur dont l’accès est restreint à certaines colonnes

Connectez-vous au client ClickHouse avec l’utilisateur clickhouse_admin

Vérifiez l’accès à la base de données, à la table et à toutes les lignes avec l’utilisateur admin.

Connectez-vous au client ClickHouse avec l’utilisateur column_user

Testez SELECT en utilisant toutes les colonnes

Vérifiez la requête SELECT avec uniquement les colonnes spécifiées et autorisées :

​Tester les privilèges du rôle avec un utilisateur soumis à une restriction au niveau des lignes

Connectez-vous au client ClickHouse avec row_user

Affichez les lignes disponibles

​Modifier les utilisateurs et les rôles

À l’aide du compte Admin, créez un nouvel utilisateur avec des rôles par défaut afin de restreindre l’accès à la fois par ligne et par colonne

Supprimez les privilèges précédents du rôle A_rows_users

Autorisez le rôle A_row_users à sélectionner uniquement column1

Connectez-vous au client ClickHouse avec row_and_column_user

Testez avec toutes les colonnes :

Testez avec les colonnes autorisées uniquement :

​Dépannage

​Lister les grants et rôles d’un utilisateur

​Lister les rôles dans ClickHouse

​Afficher les politiques

​Afficher la définition d’une politique et les privilèges actuels

​Exemple de commandes pour gérer les rôles, les politiques et les utilisateurs

​Retirer un privilège d’un rôle

​Supprimer une politique

​Retirer un utilisateur d’un rôle

​Supprimer un rôle

​Supprimer un utilisateur

​Résumé

Utilisateur admin

Authentification sans mot de passe

Contrôle d’accès granulaire

Tester les privilèges d’administrateur

Utilisateurs non administrateurs

Préparation

Création d’une base de données, d’une table et de lignes d’exemple

Créer `column_user`

Créer `row_user`

Création de rôles

Créer un rôle pour limiter les utilisateurs de ce rôle à la consultation de `column1` uniquement dans la base de données `db1` et la table `table1` :

Définir les privilèges pour autoriser l’accès à `column1`

Ajouter l’utilisateur `column_user` au rôle `column1_users`

Créer un rôle pour limiter les utilisateurs de ce rôle à la consultation de certaines lignes uniquement, en l’occurrence celles contenant `A` dans `column1`

Ajouter l’utilisateur `row_user` au rôle `A_rows_users`

Créer une politique pour autoriser l’accès uniquement aux lignes où `column1` a la valeur `A`

Vérification

Tester les privilèges de rôle avec un utilisateur dont l’accès est restreint à certaines colonnes

Connectez-vous au client ClickHouse avec l’utilisateur `clickhouse_admin`

Connectez-vous au client ClickHouse avec l’utilisateur `column_user`

Testez `SELECT` en utilisant toutes les colonnes

Vérifiez la requête `SELECT` avec uniquement les colonnes spécifiées et autorisées :

Tester les privilèges du rôle avec un utilisateur soumis à une restriction au niveau des lignes

Connectez-vous au client ClickHouse avec `row_user`

Modifier les utilisateurs et les rôles

Supprimez les privilèges précédents du rôle `A_rows_users`

Autorisez le rôle `A_row_users` à sélectionner uniquement `column1`

Connectez-vous au client ClickHouse avec `row_and_column_user`

Dépannage

Lister les grants et rôles d’un utilisateur

Lister les rôles dans ClickHouse

Afficher les politiques

Afficher la définition d’une politique et les privilèges actuels

Exemple de commandes pour gérer les rôles, les politiques et les utilisateurs

Retirer un privilège d’un rôle

Supprimer une politique

Retirer un utilisateur d’un rôle

Supprimer un rôle

Supprimer un utilisateur

Résumé