Practical Use of MaxCompute Metadata: Data Permission Statistics

By Haiqing

Overview

The tenant-level Information Schema of MaxCompute provides project metadata and historical data from the perspective of tenants. You can pull a type of metadata from all your projects in the same metadata center at a time for statistical analysis. We are launching a series of articles about metadata usage practices.

This article introduces how to conduct permission statistics by using metadata-related permission views.

If you haven't used the tenant-level Information Schema, read the background information, feature introduction, fees, usage limits, and precautions written in Tenant-level Information Schema in advance to avoid unnecessary problems.

Cleanup of Member Permission Statistics

Scenario

Clearance of a resigned employee’s account:

When an employee who had a RAM account resigns, that RAM account must be canceled. Before canceling, it is necessary to clear the corresponding data permissions to avoid leaving residual data in MaxCompute's permission metadata, which could interfere with permission audits in other scenarios. It is essential to identify the specific permissions and projects associated with the RAM user.

Solution

Locate the project permissions associated with the RAM user account you intend to cancel. Remove the RAM user from the projects and completely clear the permissions. It is important to note that to remove a user from a project, you must first revoke the user's roles in the project.

• Identify which role permissions the user has in which projects and proceed to clear these role permissions. Use the following metadata query to find out the user's role permissions across projects: system_catalog.information_schema.user_roles metadata

select * from system_catalog.information_schema.user_roles where user_name='RAM$mc_schema@test.aliyunid.com:hq_schema';

There are two methods to remove a user from roles:

o Remove by command. The command is as follows:

use proejct_name; -- Enter the project to which the roles belong.
revoke <role_name> from <user_name>;

o Remove through the console. Log on to the Console > Project Management > Management > Role Permissions. Go to the Members page to remove the user from the roles.

• Check which projects the user is a member of, cancel the user account, and then completely clear the legacy authorization data. Use the following metadata to see which projects the user is a member of: system_catalog.information_schema.users

select * from system_catalog.information_schema.users where user_name='RAM$xxxx:xxxx';
-- Based on the results found earlier, enter the corresponding projects to remove the user and clear permissions. 
use project_name;
remove user RAM$xxxx:xxxx;
purge privs from user RAM$xxxx:xxxx;-- Completely clear the authorization data such as ACLs, policies, and labels.

Rationality Audit of Management Permission Authorization

The super administrator of a project has the same permissions as the project owner, which means the authority is very significant. The admin role also has all data query permissions and partial management permissions, which is also a large authority. Therefore, it is recommended to regularly audit the authorization of these two management roles. You can use the following metadata to view the authorization: system_catalog.information_schema.user_roles

select * from system_catalog.information_schema.user_roles where role_name in ('super_administrator','admin');

If any user_role_catalog in the result is null, it indicates a tenant-level role. You can judge the rationality of the authorization given to users of these two roles based on the result.

Audit of Important Data Permissions

Scenario

Access to core table data of businesses requires strict authorization, and it is recommended to conduct regular permission audits. For example, the table1 data of projec_a is sensitive, and it is necessary to audit which members have permissions to query data, download data, update data, and delete the table. You can use the following metadata for statistics: system_catalog.information_schema.table_privileges

SELECT  *
FROM    system_catalog.information_schema.TABLE_PRIVILEGES
WHERE   table_catalog = 'projec_a'
AND     table_name = 'table1'
AND     privilege_type in ('all','select','update','download');

If the privilege_type parameter is all, it means that all permissions are given to the table. Therefore, do not overlook this parameter.

Summary

The above are just a few common scenarios. There are more permission-related metadata tables available under system_catalog.information_schema. You can select tables according to your own scenario for querying.

Community

Practical Use of MaxCompute Metadata: Data Permission Statistics

Overview

Cleanup of Member Permission Statistics

Scenario

Solution

Rationality Audit of Management Permission Authorization

Audit of Important Data Permissions

Scenario

Summary

Read previous post:

Read next post:

Alibaba Cloud MaxCompute

You may also like

Comments

Alibaba Cloud MaxCompute

Related Products

Big Data Consulting for Data Technology Solution

MaxCompute

Big Data Consulting Services for Retail Solution

Digital Marketing Solution