UDFs sécurisés

Ce chapitre traite des concepts et de la syntaxe, y compris des exemples, relatifs à l’utilisation d’UDFs sécurisés (fonctions définies par l’utilisateur).

Dans ce chapitre :

Vue d’ensemble

Pourquoi devrais-je utiliser des UDFs sécurisés ?

Certaines des optimisations internes des UDFs SQL nécessitent l’accès aux données sous-jacentes dans les tables de base. Cet accès peut permettre aux données qui sont cachées aux utilisateurs de l’UDF d’être affichées indirectement au moyen de méthodes programmatiques. Les UDFs sécurisés n’utilisent pas ces optimisations, ce qui garantit que les utilisateurs n’ont même pas accès indirectement aux données sous-jacentes.

De plus, l’expression SQL ou le code JavaScript utilisé pour créer un UDF, également connu sous le nom de définition ou de texte de l’UDF, est visible par les utilisateurs dans les commandes et interfaces suivantes :

For security or privacy reasons, you might not wish to expose the underlying tables or algorithmic details for a UDF. With secure UDFs, the definition and details are visible only to authorized users (i.e. users who are granted the role that owns the UDF).

Quand devrais-je utiliser un UDF sécurisé ?

Les UDFs SQL doivent être définis comme sécurisés lorsqu’ils sont spécifiquement conçus pour la confidentialité des données, c’est-à-dire pour limiter l’accès aux données sensibles qui ne doivent pas être exposées à tous les utilisateurs des tables sous-jacentes.

Les UDFs sécurisés ne doivent pas être utilisés pour des UDFs SQL qui sont définis pour simplifier les requêtes, tels que ceux créés pour simplifier les interrogations de données pour lesquelles les utilisateurs n’ont pas besoin de comprendre la représentation des données sous-jacente. En effet, l’optimiseur de requêtes Snowflake, lorsqu’il évalue les UDFs sécurisés, contourne les optimisations utilisées pour les UDFs classiques. Cela pourrait réduire les performances de la requête pour les UDFs sécurisés.

Astuce

Lorsque vous décidez d’utiliser ou non un UDF sécurisé, vous devez tenir compte de l’objectif de l’UDF et peser le pour et le contre entre confidentialité/sécurité des données et performance des requêtes.

De plus, si vos données sont suffisamment sensibles pour que vous décidiez que les accès via un type d’objet (tel que des UDFs) doivent être sécurisés, alors vous devriez fortement envisager de vous assurer que les accès via d’autres types d’objets (tels que les vues) le sont également.

Par exemple, si vous autorisez seulement les UDFs sécurisés à accéder à une table donnée, alors toutes les vues que vous autorisez à accéder à la même table devraient probablement être aussi sécurisées.

Comment les données peuvent-elles être exposées par un UDF normal ?

Les utilisateurs pourraient se demander comment la sécurité d’un UDF normal pourrait être contournée. Dans certaines situations spécifiques, un utilisateur peut être en mesure de déduire des informations sur des lignes qu’il ne peut pas voir directement. Pour plus de détails, voir Pushdown (dans ce chapitre).

Pushdown

Pour comprendre comment les données peuvent être exposées indirectement, il est utile de comprendre le concept de pushdown.

Qu’est-ce que le pushdown ?

La pushdown améliore les performances en filtrant les lignes inutiles le plus tôt possible pendant le traitement de la requête. Le pushdown peut également réduire la consommation de mémoire. Toutefois, le pushdown peut permettre d’exposer indirectement des données confidentielles.

Prenons la requête suivante :

SELECT col1
  FROM tab1
  WHERE location = 'New York';

Une approche pour traiter la requête est la suivante :

  1. Lire toutes les lignes de la table dans la mémoire (c’est-à-dire exécuter la clause FROM).

  2. Analysez les lignes en mémoire, en filtrant toutes les lignes qui ne correspondent pas à New York (c’est-à-dire exécutez la clause WHERE).

  3. Sélectionnez col1 dans les lignes encore en mémoire (c’est-à-dire exécutez la liste SELECT).

