Proteggi e controlla l'accesso ai dati dell'applicazione utilizzando viste sicure parametrizzate

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

Questo tutorial descrive come configurare viste sicure parametrizzate 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 parametrizzate?

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

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

L'utilizzo di viste sicure parametrizzate 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 unico ruolo di database condiviso per tutti gli utenti dell'applicazione anziché gestire ruoli di database separati per ogni singolo utente.

Obiettivi

  • Crea viste sicure parametrizzate con parametri di visualizzazione denominati.
  • Crea il ruolo del database utilizzato dall'applicazione per connettersi al database e accedere alle viste sicure con parametri.
  • Concedi le autorizzazioni del nuovo ruolo alle viste sicure parametrizzate 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 utenti di Google Cloud potrebbero avere diritto a una prova senza costi.

Per evitare l'addebito di ulteriori costi, elimina le risorse create al termine delle attività descritte in questo documento. Per ulteriori informazioni, consulta Esegui la pulizia.

Prima di iniziare

Prima di creare viste sicure parametrizzate, completa i seguenti prerequisiti.

Abilita la fatturazione e le API richieste

  1. Nella console Google Cloud , seleziona un progetto.

    Vai al selettore di progetti

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

  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 quanto segue:

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

Creare e connettersi a un database

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

  3. Abilita l'API di dati Cloud SQL sulla tua istanza Cloud SQL per MySQL in modo che l'API QueryData possa eseguire query su di essa:

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

    Sostituisci quanto segue:

    • INSTANCE_ID: l'ID dell'istanza Cloud SQL per MySQL.
    • PROJECT_ID: il tuo ID progetto Google Cloud .

Concedi i ruoli richiesti e crea un utente del database IAM

Per eseguire l'autenticazione con Identity and Access Management (IAM) ed eseguire richieste queryData su viste sicure parametrizzate, configura l'utente o il account di servizio IAM:

  1. Concedi i seguenti ruoli IAM a IAM_USER_EMAIL nel tuo progetto Google Cloud :

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

    Per saperne di più sulla concessione dei ruoli IAM, consulta Aggiungi l'associazione della policy IAM per un progetto.

  2. Abilita l'autenticazione del database IAM sulla tua istanza Cloud SQL e aggiungi IAM_USER_EMAIL come utente del database IAM nell'istanza. Devi creare questo utente del database IAM prima di concedergli il ruolo database psv_user. Per saperne di più sulla configurazione degli utenti del database IAM, consulta Gestisci gli utenti con l'autenticazione IAM del database.

prepara l'ambiente

Per prepararti a eseguire query su una vista sicura parametrizzata, 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 la tua istanza Cloud SQL per MySQL 8.0 esegue una versione secondaria precedente, puoi eseguire un upgrade della versione secondaria.
  • Attiva il flag di database cloudsql_parameterized_secure_view per la tua istanza. Questa modifica del flag non richiede il riavvio del database. Per saperne di più, consulta Configura i flag di database di un'istanza.

Configura il database e i ruoli

  1. Nella console Google Cloud , 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 un account utente amministrativo.

  5. Fai clic su Authenticate (Autentica). Il riquadro Explorer mostra un elenco degli oggetti nel database.

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

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

    CREATE DATABASE IF NOT EXISTS store_db;
USE store_db;

  8. Crea un nuovo ruolo del database per eseguire query su viste parametrizzate. L'applicazione utilizza questo ruolo per connettersi al database ed eseguire query con accesso limitato esclusivamente alle viste autorizzate:

    CREATE ROLE 'psv_user';

Crea la tabella e inserisci i dati

  1. Nella console Google Cloud , 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 parametrizzate e configura i privilegi di accesso

Per creare viste sicure parametrizzate, segui questi passaggi:

  1. Nella console Google Cloud , 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 store_db come account utente amministrativo, ad esempio root o ADMIN_USER, che hai utilizzato nella sezione precedente.

  5. Fai clic su Authenticate (Autentica). Il riquadro Explorer mostra un elenco degli oggetti nel database.

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

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

    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 parametrizzata al ruolo psv_user:

    GRANT SELECT ON store_db.secure_checked_items TO 'psv_user';

  9. Concedi l'autorizzazione di esecuzione sulla stored procedure di sistema integrata utilizzata per eseguire query con parametri in modo sicuro:

    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 bypassare la vista sicura:

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

  11. Assicurati che IAM_USER_EMAIL sia stato aggiunto all'istanza come utente del database IAM, quindi concedi il ruolo psv_user all'utente del database autenticato IAM o all'account di accesso dell'applicazione:

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

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

    Sostituisci IAM_USERNAME con il tuo nome utente del database IAM o l'account di accesso all'applicazione. Per un account utente IAM, Cloud SQL per MySQL rimuove @ e il nome di dominio dall'indirizzo email, quindi specifica il prefisso dell'email prima di @, ad esempio user per user@example.com.

Verifica la sicurezza dei dati

Per verificare che sia possibile eseguire query sulle viste sicure parametrizzate, segui questi passaggi:

  1. Accedi a Cloud SQL Studio utilizzando l'indirizzo email IAM limitato 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_USERNAME'@'%' for table 'checked_items'.

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

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

  5. Accedi a Google Cloud CLI come IAM_USER_EMAIL eseguendo gcloud auth application-default login in modo che gcloud auth application-default print-access-token generi un token per l'utente database IAM con limitazioni, quindi esegui una query sulle viste sicure parametrizzate utilizzando il metodo queryData con i parametri 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
     }
   }'

    Utilizza il campo table_ids in database_reference per assicurarti che QueryData generi SQL solo per la vista sicura con parametri secure_checked_items. Se ometti table_ids e il ruolo del database ha accesso SELECT alla tabella di base checked_items, QueryData potrebbe eseguire query direttamente sulla tabella di base, ignorando la vista sicura.

    Sostituisci quanto segue:

    • PROJECT_ID: il tuo ID progetto Google Cloud .
    • 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 tuo database Cloud SQL per MySQL, che in questo tutorial è store_db.

Esegui la pulizia

Elimina l'istanza

Quando elimini l'istanza che hai creato nella sezione Prima di iniziare, elimini anche tutti gli oggetti che hai creato.

  1. Nella console Google Cloud , 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 parametrizzate.

  • Scopri come utilizzare le viste sicure parametrizzate.