Implement row-level access control - MaxCompute - Alibaba Cloud Documentation Center

MaxCompute allows you to implement row-level access control by creating views. This topic describes the specific implementation method with practical examples.

Background information

MaxCompute has introduced a row-level access control feature that is suitable for defining rules for a large number of users. This method eliminates the need to create shared objects for each individual user, and avoids redundant storage of such objects. However, this method may result in a more complex query plan based on the original table and comes with certain limitations. For more information, see Row-level access control.

If you need to implement row-level access control for different users with their own filtering rules, you can achieve this by creating views or tables and sharing them accordingly. This method supports computation based on pre-filtered data and offers a more straightforward and intuitive operation.

Scenario

In this example, the table_order table in Project A is an order transaction information table for all merchants. This table can be accessed by all merchants. However, each merchant can view only the order transaction information of its own store.

Solution

The table_order table contains merchant IDs (sellerid). Data can be filtered based on merchant IDs. Row-level access control is required to ensure that each merchant can read only its own data. You can use one of the following methods to perform row-level permission control:

Method 1: Create a separate view for each merchant based on the table_order table, and grant permissions on the view to the related merchant. This method is recommended. This method can meet the requirements of row-level permission control.
Method 2: Create a separate table for each merchant based on the table_order table, and grant permissions on the table to the related merchant. This method can meet the requirements of row-level access control. However, this method causes data to be repeatedly stored. In addition, to ensure data consistency, you must make sure that the tables are updated in real time with the table_order table.

Procedure

If you want to create a view on the MaxCompute client to perform row-level permission control, perform the following steps:

Log on to the MaxCompute client by using an Alibaba Cloud account. For more information, see Install and start the MaxCompute client.
Create a view for a specified merchant in Project A. Sample statement:
```
create view <view_name> as select * from table_order WHERE sellerid='xxxx';
```
For more information about how to create a view, see View-related operations.

Create a package in Project A and grant the merchant the permissions on the view by using the package. Sample statements:

-- Create a package. 
create package <package_name>;
-- Add the view that you created to the package. 
add table <view_name> to package <package_name>;
-- Use the package to share resources with merchants. 
allow project <project_name> to install package <package_name>;

For more information about how to share resources by using a package, see Cross-project resource access based on packages.

Install the package in the MaxCompute project of the merchant and grant the merchant the required permissions to use the view. Sample statements:

-- Install the package in the project of the merchant. 
install package <Project A>.<package_name>;
-- Grant the merchant the read permission on the package. 
grant read on package <Project A>.<package_name> to user <user_name>;

Note

This example shows how to grant view permissions by using a package. The owner of the MaxCompute project can also directly execute the following statements to grant users the SELECT and DESCRIBE permissions on views. You can select a method based on your business requirements.

grant select,describe on table <view_name> to user <user_name>;