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;

Remplir la table avec des lignes d’exemple

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 à illustrer 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 à illustrer la restriction de l’accès aux lignes ayant 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

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     │
└────┴─────────┴─────────┘

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

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;

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éation de rôles

​Vérification

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

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

​Modifier les utilisateurs et les rôles

​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éation de rôles

Vérification

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

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

Modifier les utilisateurs et les rôles

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é