MaxCompute:Grant permissions on federated external tables

Grant permissions on federated external tables

The creator of an External Schema becomes its owner. Use this topic to grant permissions on an External Schema or its tables to other RAM users.

All examples in this topic use the project test_lakehouse_project.

Permissions reference

The table below shows the permission required for each External Schema operation. Grant permissions using the GRANT command as shown in each section.

Create an External Schema

Required permissions: CreateSchema on the project. If Tenant-based Object Authorization is enabled, also Usage on the foreign server.

1. Set the current project and grant the CreateSchema permission.

   -- Set the current project.
   use test_lakehouse_project;
   
   -- Grant the CreateSchema permission on the project to a RAM user.
   GRANT CreateSchema ON project test_lakehouse_project TO USER RAM$xxx@test.aliyunid.com:test_user;

2. (Optional) If Tenant-based Object Authorization is enabled and you have mounted an external data source on the project, grant the RAM user permission to use the foreign server.

   -- Add the RAM account provider to the current project.
   ADD accountprovider ram;
   
   -- Add a RAM user.
   ADD USER `RAM$xxx@test.aliyunid.com:test_user`;
   
   -- Create a project role.
   CREATE role test_lakhouse_role;
   
   -- Add an access policy to the role to allow usage of the external data source.
   put policy D:\bin\allow.policy ON role test_lakhouse_role;
   
   -- Grant the role to the RAM user.
   GRANT role test_lakhouse_role TO `RAM$xxx@test.aliyunid.com:test_user`;

   The allow.policy file must contain the following:

   {
     "Version": "1",
     "Statement": [{
       "Action": "odps:Usage",
       "Effect": "Allow",
       "Resource": ["acs:odps:*:servers/fs_hive"]
     }]
   }

3. Create the External Schema.

   CREATE EXTERNAL SCHEMA IF NOT EXISTS es_hive3
   with fs_hive
   ON 'default' ;

List all External Schemas

Required permissions: CreateInstance and List on the project.

1. Grant the CreateInstance permission on the project.

   GRANT CreateInstance ON project test_lakehouse_project TO USER RAM$xxx@test.aliyunid.com:test_user;

2. Grant the List permission on the project.

   GRANT List ON project test_lakehouse_project TO USER RAM$xxx@test.aliyunid.com:test_user;

3. List all schemas.

   SHOW schemas;

Describe an External Schema

Required permissions: Describe on the schema.

1. Grant the Describe permission on the schema.

   GRANT DESCRIBE ON SCHEMA es_hive3 TO USER RAM$xxx@test.aliyunid.com:test_user;

2. Describe the External Schema.

   DESC SCHEMA es_hive3;

Modify External Schema properties

Modifying External Schema properties is not supported.

Drop an External Schema

Required permissions: Drop on the schema.

1. Grant the Drop permission on the schema.

   GRANT DROP ON SCHEMA es_hive3 TO USER RAM$xxx@test.aliyunid.com:test_user;

2. Drop the External Schema.

   DROP SCHEMA es_hive3;

Use an External Schema

The required permission depends on how the project was created:

• Three-tier model (native): No permission required.

• Upgraded from two-tier to three-tier model: Describe on the schema is required.

Set the current schema to the External Schema.

USE SCHEMA es_hive3;

If the project was upgraded from the two-tier model, grant the Describe permission before running USE SCHEMA.

GRANT DESCRIBE ON SCHEMA es_hive3 TO USER RAM$xxx@test.aliyunid.com:test_user;

List tables in an External Schema

Required permissions: List on the schema.

1. Grant the List permission on the schema.

   GRANT List ON schema es_hive3 TO USER RAM$xxx@test.aliyunid.com:test_user;

2. List the tables in the External Schema.

   SHOW tables IN es_hive3;

Query a table in an External Schema

Required permissions: Select on the table.

1. Grant the Select permission on the table.

   GRANT SELECT ON TABLE es_hive3.t1 TO USER RAM$xxx@test.aliyunid.com:test_user;

2. Query the table.

   SELECT * FROM es_hive3.t1;

Import data from an External Schema table

Required permissions: Select on the source table; CreateTable and CreateInstance on the project.

1. Grant the CreateTable permission on the project.

   GRANT CreateTable ON project test_lakehouse_project TO USER RAM$xxx@test.aliyunid.com:test_user;

2. Grant the CreateInstance permission on the project.

   GRANT CreateInstance ON project test_lakehouse_project TO USER RAM$xxx@test.aliyunid.com:test_user;

3. Grant the Select permission on the source table.

   GRANT SELECT ON TABLE es_hive3.t1 TO USER RAM$xxx@test.aliyunid.com:test_user;

4. Import data from the External Schema table into a new managed table.

   CREATE TABLE default.t1_copy_ram3 AS SELECT * FROM t1;