Sécurisez et contrôlez l'accès aux données de l'application à l'aide de vues sécurisées paramétrées.

Les vues sécurisées paramétrées assurent la sécurité des données et le contrôle des accès aux lignes pour les applications. Ils filtrent les résultats de la requête en fonction de l'identité de l'utilisateur de l'application.

Ce tutoriel explique comment configurer des vues sécurisées paramétrées dans Cloud SQL, configurer des rôles et des droits de base de données pour limiter l'accès aux tables de base, et vérifier la sécurité des données. Les exemples fournis dans ce document ne sont donnés qu'à titre de démonstration.

Que sont les vues sécurisées paramétrées ?

En règle générale, votre application doit s'exécuter à l'aide d'un compte de service disposant de l'accès minimal requis à votre base de données. Par exemple, si votre application ne doit pas écrire dans votre base de données, elle doit utiliser un rôle avec un accès en lecture seule. Vous devez configurer ces contrôles d'accès au niveau de la base de données.

Lorsque votre application nécessite une sécurité plus précise que celle autorisée par l'accès standard au niveau de la base de données, vous pouvez utiliser des vues sécurisées paramétrées pour vous assurer que les utilisateurs ne voient que les données autorisées. Les vues sécurisées paramétrées offrent une sécurité des données dynamique et un contrôle des accès aux lignes adaptés aux applications SaaS mutualisées, sans nécessiter de rôles de base de données distincts pour chaque utilisateur final de l'application.

L'utilisation de vues sécurisées paramétrées présente trois avantages principaux :

  • Sécurité dynamique des données et contrôle des accès aux lignes : filtre les requêtes à l'aide des identifiants des utilisateurs finaux afin que les utilisateurs n'accèdent qu'aux données autorisées, quelle que soit la formulation de la requête.
  • Gestion simplifiée des rôles : utilise un seul rôle de base de données partagé pour tous les utilisateurs de l'application au lieu de gérer des rôles de base de données distincts pour chaque individu.

Objectifs

  • Créez des vues sécurisées paramétrées avec des paramètres de vue nommés.
  • Créez le rôle de base de données utilisé par l'application pour se connecter à la base de données et accéder aux vues sécurisées paramétrées.
  • Accordez les autorisations du nouveau rôle aux vues sécurisées paramétrées et révoquez l'accès aux tables de base.
  • Connectez-vous à l'aide du nouveau rôle et vérifiez que les tables restreintes ne sont pas accessibles.
  • Exécutez des requêtes sur la vue paramétrée à l'aide de la procédure stockée mysql.execute_parameterized_query ou de l'API QueryData.

Coûts

Dans ce document, vous utilisez les composants facturables suivants de Google Cloud :

Obtenez une estimation des coûts en fonction de votre utilisation prévue à l'aide du simulateur de coût.

Les nouveaux utilisateurs de Google Cloud peuvent bénéficier d'un essai sans frais.

Pour éviter de continuer à payer des frais, supprimez les ressources que vous avez créées lorsque vous avez terminé les tâches décrites dans ce document. Pour en savoir plus, consultez la section Effectuer un nettoyage.

Avant de commencer

Avant de créer des vues sécurisées paramétrées, remplissez les conditions préalables suivantes.

Activer la facturation et les API requises

  1. Dans la console Google Cloud , sélectionnez un projet.

    Accéder au sélecteur de projet

  2. Assurez-vous que la facturation est activée pour votre projet Google Cloud .

  3. Activez les APIs Cloud nécessaires pour créer une instance Cloud SQL et vous y connecter.

    1. Activer l'API

    2. À l'étape Confirmer le projet, cliquez sur Suivant pour confirmer le nom du projet que vous allez modifier.

    3. À l'étape Activer les API, cliquez sur Activer pour activer les éléments suivants :

      • API Cloud SQL pour MySQL
      • API Knowledge Catalog
      • API Data Analytics avec Gemini (geminidataanalytics.googleapis.com)

Créer une base de données et s'y connecter

  1. Créer une instance
  2. Connectez-vous à votre instance et créez une base de données.

  3. Activez l'API Cloud SQL Data sur votre instance Cloud SQL pour MySQL afin que l'API QueryData puisse exécuter des requêtes sur celle-ci :

    gcloud sql instances patch INSTANCE_ID \
    --project=PROJECT_ID \
    --data-api-access=ALLOW_DATA_API

    Remplacez les éléments suivants :

    • INSTANCE_ID : ID de votre instance Cloud SQL pour MySQL.
    • PROJECT_ID : ID de votre projet Google Cloud .

Attribuer les rôles requis et créer un utilisateur de base de données IAM

