Filtrer les données de table sensibles à l’aide de filtres de lignes et de masques de colonne

  • Article

Important

Cette fonctionnalité est disponible en préversion publique.

Cet article fournit des conseils et des exemples d’utilisation de filtres de lignes, de masques de colonne et de tables de mappage pour filtrer les données sensibles dans vos tables.

Qu’est-ce que les filtres de lignes ?

Les filtres de lignes vous permettent d’appliquer un filtre à une table afin que les requêtes suivantes retournent uniquement les lignes pour lesquelles le prédicat de filtre est évalué comme « true ». Un filtre de lignes est implémenté en tant que fonction définie par l’utilisateur (UDF) SQL.

Pour créer un filtre de lignes, écrivez d’abord une fonction UDF SQL pour définir la stratégie de filtre, puis appliquez-la à une table avec une instruction ALTER TABLE. Vous pouvez également spécifier un filtre de lignes pour une table dans l’instruction initiale CREATE TABLE. Chaque table ne peut avoir qu’un seul filtre de ligne. Un filtre de lignes accepte zéro ou plusieurs paramètres d’entrée où chaque paramètre d’entrée est lié à une colonne de la table correspondante.

Quelle est la différence entre ces filtres et les vues dynamiques ?

La vue dynamique est une vue abstraite et en lecture seule d’une ou plusieurs tables sources. L’utilisateur peut accéder directement à la vue dynamique sans avoir accès aux tables sources. La création d’une vue dynamique définit un nouveau nom de table qui ne doit pas correspondre au nom des tables sources ou d’autres tables et vues présentes dans le même schéma.

En revanche, l’association d’un filtre de lignes ou d’un masque de colonne à une table cible applique directement la logique correspondante à la table elle-même sans introduire de nouveaux noms de table. Les requêtes suivantes peuvent continuer à faire référence directement à la table cible à l’aide de son nom d’origine.

Les vues dynamiques, les filtres de lignes et les masques de colonne vous permettent d’appliquer une logique complexe aux tables et de traiter leurs décisions de filtrage lors de l’exécution de la requête.

Utilisez des vues dynamiques si vous avez besoin d’appliquer une logique de transformation telle que des filtres et des masques aux tables en lecture seule, et s’il est acceptable pour les utilisateurs de faire référence aux vues dynamiques à l’aide de différents noms. Utilisez des filtres de lignes et des masques de colonne si vous souhaitez filtrer ou calculer des expressions sur des données spécifiques, mais fournissez toujours aux utilisateurs l’accès aux tables à l’aide de leurs noms d’origine.

Syntaxe du filtre de ligne

Pour créer un filtre de lignes et l’ajouter à une table existante, utilisez la syntaxe suivante :

Créez le filtre de lignes :

CREATE FUNCTION <function_name> (<parameter_name> <parameter_type>, ...)
RETURN {filter clause whose output must be a boolean};

Appliquez le filtre de ligne à une table :

ALTER TABLE <table_name> SET ROW FILTER <function_name> ON (<column_name>, ...);

Supprimez un filtre de lignes d’une table :

ALTER TABLE <table_name> DROP ROW FILTER;

Modifiez un filtre de lignes :

Run a DROP FUNCTION statement to drop the existing function, or use CREATE OR REPLACE FUNCTION to replace it.

Supprimer un filtre de ligne :

ALTER TABLE <table_name> DROP ROW FILTER;
DROP FUNCTION <function_name>;

Remarque

Vous devez exécuter la commande ALTER TABLE ... DROP ROW FILTER avant de supprimer la fonction, ou la table sera dans un état inaccessible.

Si la table devient inaccessible de cette façon, modifiez la table et supprimez la référence de filtre de ligne orpheline à l’aide de ALTER TABLE <table_name> DROP ROW FILTER;.

Exemples de filtre de lignes

Créez une fonction SQL définie par l’utilisateur appliquée aux membres du groupe admin dans la région US.

