AnalyticDB:CREATE VIEW - AnalyticDB

A view is a virtual table built from the result of a query on one or more base tables. It stores no actual data. Querying a view executes the underlying SELECT statement and returns the result set.

Views serve two purposes: simplifying complex queries and restricting data access through controlled permissions.

Syntax

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

Parameters

Parameter	Required	Description	Default
`OR REPLACE`	No	If a view with the same name exists, drops it and creates a new one. Without this option, the statement fails if a same-name view already exists.	No replacement
`SQL SECURITY`	No	Determines whose privileges are checked when a user queries the view. Valid values: `INVOKER` or `DEFINER`. See SQL SECURITY.	`INVOKER`
`view_name`	Yes	The name of the view. Optionally prefix with the database name: `database_name.view_name`. If no database is specified, the view is created in the current database.	—
`select_statement`	Yes	The `SELECT` statement that defines the view's data source.	—

SQL SECURITY

SQL SECURITY controls which user's privileges are checked when data is queried from a view.

	`INVOKER` (default)	`DEFINER`
Privilege check on the view itself	Invoker must have `SELECT` on the view	Invoker must have `SELECT` on the view
Privilege check on the underlying objects	Invoker must have `SELECT` on the base tables	Definer must have `SELECT` on the base tables
Effect of revoking definer's access	Not applicable	Queries from all invokers fail, even if those invokers have view-level access

When to use each:

Use INVOKER when each user should only see data they are already authorized to access at the table level.
Use DEFINER when you want to grant access to specific derived data without exposing the underlying tables — for example, sharing a filtered view of a sensitive table with an account that has no table-level privileges.

Note

The SQL SECURITY option requires AnalyticDB for MySQL V3.1.4.0 or later. To check your cluster's minor version (Data Lakehouse Edition), run SELECT adb_version();. For more information about how to query the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster? To upgrade, contact technical support.

MySQL compatibility notes

V3.1.9.0 and later

AnalyticDB for MySQL V3.1.9.0 and later is compatible with MySQL's default SELECT * behavior: when you create a view with SELECT *, the cluster expands the asterisk into explicit column names at parse time. This means adding or removing a column from the base table does not invalidate the view.

Edge case: If you rename Column C to Column D after creating a view that references Column C, the cluster reports an error when the view is queried — even if Column C is not used in the query result. This happens because column resolution occurs at the parsing stage, before any optimization that might eliminate unused columns. This is the expected MySQL-compatible behavior.

Versions earlier than 3.1.9.0

The cluster does not expand SELECT * at creation time. Instead, it tracks only the number of columns. If a column is added to or removed from the base table, querying the view returns:

View '<view_name>' is stale; it must be re-created

Disable MySQL-compatible behavior

To revert to the pre-3.1.9.0 behavior, use either of these approaches:

For a single view — add a hint to the CREATE VIEW statement:

/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
CREATE VIEW v0
AS
SELECT * FROM base0;

For all views in the cluster — set a global parameter:

SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;

Note

Use AnalyticDB for MySQL V3.1.9.0 or later to create views. This prevents unexpected issues caused by SELECT *, such as ambiguous semantics and errors.

Examples

Prepare test data

Run the following statements using the privileged account of your AnalyticDB for MySQL cluster.

Create a user named user1:

CREATE USER user1 IDENTIFIED BY 'user1_pwd';

Create a table named t1 in the adb_demo database:

CREATE TABLE `t1` (
  `id`          bigint AUTO_INCREMENT,
  `id_province` bigint NOT NULL,
  `user_info`   varchar,
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(`id`);

Insert test data:

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

Create views with different SQL SECURITY settings

The following examples create three views over t1, each with a different security setting.

-- v1: INVOKER — the querying user must have access to both the view and t1
CREATE SQL SECURITY INVOKER VIEW v1
AS SELECT id_province, user_info FROM t1 WHERE id_province = 1;

-- v2: DEFINER — the querying user only needs access to the view;
--     the privileged account (definer) provides access to t1
CREATE SQL SECURITY DEFINER VIEW v2
AS SELECT id_province, user_info FROM t1 WHERE id_province = 1;

-- v3: no SQL SECURITY specified — defaults to INVOKER
CREATE VIEW v3
AS SELECT id_province, user_info FROM t1 WHERE id_province = 1;

Compare permissions

Grant view-level access only (no table access)

Using the privileged account, grant user1 the right to query all three views:

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

When user1 connects to adb_demo and runs:

SELECT * FROM v2;

The query succeeds because v2 uses DEFINER — the privileged account's access to t1 satisfies the privilege check:

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

Querying v1 or v3 fails because both use INVOKER, and user1 does not have SELECT on t1:

SELECT * FROM v1;
-- or
SELECT * FROM v3;

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

Grant table-level access

After granting user1 access to t1:

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

All three views can now be queried successfully, returning the same result:

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

FAQ

Column names defined in lowercase appear in uppercase in view results

By default, AnalyticDB for MySQL displays column names in view result sets in uppercase. To preserve lowercase column names, run:

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;