Grant permissions to different users to access different data in a table using views - AnalyticDB

Views let you expose a filtered subset of a table to specific accounts without granting those accounts direct access to the underlying table. This makes views an effective mechanism for row-level access control: each account queries only the view it has permission on, and AnalyticDB for MySQL enforces the filter defined in the view.

How it works

When you create a view with SQL SECURITY DEFINER, the view runs with the permissions of its creator (the definer), not the querying account. This means:

Accounts granted SELECT on a view can query it without needing any permissions on the underlying table.
AnalyticDB for MySQL applies the view's WHERE clause automatically, so accounts see only the rows the view exposes.

This lets you isolate data across accounts by creating one view per data partition and granting each account access to its corresponding view.

Scenario

A customer table stores customer records from multiple provinces. The goal is to give user1 access only to Province 1 records (province_id=1) and user2 access only to Province 2 records (province_id=2).

Table definition:

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

Test data:

INSERT INTO customer (province_id, user_info)
VALUES (1, 'Tom'), (1, 'Jerry'), (2, 'Jerry'), (3, 'Mark');

The full table contains four rows across three provinces:

+---------------------+-------------+-----------+
| id                  | province_id | user_info |
+---------------------+-------------+-----------+
| 1369417242420617216 |           1 | Tom       |
| 1369417242424811520 |           1 | Jerry     |
| 1369417242424811522 |           3 | Mark      |
| 1369417242424811521 |           2 | Jerry     |
+---------------------+-------------+-----------+

Set up view-based access control

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL cluster with the adb_demo database
Admin privileges to create views and grant permissions
The user1 and user2 accounts already created (see CREATE USER)

Step 1: Create views for each province

Create one view per province. Each view filters the customer table to a specific province_id. The SQL SECURITY DEFINER clause makes the view execute with the creator's permissions, so accounts querying the view don't need direct access to customer.

-- View for Province 1: exposes only rows where province_id = 1
CREATE SQL SECURITY DEFINER VIEW v1 AS
  SELECT * FROM customer WHERE province_id = 1;

-- View for Province 2: exposes only rows where province_id = 2
CREATE SQL SECURITY DEFINER VIEW v2 AS
  SELECT * FROM customer WHERE province_id = 2;

For the full CREATE VIEW syntax and parameters, see CREATE VIEW.

Step 2: Grant each account access to its view

Grant SELECT on the view rather than on the underlying table. This way, each account can only read the rows exposed by its assigned view, and the underlying customer table remains inaccessible to both accounts.

-- user1 can query v1 (Province 1 data only).
-- Granting on the view, not on customer, prevents user1 from accessing other provinces.
GRANT SELECT ON v1 TO user1;

-- user2 can query v2 (Province 2 data only).
-- Granting on the view, not on customer, prevents user2 from accessing other provinces.
GRANT SELECT ON v2 TO user2;

Verify the result

Connect to the adb_demo database as each account and run a SELECT on the assigned view. The following example shows both the permitted query and the denied query for each account.

As user1:

-- user1 has SELECT on v1. This query returns Province 1 rows only.
SELECT * FROM v1;

+---------------------+-------------+-----------+
| ID                  | PROVINCE_ID | USER_INFO |
+---------------------+-------------+-----------+
| 1369417242420617216 |           1 | Tom       |
| 1369417242424811520 |           1 | Jerry     |
+---------------------+-------------+-----------+

-- user1 has no permission on v2. This query is denied.
SELECT * FROM v2;

ERROR 1815 (HY000): [9001, 2021083114191719216818804803453965343] : Access Denied

As user2:

-- user2 has SELECT on v2. This query returns Province 2 rows only.
SELECT * FROM v2;

+---------------------+-------------+-----------+
| ID                  | PROVINCE_ID | USER_INFO |
+---------------------+-------------+-----------+
| 1369417242424811521 |           2 | Jerry     |
+---------------------+-------------+-----------+

-- user2 has no permission on v1. This query is denied.
SELECT * FROM v1;

ERROR 1815 (HY000): [9001, 2021083114191719216818804803453965343] : Access Denied

What's next

Scale this pattern to additional provinces by creating a view per province_id value and granting the corresponding account SELECT on that view.
To revoke access, run REVOKE SELECT ON <view_name> FROM <account_name>.
For a full reference of view creation options, see CREATE VIEW.