Creare e gestire schemi denominati

Questa pagina descrive come creare, utilizzare ed eliminare schemi denominati e come applicare controllo dell'accesso granulare agli oggetti negli schemi denominati. Per informazioni generali sugli schemi con nome, consulta la sezione Schemi con nome.

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

Prima di iniziare

Per eseguire le procedure in questa pagina, devi disporre di quanto segue:

• Il ruolo Database Admin roles/spanner.databaseAdmin nel 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 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 di creare solo un indice che utilizza lo stesso schema della tabella che lo utilizza. Dobbiamo assicurarci che i nomi dello schema dell'indice e della tabella 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 la creazione di 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.

Aggiungere il controllo dell'accesso granulare a uno schema denominato

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

• Il privilegio USAGE concede privilegi all'oggetto dello schema. Il privilegio USAGE viene concesso per impostazione predefinita 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 tutto l'accesso agli oggetti nello schema predefinito.
• L'istruzione ALL esegue la concessione collettiva dei privilegi su TUTTI gli oggetti di un tipo nello schema.
• La parola chiave DEFAULT fa riferimento allo schema predefinito nelle istruzioni DDL di 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 del database che utilizzano lo schema. Le seguenti istruzioni forniscono queste autorizzazioni:

• GRANT ALL (GoogleSQL e PostgreSQL) concede al ruolo l'autorizzazione di accesso a tutti gli oggetti della tabella creata con lo schema denominato. Questa affermazione si applica solo agli oggetti esistenti in quel momento. Se aggiungi altri oggetti alla tabella in un secondo momento, devi concedere accesso a questi oggetti durante la loro creazione.
• GRANT USAGE (GoogleSQL e PostgreSQL) conferisce al ruolo l'autorizzazione di accesso agli oggetti contenuti nello schema. In questo modo, il concesso può cercare gli 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 DDL.

   GoogleSQL

   Crea il ruolo personalizzato per lo schema denominato. Nell'esempio seguente, utilizziamo role1 e role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Concedi il ruolo alle tabelle che utilizzano lo schema denominato utilizzando 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 il ruolo personalizzato per lo schema denominato. Nell'esempio seguente, utilizziamo role1 e role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Concedi il ruolo alle tabelle che utilizzano lo schema denominato utilizzando 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
   

Aggiungere e revocare il controllo dell'accesso granulare a uno schema predefinito

Quando hai definito degli schemi, lo schema predefinito è chiamato default. Devi usare il nome dello schema default quando aggiungi o revochi controllo dell'accesso granulare.

Aggiungere il controllo dell'accesso granulare a uno schema predefinito

Per impostazione predefinita, tutti gli utenti e i ruoli dispongono dell'autorizzazione USAGE nello 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 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
   

Revocare il controllo dell'accesso granulare per uno schema predefinito

Puoi revocare le autorizzazioni di controllo dell'accesso granulare predefinite nello 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 DDL.

   GoogleSQL

   REVOKE USAGE ON SCHEMA DEFAULT FROM ROLE public
   

   PostgreSQL

   REVOKE USAGE ON SCHEMA public FROM public
   

7. Dopo aver eseguito il comando precedente, dobbiamo concedere esplicitamente le autorizzazioni ai ruoli che devono accedere allo schema predefinito. Nell'esempio seguente, consegniamo le autorizzazioni a role1.

   GoogleSQL

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

   SCHEMA default to ROLE role1
   

   PostgreSQL

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

   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 tabellainformation_schema.schemata.

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

SELECT schema_name, schema_owner
  FROM information_schema.schemata
  ORDER BY schema_owner

Questa dichiarazione fornisce un elenco di schemi e proprietari, simile al 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

Elimina uno schema denominato

Nota: Spanner non supporta un calo a cascata per gli schemi denominati. Devi eliminare gli oggetti del database che utilizzano lo schema denominato prima di eliminare lo schema.

Il comando DROP SCHEMA (GoogleSQL e PostgreSQL) viene utilizzato per eliminare 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 DDL.

   GoogleSQL

   Nell'esempio seguente, eliminiamo sch1.

   DROP SCHEMA IF EXISTS sch1;
   

   PostgreSQL

   Nell'esempio seguente, eliminiamo sch1.

   DROP SCHEMA IF EXISTS sch1;
   

Passaggi successivi

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