Crea e gestisci schemi denominati

Questa pagina descrive come creare, utilizzare e rilasciare schemi denominati e come applicare controllo dell'accesso granulare agli oggetti negli schemi denominati. Per una panoramica degli schemi con nome, consulta Schemi denominati.

Nota: per fare riferimento a un oggetto di database in uno schema con nome non predefinito in Spanner, devi utilizzare il nome completo. Non è supportato l'utilizzo di un nome non qualificato con un percorso di ricerca schema denominato configurato.

Prima di iniziare

Per eseguire le procedure in questa pagina, è necessario quanto segue:

• Il ruolo Database Admin roles/spanner.databaseAdmin per il tuo account utente.
• Scopri come funziona il controllo dell'accesso granulare.

Crea uno schema denominato

Il comando CREATE SCHEMA (GoogleSQL e PostgreSQL) viene utilizzato per creare uno schema denominato.

1. Nella console Google Cloud, apri la pagina Spanner.

   Vai a Spanner

2. Seleziona un'istanza dall'elenco.

3. Seleziona un database.

4. Nel menu di navigazione, fai clic su Spanner Studio.

5. Apri una nuova scheda facendo clic su Nuova scheda dell'editor SQL o Nuova scheda.

6. Nella scheda Editor, inserisci il tuo DDL.

   GoogleSQL

   Esegui l'istruzione CREATE SCHEMA per creare lo schema denominato, ad esempio:

   CREATE SCHEMA sch1;
   

   Aggiungi oggetti di database nello schema denominato, ad esempio:

   CREATE SEQUENCE sch1.sequence OPTIONS (
     sequence_kind = 'bit_reversed_positive'
   );
   
   CREATE TABLE Singers (
     SingerId INT64 NOT NULL,
     FirstName STRING(1024),
     LastName STRING(1024),
     SingerInfo BYTES(MAX),
   ) PRIMARY KEY(SingerId);
   
   CREATE INDEX indexOnSingers ON Singers(FirstName);
   
   CREATE TABLE Albums (
     SingerId INT64 NOT NULL,
     AlbumId INT64 NOT NULL,
     AlbumTitle STRING(MAX),
   ) PRIMARY KEY(SingerId, AlbumId),
     INTERLEAVE IN PARENT Singers ON DELETE CASCADE;
   
   CREATE TABLE Songs (
     SingerId INT64 NOT NULL,
     AlbumId INT64 NOT NULL,
     TrackId INT64 NOT NULL,
     SongName STRING(MAX),
   ) PRIMARY KEY(SingerId, AlbumId, TrackId),
     INTERLEAVE IN PARENT Albums ON DELETE CASCADE;
   
   CREATE TABLE sch1.Singers (
     SingerId INT64 NOT NULL,
     FirstName STRING(1024),
     LastName STRING(1024),
     SingerInfo BYTES(MAX),
   ) PRIMARY KEY(SingerId);
   
   CREATE INDEX sch1.indexOnSingers ON sch1.Singers(FirstName);
   
   CREATE TABLE sch1.Albums (
     SingerId INT64 NOT NULL,
     AlbumId INT64 NOT NULL,
     AlbumTitle STRING(MAX),
   ) PRIMARY KEY(SingerId, AlbumId),
     INTERLEAVE IN PARENT sch1.Singers ON DELETE CASCADE;
   
   CREATE TABLE sch1.Songs (
     SingerId INT64 NOT NULL,
     AlbumId INT64 NOT NULL,
     TrackId INT64 NOT NULL,
     SongName STRING(MAX),
   ) PRIMARY KEY(SingerId, AlbumId, TrackId),
     INTERLEAVE IN PARENT sch1.Albums ON DELETE CASCADE;
   
   CREATE VIEW sch1.SingerView SQL SECURITY INVOKER
     AS Select s.FirstName, s.LastName, s.SingerInfo
     FROM sch1.Singers AS s WHERE s.SingerId = 123456;
   
   CREATE VIEW SingerView SQL SECURITY INVOKER
     AS Select s.FirstName, s.LastName, s.SingerInfo
     FROM Singers AS s WHERE s.SingerId = 123456;
   

   Spanner ti consente solo di creare un indice che usa lo stesso schema della tabella che usa l'indice. Dobbiamo assicurarci che i nomi degli schemi degli indici e delle tabelle siano gli stessi.

   PostgreSQL

   Esegui l'istruzione CREATE SCHEMA per creare lo schema denominato, ad esempio:

   CREATE SCHEMA sch1;
   

   Aggiungi oggetti di database nello schema denominato, ad esempio:

   CREATE SEQUENCE sch1.sequence BIT_REVERSED_POSITIVE
   CREATE TABLE sch1.singers(
     singer_id bigint primary key, album_id bigint default(nextval('sch1.sequence')))
   CREATE TABLE sch1.albums(k bigint default(nextval('sch1.sequence'))primary key, album_id bigint)
   
   CREATE VIEW sch1.singer_view SQL SECURITY INVOKER
     AS SELECT * FROM sch1.singers
   CREATE INDEX index_singers ON TABLE sch1.singers(album_id)
   

   Spanner consente di creare indici solo nello stesso schema. In Spanner, le istruzioni PostgreSQL eseguono questa operazione per impostazione predefinita. Non è necessario utilizzare nomi completi per creare indici su schemi denominati.

