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 précis des accès aux objets des schémas nommés. Pour obtenir des informations générales sur les schémas nommés, consultez Schémas nommés.

Remarque : Pour faire référence à un objet de base de données dans un schéma nommé non défini par défaut dans Spanner, vous devez utiliser son nom complet. L'utilisation d'un nom incomplet 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 Administrateur de base de données 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 de l'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez le 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 vous permet seulement de créer un index qui utilise le même schéma que la table qui utilise l'index. Nous devons nous assurer que le nom des schémas d'index et de 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 Explorateur.

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

Les instructions LDD suivantes ajoutent un contrôle précis des accès à un schéma nommé :

• Le droit USAGE accorde des droits à l'objet de schéma. Par défaut, le droit USAGE est accordé au schéma par défaut. Toutefois, vous pouvez révoquer le droit USAGE pour le schéma par défaut. Soyez prudent lorsque vous révoquez l'accès, car les utilisateurs et les rôles auxquels l'accès est révoqué perdent tout accès aux objets du schéma par défaut.
• L'instruction ALL accorde des droits d'accès groupés 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 sur le contrôle précis des accès.

Pour accéder à un objet dans un schéma nommé, vous devez disposer de l'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. Par exemple, les instructions suivantes accordent des autorisations de lecture d'une table :

• GRANT SELECT ON TABLE TABLE_NAME IN SCHEMA TO ROLE ROLE_NAME(GoogleSQL et PostgreSQL) accorde au rôle l'autorisation de lire une table spécifique dans un schéma. Vous pouvez également autoriser le rôle à lire sur toutes les tables du schéma à l'aide du mot clé ALL. Par exemple, GRANT SELECT ON ALL TABLES IN SCHEMA TO ROLE ROLE_NAME. L'instruction ALL précédente ne s'applique qu'aux tables présentes au moment de l'exécution de l'instruction.
• GRANT USAGE ON SCHEMA SCHEMA_NAME TO ROLE ROLE_NAME(GoogleSQL et PostgreSQL) autorise le rôle à accéder aux objets contenus dans le schéma si les autorisations de ces objets sont également respectées. Par exemple, pour une table dans un schéma, vous aurez besoin de l'autorisation "USAGE" sur le schéma et de l'autorisation "SELECT" sur la table pour pouvoir lire les données de la table.

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 de l'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez le 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
   

   Attribuez 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 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 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
   

   Attribuez 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 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 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 de schéma default lorsque vous ajoutez ou révoquez un contrôle pré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 les 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 de l'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez le 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 dans un schéma par défaut

Vous pouvez révoquer les autorisations par défaut de contrôle précis des accès 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 de l'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez le LDD.

   GoogleSQL

   REVOKE USAGE ON SCHEMA DEFAULT FROM ROLE public
   

   PostgreSQL

   REVOKE USAGE ON SCHEMA public FROM public
   

7. Après l'exécution de la commande précédente, nous devons accorder explicitement les 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.

   GRANT USAGE ON 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 Explorateur, développez la liste déroulante Schémas.

   

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

L'exemple suivant montre comment afficher le nom des schémas et leur propriétaire :

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 à la suivante :

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'accepte pas la suppression en cascade des 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 de l'éditeur SQL ou Nouvel onglet.

6. Dans l'onglet Éditeur, saisissez le 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;
   

Étapes suivantes

• Découvrez les bonnes pratiques concernant les schémas.
• Découvrez les vues.
• Découvrez les index secondaires.
• Découvrez les clés étrangères.