Pour vous authentifier avec Identity and Access Management (IAM) et exécuter des requêtes queryData sur des vues sécurisées paramétrées, configurez l'utilisateur ou le compte de service IAM :

  1. Attribuez les rôles IAM suivants à IAM_USER_EMAIL sur votre projet Google Cloud  :

    • roles/geminidataanalytics.queryDataUser
    • roles/serviceusage.serviceUsageConsumer
    • roles/cloudsql.instanceUser

    Pour en savoir plus sur l'attribution de rôles IAM, consultez Ajouter une liaison de stratégie IAM pour un projet.

  2. Activez l'authentification IAM pour les bases de données sur votre instance Cloud SQL et ajoutez IAM_USER_EMAIL en tant qu'utilisateur IAM de base de données sur l'instance. Vous devez créer cet utilisateur de base de données IAM avant de lui attribuer le rôle de base de données psv_user. Pour en savoir plus sur la configuration des utilisateurs de base de données IAM, consultez Gérer les utilisateurs avec l'authentification IAM pour les bases de données.

Préparer votre environnement

Pour vous préparer à exécuter des requêtes sur une vue sécurisée paramétrée, configurez la base de données, les rôles de base de données et le schéma d'application.

Activer l'option de base de données

  • Assurez-vous que votre instance Cloud SQL pour MySQL exécute Cloud SQL pour MySQL 8.0.43 ou une version ultérieure. Si votre instance Cloud SQL pour MySQL 8.0 exécute une version mineure antérieure, vous pouvez effectuer une mise à niveau vers une version mineure.
  • Activez l'indicateur de base de données cloudsql_parameterized_secure_view pour votre instance. Cette modification d'option ne nécessite pas de redémarrer la base de données. Pour en savoir plus, consultez Configurer les indicateurs de base de données d'une instance.

Configurer la base de données et les rôles

  1. Dans la console Google Cloud , accédez à la page Cloud SQL.

    Accéder à Cloud SQL

  2. Sélectionnez une instance dans la liste.

  3. Dans le menu de navigation, cliquez sur Cloud SQL Studio.

  4. Connectez-vous à Studio à l'aide d'un compte utilisateur avec accès administrateur.

  5. Cliquez sur Authentifier. Le volet "Explorateur" affiche la liste des objets de votre base de données.

  6. Cliquez sur Nouvel onglet de l'éditeur SQL ou sur Nouvel onglet pour ouvrir un nouvel onglet.

  7. Créez une base de données nommée store_db pour les données et les vues de l'application :

    CREATE DATABASE IF NOT EXISTS store_db;
USE store_db;

  8. Créez un rôle de base de données pour exécuter des requêtes sur des vues paramétrées. L'application utilise ce rôle pour se connecter à la base de données et exécuter des requêtes avec un accès strictement limité aux vues autorisées :

    CREATE ROLE 'psv_user';

Créer la table et insérer des données

  1. Dans la console Google Cloud , accédez à la page Cloud SQL.

    Accéder à Cloud SQL

  2. Sélectionnez une instance Cloud SQL pour MySQL dans la liste.

  3. Dans le menu de navigation, cliquez sur Cloud SQL Studio.

  4. Connectez-vous à Studio à l'aide de l'authentification de base de données.

  5. Cliquez sur Nouvel onglet pour ouvrir un nouvel onglet de l'éditeur SQL.

  6. Créez les tables et insérez les données :

    CREATE TABLE store_db.checked_items (
bag_id INT,
timestamp TIMESTAMP,
loc_code CHAR(3),
scan_type CHAR(1),
location TEXT,
customer_id INT
);

INSERT INTO store_db.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

Créer des vues sécurisées paramétrées et configurer des droits d'accès

Pour créer des vues sécurisées paramétrées, procédez comme suit :

  1. Dans la console Google Cloud , accédez à la page Cloud SQL.

    Accéder à Cloud SQL

  2. Sélectionnez une instance dans la liste.

  3. Dans le menu de navigation, cliquez sur Cloud SQL Studio.

  4. Connectez-vous à Studio et connectez-vous à la base de données store_db en tant que compte utilisateur avec accès administrateur, tel que root ou ADMIN_USER, que vous avez utilisé dans la section précédente.

  5. Cliquez sur Authentifier. Le volet "Explorateur" affiche la liste des objets de votre base de données.

  6. Cliquez sur Nouvel onglet de l'éditeur SQL ou sur Nouvel onglet pour ouvrir un nouvel onglet.

  7. Pour fournir un accès limité à la vue, créez une vue paramétrée :

    CREATE VIEW store_db.secure_checked_items AS
