AnalyticDB:CREATE VIEW

注意事項

V3.1.9.0 以降を実行し、MySQL の動作と互換性がある AnalyticDB for MySQL クラスターでは、特殊なケースで副作用が発生する可能性があります。 たとえば、列の名前を C から D に変更し、ビューが列 A、B、および C を正確に参照する必要がある場合、列 C が存在しなくなるため、ビューは使用不可になり、エラーが返されます。 これは想定どおりの動作です。 列プルーニングは最適化フェーズで行われるのに対し、SQL 構文チェックと権限検証は解析フェーズで行われるため、クエリが最終的に列 C を使用しない場合でもエラーが返されます。 しかし、3.1.9.0 より前のバージョンでは、ビューの列数のみがチェックされ、その数は名前変更操作後も同じままであり、現在の * によって参照される列数も一貫しているため、すべてのチェックに合格し、エラーは返されません。 ビューの可用性チェックに合格すると、列 C はベーステーブルの順序で 3 番目の列にマッピングされます。 名前変更操作後に列 C をクエリしてもエラーは返されず、クエリ結果は予期しないものになります。

構文

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

例

• テストデータの準備

  AnalyticDB for MySQL クラスターの特権アカウントを使用して、次の操作を実行します:

  1. user1 という名前のアカウントを作成します：

     CREATE USER user1 IDENTIFIED BY 'user1_pwd';

  2. adb_demo という名前のデータベースと、そのデータベース内に t1 という名前のテーブルを作成します。次のステートメントは、t1 テーブルを作成します：

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

     t1 テーブルにテストデータを挿入します：

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

• ビューの作成

  説明

  次の例では、t1 テーブルを基に異なるセキュリティ検証メソッドでビューを作成し、DEFINER と INVOKER における権限の違いを説明します。

  • v1 という名前のビューを作成し、SQL SECURITY を INVOKER に設定するには、次のステートメントを実行します：

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

  • v2 という名前のビューを作成し、SQL SECURITY を DEFINER に設定するには、次のステートメントを実行します：

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

  • v3 という名前のビューを SQL SECURITY を指定せずに作成する (この場合、システムはデフォルトで INVOKER を使用します) には、次のステートメントを実行します：

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

• 権限の比較

  • 特権アカウントを使用して、3 つのビューをクエリする権限のみを user1 に付与します：

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

    この場合、user1 は AnalyticDB for MySQL クラスターの adb_demo データベースに接続した後、v2 ビューのみをクエリできます。 クエリ文:

    SELECT * FROM v2;

    次の結果が返されます：

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

    ただし、user1 が v1 または v3 ビューをクエリすると、エラーが返されます。クエリステートメント：

    SELECT * FROM v1

    または

    SELECT * FROM v3

    どちらのステートメントも次のエラーを返します：

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

  • user1 に 3 つのビューをクエリする権限を付与した後、特権アカウントを使用して user1 に t1 テーブルをクエリする権限を付与します：

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

    この場合、user1 が AnalyticDB for MySQL クラスターの adb_demo データベースに接続すると、user1 はすべての v1、v2、および v3 ビューをクエリできます。 クエリ文:

    SELECT * FROM v1;

    または

    SELECT * FROM v2;

    または

    SELECT * FROM v3;

    3 つのクエリステートメントはすべて同じ結果を返します：

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

よくある質問

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;

ベストプラクティス

詳細については、「ビューを使用した権限管理」をご参照ください。