Alcune o tutte le informazioni in questa pagina potrebbero non essere applicabili a Cloud de Confiance di S3NS. Per maggiori dettagli, consulta Differenze rispetto a Google Cloud.

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

Utilizzare viste sicure con parametri

MySQL | PostgreSQL | SQL Server

Questo documento descrive come utilizzare le visualizzazioni sicure con parametri in Cloud SQL per PostgreSQL, che consentono di limitare l'accesso ai dati in base a parametri denominati specifici dell'applicazione, come le credenziali utente dell'applicazione. Le visualizzazioni sicure con parametri migliorano la sicurezza e il controllo dell'accesso estendendo la funzionalità delle visualizzazioni PostgreSQL. Queste visualizzazioni riducono anche i rischi di esecuzione di query non attendibili dalle applicazioni applicando automaticamente una serie di restrizioni a qualsiasi query eseguita.

Per ulteriori informazioni, consulta la panoramica delle visualizzazioni sicure con parametri e il tutorial sulle visualizzazioni sicure con parametri.

Prima di iniziare

Questo documento presuppone che tu abbia creato un'istanza Cloud SQL per PostgreSQL.

Prima di poter utilizzare le visualizzazioni sicure con parametri, devi:

Attiva il flag di database cloudsql.enable_parameterized_views per l'istanza Cloud SQL. Questa modifica del flag richiede il riavvio del database. Per ulteriori informazioni, consulta Configurare i flag di database.
Utilizza Cloud SQL Studio o psql per creare l'estensione parameterized_views in qualsiasi database in cui viene creata una visualizzazione con parametri:
```
-- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;
```
Quando viene creata l'estensione, il sistema crea anche uno schema denominato parameterized_views in modo che le API siano contenute nello spazio dei nomi dello schema e non entrino in conflitto con le API esistenti.

Creare una visualizzazione sicura con parametri

Per creare una visualizzazione sicura con parametri:

Esegui il comando DDL CREATE VIEW utilizzando l'opzione security_barrier:
```
CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;
```
Sostituisci quanto segue:
- VIEW_NAME: il nome della visualizzazione sicura con parametri
- TABLE_NAME: il nome della tabella da utilizzare nella visualizzazione sicura con parametri
- ALIAS: l'alias del nome della tabella da utilizzare nella visualizzazione sicura con parametri
- COLUMNNAME or COLUMN_NAMEN: il nome della colonna o delle colonne della tabella da utilizzare nella visualizzazione sicura con parametri
- CONDITION: l'istruzione della condizione utilizzata per limitare gli utenti dell'applicazione in modo che possano visualizzare solo le righe a cui è consentito l'accesso. Aggiungi i parametri obbligatori utilizzando la sintassi $@PARAMETER_NAME nella clausola WHERE. Un caso d'uso comune è il controllo del valore di una colonna utilizzando WHERE COLUMN = $@PARAMETER_NAME.
  
  $@PARAMETER_NAME indica un parametro di visualizzazione denominato. Il valore viene fornito quando utilizzi l'API execute_parameterized_query. I parametri di visualizzazione denominati hanno i seguenti requisiti:
  - I parametri di visualizzazione denominati devono iniziare con una lettera (a-z) o un trattino basso (_).
  - I caratteri successivi possono essere lettere, trattini bassi o cifre (0-9).
  - I parametri di visualizzazione denominati sono sensibili alle maiuscole. Ad esempio, $@PARAMETER_NAME viene interpretato in modo diverso da $@parameter_name.
  Di seguito è riportato un esempio di creazione di una visualizzazione sicura con parametri che utilizza un parametro di visualizzazione denominato:
```
CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;
```
Concedi SELECT alla visualizzazione a qualsiasi utente del database autorizzato a eseguire query sulla visualizzazione.
Concedi USAGE allo schema contenente le tabelle definite nella visualizzazione a qualsiasi utente del database autorizzato a eseguire query sulla visualizzazione.

Per ulteriori informazioni, consulta Proteggere e controllare l'accesso ai dati delle applicazioni utilizzando le visualizzazioni sicure con parametri (tutorial).

Configurare la sicurezza per l'applicazione

Per configurare la sicurezza per le applicazioni utilizzando le visualizzazioni sicure con parametri:

Crea la visualizzazione sicura con parametri come utente amministratore. Si tratta di un utente del database Cloud SQL che esegue operazioni amministrative per l'applicazione.
Crea un nuovo ruolo di database per l'esecuzione di query sulle visualizzazioni sicure con parametri. Si tratta di un ruolo di database Cloud SQL che l'applicazione utilizza per connettersi e accedere al database.
1. Concedi al nuovo ruolo le autorizzazioni per le visualizzazioni sicure, che in genere includono i privilegi SELECT per le visualizzazioni e USAGE per gli schemi.
2. Limita gli oggetti a cui questo ruolo può accedere al set minimo richiesto di funzioni e oggetti pubblici di cui l'applicazione ha bisogno. Evita di fornire l'accesso a schemi e tabelle non pubblici.
Quando esegui query sulle visualizzazioni, l'applicazione fornisce i valori dei parametri di visualizzazione obbligatori, che sono collegati all'identità dell'utente dell'applicazione.

