Requêtes courantes de gestion des accès

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.

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;

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.

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

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;

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.

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

CREATE DATABASE db1;

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

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

SELECT *
FROM db1.table1

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

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

CREATE USER column_user IDENTIFIED BY 'password';

CREATE USER row_user IDENTIFIED BY 'password';

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.

CREATE ROLE column1_users;

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

GRANT column1_users TO column_user;

CREATE ROLE A_rows_users;

GRANT A_rows_users TO row_user;

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

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

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

clickhouse-client --user clickhouse_admin --password password

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

clickhouse-client --user column_user --password password

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)

SELECT
    id,
    column1
FROM db1.table1

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

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

clickhouse-client --user row_user --password password

SELECT *
FROM db1.table1

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

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

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.

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

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

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

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

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)

SELECT
    id,
    column1
FROM db1.table1

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

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

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.

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

SHOW ROLES

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

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

SHOW ROW POLICIES

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

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

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

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
  

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

DROP ROW POLICY A_row_filter ON db1.table1;

REVOKE A_rows_users FROM row_user;

DROP ROLE A_rows_users;

DROP USER row_user;

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.