Avec cette fonction, les membres du groupe admin peuvent accéder à tous les enregistrements de la table. Si la fonction est appelée par un non-administrateur, la condition RETURN_IF échoue et l’expression region='US' est évaluée, en filtrant la table pour afficher uniquement les enregistrements dans la région US.

CREATE FUNCTION us_filter(region STRING)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), true, region='US');

Appliquez la fonction à une table en tant que filtre de ligne. Les requêtes suivantes de la table sales retournent ensuite un sous-ensemble de lignes.

CREATE TABLE sales (region STRING, id INT);
ALTER TABLE sales SET ROW FILTER us_filter ON (region);

Désactivez le filtre de ligne. Les futures requêtes utilisateur de la table sales retournent ensuite toutes les lignes de la table.

ALTER TABLE sales DROP ROW FILTER;

Créez une table avec la fonction appliquée en tant que filtre de ligne dans le cadre de l’instruction CREATE TABLE. Les futures requêtes de la table sales retournent ensuite un sous-ensemble de lignes.

CREATE TABLE sales (region STRING, id INT)
WITH ROW FILTER us_filter ON (region);

Qu’est-ce que les masques de colonne ?

Les masques de colonne vous permettent d’appliquer une fonction de masquage à une colonne de table. La fonction de masquage est évaluée au moment de l’exécution de la requête, en remplaçant chaque référence de la colonne cible par les résultats de la fonction de masquage. Pour la plupart des cas d’usage, les masques de colonne déterminent s’il faut retourner la valeur de colonne d’origine ou la censurer en fonction de l’identité de l’utilisateur appelant. Les masques de colonne sont des expressions écrites en tant qu’UDF SQL.

Chaque colonne de table peut éventuellement avoir une fonction de masquage qui lui est appliquée. La fonction de masquage prend la valeur non masquée de la colonne comme entrée et retourne la valeur masquée comme résultat. La valeur de retour de la fonction de masquage doit être du même type que la colonne masquée. La fonction de masquage peut également prendre des colonnes supplémentaires en tant que paramètres d’entrée et les utiliser dans sa logique de masquage.

Pour appliquer des masques de colonne, créez une fonction et appliquez-la à une colonne de table à l’aide d’une instruction ALTER TABLE. Vous pouvez également appliquer la fonction de masquage lorsque vous créez la table.

Syntaxe du masque de colonne

Dans la clause MASK, vous pouvez utiliser l’une des fonctions d’exécution intégrées d’Azure Databricks ou appeler d’autres fonctions définies par l’utilisateur. Les cas d’usage courants incluent l’inspection de l’identité de l’utilisateur appelant exécutant la fonction à l’aide current_user( ) ou les groupes dont ils sont membres à l’aide is_account_group_member( ).

Créer un masque de colonne :

CREATE FUNCTION <function_name> (<parameter_name> <parameter_type>, ...)
RETURN {expression with the same type as the first parameter};

Appliquer un masque de colonne à une colonne dans une table existante :

ALTER TABLE <table_name> ALTER COLUMN <col_name> SET MASK <mask_func_name> [USING COLUMNS <additional_columns>];

Supprimer un masque de colonne d’une colonne dans une table :

ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;

Modifier un masque de colonne :

DROP la fonction existante ou utilisez CREATE OR REPLACE TABLE.

Supprimer un masque de colonne :

ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;
DROP FUNCTION <function_name>;

Remarque

Vous devez exécuter la commande ALTER TABLE avant de supprimer la fonction ou la table sera dans un état inaccessible.

Si la table devient inaccessible de cette façon, modifiez la table et supprimez la référence de masque orpheline à l’aide de ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;.

Exemples de masque de colonne

Dans cet exemple, vous créez une fonction définie par l’utilisateur qui masque la colonne ssn afin que seuls les utilisateurs membres du groupe HumanResourceDept puissent voir les valeurs de cette colonne.