SELECT bag_id, timestamp, location
FROM store_db.checked_items t
WHERE customer_id = @app_end_userid;

  8. Accordez l'accès SELECT sur la vue paramétrée au rôle psv_user :

    GRANT SELECT ON store_db.secure_checked_items TO 'psv_user';

  9. Accordez l'autorisation d'exécution sur la procédure stockée du système intégré utilisée pour exécuter des requêtes paramétrées de manière sécurisée :

    GRANT EXECUTE ON PROCEDURE mysql.execute_parameterized_query TO 'psv_user';

  10. Facultatif. Révoquez explicitement tous les droits directs sur la table de base de psv_user pour vous assurer que les utilisateurs ne peuvent pas contourner la vue sécurisée :

    REVOKE ALL PRIVILEGES ON store_db.checked_items FROM 'psv_user';

  11. Assurez-vous que IAM_USER_EMAIL a été ajouté à l'instance en tant qu'utilisateur de base de données IAM, puis accordez le rôle psv_user à l'utilisateur de base de données authentifié par IAM ou au compte de connexion de l'application :

    GRANT 'psv_user' TO 'IAM_USERNAME'@'%';

SET DEFAULT ROLE 'psv_user' TO 'IAM_USERNAME'@'%';

    Remplacez IAM_USERNAME par votre nom d'utilisateur de base de données IAM ou votre compte de connexion à l'application. Pour un compte utilisateur IAM, Cloud SQL pour MySQL supprime @ et le nom de domaine de l'adresse e-mail. Spécifiez donc le préfixe de l'adresse e-mail avant @, par exemple user pour user@example.com.

Vérifier la sécurité des données

Pour vérifier que vous pouvez interroger les vues sécurisées paramétrées, procédez comme suit :

  1. Connectez-vous à Cloud SQL Studio à l'aide de l'adresse e-mail IAM restreinte ou de l'identifiant de l'application.

  2. Cliquez sur Nouvel onglet pour ouvrir un nouvel onglet de l'éditeur SQL.

  3. Interrogez la table de base et vérifiez que l'accès est refusé.

    SELECT * FROM store_db.checked_items;

    La base de données renvoie l'erreur ERROR 1142 (42000): SELECT command denied to user 'IAM_USERNAME'@'%' for table 'checked_items'.

  4. Interrogez la vue sécurisée paramétrée à l'aide de la procédure stockée mysql.execute_parameterized_query intégrée.

    CALL mysql.execute_parameterized_query(
  'SELECT * FROM store_db.secure_checked_items',
  'app_end_userid=303'
);

  5. Connectez-vous à la Google Cloud CLI en tant que IAM_USER_EMAIL en exécutant gcloud auth application-default login afin que gcloud auth application-default print-access-token génère un jeton pour l'utilisateur de base de données IAM restreint, puis interrogez les vues sécurisées paramétrées à l'aide de la méthode queryData avec les paramètres PSV :

     curl -X POST \
   "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
   -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
   -H "x-goog-user-project: PROJECT_ID" \
   -H "Content-Type: application/json; charset=utf-8" \
   -d '{
     "prompt": "Show me the checked items.",
     "context": {
       "datasource_references": {
         "cloud_sql_reference": {
           "database_reference": {
             "engine": "MYSQL",
             "project_id": "PROJECT_ID",
             "region": "REGION",
             "instance_id": "INSTANCE_ID",
             "database_id": "DATABASE_ID",
             "table_ids": [
               "secure_checked_items"
             ]
           }
         }
       },
       "parameterized_secure_view_parameters": {
         "parameters": [
           {
             "key": "app_end_userid",
             "value": "303"
           }
         ]
       }
     },
     "generation_options": {
       "generate_query_result": true,
       "generate_natural_language_answer": true,
       "generate_explanation": true
     }
   }'

    Utilisez le champ table_ids dans database_reference pour vous assurer que QueryData ne génère du code SQL que pour la vue sécurisée paramétrée secure_checked_items. Si vous omettez table_ids et que le rôle de base de données dispose d'un accès SELECT à la table de base checked_items, QueryData peut interroger directement la table de base, en contournant la vue sécurisée.

    Remplacez les éléments suivants :

    • PROJECT_ID : ID de votre projet Google Cloud .
    • REGION : région où se trouve votre instance Cloud SQL pour MySQL.
    • INSTANCE_ID : ID de votre instance Cloud SQL pour MySQL.
    • DATABASE_ID : ID de votre base de données Cloud SQL pour MySQL, qui est store_db dans ce tutoriel.

Effectuer un nettoyage

Supprimer l'instance

Lorsque vous supprimez l'instance que vous avez créée dans la section Avant de commencer, vous supprimez également tous les objets que vous avez créés.

  1. Dans la console Google Cloud , accédez à la page Cloud SQL.

    Accéder à Cloud SQL

  2. Sélectionnez une instance dans la liste.

  3. Cliquez sur Supprimer.

  4. Confirmez que vous souhaitez supprimer l'instance en saisissant son nom et en cliquant sur Supprimer.

Étapes suivantes

  • En savoir plus sur les vues sécurisées paramétrées.

  • Découvrez comment utiliser des vues sécurisées paramétrées.