Proteggere e controllare l'accesso ai dati delle applicazioni

Le viste sicure con parametri forniscono sicurezza dei dati e controllo dell'accesso alle righe per le applicazioni. Filtrano i risultati delle query in base all'identità dell'utente dell'applicazione.

Questo tutorial descrive come configurare le viste sicure con parametri in Cloud SQL, configurare i ruoli e i privilegi del database per limitare l'accesso alle tabelle di base e verificare la sicurezza dei dati. Gli esempi forniti in questo documento sono solo a scopo dimostrativo.

Che cosa sono le viste sicure con parametri?

Come best practice generale, l'applicazione deve essere eseguita utilizzando un account di servizio con l'accesso minimo richiesto al database. Ad esempio, se l'applicazione non deve scrivere nel database, deve utilizzare un ruolo con accesso di sola lettura. Devi configurare questi controlli dell'accesso a livello di database.

Quando l'applicazione richiede una sicurezza più granulare rispetto a quella consentita dall'accesso standard a livello di database, puoi utilizzare le viste sicure con parametri per assicurarti che gli utenti visualizzino solo i dati autorizzati. Le viste sicure con parametri forniscono sicurezza dei dati dinamica e controllo dell'accesso alle righe adatti alle applicazioni SaaS multi-tenant senza richiedere ruoli di database separati per ogni utente finale dell'applicazione.

L'utilizzo di viste sicure con parametri offre tre vantaggi principali:

  • Sicurezza dei dati dinamica e controllo dell'accesso alle righe: filtra le query utilizzando gli identificatori degli utenti finali in modo che gli utenti accedano solo ai dati autorizzati, indipendentemente dalla formulazione della query.
  • Gestione dei ruoli più semplice: utilizza un singolo ruolo di database condiviso per tutti gli utenti dell'applicazione anziché gestire ruoli di database separati per ogni singolo utente.

Obiettivi

  • Crea viste sicure con parametri con parametri di visualizzazione denominati.
  • Crea il ruolo di database utilizzato dall'applicazione per connettersi al database e accedere alle viste sicure con parametri.
  • Concedi le autorizzazioni del nuovo ruolo alle viste sicure con parametri e revoca l'accesso alle tabelle di base.
  • Connettiti utilizzando il nuovo ruolo e verifica che non sia possibile accedere alle tabelle con limitazioni.
  • Esegui query sulla vista con parametri utilizzando la stored procedure mysql.execute_parameterized_query o l'API QueryData.

Costi

In questo documento vengono utilizzati i seguenti componenti fatturabili di Google Cloud:

Per generare una stima dei costi in base all'utilizzo previsto, utilizza il calcolatore prezzi.

I nuovi Google Cloud utenti potrebbero avere diritto a una prova senza costi.

Per evitare l'addebito di ulteriori costi, elimina le risorse che hai creato al termine delle attività descritte in questo documento. Per ulteriori informazioni, consulta Liberare spazio.

Prima di iniziare

Prima di creare viste sicure con parametri, completa i seguenti prerequisiti.

Abilita fatturazione e API richieste

  1. Nellaconsole, seleziona un progetto. Google Cloud

    Vai al selettore di progetti

  2. Assicurati che la fatturazione sia abilitata per il tuo Google Cloud progetto.

  3. Abilita le API Cloud necessarie per creare e connetterti a Cloud SQL.

    1. Abilita l'API

    2. Nel passaggio Conferma progetto, fai clic su Avanti per confermare il nome del progetto a cui apporterai le modifiche.

    3. Nel passaggio Abilita API, fai clic su Abilita per abilitare le seguenti API:

      • API Cloud SQL per MySQL
      • API Knowledge Catalog

Creare e connettersi a un database

  1. Crea un'istanza.
  2. Connettiti all'istanza e crea un database.

Prepara l'ambiente

Per prepararti a eseguire query su una vista sicura con parametri, configura il database, i ruoli del database e lo schema dell'applicazione.

Abilita il flag di database

  • Assicurati che l'istanza Cloud SQL per MySQL esegua Cloud SQL per MySQL 8.0.43 o versioni successive. Se l'istanza Cloud SQL per MySQL 8.0 esegue una versione secondaria precedente, puoi eseguire un upgrade della versione secondaria.
  • Abilita il flag di database cloudsql_parameterized_secure_view per l'istanza. Questa modifica del flag non richiede il riavvio del database. Per ulteriori informazioni, consulta Configurare i flag di database di un'istanza.