CREATE FUNCTION ssn_mask(ssn STRING)
  RETURN CASE WHEN is_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;

Appliquez la nouvelle fonction à une table en tant que masque de colonne. Vous pouvez ajouter le masque de colonne au moment de la création de la table ou après.

--Create the `users` table and apply the column mask in a single step:

CREATE TABLE users (
  name STRING,
  ssn STRING MASK ssn_mask);

--Create the `users` table and apply the column mask after:

CREATE TABLE users
  (name STRING, ssn STRING);

ALTER TABLE users ALTER COLUMN ssn SET MASK ssn_mask;

Les requêtes sur cette table retournent désormais des valeurs de colonne ssn masquées lorsque l’utilisateur qui exécute les requêtes n’est pas membre du groupe HumanResourceDept :

SELECT * FROM users;
  James  ***-**-****

Pour désactiver le masque de colonne afin que les requêtes retournent les valeurs d’origine dans la colonne ssn :

ALTER TABLE users ALTER COLUMN ssn DROP MASK;

Utiliser des tables de mappage pour créer une liste de contrôle d’accès

Pour obtenir une sécurité au niveau des lignes, envisagez de définir une table de mappage (ou une liste de contrôle d’accès). Chaque table de mappage est une table de mappage complète qui encode les lignes de données de la table d’origine accessibles à certains utilisateurs ou groupes. Les tables de mappage sont utiles, car elles offrent une intégration simple à vos tables de faits via des jointures directes.

Cette méthodologie s’avère utile pour répondre à de nombreux cas d’usage avec des exigences personnalisées. Voici quelques exemples :

  • Imposer des restrictions basées sur l’utilisateur connecté tout en tenant compte de différentes règles pour des groupes d’utilisateurs spécifiques.
  • Création de hiérarchies complexes, telles que des structures organisationnelles, nécessitant différents ensembles de règles.
  • Réplication de modèles de sécurité complexes à partir de systèmes sources externes.

En adoptant des tables de mappage de cette façon, vous pouvez résoudre efficacement ces scénarios difficiles et garantir des implémentations de sécurité robustes au niveau des lignes et des colonnes.

Exemples de table de mappage

Utiliser une table de mappage pour vérifier si l’utilisateur actuel se trouve dans une liste :

USE CATALOG main;

Créer une table de mappage :

DROP TABLE IF EXISTS valid_users;

CREATE TABLE valid_users(username string);
INSERT INTO valid_users
VALUES
  ('fred@databricks.com'),
  ('barney@databricks.com');

Créer un nouveau filtre :

Remarque

Tous les filtres s’exécutent avec les droits de définition, à l’exception des fonctions qui vérifient le contexte utilisateur (par exemple, les fonctions CURRENT_USER et IS_MEMBER) qui s’exécutent en tant qu’appelant.

Dans cet exemple, la fonction vérifie si l’utilisateur actuel se trouve dans la table valid_users. Si l’utilisateur est trouvé, la fonction retourne « true ».

DROP FUNCTION IF EXISTS row_filter;

CREATE FUNCTION row_filter()
  RETURN EXISTS(
    SELECT 1 FROM valid_users v
    WHERE v.username = CURRENT_USER()
);

L’exemple ci-dessous applique le filtre de lignes lors de la création de la table. Vous pouvez également ajouter le filtre ultérieurement à l’aide d’une instruction ALTER TABLE. Lorsque vous appliquez à une table entière, utilisez la syntaxe ON (). Pour une ligne spécifique, utilisez ON (row);.

DROP TABLE IF EXISTS data_table;

CREATE TABLE data_table
  (x INT, y INT, z INT)
  WITH ROW FILTER row_filter ON ();

INSERT INTO data_table VALUES
  (1, 2, 3),
  (4, 5, 6),
  (7, 8, 9);

Sélectionnez des données dans la table. Cela ne doit retourner des données que si l’utilisateur se trouve dans la table valid_users.