Vous pouvez considérer cette démarche comme une stratégie de « chargement d’abord, filtrage plus tard », qui est simple, mais inefficace.

Il est généralement plus efficace d’appliquer le filtre le plus tôt possible. Le filtrage précoce s’appelle « intégrer le filtre le plus étroitement possible au plan de requête », ou plus simplement « pushdown ».

Dans notre exemple de requête, il serait plus efficace d’indiquer au code de balayage de table de ne pas charger les enregistrements qui ne correspondent pas à la clause WHERE. Cela ne fait pas gagner du temps de filtrage (l’emplacement de chaque ligne doit encore être lu une fois), mais cela peut économiser beaucoup de mémoire et réduire le temps de traitement ultérieur car il y a moins de lignes à traiter.

Dans certains cas, vous pouvez traiter les données encore plus efficacement. Par exemple, supposons que les données soient partitionnées par état (c’est-à-dire que toutes les données pour New York se trouvent dans une micro-partition, toutes les données pour la Floride dans une autre micro-partition, et ainsi de suite). Dans ce scénario :

  • Snowflake n’a pas besoin de stocker toutes les lignes en mémoire.

  • Snowflake n’a pas besoin de lire toutes les lignes.

Nous définissons vaguement ceci comme une autre forme de « pushdown ».

Le principe de « pushdown les filtres » s’applique à un large éventail de requêtes. Bien souvent, le filtre le plus sélectif (qui filtre le plus de données) est intégré le plus étroitement possible (exécuté au plus tôt) pour réduire le travail que la requête restante doit effectuer.

Le pushdown peut être combiné à d’autres techniques, telles que le clustering (tri/ordre des données), pour réduire la quantité de données non pertinentes qui doivent être lues, chargées et traitées.

Comment les UDFs sécurisés protègent les données ?

Si l’optimiseur réorganise les filtres d’une manière qui permet à un filtre général de fonctionner avant que le ou les filtres appropriés utilisés pour sécuriser les données soient appliqués, les détails sous-jacents pourraient être exposés. Par conséquent, la solution consiste à empêcher l’optimiseur d’effectuer un « pushdown » de certains types de filtres (plus généralement, d’empêcher l’optimiseur d’utiliser certains types d’optimisations, y compris, mais sans s’y limiter, le pushdown du filtre) si ces optimisations ne sont pas sûres.

Déclarer un UDF comme « sécurisé » indique à l’optimiseur de ne pas effectuer de « pushdown » de certains filtres (plus généralement, de ne pas utiliser certaines optimisations). Cependant, la prévention de certains types d’optimisations peut avoir un impact sur la performance.

Exemple d’exposition indirecte aux données via le pushdown

L’exemple suivant montre une façon dont le pushdown peut indirectement entraîner l’exposition des détails sous-jacents d’une requête. Cet exemple se concentre sur les vues, mais les mêmes principes s’appliquent aux UDFs.

Supposons qu’il existe une table qui stocke des informations sur les patients :

CREATE TABLE patients
  (patient_ID INTEGER,
   category VARCHAR,      -- 'PhysicalHealth' or 'MentalHealth'
   diagnosis VARCHAR
   );

INSERT INTO patients (patient_ID, category, diagnosis) VALUES
  (1, 'Mental', 'paranoia'),
  (2, 'Physical', 'lung cancer');

Il existe deux vues : l’une présente des informations sur la santé mentale et l’autre, des informations sur la santé physique :

CREATE VIEW mental_health_view AS
  SELECT * FROM patients WHERE category = 'MentalHealth';

CREATE VIEW physical_health_view AS
  SELECT * FROM patients WHERE category = 'PhysicalHealth';

La plupart des utilisateurs n’ont pas d’accès direct à la table. Au lieu de cela, les utilisateurs se voient assigner l’un des deux rôles suivants :

  • MentalHealth, qui a les privilèges pour lire à partir de mental_health_view, ou

  • PhysicalHealth, qui a des privilèges pour lire à partir de physical_health_view.