Configura il database e i ruoli

  1. Nella Google Cloud console, vai alla pagina Cloud SQL.

    Vai a Cloud SQL

  2. Seleziona un'istanza dall'elenco.

  3. Nel menu di navigazione, fai clic su Cloud SQL Studio.

  4. Accedi a Studio utilizzando l'account utente amministratore.

  5. Fai clic su Autentica. Il riquadro Spazio di esplorazione mostra un elenco degli oggetti nel database.

  6. Fai clic su Nuova scheda dell'editor SQL o Nuova scheda per aprire una nuova scheda.

  7. Crea un database denominato store_db per i dati e le viste dell'applicazione:

    CREATE DATABASE IF NOT EXISTS store_db;
USE store_db;

  8. Crea un nuovo ruolo di database per l'esecuzione di query sulle viste con parametri. L'applicazione utilizza questo ruolo per connettersi al database ed eseguire query con accesso limitato rigorosamente alle viste autorizzate:

    CREATE ROLE 'psv_user';

Crea la tabella e inserisci i dati

  1. Nella Google Cloud console, vai alla pagina Cloud SQL.

    Vai a Cloud SQL

  2. Seleziona un'istanza Cloud SQL per MySQL dall'elenco.

  3. Nel menu di navigazione, fai clic su Cloud SQL Studio.

  4. Accedi a Studio utilizzando l'autenticazione del database.

  5. Fai clic su Nuova scheda per aprire una nuova scheda dell'editor SQL.

  6. Crea le tabelle e inserisci i dati:

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

Crea viste sicure con parametri e configura i privilegi di accesso

Per creare viste sicure con parametri:

  1. Nella Google Cloud console, vai alla pagina Cloud SQL.

    Vai a Cloud SQL

  2. Seleziona un'istanza dall'elenco.

  3. Nel menu di navigazione, fai clic su Cloud SQL Studio.

  4. Accedi a Studio e connettiti al database come account utente store_db che hai utilizzato nella sezione precedente.

  5. Fai clic su Autentica. Il riquadro Spazio di esplorazione mostra un elenco degli oggetti nel database.

  6. Fai clic su Nuova scheda dell'editor SQL o Nuova scheda per aprire una nuova scheda.

  7. Per fornire un accesso limitato alla vista, crea una vista con parametri:

    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. Concedi l'accesso SELECT alla vista con parametri al ruolo psv_user:

    GRANT SELECT ON store_db.secure_checked_items TO 'psv_user';

  9. Concedi l'autorizzazione di esecuzione alla stored procedure di sistema integrata utilizzata per eseguire in sicurezza le query con parametri:

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

  10. (Facoltativo) Revoca esplicitamente tutti i privilegi diretti sulla tabella di base da psv_user per assicurarti che gli utenti non possano ignorare la vista sicura:

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

  11. Concedi il ruolo psv_user all'utente del database autenticato tramite IAM o all'account di accesso dell'applicazione:

    GRANT 'psv_user' TO 'IAM_USER_EMAIL'@'%';

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

    Sostituisci IAM_USER_EMAIL con l'indirizzo email dell'utente IAM o l'account di accesso dell'applicazione.

Verifica la sicurezza dei dati

Per verificare che sia possibile eseguire query sulle viste sicure con parametri:

  1. Accedi a Cloud SQL Studio utilizzando l'indirizzo email IAM con limitazioni o l'accesso all'applicazione.

  2. Fai clic su Nuova scheda per aprire una nuova scheda dell'editor SQL.

  3. Esegui una query sulla tabella di base e verifica che l'accesso sia negato.

    SELECT * FROM store_db.checked_items;

    Il database restituisce l'errore ERROR 1142 (42000): SELECT command denied to user 'IAM_USER_EMAIL'@'%' for table 'checked_items'.

  4. Esegui una query sulla vista sicura con parametri utilizzando la stored procedure integrata mysql.execute_parameterized_query.

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

  5. Esegui query sulle viste sicure con parametri utilizzando la sintassi SQL e la richiesta QueryData con i parametri 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
     }
   }'

    Sostituisci i seguenti valori:

    • PROJECT_ID: l' Google Cloud ID progetto.
    • REGION: la regione in cui si trova l'istanza Cloud SQL per MySQL.
    • INSTANCE_ID: l'ID dell'istanza Cloud SQL per MySQL.
    • DATABASE_ID: l'ID del database Cloud SQL per MySQL.

Libera spazio

Elimina il cluster

Quando elimini il cluster che hai creato nella sezione Prima di iniziare, elimini anche tutti gli oggetti che hai creato.

  1. Nella Google Cloud console, vai alla pagina Cloud SQL.

    Vai a Cloud SQL

  2. Seleziona un'istanza dall'elenco.

  3. Fai clic su Elimina.

  4. Conferma di voler eliminare l'istanza inserendo il nome dell'istanza e facendo clic su Elimina.

Passaggi successivi

  • Scopri di più sulle viste sicure con parametri.

  • Scopri come utilizzare le viste sicure con parametri.