Per ulteriori informazioni, consulta Creare e gestire gli utenti.

Eseguire query su una visualizzazione sicura con parametri

Per eseguire query su una visualizzazione sicura con parametri, utilizza una delle seguenti opzioni:

Basata su JSON: utilizza questa API per eseguire la query in un'unica operazione e restituire righe JSON.
Basata su CURSORE: utilizza questa API quando hai query a esecuzione più lunga o query di grandi dimensioni e vuoi recuperare il risultato in batch.
Istruzione PREPARE .. AS RESTRICTED: utilizza PREPARE .. AS RESTRICTED per definire il piano di query, quindi esegui EXECUTE ... WITH VIEW PARAMETERS (...) per eseguirlo con parametri specifici per la visualizzazione.

API JSON

Esegui la funzione execute_parameterized_query(), che ha la seguente sintassi:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

La funzione restituisce una tabella di oggetti JSON. Ogni riga della tabella è equivalente al valore row_to_json() della riga del risultato della query originale.

L'utilizzo di questa API limita le dimensioni del set di risultati in base alle dimensioni (in KB) e al numero di righe. Puoi configurare questi limiti utilizzando parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

API CURSORE

Esegui la funzione execute_parameterized_query() con un nome di cursore, che crea e restituisce un CURSORE con ambito di transazione:

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

Istruzioni preparate

Il metodo delle istruzioni preparate consente di preparare un piano di query una sola volta e poi eseguirlo più volte con valori diversi sia per i parametri posizionali della query sia per i parametri denominati della visualizzazione.

Per utilizzare un'istruzione preparata:

Crea l'istruzione preparata.
```
-- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;
```
Sostituisci quanto segue:
- PREPARED_STATEMENT_NAME: il nome dell'istruzione preparata
- QUERY_PARAM_TYPE_N: il tipo di dati del parametro di query, ad esempio TEXT
- SQL_QUERY: la query SQL da eseguire come parte dell'istruzione preparata con il valore o i valori specificati
Esegui l'istruzione preparata.
```
-- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');
```
Sostituisci quanto segue:
- PREPARED_STATEMENT_NAME: il nome dell'istruzione preparata.
- QUERY_VALUE_N: il valore o i valori da fornire come parametro o parametri alla query SQL
- PARAMETER_NAME_N: il nome del parametro o dei parametri di visualizzazione denominati definiti durante la creazione della visualizzazione sicura con parametri. Crea il parametro di visualizzazione denominato dalla colonna della tabella.
- PARAMETER_VALUE_N: il valore o i valori per il parametro di visualizzazione denominato che limita la visualizzazione sicura con parametri alle righe associate al valore.

Libera spazio nell'istruzione preparata.

-- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

L'esempio seguente utilizza una visualizzazione sicura con parametri denominata user_specific_items che richiede il parametro di visualizzazione denominato $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Dopo aver creato l'istruzione preparata, puoi eseguirla più volte e assegnare valori diversi sia alla query sia al parametro di visualizzazione denominato.

Ad esempio, la prima esecuzione della query:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

La seconda esecuzione della query:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

La clausola WITH VIEW PARAMETERS è il punto in cui vengono forniti i parametri di visualizzazione denominati (123, 456) per la visualizzazione sicura con parametri, separati dai parametri posizionali per la query preparata (%Laptop%, %Book%).

Infine, libera spazio dall'istruzione preparata.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Restrizioni imposte alle query

Di seguito è riportato l'elenco delle operazioni con restrizioni per le query eseguite utilizzando le opzioni descritte in Eseguire query su una visualizzazione sicura con parametri:

Sola lettura: sono consentite solo le istruzioni SELECT di sola lettura. DML (INSERT, UPDATE, DELETE) e DDL (CREATE, ALTER) sono vietati.
Nessun annidamento: le chiamate ricorsive a execute_parameterized_query sono vietate.
Limiti di estensione: alcune estensioni che avviano nuove sessioni in background (ad es. dblink, pg_cron) non sono consentite.
Le istruzioni EXPLAIN non sono consentite per impedire la potenziale perdita di informazioni utilizzando i piani di query.

Elencare tutte le visualizzazioni con parametri

Utilizza la visualizzazione parameterized_views.all_parameterized_views per elencare tutte le visualizzazioni con parametri (quelle che contengono almeno un parametro denominato $@...).

SELECT * FROM parameterized_views.all_parameterized_views;