7. Visualizza gli schemi denominati e gli oggetti correlati nel riquadro Explorer.

Aggiungi controllo dell'accesso granulare a uno schema denominato

Le seguenti istruzioni DDL aggiungono un controllo dell'accesso granulare a uno schema denominato:

• Il privilegio USAGE concede i privilegi all'oggetto dello schema. Per impostazione predefinita, il privilegio USAGE viene concesso allo schema predefinito. Tuttavia, puoi revocare il privilegio USAGE per lo schema predefinito. Presta attenzione quando revochi l'accesso perché gli utenti e i ruoli revocati perdono l'accesso agli oggetti nello schema predefinito.
• L'istruzione ALL esegue la concessione di privilegi in blocco su TUTTI gli oggetti di un tipo nello schema.
• La parola chiave DEFAULT fa riferimento allo schema predefinito nelle istruzioni DDL FGAC.

Per accedere a un oggetto in uno schema denominato, devi disporre dell'autorizzazione di utilizzo per lo schema denominato e delle autorizzazioni corrispondenti per gli oggetti di database che utilizzano tale schema. Le seguenti istruzioni forniscono queste autorizzazioni:

• GRANT ALL (GoogleSQL e PostgreSQL) concede l'autorizzazione del ruolo per accedere a tutti gli oggetti nella tabella creata con lo schema denominato. Questa istruzione si applica solo agli oggetti esistenti in quel momento. Se in un secondo momento aggiungi altri oggetti alla tabella, devi concedere l'accesso a questi oggetti man mano che li crei.
• GRANT USAGE (GoogleSQL e PostgreSQL) concede l'autorizzazione del ruolo per accedere agli oggetti contenuti nello schema. In questo modo il beneficiario può cercare oggetti all'interno dello schema.

1. Nella console Google Cloud, apri la pagina Spanner.

   Vai a Spanner

2. Seleziona un'istanza dall'elenco.

3. Seleziona un database.

4. Nel menu di navigazione, fai clic su Spanner Studio.

5. Apri una nuova scheda facendo clic su Nuova scheda dell'editor SQL o Nuova scheda.

6. Nella scheda Editor, inserisci il tuo DDL.

   GoogleSQL

   Crea un ruolo personalizzato per lo schema denominato. Nell'esempio seguente vengono utilizzati role1 e role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Concedi il ruolo alle tabelle che utilizzano lo schema denominato mediante GRANT ALL. Nell'esempio seguente, utilizziamo sch1 per lo schema denominato e role1 per il ruolo.

   GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO ROLE role1
   

   Concedi l'utilizzo dello schema per i ruoli che hai creato. Nell'esempio seguente, concediamo l'utilizzo di sch1 a role1 e role2.

   GRANT USAGE ON SCHEMA sch1 TO ROLE role1, role2
   

   PostgreSQL

   Crea un ruolo personalizzato per lo schema denominato. Nell'esempio seguente vengono utilizzati role1 e role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Concedi il ruolo alle tabelle che utilizzano lo schema denominato mediante GRANT ALL. Nell'esempio seguente, utilizziamo sch1 per lo schema denominato e role1 per il ruolo.

   GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO role1
   

   Concedi l'utilizzo dello schema per i ruoli che hai creato. Nell'esempio seguente, concediamo l'utilizzo di sch1 a role1 e role2.

   GRANT USAGE ON SCHEMA sch1 TO role1, role2
   

Aggiungi e revoca controllo dell'accesso granulare a uno schema predefinito

Se hai degli schemi denominati, quello predefinito si chiama default. Devi utilizzare il nome dello schema default quando aggiungi o revoca controllo dell'accesso granulare.

