Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

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

MySQL | PostgreSQL | SQL Server

Les vues sécurisées paramétrées offrent une sécurité des données au niveau des lignes et un contrôle des accès en filtrant 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 des rôles et des 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 présentés à des fins de démonstration uniquement.

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.

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

Sécurité dynamique au niveau des 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 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 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 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 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 une fois les tâches décrites dans ce document terminées. 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 PostgreSQL
  - 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, l'extension parameterized_views et le schéma d'application.

Activer l'option de base de données

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

Configurer la base de données

Créez une base de données nommé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 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 de l'authentification 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 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 superutilisateur, 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 au minimum requis.
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
Pour en savoir plus, consultez CREATE USER.
Connectez-vous en tant qu'utilisateur administratif.
```
SET role TO postgres;
```
Créez le schéma contenant les tables.
```
CREATE SCHEMA store;
```

Créez les tables et insérez des 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 des droits d'accès appropriés pour la table et les vues de base, 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 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 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. En outre, l'utilisateur de la vue paramétrée doit disposer de USAGE sur le schéma de la vue et de SELECT sur la vue. Par exemple, l'utilisateur postgres est propriétaire de la vue paramétrée. Il dispose donc des droits SELECT sur les tables de base, tandis que l'utilisateur psv_user doit disposer de USAGE sur le schéma store et de SELECT sur la vue.
Connectez-vous en tant qu'administrateur postgres et accordez le rôle psv_user à un 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']
);

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": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "instance_id": "INSTANCE_ID",
              "database_id": "DATABASE_ID"
            }
          }
        },
        "parameterized_secure_view_parameters": {
          "parameters": {
            "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 PostgreSQL.
INSTANCE_ID : ID de votre instance Cloud SQL pour PostgreSQL.
DATABASE_ID : ID de votre base de données Cloud SQL pour PostgreSQL.

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.