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 appliquer un contrôle d'accès précis aux objets de schémas nommés. Pour en savoir plus sur les 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 avez besoin des éléments suivants:

• Le rôle Database Admin roles/spanner.databaseAdmin sur votre compte utilisateur.
• Découvrez le fonctionnement du contrôle des accès 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 LDD.

   GoogleSQL

   Exécutez l'instruction CREATE SCHEMA pour créer le schéma nommé, par 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é, par 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 n'autorise la création d'index que 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 LDD suivantes ajoutent un contrôle d'accès précis à un schéma nommé:

• Le droit USAGE accorde des droits à l'objet de schéma. Le privilège USAGE est accordé par défaut au schéma par défaut. Toutefois, vous pouvez révoquer le privilège USAGE pour le schéma par défaut. Soyez prudent lorsque vous révoquez l'accès, car les utilisateurs et les rôles révoqués perdent tout accès aux objets du schéma par défaut.
• L'instruction ALL accorde des droits d'accès groupés à 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 DDL 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 vous ajoutez d'autres objets à la table ultérieurement, vous devez accorder l'accès à ces objets lorsque vous les créez.
• GRANT USAGE (GoogleSQL 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 LDD.

   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 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 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 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 role1, role2
   

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

Lorsque vous avez des schémas nommés, le schéma par défaut est appelé default. Vous devez utiliser le nom du schéma default lorsque vous ajoutez ou révoquez un contrôle d'accès précis.

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 schéma par défaut.

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 LDD.

   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 à role1 l'accès à toutes les tables.

   GRANT SELECT ON ALL TABLES IN SCHEMA default TO role1
   

Révoquer le contrôle précis des accès à 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 LDD.

   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 à role1 l'accès au schéma par défaut.

   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 LDD.

   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 vues
• En savoir plus sur les index secondaires
• Découvrez les clés étrangères.