本文介绍如何使用CREATE VIEW语法创建视图。

语法

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;
参数 是否必填 说明
OR REPLACE 选填 根据是否存在重名视图,选择对应的规则来创建视图。具体规则如下:
  • 若不存在重名物化视图,AnalyticDB MySQL版会直接创建一个新视图。
  • 若存在重名物化视图,AnalyticDB MySQL版会先删除原有的重名物化视图,再重新创建。
说明 若未设置该参数,当新建的视图名称与已存在视图重名时,会创建失败。
[SQL SECURITY] 定义查询视图的数据时的安全验证方式,支持如下取值:
  • INVOKER:表示按照INVOKER(调用者)的身份来执行查询SQL。
    该安全验证方式下,系统会在用户查询视图数据时,验证用户是否拥有如下权限:
    • 查询视图的权限。
    • 查询视图所引用的对象的权限。
    仅当用户同时拥有上述权限时,才能查询视图的数据。
  • DEFINER:表示按照DEFINER(定义者)的身份来执行查询SQL。

    该安全验证方式下,只要用户拥有查询视图的权限,就可以查询视图的数据,而无需拥有视图所引用的对象的权限。

说明
  • 若未设置该参数,AnalyticDB MySQL版默认使用INVOKER安全验证方式,即用户查询视图数据时,需要同时拥有查询视图的权限和查询视图所引用对象的权限。
  • 需为仅V3.1.4.0或以上版本的AnalyticDB MySQL版集群支持该配置。

    如何查看集群版本,请参见查看版本

    如需升级版本,请提交工单联系技术支持。

view_name 必填 视图的名字。
说明 您也可以在视图名字前加上数据库名称来定义该视图所属的数据库,例如adb_demo.view。若不添加,默认该视图属于当前数据库。
select_statement 视图中的数据来源。

示例

  • 数据准备

    通过AnalyticDB MySQL版高权限账号执行如下操作:

    1. 创建账号user1,语句如下:
      CREATE USER user1 IDENTIFIED BY 'user1_pwd';
    2. 已创建数据库adb_demo,并在库中创建表t1,建表语句如下:
      Create Table `t1` (
       `id` bigint AUTO_INCREMENT,
       `id_province` bigint NOT NULL,
       `user_info` varchar,
       primary key (`id`)
      ) DISTRIBUTE 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 SECURITYINVOKER,语句如下:
      CREATE SQL SECURITY INVOKER VIEW v1 
        AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
    • 创建视图v2时,设置SQL SECURITYDEFINER,语句如下:
      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;
  • 权限对比
    • 仅通过高权限账号授予user1查询3个视图的权限,语句如下:
      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 MySQL版集群的adb_demo数据库后,user1仅能查询视图v2数据。查询语句如下:
      SELECT * FROM v2
      查询结果如下:
      +-------------+-----------+
      | ID_PROVINCE | USER_INFO |
      +-------------+-----------+
      |           1 | Tom       |
      |           1 | Jerry     |
      +-------------+-----------+
      而通过SELECT语句查询v1v3视图数据时则会报错。查询语句如下:
      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 MySQL版集群的adb_demo数据库后,user1账号能够查询全部视图v1v2v3的数据,查询语句如下:
      SELECT * FROM v1
      SELECT * FROM v2
      SELECT * FROM v3
      执行上述3条查询语句均会返回相同的结果,结果如下:
      +-------------+-----------+
      | ID_PROVINCE | USER_INFO |
      +-------------+-----------+
      |           1 | Tom       |
      |           1 | Jerry     |
      +-------------+-----------+

最佳实践

更多详情,请参见通过视图管控数据权限