Google utilise la technologie IA pour traduire le contenu dans votre langue préférée. Les traductions générées par IA peuvent contenir des erreurs.

Sécuriser et contrôler l'accès aux données des applications

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. Elles filtrent les résultats des requêtes 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 les rôles et les droits d'accès à la 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 sont uniquement destinés à des fins 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 du minimum d'accès 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 a besoin d'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 auxquelles ils sont autorisés à accéder. Les vues sécurisées paramétrées offrent une sécurité dynamique des données et un contrôle des accès aux lignes adaptés aux applications SaaS multitenants 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 d'identifiants d'utilisateur final afin que les utilisateurs n'accèdent qu'aux données auxquelles ils sont autorisés, quelle que soit la formulation de la requête.
Gestion des rôles simplifiée : 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 personne.

Objectifs

Créer des vues sécurisées paramétrées avec des paramètres de vue nommés.
Créer 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.
Accorder les autorisations du nouveau rôle aux vues sécurisées paramétrées et révoquer l'accès aux tables de base.
Se connecter à l'aide du nouveau rôle et vérifier que les tables restreintes ne sont pas accessibles.
Exécuter 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 tutoriel, vous utilisez les composants facturables suivants de Google Cloud:

Cloud SQL pour MySQL

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

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

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 Libérer de l'espace.

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

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

Accéder au sélecteur de projet
Assurez-vous que la facturation est activée pour votre Google Cloud projet.
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

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

Préparer votre environnement

Pour préparer l'exécution de 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 de la 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émarrage de la base de données. Pour en savoir plus, consultez Configurer les options de base de données d'une instance.

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

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

Accéder à Cloud SQL
Sélectionnez une instance dans la liste.
Dans le menu de navigation, cliquez sur Cloud SQL Studio.
Connectez-vous à Studio à l'aide d'un compte utilisateur administrateur.
Cliquez sur Authentifier. Le volet Explorateur affiche la liste des objets de votre base de données.
Cliquez sur Nouvel onglet de l'éditeur SQL ou Nouvel onglet pour ouvrir un nouvel onglet.
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;
```
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

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

Accéder à Cloud SQL
Sélectionnez une instance Cloud SQL pour MySQL dans la liste.
Dans le menu de navigation, cliquez sur Cloud SQL Studio.
Connectez-vous à Studio à l'aide de l'authentification de la base de données.
Cliquez sur Nouvel onglet pour ouvrir un nouvel onglet de l'éditeur SQL.

Créez les tables et insérez des 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 les droits d'accès

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

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

Accéder à Cloud SQL
Sélectionnez une instance dans la liste.
Dans le menu de navigation, cliquez sur Cloud SQL Studio.
Connectez-vous à Studio et connectez-vous à la database en tant que compte utilisateur store_db que vous avez utilisé dans la section précédente.
Cliquez sur Authentifier. Le volet Explorateur affiche la liste des objets de votre base de données.
Cliquez sur Nouvel onglet de l'éditeur SQL ou Nouvel onglet pour ouvrir un nouvel onglet.
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;
```
Remarque : Veillez à utiliser des noms de paramètres uniques, tels que @app_end_userid, lorsque vous créez des vues sécurisées paramétrées.

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

GRANT SELECT ON store_db.secure_checked_items TO 'psv_user';

Accordez l'autorisation d'exécution à la procédure stockée du système intégrée utilisée pour exécuter des requêtes paramétrées en toute sécurité :
```
GRANT EXECUTE ON PROCEDURE mysql.execute_parameterized_query TO 'psv_user';
```
Facultatif. Révoquez explicitement tous les droits d'accès directs sur la table de base à partir 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';
```
Remarque Si une Error 1147: There is no such grant defined for user se produit, vous pouvez l'ignorer en toute sécurité. Cette erreur s'affiche si vous tentez de révoquer un droit d'accès qui n'a jamais été accordé. Par défaut, un nouveau rôle ne dispose d'aucun droit d'accès. N'exécutez cette commande que si vous modifiez un rôle existant qui avait déjà accès à la table de base.
Accordez le rôle psv_user à l'utilisateur de la base de données authentifié par IAM ou au compte de connexion de l'application :
```
GRANT 'psv_user' TO 'IAM_USER_EMAIL'@'%';

SET DEFAULT ROLE 'psv_user' TO 'IAM_USER_EMAIL'@'%';
```
Remplacez IAM_USER_EMAIL par l'adresse e-mail de votre utilisateur IAM ou par le compte de connexion de l'application.

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 :

Connectez-vous à Cloud SQL Studio à l'aide de l'adresse e-mail IAM restreinte ou de la connexion à l'application.
Cliquez sur Nouvel onglet pour ouvrir un nouvel onglet de l'éditeur SQL.
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_USER_EMAIL'@'%' for table 'checked_items'.

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

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

Interrogez les vues sécurisées paramétrées à l'aide de la syntaxe SQL et de la requête QueryData avec des paramètres PSV.

 curl -X POST \
   "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
   -H "Authorization: Bearer $(gcloud auth print-access-token)" \
   -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"
           }
         }
       },
       "parameterized_secure_view_parameters": {
         "parameters": {
           "key": "app_end_userid"
           "app_end_userid": "303"
         }
       }
     },
     "generation_options": {
       "generate_query_result": true,
       "generate_natural_language_answer": true,
       "generate_explanation": true
     }
   }'

Remplacez les valeurs suivantes :

PROJECT_ID: ID de votre Google Cloud projet.
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.

Libérer de l'espace

Supprimer le cluster

Lorsque vous supprimez le cluster que vous avez créé dans la section Avant de commencer, vous supprimez également tous les objets que vous avez créés.

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

Accéder à Cloud SQL
Sélectionnez une instance dans la liste.
Cliquez sur Supprimer.
Confirmez que vous souhaitez supprimer l'instance en saisissant son nom et en cliquant sur Supprimer.

Étape suivante

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

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