SELECT * FROM data_table;

Créez une table de mappage comprenant des comptes qui doivent toujours avoir accès à toutes les lignes de la table, quelles que soient les valeurs de colonne :

CREATE TABLE valid_accounts(account string);
INSERT INTO valid_accounts
VALUES
  ('admin'),
  ('cstaff');

Créez maintenant une fonction UDF SQL qui retourne true si les valeurs de toutes les colonnes de la ligne sont inférieures à cinq, ou si l’utilisateur appelant est membre de la table de mappage ci-dessus.

CREATE FUNCTION row_filter_small_values (x INT, y INT, z INT)
  RETURN (x < 5 AND y < 5 AND z < 5)
  OR EXISTS(
    SELECT 1 FROM valid_accounts v
    WHERE IS_ACCOUNT_GROUP_MEMBER(v.account));

Enfin, appliquez la fonction UDF SQL à la table en tant que filtre de ligne :

ALTER TABLE data_table SET ROW FILTER row_filter_small_values ON (x, y, z);

Prise en charge

  • Le SQL et les notebooks Databricks pour les charges de travail SQL sont pris en charge.
  • Les commandes DML exécutées par les utilisateurs disposant de privilèges MODIFIER sont prises en charge. Les filtres et les masques sont appliqués aux données lues par des opérations UPDATE et DELETE, et ne sont pas appliqués aux données qui sont écrites (y compris les données faisant l’objet d’une opération INSERT).
  • Formats pris en charge : Delta et Parquet. Parquet est pris en charge seulement pour les tables managées ou externes.
  • Les vues sur les tables avec des masques de colonne ou des filtres de lignes sont prises en charge.
  • Les flux de données de modification Delta Lake sont pris en charge tant que le schéma est compatible avec les filtres de lignes et les masques de colonne qui peuvent s’appliquer à la table cible.
  • Les tables étrangères sont prises en charge.

Limites

  • Les versions Databricks Runtime inférieures à la version 12.2 LTS ne prennent pas en charge les filtres de lignes ou les masques de colonnes. Ces runtimes échouent de manière sécurisée, ce qui signifie que si vous essayez d’accéder aux tables à partir de versions non prises en charge de ces runtimes, aucune donnée n’est retournée.
  • Les vues matérialisées delta Live Tables et les tables de diffusion en continu ne prennent pas en charge les filtres de lignes ou les masques de colonne.
  • Les fonctions Python et Scala définies par l’utilisateur ne sont pas prises en charge en tant que fonctions de filtre de ligne ou de masque de colonne directement. Toutefois, il est possible de les référencer dans les UDF SQL tant que leurs définitions sont stockées définitivement dans le catalogue (au-delà du temps de la session).
  • Delta Sharing ne fonctionne pas avec des masques de sécurité ou de colonne au niveau des lignes.
  • Voyage dans le temps ne fonctionne pas avec des masques de sécurité au niveau des lignes ou des colonnes.
  • L’échantillonnage de table ne fonctionne pas avec des masques de sécurité au niveau des lignes ou des colonnes.
  • L’accès en fonction du chemin d’accès aux fichiers dans des tables avec des stratégies n’est actuellement pas pris en charge.
  • Les stratégies de filtre de lignes ou de masque de colonne avec des dépendances circulaires jusqu’aux stratégies d’origine ne sont pas prises en charge.
  • MERGE et les clones superficiels ne sont pas pris en charge.

Limitation des clusters mono-utilisateurs

N’ajoutez pas de filtres de lignes ou de masques de colonne à une table à laquelle vous accédez à partir de clusters mono-utilisateur. Cela est généralement effectué dans le contexte des travaux Azure Databricks. Pendant la préversion publique, vous ne pourrez pas accéder à la table à partir d’un cluster mono-utilisateur une fois qu’un filtre ou un masque a été appliqué.