Créer et gérer des vues

Cette page explique comment créer et gérer des vues Spanner pour les bases de données utilisant le dialecte GoogleSQL et celles utilisant le dialecte PostgreSQL. Pour en savoir plus sur les vues Spanner, consultez Présentation des vues.

Autorisations

Pour créer, accorder et révoquer l'accès à une vue, vous devez disposer de l'autorisation spanner.database.updateDdl.

Créer une vue

Pour créer une vue, utilisez l'instruction LDD CREATE VIEW pour nommer la vue et fournir la requête qui la définit. Cette instruction se présente sous deux formes:

  • CREATE VIEW définit une nouvelle vue dans la base de données actuelle. Si une vue nommée view_name existe déjà, l'instruction CREATE VIEW échoue.

  • CREATE OR REPLACE VIEW définit une nouvelle vue dans la base de données actuelle. Si une vue nommée view_name existe déjà, sa définition est remplacée.

La syntaxe de l'instruction CREATE VIEW est la suivante:

{CREATE | CREATE OR REPLACE } VIEW  view_name
SQL SECURITY { INVOKER | DEFINER }
AS query

Étant donné qu'une vue est une table virtuelle, le query que vous spécifiez doit fournir des noms pour toutes les colonnes de cette table virtuelle.

De plus, Spanner vérifie le query que vous spécifiez à l'aide de la résolution de noms stricte, ce qui signifie que tous les noms d'objets de schéma utilisés dans la requête doivent être qualifiés de manière à identifier sans ambiguïté un seul objet de schéma. Par exemple, dans les exemples qui suivent la colonne SingerId de la table Singers doit être qualifiée en tant que Singers.SingerId.

Vous devez spécifier SQL SECURITY en tant que INVOKER ou DEFINER dans l'instruction CREATE VIEW ou CREATE OR REPLACE VIEW. Pour en savoir plus sur la différence entre les deux types de sécurité, consultez Présentation des vues.

Par exemple, supposons que la table Singers soit définie comme suit :

GoogleSQL 

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);

PostgreSQL 

CREATE TABLE Singers (
  SingerId   BIGINT PRIMARY KEY,
  FirstName  VARCHAR(1024),
  LastName   VARCHAR(1024),
  SingerInfo BYTEA
);

Vous pouvez définir la vue SingerNames avec les droits de l'appelant, comme indiqué ci-dessous :

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

La table virtuelle créée lorsque la vue SingerNames est utilisée dans une requête contient deux colonnes, SingerId et Name.

Bien que cette définition de la vue SingerNames soit valide, elle ne respecte pas les bonnes pratiques liées au casting de types de données afin de garantir la stabilité entre les modifications de schéma, comme décrit dans la section suivante.

Créer une vue paramétrée

Pour créer des vues paramétrées, incluez la fonction SECURE_CONTEXT() dans la clause WHERE de l'instruction SELECT.

Dans l'exemple suivant, la vue paramétrée accorde à l'utilisateur de l'application l'accès aux colonnes SingerId, FirstName et LastName de la table Singers. La vue limite les résultats aux lignes où UserID correspond à un paramètre obligatoire, app_end_userid.

  CREATE VIEW SingerNames
  SQL SECURITY DEFINER
  AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
  FROM Singers
  WHERE UserID = CAST(SECURE_CONTEXT('app_end_userid'), INT64);

Pour utiliser la vue paramétrée, vous devez disposer du droit SELECT sur une vue. Pour en savoir plus, consultez Accorder et révoquer l'accès à une vue.

Bonnes pratiques lors de la création de vues

Pour réduire la nécessité de mettre à jour la définition d'une vue, castez explicitement le type de données de toutes les colonnes de table dans la requête qui définit cette vue. Dans ce cas, la définition de la vue peut rester valide lors de toutes les modifications de schéma du type d'une colonne.

Par exemple, la définition suivante de la vue SingerNames peut devenir non valide si vous modifiez le type de données d'une colonne dans la table Singers.

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Vous pouvez éviter que la vue ne devienne non valide en castant explicitement les colonnes vers les types de données nécessaires, comme illustré dans l'exemple suivant :

GoogleSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS INT64) AS SingerId,
 CAST(Singers.FirstName AS STRING) || " " || CAST(Singers.LastName AS STRING) AS Name
