Utilisation des politiques d’accès aux lignes¶

Exemple : recherche dans la table de mappage¶

Les étapes suivantes constituent un guide représentatif pour configurer les privilèges de politiques d’accès aux lignes et ajouter des politiques d’accès aux lignes aux tables et aux vues.

Ces étapes reposent sur les hypothèses suivantes :

• L’approche de gestion est centralisée.

  Si le cas d’utilisation de la politique d’accès aux lignes comprend une approche de gestion hybride ou décentralisée, voir Gestion des politiques d’accès aux lignes pour une distribution représentative des rôles et des privilèges.

• Une table de mappage est nécessaire, similaire à la table Cas d’utilisation représentatif : Utiliser une table de mappage pour filtrer le résultat de la requête.

  Les étapes suivantes utilisent la fonction de contexte CURRENT_ROLE pour déterminer si les utilisateurs voient des lignes dans le résultat d’une requête, tandis que le cas d’utilisation représentatif se concentre sur le prénom de l’utilisateur (c’est-à-dire CURRENT_USER).

  Si l’activation et la hiérarchie des rôles sont importantes, Snowflake recommande que les conditions de la politique utilisent la fonction de contexte IS_ROLE_IN_SESSION. Pour des exemples de politiques, voir la section Exemples de la fonction IS_ROLE_IN_SESSION.

  Le processus global de mise en œuvre d’une politique d’accès aux lignes avec des tables de mappage reste le même, même si les fonctions contextuelles sont différentes.

• Le rôle système SECURITYADMIN accorde des privilèges aux rôles personnalisés pour gérer et mettre en œuvre les politiques d’accès aux lignes.

  Si vous ne souhaitez pas utiliser des rôles à privilèges plus élevés (c’est-à-dire SECURITYADMIN ou ACCOUNTADMIN) dans un environnement de production en faveur de rôles personnalisés moins privilégiés (par exemple database_admin, finance_admin), vérifiez que les rôles moins privilégiés disposent des privilèges nécessaires pour gérer et mettre en œuvre les politiques d’accès aux lignes.

  Pour plus d’informations, voir Privilèges de la politique d’accès aux lignes et Résumé des commandes, opérations et privilèges DDL.

• Il existe des étapes distinctes pour créer une table à protéger par une politique d’accès aux lignes (étape 1) et pour ajouter la politique d’accès aux lignes à la table (étape 5). Il est possible d’ajouter une politique d’accès aux lignes à la table lors de sa création, en supposant qu’une politique d’accès aux lignes existe déjà. Pour plus d’informations sur la syntaxe, voir CREATE TABLE.

Étape 1 : Créer une table pour les données¶

Créez une table pour les données de ventes.

CREATE TABLE sales (
  customer   varchar,
  product    varchar,
  spend      decimal(20, 2),
  sale_date  date,
  region     varchar
);

Etape 2 : Créez une table de mappage, un rôle personnalisé et accordez le privilège SELECT¶

Dans le schéma security, créez une table de mappage comme indiqué dans l’exemple représentatif. Cette table définit les lignes de la table sales que les responsables des ventes peuvent voir.

CREATE TABLE security.salesmanagerregions (
  sales_manager varchar,
  region        varchar
);

Copy

Ensuite, un administrateur de sécurité crée le rôle personnalisé mapping_role et accorde le privilège SELECT au rôle personnalisé. Cette autorisation permet aux utilisateurs ayant le rôle personnalisé d’interroger la table de mappage.

USE ROLE SECURITYADMIN;

CREATE ROLE mapping_role;

GRANT SELECT ON TABLE security.salesmanagerregions TO ROLE mapping_role;

Copy

Étape 3 : Créer une politique d’accès aux lignes¶

En utilisant le rôle de propriétaire de schéma, créez une politique d’accès aux lignes avec les deux conditions suivantes :

1. Les utilisateurs ayant le rôle personnalisé sales_executive_role peuvent voir toutes les lignes.

2. Les utilisateurs ayant le rôle personnalisé sales_manager peuvent visualiser les lignes basées sur la table de mappage salesmanagerregions .

Notez que le rôle de propriétaire de schéma se voit automatiquement accorder le privilège CREATE ROW ACCESS POLICY. Si d’autres rôles doivent pouvoir créer des politiques d’accès aux lignes, le rôle de propriétaire du schéma peut accorder le privilège de politique CREATE ROW ACCESS à d’autres rôles.

use role SCHEMA_OWNER_ROLE;

CREATE OR REPLACE ROW ACCESS POLICY security.sales_policy
AS (sales_region varchar) RETURNS BOOLEAN ->
  'sales_executive_role' = CURRENT_ROLE()
      OR EXISTS (
            SELECT 1 FROM salesmanagerregions
              WHERE sales_manager = CURRENT_ROLE()
                AND region = sales_region
          )
;

Où :

security.sales_policy

Le nom de la politique d’accès aux lignes dans le schéma Security .

AS (sales_region varchar)

La signature de la politique d’accès aux lignes.

Une signature spécifie l’attribut de la table de mappage et le type de données. La valeur renvoyée détermine si l’utilisateur a accès à une ligne donnée de la table ou de la vue à laquelle la politique d’accès aux lignes est ajoutée.

RETURNS BOOLEAN ->

Spécifie l’application de la politique d’accès aux lignes.

Notez que les expression de la politique d’accès aux lignes suivent immédiatement la flèche de droite (c’est-à-dire ->).

L’expression peut être n’importe quelle expression SQL à valeur booléenne. Snowflake prend en charge les expressions qui appellent des UDFs, des fonctions externes et des expressions qui utilisent des sous-requêtes.

'sales_executive_role' = CURRENT_ROLE()

