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é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 les vues sécurisées paramétrées à l'aide de la fonction execute_parameterized_query() ou de l'API QueryData.

Coûts

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

Cloud SQL pour PostgreSQL

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

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 projet Google Cloud .
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 PostgreSQL
  - API Knowledge Catalog
  - API Data Analytics avec Gemini (geminidataanalytics.googleapis.com)

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

Créer une instance
Connectez-vous à votre instance et créez une base de données.
Activez l'API Cloud SQL Data sur votre instance Cloud SQL pour PostgreSQL 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 PostgreSQL.
- 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 :

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.
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, l'extension parameterized_views et le schéma d'application.

Activer l'option de base de données

Activez l'indicateur de base de données cloudsql.enable_parameterized_views, qui charge les bibliothèques d'extensions requises. Pour en savoir plus, consultez Configurer les indicateurs de base de données d'une instance.

Configurer la base de données

Créez une base de données appelée database pour les données d'application et les vues sécurisées paramétrées. Pour en savoir plus, consultez Créer une base de données.

Créer des rôles de base de données, l'extension et le schéma d'application

Dans la console Google Cloud , 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 de l'authentification du compte 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 sur Nouvel onglet pour ouvrir un nouvel onglet.
Pour utiliser des vues paramétrées, créez l'extension parameterized_views dans votre base de données :
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
Lorsque l'extension est créée, la base de données crée également un schéma nommé parameterized_views afin que les API soient contenues dans l'espace de noms de ce schéma et qu'elles n'entrent pas en conflit avec les API existantes.

Remarque : Vous devez créer une extension dans chaque base de données dans laquelle des vues paramétrées sont créées.
Connectez-vous en tant qu'utilisateur disposant de droits de super-utilisateur, tel que l'utilisateur postgres intégré.
Créez un rôle de base de données pour exécuter des requêtes sur des vues sécurisées paramétrées. Il s'agit d'un rôle Cloud SQL que l'application utilise pour se connecter à la base de données et s'y connecter afin d'exécuter des requêtes avec un accès limité aux fonctions ou objets publics à l'ensemble minimal requis.
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
Pour en savoir plus, consultez les sections sur CREATE USER
Connectez-vous en tant qu'utilisateur avec accès administrateur.
```
SET role TO postgres;
```
Créez le schéma contenant les tables.
```
CREATE SCHEMA store;
```

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

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

INSERT INTO store.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 et configurer les droits d'accès appropriés pour la table de base et les vues, procédez comme suit :

Dans la console Google Cloud , 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 base de données nommée database en tant qu'utilisateur postgres.
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 sur 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.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM store.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.

Accorder l'accès à la vue.

GRANT SELECT ON store.secure_checked_items TO psv_user;

Pour accéder à la vue, accordez l'accès au schéma.
```
GRANT USAGE ON SCHEMA store TO psv_user;
```
Révoquez l'accès direct à la table de base.
```
REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;
```
Remarque : Le propriétaire de la vue paramétrée doit disposer des droits SELECT sur les tables de base. De plus, l'utilisateur de la vue paramétrée doit disposer de l'autorisation USAGE sur le schéma de la vue et de l'autorisation SELECT sur la vue. Par exemple, l'utilisateur postgres est propriétaire de la vue paramétrée. Il dispose donc de droits SELECT sur les tables de base, tandis que l'utilisateur psv_user doit disposer de droits USAGE sur le schéma store et de droits SELECT sur la vue.
Assurez-vous que IAM_USER_EMAIL a été ajouté à l'instance en tant qu'utilisateur de base de données IAM, connectez-vous en tant qu'utilisateur postgres et accordez le rôle psv_user à l'utilisateur authentifié par IAM :
```
GRANT psv_user TO "IAM_USER_EMAIL";
```
Remplacez IAM_USER_EMAIL par l'adresse e-mail de votre utilisateur IAM.

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

Pour vérifier que les vues sécurisées paramétrées limitent l'accès aux vues désignées, connectez-vous à la base de données en tant qu'utilisateur authentifié par IAM. Dans Cloud SQL, les utilisateurs IAM héritent des autorisations des rôles de base de données qui leur sont attribués.

Connectez-vous à la base de données en tant qu'utilisateur authentifié par IAM.

Vérifiez que la table de base n'est pas accessible.

SELECT * FROM store.checked_items;
ERROR:  permission denied for table checked_items

Accédez aux vues sécurisées paramétrées à l'aide de la fonction execute_parameterized_query :

SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from store.secure_checked_items',
  param_names => ARRAY ['app_end_userid'],
  param_values => ARRAY ['303']
);

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": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "instance_id": "INSTANCE_ID",
              "database_id": "DATABASE_ID",
              "table_ids": [
                "store.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 store.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 store.checked_items, QueryData peut interroger directement la table de base, en contournant la vue sécurisée. Spécifiez le nom de la vue qualifié par le schéma, car les noms non qualifiés dans table_ids sont définis par défaut sur le schéma public.

Remplacez les éléments suivants :

PROJECT_ID : ID de votre projet Google Cloud .
REGION : région où se trouve votre instance Cloud SQL pour PostgreSQL.
INSTANCE_ID : ID de votre instance Cloud SQL pour PostgreSQL.
DATABASE_ID : ID de votre base de données Cloud SQL pour PostgreSQL, qui est database 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.

Dans la console Google Cloud , 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.

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