FROM Singers;

PostgreSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS bigint) AS SingerId,
 CAST(Singers.FirstName AS varchar) || ' ' || CAST(Singers.LastName AS varchar) AS Name
FROM Singers;

Accorder et révoquer l'accès à une vue

En tant qu'utilisateur du contrôle des accès ultraprécis, vous devez disposer du droit SELECT sur une vue. Pour accorder le droit SELECT sur une vue à un rôle de base de données :

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

Pour révoquer le droit SELECT sur une vue d'un rôle de base de données :

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Interroger une vue

La méthode d'interrogation d'une vue des droits de l'appelant ou d'une vue des droits du définisseur est la même. Toutefois, selon le type de sécurité de la vue, Spanner peut avoir besoin ou non de vérifier les objets de schéma référencés dans la vue par rapport au rôle de base de données du compte principal qui a appelé la requête.

Interroger une vue des droits de l'appelant

Si une vue dispose des droits de l'appelant, l'utilisateur doit disposer de droits sur tous les objets de schéma sous-jacents de la vue pour pouvoir l'interroger.

Par exemple, si un rôle de base de données a accès à tous les objets référencés par la vue SingerNames, il peut interroger la vue SingerNames :

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Interroger une vue des droits d'un définisseur

Si une vue dispose des droits du définisseur, un utilisateur peut l'interroger sans avoir besoin de droits sur les objets sous-jacents, à condition que vous accordiez au rôle requis le droit SELECT sur la vue.

Dans l'exemple suivant, un utilisateur disposant du rôle de base de données "Analyste" souhaite interroger la vue SingerNames. Toutefois, l'accès est refusé à l'utilisateur, car SingerNames est une vue des droits de l'appelant et le rôle d'analyste n'a pas accès à tous les objets sous-jacents. Dans ce cas, si vous décidez d'accorder à l'analyste l'accès à la vue, mais que vous ne souhaitez pas lui accorder l'accès à la table Singers, vous pouvez remplacer le type de sécurité de la vue par les droits du définisseur. Après avoir remplacé le type de sécurité de la vue, accordez au rôle Analyste l'accès à la vue. L'utilisateur peut désormais interroger la vue SingerNames, même s'il n'a pas accès à la table Singers.

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Interroger une vue paramétrée

Vous interrogez les vues paramétrées de la même manière que les autres vues. La vue filtre les données pour transmettre de manière sécurisée le contexte au niveau de l'application en fonction de ses paramètres sécurisés (par exemple, l'ID utilisateur).

Remplacer une vue

Vous pouvez remplacer une vue à l'aide de l'instruction CREATE OR REPLACE VIEW pour modifier la définition ou le type de sécurité de la vue.

Le remplacement d'une vue est semblable à la suppression et à la recréation de la vue. Toutes les autorisations d'accès accordées à la vue initiale doivent être accordées à nouveau après le remplacement de la vue.

Pour remplacer une vue des droits de l'appelant par une vue des droits du créateur, procédez comme suit :

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY DEFINER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Supprimer une vue

Une fois qu'une vue est supprimée, les rôles de base de données disposant de droits sur celle-ci n'y ont plus accès. Pour supprimer une vue, utilisez l'instruction DROP VIEW.

DROP VIEW SingerNames;

Obtenir des informations sur une vue

Vous pouvez obtenir des informations sur les vues d'une base de données en interrogeant les tables de son schéma INFORMATION_SCHEMA.

  • Le tableau INFORMATION_SCHEMA.TABLES fournit les noms de toutes les vues définies.

  • La table INFORMATION_SCHEMA.VIEWS fournit les noms, la définition de la vue, le type de sécurité et le texte de la requête de toutes les vues définies. Les utilisateurs FGAC disposant du droit SELECT sur la vue peuvent obtenir des informations sur la vue à partir de la table INFORMATION_SCHEMA.VIEWS. Les autres utilisateurs du FGAC ont besoin du rôle spanner_info_reader s'ils ne disposent pas du droit SELECT pour la vue.

Pour vérifier la définition de la vue et le type de sécurité d'une vue appelée ProductSoldLastWeek :

  SELECT *
  FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_NAME = 'ProductSoldLastWeek';