Supposons maintenant qu’un médecin qui n’a des privilèges que sur les données de santé physique veuille savoir s’il existe actuellement des patients en santé mentale dans la table. Le médecin peut construire une requête similaire à la suivante :

SELECT * FROM physical_health_view
  WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1;

Cette requête est équivalente à la suivante :

SELECT * FROM patients
  WHERE
    category = 'PhysicalHealth' AND
    1/IFF(category = 'MentalHealth', 0, 1) = 1;

Snowflake utilise (au moins) deux méthodes pour traiter cette requête.

  • Méthode 1 :

    1. Lisez toutes les lignes de la table des patients.

    2. Appliquez le filtre de sécurité de la vue (c’est-à-dire filtrez les lignes pour lesquelles la catégorie n’est pas PhysicalHealth).

    3. Appliquez la clause WHERE dans la requête (c’est-à-dire le filtre basé sur WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1).

  • La méthode 2 modifie l’ordre des filtres, de sorte que la requête s’exécute comme suit :

    1. Lisez toutes les lignes de la table des patients.

    2. Appliquez la clause WHERE dans la requête (c’est-à-dire le filtre basé sur WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1).

    3. Appliquez le filtre de sécurité de la vue (c’est-à-dire filtrez les lignes pour lesquelles la catégorie n’est pas PhysicalHealth).

Logiquement, ces deux séquences semblent équivalentes ; elles renvoient le même ensemble de lignes. Cependant, selon le degré de sélectivité de ces deux filtres, un ordre de traitement pourrait être plus rapide, et le planificateur de requêtes de Snowflake pourrait choisir le plan qui s’exécute plus rapidement.

Supposons que l’optimiseur choisisse le second plan, dans lequel la clause WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1 est exécutée avant le filtre de sécurité. Si la table des patients contient des lignes dans lesquelles category = 'MentalHealth', alors la fonction IFF renvoie 0 pour cette ligne, et la clause devient effectivement WHERE 1/0 = 1, donc l’instruction provoque une erreur de division par zéro. L’utilisateur avec des privilèges physical_health_view ne voit pas de lignes pour les personnes ayant des problèmes de santé mentale, mais peut comprendre qu’au moins une personne dans la catégorie santé mentale existe.

Notez que cette technique n’aboutit pas toujours à exposer les détails sous-jacents ; elle repose fortement sur les choix que fait le planificateur de requêtes, ainsi que sur la façon dont les vues (ou UDFs) sont écrites. Mais cet exemple montre qu’un utilisateur peut comprendre des informations sur des lignes qu’il ne peut pas voir directement.

Créer des UDFs sécurisés

Les UDFs sécurisés sont définis à l’aide du mot clé SECURE avec le DDL standard pour les fonctions :

  • Pour créer une fonction sécurisée, spécifiez le mot clé SECURE dans la commande CREATE FUNCTION.

  • Pour convertir une fonction existante en fonction sécurisée ou revenir à une fonction normale, réglez ou désélectionnez le mot clé SECURE dans la

  • commande ALTER FUNCTION.

Interagir avec des UDFs sécurisés

Affichage de la définition des fonctions sécurisées

La définition d’une fonction sécurisée n’est exposée qu’aux utilisateurs autorisés, c’est-à-dire aux utilisateurs qui se sont vu attribuer le rôle qui possède la fonction. Si un utilisateur non autorisé utilise l’une des commandes ou interfaces suivantes, la définition de la fonction n’est affichée par aucun des éléments suivants :

Déterminer si une fonction est sécurisée

La colonne IS_SECURE de la vue FUNCTIONS Schéma d’information indique s’il s’agit d’une fonction sécurisée ou non. Par exemple :

select is_secure from information_schema.functions where function_name = 'MYFUNCTION';

Affichage des détails de la fonction sécurisée dans le profil de requête

Les éléments internes d’une fonction sécurisée ne sont pas exposés dans Profil de requête (dans l’interface Web). C’est aussi le cas pour le propriétaire de la fonction sécurisée, puisque des non-propriétaires peuvent avoir accès au profil de requête d’un propriétaire.

