This topic describes how to execute the CREATE VIEW statement to create a view.

Syntax

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;
Option Required Description
OR REPLACE No Creates a view based on whether an existing view of the same name already exists. The following section describes the rules:
  • If no view of the same name is found, AnalyticDB for MySQL creates a view.
  • If a view of the same name already exists, AnalyticDB for MySQL deletes the existing view and creates a view.
Note If this option is not specified, the view cannot be created when an existing view of the same name already exists.
[SQL SECURITY] Defines the method for security authentication when you query data in a view. Valid values:
  • INVOKER: execute an SQL query statement by using the permissions of the INVOKER.
    This authentication method allows the system to check whether a user has the following permissions when the user queries data in a view:
    • Permissions to query views
    • Permissions to query the objects that are referenced by views
    Only a user that has the preceding permissions can query data in a view.
  • DEFINER: execute an SQL query statement by using the permissions of the DEFINER.

    This authentication method allows a user that has the permissions to query views to query data in a view. The user does not need to have the permissions to query the objects that are referenced by views.

Note
  • If this option is not specified, INVOKER is used as the default authentication method in AnalyticDB for MySQL. This indicates that when a user queries data in a view, the user must have the permissions to query views and the objects that are referenced by views.
  • This option is available for AnalyticDB for MySQL clusters of V3.1.4.0 or later.

    For information about how to view the version of an AnalyticDB for MySQL cluster, see How can I view the version of an AnalyticDB for MySQL cluster?

    To upgrade the version,submit a ticket.

view_name Yes Specifies the name of the view.
Note When you name a view, you can also add the name of the database that contains the view before the name of the view. Example: adb_demo.view. By default, if you do not specify a database in which the view is created, the view is created in the current database.
select_statement Specifies the data source of the view.

Example

  • Prepare data

    Perform the following operations by using a privileged account of an AnalyticDB for MySQL cluster:

    1. Create an account named user1. Sample statement:
      CREATE USER user1 IDENTIFIED BY 'user1_pwd';
    2. Create a table named t1 in an existing database named adb_demo. Sample statement:
      Create Table `t1` (
       `id` bigint AUTO_INCREMENT,
       `id_province` bigint NOT NULL,
       `user_info` varchar,
       primary key (`id`)
      ) DISTRIBUTE BY HASH(`id`);
      Insert the test data into the t1 table. Sample statement:
      INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');
  • Create views
    Note This example shows how to specify different authentication methods for a view when the view is created. The data in the views comes from the t1 table.
    • When you create a view named v1, set SQL SECURITY to INVOKER. Sample statements:
      CREATE SQL SECURITY INVOKER VIEW v1 
        AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
    • When you create a view named v2, set SQL SECURITY to DEFINER. Sample statements:
      CREATE SQL SECURITY DEFINER VIEW v2 
        AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
    • When you create a view named v3, do not specify the SQL SECURITY option. In this example, INVOKER is used. Sample statements:
      CREATE VIEW v3 
        AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
  • Compare permissions
    • Use the privileged account to grant the user1 account the permissions to query the three views. Sample statements:
      GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
      GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
      GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';
      In this case, after you use the user1 account to connect to the adb_demo database of the AnalyticDB for MySQL cluster, you can use the user1 account to query only the data in the v2 view. Sample statement:
      SELECT * FROM v2
      The following result is returned:
      +-------------+-----------+
      | ID_PROVINCE | USER_INFO |
      +-------------+-----------+
      |           1 | Tom       |
      |           1 | Jerry     |
      +-------------+-----------+
      When you query the data in the v1 or v3 view by using the SELECT statement, an error is returned. Sample statement:
      SELECT * FROM v1
      or
      SELECT * FROM v3
      The following error message is returned when you execute one of the preceding two SELECT statements:
      ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view
    • After the user1 account is granted the permissions to query the three views, use the privileged account to grant the user1 account the permissions to query the t1 table. Sample statement:
      GRANT SELECT ON adb_demo.t1 to user1@'%';
      In this case, after you use the user1 account to connect to the adb_demo database of the AnalyticDB for MySQL cluster, you can use the user1 account to query the data in the v1, v2, and v3 views. Sample statement:
      SELECT * FROM v1
      or
      SELECT * FROM v2
      or
      SELECT * FROM v3
      The following result is returned when you execute one of the preceding three SELECT statements:
      +-------------+-----------+
      | ID_PROVINCE | USER_INFO |
      +-------------+-----------+
      |           1 | Tom       |
      |           1 | Jerry     |
      +-------------+-----------+

Best practices

For more information, see Permission management by using views.