Utilisez les politiques d’accès aux lignes¶

Mettez en œuvre des politiques d’accès aux lignes¶

Les sous-sections suivantes fournissent des exemples sur la façon de mettre en œuvre des politiques d’accès aux lignes :

• Utiliser une politique d’accès aux lignes typique avec une recherche dans la table de mappage.

• Remplacer les sous-requêtes existantes de la politique d’accès aux lignes par des fonctions mémoïsables pour augmenter les performances des requêtes.

• Référencer une table de mappage protégée par une politique d’accès aux lignes dans une autre politique 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 Gérez les 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, des opérations et des 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.

Par exemple :

1. 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
   );
   

   Copy

2. 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

3. 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

4. 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 :

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

   • 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
       )
   ;
   

   Copy

   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.

   'sales_executive_role' = CURRENT_ROLE()

   Le début du body dans la politique d’accès aux lignes.

   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} .

   Astuce

   Pour améliorer les performances des requêtes sur la table protégée par des règles, remplacez la sous-requête de recherche dans la table de mappage dans la clause EXISTS par une fonction mémoïsable.

   Pour plus de détails, voir l’exemple de fonction mémoïsable (dans cette rubrique).

5. 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;
   

   Copy

   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.

6. 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);
   

   Copy

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

   GRANT SELECT ON TABLE sales TO ROLE sales_manager_role;
   

   Copy

8. 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;
   

   Copy

Exemple : remplacer 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

Exemple : protéger la table de mappage avec une politique d’accès aux lignes¶

Cet exemple montre comment référencer une table de mappage protégée par une politique d’accès aux lignes dans une autre politique d’accès aux lignes. La politique d’accès aux lignes qui protège la table de mappage fait appel à la fonction contextuelle IS_ROLE_IN_SESSION pour tenir compte de la hiérarchie des rôles. Une autre politique d’accès aux lignes protège la table que l’utilisateur interroge. Cette politique d’accès aux lignes utilise une sous-requête pour effectuer une recherche dans la table de mappage. Par exemple :

1. Créez une table de mappage pour définir les rôles autorisés en fonction des régions géographiques de vente et insérez des données dans la table :

   CREATE OR REPLACE TABLE sales.tables.regional_managers (
     allowed_regions varchar
     allowed_roles varchar
   );
   

   Copy

   INSERT INTO sales.tables.regional_managers
   (allowed_regions, allowed_roles)
   VALUES
   ('na', 'NA_MANAGER'),
   ('eu', 'EU_MANAGER'),
   ('apac', 'APAC_MANAGER');
   

   Copy

2. Créez une politique d’accès aux lignes pour spécifier la colonne ALLOWED_ROLES dans la table de mappage :

   CREATE OR REPLACE ROW ACCESS POLICY governance.policies.rap_map_exempt
   AS (allowed_roles varchar) RETURNS BOOLEAN ->
   IS_ROLE_IN_SESSION(allowed_roles);
   

   Copy

3. Ajoutez la politique d’accès aux lignes sur la table de mappage à l’aide d’une instruction ALTER TABLE :

   ALTER TABLE sales.tables.regional_managers
     ADD ROW ACCESS POLICY governance.policies.rap_map_exempt
     ON (allowed_roles);
   

   Copy

4. Créez une nouvelle politique d’accès aux lignes qui spécifie la consultation de la table de mappage sur la table de mappage protégée :

    CREATE OR REPLACE ROW ACCESS POLICY governance.policies.rap_map_lookup
    AS (allowed_regions varchar) RETURNS BOOLEAN ->
    EXISTS (
      SELECT * FROM sales.tables.regional_managers
      WHERE
        REGION = allowed_regions
   );
   

   Copy

5. Ajoutez la politique d’accès aux lignes nommée governance.policies.rap_map_lookup sur la table nommée sales.tables.data à l’aide d’une instruction ALTER TABLE :

   ALTER TABLE sales.tables.data
     ADD ROW ACCESS POLICY governance.policies.rap_map_lookup
     ON (allowed_regions);
   

   Copy

6. Accordez des privilèges aux rôles dans la table de mappage pour permettre aux utilisateurs ayant ces rôles d’interroger les données protégées. Par exemple, ces accords concernent le rôle personnalisé na_manager :

   USE ROLE SECURITYADMIN;
   GRANT USAGE ON DATABASE sales TO ROLE na_manager;
   GRANT USAGE ON SCHEMA sales.tables TO ROLE na_manager;
   GRANT SELECT ON TABLE sales.tables.regional_managers TO ROLE na_manager;
   GRANT SELECT ON TABLE sales.tables.data TO ROLE na_manager;
   

   Copy

   Si nécessaire, répétez les commandes de cette étape pour chaque rôle de la table de mappage.