MaxCompute:Authorization best practices

Use case:

Grant a group of project members permissions to create tables, upload resources, create functions, and run tasks. Access is restricted to read-only for tables whose names start with the prefix t_app_.

Use case: Some tables are critical to the business and must be protected from accidental deletion. The goal is to prevent specific users from deleting any table whose name starts with the prefix tb_.

Procedure:

Use case:

A business analyst needs to view data in production tables without seeing the production task code. This analyst needs access to a specific subset of tables from multiple production projects.

Solution:

Because the analyst must not see production tasks, create a separate project for analysis. In each production project, create a package, add the tables to share to the package, and then allow the analysis project to install it. After the package is installed in the analysis project, grant permissions to the analyst. This approach reduces management overhead by removing the need to add the analyst to every production project, and it ensures that the analyst can only view the shared production tables from within the analysis project.

This example demonstrates the workflow: a package named Package_test is created in a production project (Project_a). A production table (table_a) is added to this package. The package is then installed in an analysis project (Project_analyze), and a user (A*****@aliyunid.com) is granted permission to query the table through the package.

Procedure:

1. In the production project, create a package.

   USE Project_a;
   CREATE PACKAGE Package_test;

2. In the production project, add the resource to share to the package.

   ADD table table_a TO PACKAGE Package_test;

3. In the production project, allow the analysis project to install the package.

   ALLOW PROJECT Project_analyze TO INSTALL PACKAGE Package_test;

4. In the analysis project, install the package.

   USE Project_analyze;
   INSTALL PACKAGE Project_a.Package_test;

5. Grant the analyst read permission on the package.

   GRANT read on package Package_test TO USER ALIYUN$A*****@aliyunid.com;

6. The analyst can now query data from the table.

   select * from Project_a.table_a;

Procedure:

1. In Project_a, use Alibaba Cloud Account A to create a package named a_to_b_pkg and add tables t1, t2, and t3 to the package.

   USE Project_a;
   CREATE PACKAGE a_to_b_pkg; 
   ADD table t1 TO PACKAGE a_to_b_pkg;
   ADD table t2 TO PACKAGE a_to_b_pkg;
   ADD table t3 TO PACKAGE a_to_b_pkg;

2. Allow project Project_b (owned by Account B) to install the a_to_b_pkg package.

   ALLOW PROJECT Project_b TO INSTALL PACKAGE a_to_b_pkg;

3. In project Project_b, use Alibaba Cloud Account B to install the a_to_b_pkg package.

   INSTALL PACKAGE Project_a.a_to_b_pkg;

4. Grant the RAM users from Account B permissions on the tables in the package.

   add user ram$B:b_user1; 
   add user ram$B:b_user2; 
   GRANT Describe , Select ON TABLE t1 TO USER ram$B:b_user1 PRIVILEGEPROPERTIES ("refobject" = "true", "refproject"="project_a", "package"="project_a.a_to_b_pkg");--Grant read permissions on table t1 to b_user1
   GRANT Describe , Select ON TABLE t2 TO USER ram$B:b_user1 PRIVILEGEPROPERTIES ("refobject" = "true", "refproject"="project_a", "package"="project_a.a_to_b_pkg");--Grant read permissions on table t2 to b_user1
   GRANT Describe , Select ON TABLE t3 TO USER ram$B:b_user2 PRIVILEGEPROPERTIES ("refobject" = "true", "refproject"="project_a", "package"="project_a.a_to_b_pkg");--Grant read permissions on table t3 to b_user2

Use case:

A table named table_order in project ProjectA contains order information for all merchants. This table must be shared with the merchants, but each merchant must only be able to see the orders corresponding to their own store.

Solution:

The table_order table contains a merchant ID column that can be used to filter data. While MaxCompute does not directly support row-level access control, implement this by creating a separate view for each merchant based on the table_order table. Then grant each merchant permissions to their corresponding view by using a package. This method effectively achieves row-level access control.

Procedure:

1. In ProjectA, create a dedicated view for each merchant.

   CREATE VIEW <viewname> as select * from table_order WHERE sellerid='xxxx';

   <viewname> is the name of the view to create.

2. In ProjectA, create a package and share the view with the merchant.

   create package <packagename>;
   add table <viewname> to package <packagename>;
   allow project <Projectname_seller> to install package <packagename>;

   <packagename> is the name of the package. <Projectname_seller> is the name of the merchant's MaxCompute project.

3. The merchant installs the package in their project and queries the view.

   USE <Projectname_seller>;
   install package <ProjectA>.<packagename>;
   grant read on package <ProjectA>.<packagename> to user <username>;
   select * from <ProjectA>.<viewname>;

   <username> is the user in the merchant's project who needs to query the view.