Créer et gérer des schémas nommés

Cette page explique comment créer, utiliser et supprimer des schémas nommés, et comment les appliquer un contrôle ultraprécis des accès aux objets dans des schémas nommés. Pour obtenir des informations générales sur des schémas nommés, consultez la section Schémas nommés.

Remarque : Pour faire référence à un objet de base de données dans un schéma nommé autre que par défaut dans Spanner, vous devez utiliser son nom complet. L'utilisation d'un nom non qualifié avec un chemin de recherche de schéma nommé configuré n'est pas prise en charge.

Avant de commencer

Pour effectuer les procédures décrites sur cette page, vous devez disposer des éléments suivants :

• Le rôle Database Admin roles/spanner.databaseAdmin sur votre compte utilisateur.
• Découvrez le fonctionnement du contrôle précis des accès.

Créer un schéma nommé

La commande CREATE SCHEMA (GoogleSQL et PostgreSQL) permet de créer un schéma nommé.

1. Dans la console Google Cloud, ouvrez la page Spanner.

   Accéder à Spanner

2. Sélectionnez une instance dans la liste.

3. Sélectionnez une base de données.

4. Dans le menu de navigation, cliquez sur Spanner Studio.

5. Ouvrez un nouvel onglet en cliquant sur Nouvel onglet d'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez votre instruction DDL.

   GoogleSQL

   Exécutez l'instruction CREATE SCHEMA pour créer le schéma nommé, pour Exemple:

   CREATE SCHEMA sch1;
   

   Ajoutez des objets de base de données dans le schéma nommé, par exemple:

   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 ne vous permet de créer qu'un index qui utilise le même schéma que la table qui utilise l'index. Nous devons nous assurer que les noms du schéma de l'index et de la table sont identiques.

   PostgreSQL

   Exécutez l'instruction CREATE SCHEMA pour créer le schéma nommé, pour Exemple:

   CREATE SCHEMA sch1;
   

   Ajoutez des objets de base de données dans le schéma nommé, par exemple:

   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 autorise uniquement la création d'index dans le même schéma. Dans Spanner, les instructions PostgreSQL le font par défaut. Vous n'avez pas besoin d'utiliser des noms complets pour créer des index sur des schémas nommés.

7. Affichez les schémas nommés et les objets associés dans le volet Explorer.

Ajouter un contrôle précis des accès à un schéma nommé

Les instructions DDL suivantes ajoutent un contrôle d'accès précis à un schéma nommé :

• Le droit USAGE accorde des droits sur l'objet de schéma. Le privilège USAGE est accordé par défaut au schéma par défaut. Cependant, vous pouvez révoquez le droit USAGE pour le schéma par défaut. Soyez prudent lorsque révocation de l'accès, car les utilisateurs et les rôles révoqués perdent tout accès aux dans le schéma par défaut.
• L'instruction ALL effectue l'attribution groupée de droits sur TOUS les objets d'un type dans le schéma.
• Le mot clé DEFAULT fait référence au schéma par défaut dans les instructions LDD FGAC.

Pour accéder à un objet dans un schéma nommé, vous devez disposer d'une autorisation d'utilisation sur le schéma nommé et des autorisations correspondantes sur les objets de base de données qui utilisent ce schéma. Les instructions suivantes fournissent ces autorisations :

• GRANT ALL (GoogleSQL) et PostgreSQL) accorde au rôle l'autorisation d'accéder à tous les objets de la table créée avec le schéma nommé. Cette instruction ne s'applique qu'aux objets qui existent à ce moment-là. Si, par la suite, vous ajoutez d'autres objets à la table, vous devez accorder l'accès à ces objets au fur et à mesure que vous les créez.
• GRANT USAGE (Google SQL et PostgreSQL) donne au rôle l'autorisation d'accéder aux objets contenus dans le schéma. Cela permet au bénéficiaire de rechercher des objets dans le schéma.

1. Dans la console Google Cloud, ouvrez la page Spanner.

   Accéder à Spanner

2. Sélectionnez une instance dans la liste.

3. Sélectionnez une base de données.

4. Dans le menu de navigation, cliquez sur Spanner Studio.

5. Ouvrez un nouvel onglet en cliquant sur Nouvel onglet d'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez votre instruction DDL.

   GoogleSQL

   Créez votre rôle personnalisé pour le schéma nommé. Dans l'exemple suivant, nous utilisons role1 et role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Accordez le rôle aux tables qui utilisent le schéma nommé à l'aide de la commande GRANT ALL Dans l'exemple suivant, nous utilisons sch1 pour le schéma nommé et role1 pour le rôle.

   GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO ROLE role1
   

   Accordez l'utilisation du schéma aux rôles que vous avez créés. Dans l'exemple suivant, nous accordons l'utilisation de sch1 à role1 et role2.

   GRANT USAGE ON SCHEMA sch1 TO ROLE role1, role2
   

   PostgreSQL

   Créez votre rôle personnalisé pour le schéma nommé. Dans l'exemple suivant, nous utilisons role1 et role2.

   CREATE ROLE role1
   CREATE ROLE role2
   

   Accordez le rôle aux tables qui utilisent le schéma nommé à l'aide de la commande GRANT ALL Dans l'exemple suivant, nous utilisons sch1 pour le schéma nommé et role1 pour le rôle.

   GRANT SELECT ON ALL TABLES IN SCHEMA sch1 TO role1
   

   Accordez une utilisation au schéma pour les rôles que vous avez créés. Dans l'exemple suivant, nous accordons l'utilisation de sch1 à role1 et role2.

   GRANT USAGE ON SCHEMA sch1 TO role1, role2
   