Aggiungi controllo dell'accesso granulare a uno schema predefinito

Per impostazione predefinita, tutti gli utenti e i ruoli hanno l'autorizzazione USAGE per lo schema predefinito.

1. Nella console Google Cloud, apri la pagina Spanner.

   Vai a Spanner

2. Seleziona un'istanza dall'elenco.

3. Seleziona un database.

4. Nel menu di navigazione, fai clic su Spanner Studio.

5. Apri una nuova scheda facendo clic su Nuova scheda dell'editor SQL o Nuova scheda.

6. Nella scheda Editor, inserisci il tuo DDL.

   GoogleSQL

   Nell'esempio seguente, concediamo l'accesso a tutte le tabelle a role1.

   GRANT SELECT ON ALL TABLES IN SCHEMA default TO ROLE role1
   

   PostgreSQL

   Nell'esempio seguente, concediamo l'accesso a tutte le tabelle a role1.

   GRANT SELECT ON ALL TABLES IN SCHEMA default TO role1
   

Revoca il controllo dell'accesso granulare a uno schema predefinito

Puoi revocare le autorizzazioni predefinite di controllo dell'accesso granulare sullo schema predefinito utilizzando il comando REVOKE USAGE.

1. Nella console Google Cloud, apri la pagina Spanner.

   Vai a Spanner

2. Seleziona un'istanza dall'elenco.

3. Seleziona un database.

4. Nel menu di navigazione, fai clic su Spanner Studio.

5. Apri una nuova scheda facendo clic su Nuova scheda dell'editor SQL o Nuova scheda.

6. Nella scheda Editor, inserisci il tuo DDL.

   GoogleSQL

   REVOKE USAGE ON SCHEMA DEFAULT FROM ROLE public
   

   PostgreSQL

   REVOKE USAGE ON SCHEMA public FROM public
   

7. Dopo l'esecuzione del comando precedente, dobbiamo concedere esplicitamente le autorizzazioni ai ruoli che devono accedere allo schema predefinito. Nell'esempio seguente, concediamo le autorizzazioni a role1.

   GoogleSQL

   Nell'esempio seguente, concediamo a role1 l'accesso allo schema predefinito.

   SCHEMA default to ROLE role1
   

   PostgreSQL

   Nell'esempio seguente, concediamo a role1 l'accesso allo schema predefinito.

   GRANT USAGE ON SCHEMA public To role1
   

Visualizza gli schemi denominati

1. Seleziona un'istanza dall'elenco.
2. Seleziona un database.
3. Nel menu di navigazione, fai clic su Spanner Studio.

4. Nel riquadro Explorer, espandi l'elenco a discesa Schemi.

   

In alternativa, puoi utilizzare SQL per visualizzare tutti gli schemi nella tabella information_schema.schemata.

L'esempio seguente mostra come visualizzare gli schemi dei nomi e i relativi proprietari:

SELECT schema_name, schema_owner
  FROM information_schema.schemata
  ORDER BY schema_owner

Questa istruzione fornisce un elenco di schemi e proprietari, come nell'esempio seguente:

public spanner_admin
products  spanner_admin
analytics  spanner_admin
logs  spanner_admin
pg_catalog spanner_system
information_schema spanner_system
spanner_sys   spanner_system

Rilascia uno schema denominato

Nota: Spanner non supporta un rilascio a cascata per gli schemi denominati. Prima di eliminare lo schema, devi rilasciare gli oggetti di database che utilizzano lo schema denominato.

Il comando DROP SCHEMA (GoogleSQL e PostgreSQL) viene utilizzato per rilasciare uno schema denominato.

1. Nella console Google Cloud, apri la pagina Spanner.

   Vai a Spanner

2. Seleziona un'istanza dall'elenco.

3. Seleziona un database.

4. Nel menu di navigazione, fai clic su Spanner Studio.

5. Apri una nuova scheda facendo clic su Nuova scheda dell'editor SQL o Nuova scheda.

6. Nella scheda Editor, inserisci il tuo DDL.

   GoogleSQL

   Nel seguente esempio, eliminiamo sch1.

   DROP SCHEMA IF EXISTS sch1;
   

   PostgreSQL

   Nel seguente esempio, eliminiamo sch1.

   DROP SCHEMA IF EXISTS sch1;
   

Passaggi successivi

• Scopri di più sulle best practice relative agli schemi.
• Scopri di più sulle viste.
• Scopri di più sugli indici secondari.
• Scopri di più sulle chiavi esterne.