AnalyticDB:CREATE VIEW

Remarques d'utilisation

Pour les clusters AnalyticDB for MySQL exécutant la version V3.1.9.0 ou ultérieure et compatibles avec le comportement MySQL, des effets secondaires peuvent survenir dans des cas particuliers. Par exemple, si vous renommez une colonne C en D et que la vue doit référencer précisément les colonnes A, B et C, la vue devient indisponible et une erreur est renvoyée, car la colonne C n'existe plus. Ce comportement est attendu. Une erreur est renvoyée même si la requête n'utilise pas finalement la colonne C, car l'élimination des colonnes (column pruning) intervient lors de la phase d'optimisation, tandis que les vérifications de syntaxe SQL et des autorisations ont lieu lors de la phase d'analyse. En revanche, dans les versions antérieures à 3.1.9.0, toutes les vérifications aboutissent et aucune erreur n'est renvoyée, car seul le nombre de colonnes de la vue est contrôlé ; ce nombre reste identique après l'opération de renommage et le nombre de colonnes référencées par l'astérisque * actuel est cohérent. Une fois la vérification de disponibilité de la vue passée, la colonne C est mappée à la troisième colonne selon l'ordre de la table de base. Même si vous interrogez la colonne C après l'opération de renommage, aucune erreur n'est renvoyée et les résultats de la requête sont inattendus.

Syntaxe

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;

Exemples

• Préparer les données de test

  Utilisez un compte privilégié du cluster AnalyticDB for MySQL pour effectuer les opérations suivantes :

  1. Créez un compte nommé user1 :

     CREATE USER user1 IDENTIFIED BY 'user1_pwd';

  2. Créez une base de données nommée adb_demo et une table nommée t1 dans cette base de données. L'instruction suivante crée la table t1 :

     Create Table `t1` (
      `id` bigint AUTO_INCREMENT,
      `id_province` bigint NOT NULL,
      `user_info` varchar,
      primary key (`id`)
     ) DISTRIBUTED BY HASH(`id`);

     Insérez des données de test dans la table t1 :

     INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');

• Créer des vues

  Remarque

  Les exemples suivants créent des vues sur la table t1 en utilisant différentes méthodes de vérification de sécurité, afin de démontrer les différences d'effets sur les autorisations entre DEFINER et INVOKER.

  • Pour créer une vue nommée v1 et définir SQL SECURITY sur INVOKER, exécutez l'instruction suivante :

    CREATE SQL SECURITY INVOKER VIEW v1
      AS SELECT id_province,user_info FROM t1 WHERE id_province=1;

  • Pour créer une vue nommée v2 et définir SQL SECURITY sur DEFINER, exécutez l'instruction suivante :

    CREATE SQL SECURITY DEFINER VIEW v2
      AS SELECT id_province,user_info FROM t1 WHERE id_province=1;

  • Pour créer une vue nommée v3 sans spécifier SQL SECURITY (dans ce cas, le système utilise INVOKER par défaut), exécutez l'instruction suivante :

    CREATE VIEW v3
      AS SELECT id_province,user_info FROM t1 WHERE id_province=1;

• Comparer les autorisations

  • Utilisez un compte privilégié pour accorder à user1 uniquement l'autorisation d'interroger les trois vues :

    GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
    GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
    GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';

    Dans ce cas, après que user1 s'est connecté à la base de données adb_demo du cluster AnalyticDB for MySQL, user1 ne peut interroger que la vue v2. Instruction de requête :

    SELECT * FROM v2;

    Le résultat suivant est renvoyé :

    +-------------+-----------+
    | ID_PROVINCE | USER_INFO |
    +-------------+-----------+
    |           1 | Tom       |
    |           1 | Jerry     |
    +-------------+-----------+

    Cependant, une erreur est renvoyée lorsque user1 interroge la vue v1 ou v3. Instruction de requête :

    SELECT * FROM v1

    ou

    SELECT * FROM v3

    Les deux instructions renvoient l'erreur suivante :

    ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view

  • Après avoir accordé à user1 l'autorisation d'interroger les trois vues, utilisez un compte privilégié pour accorder à user1 l'autorisation d'interroger la table t1 :

    GRANT SELECT ON adb_demo.t1 to user1@'%';

    Dans ce cas, après que user1 s'est connecté à la base de données adb_demo du cluster AnalyticDB for MySQL, user1 peut interroger toutes les vues v1, v2 et v3. Instruction de requête :

    SELECT * FROM v1;

    ou

    SELECT * FROM v2;

    ou

    SELECT * FROM v3;

    Les trois instructions de requête renvoient le même résultat :

    +-------------+-----------+
    | ID_PROVINCE | USER_INFO |
    +-------------+-----------+
    |           1 | Tom       |
    |           1 | Jerry     |
    +-------------+-----------+

FAQ

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;

Bonnes pratiques

Pour plus d'informations, consultez Gestion des autorisations à l'aide des vues.