Ajouter et révoquer le contrôle ultraprécis des accès à un schéma par défaut

Lorsque vous avez nommé des schémas, le schéma par défaut est appelé default. Vous devez Utilisez le nom de schéma default lors de l'ajout ou de la révocation du contrôle ultraprécis des accès.

Ajouter un contrôle précis des accès à un schéma par défaut

Par défaut, tous les utilisateurs et rôles disposent de l'autorisation USAGE sur le du schéma.

1. Dans la console Google Cloud, ouvrez la page Spanner.

   Accéder à Spanner

2. Sélectionnez une instance dans la liste.

3. Sélectionnez une base de données.

4. Dans le menu de navigation, cliquez sur Spanner Studio.

5. Ouvrez un nouvel onglet en cliquant sur Nouvel onglet d'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez votre instruction DDL.

   GoogleSQL

   Dans l'exemple suivant, nous accordons à role1 l'accès à toutes les tables.

   GRANT SELECT ON ALL TABLES IN SCHEMA default TO ROLE role1
   

   PostgreSQL

   Dans l'exemple suivant, nous accordons l'accès à toutes les tables à role1.

   GRANT SELECT ON ALL TABLES IN SCHEMA default TO role1
   

Révoquer le contrôle des accès ultraprécis à un schéma par défaut

Vous pouvez révoquer les autorisations de contrôle précis des accès par défaut sur le schéma par défaut à l'aide de la commande REVOKE USAGE.

1. Dans la console Google Cloud, ouvrez la page Spanner.

   Accéder à Spanner

2. Sélectionnez une instance dans la liste.

3. Sélectionnez une base de données.

4. Dans le menu de navigation, cliquez sur Spanner Studio.

5. Ouvrez un nouvel onglet en cliquant sur Nouvel onglet d'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez votre instruction DDL.

   GoogleSQL

   REVOKE USAGE ON SCHEMA DEFAULT FROM ROLE public
   

   PostgreSQL

   REVOKE USAGE ON SCHEMA public FROM public
   

7. Une fois la commande précédente exécutée, nous devons accorder explicitement des autorisations aux rôles qui doivent accéder au schéma par défaut. Dans l'exemple suivant, nous accordons des autorisations à role1.

   GoogleSQL

   Dans l'exemple suivant, nous accordons à role1 l'accès au schéma par défaut.

   SCHEMA default to ROLE role1
   

   PostgreSQL

   Dans l'exemple suivant, nous accordons l'accès au schéma par défaut pour role1

   GRANT USAGE ON SCHEMA public To role1
   

Afficher les schémas nommés

1. Sélectionnez une instance dans la liste.
2. Sélectionnez une base de données.
3. Dans le menu de navigation, cliquez sur Spanner Studio.

4. Dans le volet Explorer, développez la liste déroulante Schémas.

   

Vous pouvez également utiliser SQL pour afficher tous les schémas de la table information_schema.schemata.

L'exemple suivant montre comment les vues nomment les schémas et leurs propriétaires :

SELECT schema_name, schema_owner
  FROM information_schema.schemata
  ORDER BY schema_owner

Cette instruction fournit une liste de schémas et de propriétaires, semblable à celle-ci :

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

Supprimer un schéma nommé

Remarque : Spanner n'est pas compatible avec la suppression en cascade pour les schémas nommés. Vous devez supprimer les objets de base de données qui utilisent le schéma nommé avant de supprimer le schéma.

La commande DROP SCHEMA (GoogleSQL et PostgreSQL) permet de supprimer un schéma nommé.

1. Dans la console Google Cloud, ouvrez la page Spanner.

   Accéder à Spanner

2. Sélectionnez une instance dans la liste.

3. Sélectionnez une base de données.

4. Dans le menu de navigation, cliquez sur Spanner Studio.

5. Ouvrez un nouvel onglet en cliquant sur Nouvel onglet d'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez votre instruction DDL.

   GoogleSQL

   Dans l'exemple suivant, nous supprimons sch1.

   DROP SCHEMA IF EXISTS sch1;
   

   PostgreSQL

   Dans l'exemple suivant, nous supprimons sch1.

   DROP SCHEMA IF EXISTS sch1;
   

Étape suivante

• Découvrez les bonnes pratiques concernant les schémas.
• En savoir plus sur les Views
• Apprenez-en plus sur les index secondaires.
• Découvrez les clés étrangères.