Bonnes pratiques liées aux UDFs sécurisés

Les UDFs sécurisés empêchent les utilisateurs d’être éventuellement exposés aux données des lignes de tables filtrées par la fonction. Toutefois, il existe d’autres moyens par lesquels un propriétaire de données risque d’exposer par inadvertance des informations sur les données sous-jacentes si les UDFs ne sont pas correctement construits. Dans cette section, nous abordons certains pièges à éviter.

Colonnes générées par séquence

Une pratique courante pour générer des clés de substitution est d’utiliser une séquence ou une colonne d’auto-incrémentation. Si ces clés sont exposées à des utilisateurs qui n’ont pas accès à toutes les données sous-jacentes, l’utilisateur pourrait deviner les détails de la distribution de données sous-jacente.

Par exemple, supposons que nous ayons une fonction get_widgets_function() qui expose la colonne ID. Si un ID est généré à partir d’une séquence, alors un utilisateur de get_widgets_function() pourrait déduire le nombre total de widgets créés entre les horodatages de création de deux widgets auxquels l’utilisateur a accès. Imaginez la requête et le résultat suivants :

select * from table(get_widgets_function()) order by created_on;

------+-----------------------+-------+-------+-------------------------------+
  ID  |         NAME          | COLOR | PRICE |          CREATED_ON           |
------+-----------------------+-------+-------+-------------------------------+
...
 315  | Small round widget    | Red   | 1     | 2017-01-07 15:22:14.810 -0700 |
 1455 | Small cylinder widget | Blue  | 2     | 2017-01-15 03:00:12.106 -0700 |
...

En se basant sur les résultats, l’utilisateur pourrait soupçonner que 1 139 widgets (1455 - 315) ont été créés entre le 7 et le 15 janvier. Si ces informations sont trop sensibles pour être exposées aux utilisateurs d’une fonction, vous pouvez utiliser l’une des options suivantes :

  • N’exposez pas la colonne générée par séquence dans le cadre de la fonction.

  • Utilisez des identificateurs aléatoires (p. ex. générés par UUID_STRING) plutôt que des valeurs générées par séquence.

  • Floutez programmatiquement les identificateurs.

Taille des données analysées

Pour les requêtes contenant des fonctions sécurisées, Snowflake n’expose pas la quantité de données analysées (en termes d’octets ou de micro-partitions) ni la quantité totale de données. Le but est de protéger les informations des utilisateurs qui n’ont accès qu’à un sous-ensemble des données. Toutefois, les utilisateurs pourraient toujours être capables de faire des observations sur la quantité de données sous-jacentes en fonction des caractéristiques de performance des requêtes. Par exemple, une requête qui dure deux fois plus longtemps pourrait traiter deux fois plus de données. Bien que ces observations soient au mieux approximatives, il est préférable que ce genre d’informations ne soit pas exposé dans certains cas.

Dans ces cas, il est préférable de matérialiser les données par utilisateur/rôle plutôt que d’exposer aux utilisateurs les fonctions sur les données de base. Dans le cas de la table widgets, une table serait créée pour chaque rôle ayant accès aux widgets, ces rôles contenant uniquement les widgets accessibles par ce rôle, et un rôle aurait accès à leur table. Cela est beaucoup plus compliqué que d’utiliser une fonction unique, mais pour les situations de très haute sécurité, cela peut être justifié.

UDFs sécurisés et partage de données

Lorsque vous utilisez des UDFs sécurisés avec le partage de données, la fonction CURRENT_ACCOUNT peut être utilisée pour autoriser les utilisateurs d’un compte spécifique à accéder aux lignes d’une table de base.

Notez que Snowflake ne recommande pas d’utiliser CURRENT_ROLE ou CURRENT_USER dans des UDFs sécurisés qui seront partagés avec d’autres comptes, puisque le propriétaire des données partagées ne contrôle généralement pas les rôles et les utilisateurs du compte avec lesquels elles sont partagées.