La première condition de l’expression de la politique d’accès aux lignes qui permet aux utilisateurs ayant le rôle personnalisé sales_executive_role de visualiser les données.

OR EXISTS (select 1 from salesmanagerregions WHERE sales_manager = CURRENT_ROLE() AND region = sales_region)

La deuxième condition de l’expression de la politique d’accès aux lignes qui utilise une sous-requête.

La sous-requête exige que CURRENT_ROLE soit le rôle personnalisé sales_manager et que la requête exécutée sur les données spécifie une région répertoriée dans la table de mappage {salesmanagerregions} .

Étape 4 : accorder des privilèges aux rôles personnalisés¶

En utilisant le rôle système SECURITYADMIN, exécutez les deux instructions suivantes :

GRANT OWNERSHIP ON ROW ACCESS POLICY security.sales_policy TO mapping_role;

GRANT APPLY ON ROW ACCESS POLICY security.sales_policy TO ROLE sales_analyst_role;

Ces deux instructions GRANT <privilèges> ont les effets suivants :

• La propriété de la politique n’appartient pas au rôle système SECURITYADMIN . Au moment de l’exécution de la requête, Snowflake utilise les privilèges accordés au rôle personnalisé, car les politiques sont exécutées avec les droits du propriétaire, et non avec le rôle système SECURITYADMIN, plus privilégié. Cette approche soutient le principe du moindre privilège.

• Le rôle personnalisé sales_analyst_role peut ajouter ou détruire la politique d’accès aux lignes d’une table selon les besoins.

Étape 5 : ajouter la politique d’accès aux lignes à une table¶

Toute table ou vue dans Snowflake peut prendre en charge jusqu’à une politique d’accès aux lignes à la fois.

Ajoutez (c’est-à-dire liez) la politique d’accès aux lignes à la colonne région dans la table de données Sales .

USE ROLE SECURITYADMIN;

ALTER TABLE sales ADD ROW ACCESS POLICY security.sales_policy ON (region);

Étape 6 : autoriser un rôle à interroger les données de la table protégée¶

Accordez le privilège SELECT sur les données protégées sales au rôle personnalisé sales_manager_role .

GRANT SELECT ON TABLE sales TO ROLE sales_manager_role;

Étape 7 : tester la politique¶

Une fois que les données de vente ont rempli les données Sales , testez la politique d’accès aux lignes.

USE ROLE sales_manager_role;
SELECT product, SUM(spend)
FROM sales
WHERE YEAR(sale_date) = 2020
GROUP BY product;

Exemple : remplacement des sous-requêtes de politique par une fonction mémoïsable¶

Les étapes de cet exemple créent une fonction mémoïsable pour chaque recherche dans table de mappage dans les conditions de la politique d’accès aux lignes. La sous-requête dans chaque clause EXISTS spécifie la recherche dans la table de mappage, où les tables sont nommées regions, customers et products, respectivement :

CREATE OR REPLACE ROW ACCESS POLICY rap_NO_memoizable_function
  AS (region_id number, customer_id number, product_id number)
  RETURNS BOOLEAN ->
    EXISTS(SELECT 1 FROM regions WHERE id = region_id) OR
    EXISTS(SELECT 1 FROM customers WHERE id = customer_id) OR
    EXISTS(SELECT 1 FROM products WHERE id = product_id)
  ;

Copy

Pour les étapes suivantes, supposez que le rôle personnalisé rap_admin peut créer des politiques d’accès aux lignes (c’est-à-dire qu’il dispose du privilège CREATE ROW ACCESS POLICY sur SCHEMA).

Effectuez les étapes suivantes pour remplacer chacune des recherches de la table de mappage des politiques d’accès aux lignes par une fonction mémoïsable :

1. Créer un rôle personnalisé nommé functions_admin pour gérer la fonction mémoïsable :

   USE ROLE USERADMIN;
   
   CREATE ROLE functions_admin;
   

   Copy

2. Accorder les privilèges suivants au rôle functions_admin pour permettre la création de la fonction mémoïsable dans un schéma existant nommé governance.functions :

   USE ROLE SECURITYADMIN;
   
   GRANT USAGE ON DATABASE governance TO ROLE functions_admin;
   
   GRANT USAGE ON SCHEMA governance.functions TO ROLE functions_admin;
   
   GRANT CREATE FUNCTION ON SCHEMA governance.functions TO ROLE functions_admin;
   

   Copy

3. Créez une fonction mémoïsable pour chacune des clauses de sous-requête EXISTS dans la politique d’accès aux lignes. Chaque définition de fonction mémoïsable prend la même forme. Par souci de concision, un seul exemple de fonction est présenté :

   USE ROLE functions_admin;
   
   USE SCHEMA governance.functions;
   
   CREATE OR REPLACE function allowed_regions()
     RETURNS array
     memoizable
     AS 'SELECT ARRAY_AGG(id) FROM regions';
   

   Copy

4. Utilisez une instruction CREATE ROW ACCESS POLICY pour définir une nouvelle politique d’accès aux lignes qui remplace les sous-requêtes par des fonctions mémoïsables :

   La nouvelle politique d’accès aux lignes permet de tester les requêtes sur une table protégée, lorsque la politique utilise ou non les fonctions mémoïsables, afin de quantifier l’impact sur les performances de l’utilisation des fonctions mémoïsables dans les conditions de la politique :

   USE ROLE rap_admin;
   
   CREATE OR REPLACE ROW ACCESS POLICY rap_with_memoizable_function
     AS (region_id number, customer_id number, product_id number)
     RETURNS BOOLEAN ->
       ARRAY_CONTAINS(region_id, allowed_regions()) OR
       ARRAY_CONTAINS(customer_id, allowed_customers()) OR
       ARRAY_CONTAINS(product_id, allowed_products())
     ;
   

   Copy