Data Lake Analytics Account and Permission System

Data lake is currently a popular concept in the big data industry, in which data is stored in its natural format, typically as object blobs or files. With a data lake, you can implement big data association analysis across various heterogeneous data sources without any pre-processing, such as ETL and data migration. This significantly reduces costs and improves the user experience. Alibaba Cloud Data Lake Analytics is an interactive analytics service that utilizes serverless architecture. As a ready-to-use service, DLA does not require any ETL tools

The following figure shows the simple architecture of DLA (__MPP computing engine + storage and computing separation + high flexibility and availability + heterogeneous dataset source) __:

Self-Created Accounts

DLA provides external services using MySQL protocol. You need to connect to the DLA service through JDBC. DLA's internal accounts and passwords are self-created (unlike RAM). When you activate the DLA service, an account and password are sent to you by internal message, email, SMS, or another method.

You may wonder why DLA implements authentication and authorization through self-created accounts instead of RAM or AK accounts. The following provides a simple explanation:

DLA is a database. When it establishes a connection on the client (authentication required) or accesses libraries, tables, and fields (authorization required), it makes many cross-services requests to the RAM system. This puts a lot of pressure on the RAM system and can cause the DLA service to lag.
DLA needs to be compatible with the MySQL permissions model, but it is difficult to perfectly map domain objects, such as libraries, tables, and fields, to the RAM system. In addition, the permissions for resource objects in the RAM system can be rough or detailed, and tend to be permissions for data lifecycle management, rather than the detailed permissions at the level of data.
All common logic that users are accustomed to, such as Grant, Revoke, and Show grants, is mapped directly to libraries, tables, and fields in traditional databases.
The designs of other database products on Alibaba Cloud and other cloud service platforms in the industry must take the same thing into account.

Relationship between the DLA account system and RAM account system:

Three Account Models

Root/Service accounts: When the RAM master account activates the DLA service in a region, the system automatically creates the first DLA account and notifies the RAM master account by internal message, SMS, and email. There is only one root account, which cannot be deleted.
User accounts/sub-accounts: The RAM master account (not the root account) creates DLA user accounts in the console. Cloud account users can create and delete user accounts and change their passwords.
Product accounts: When another cloud product (such as DBS) interacts with DLA, a user authorizes the cloud product in the RAM system. DLA automatically generates an account and dispatches it to the cloud product.
Root accounts and user accounts: All associated RAM UIDs correspond to cloud accounts. Therefore, root accounts and user accounts can access all the resources in the cloud account (within their authorized scopes).

Account Tests

Activate and Initialize the Service

Locate the service:

Purchase:

Service activation is complete. Click to enter the console:

DLA, as a service, can be activated in different regions. For demonstration purposes, we use Shanghai region as an example

Click 'Activate Service', and have your DLA account automatically created.

Once the service is successfully activated in a given region, you can find your credentials in the Alibaba Cloud webmail.

The initialization is completed and you obtain a root account:

Return to the homepage: https://openanalytics.console.aliyun.com/overview Set the service access point:

Configure a service access point.

Default value 0.0.0.0/0 indicates that all clients are authorized to access. If you want to activate DLA service within a VPC, please click "Create VPC Network".

Notice that this is your JDBC connectivity information (with hostname and port number), please secure the information, and be careful when sharing this with others.

Connect to the Database and Pass Authentication

Connect to the DLA service and enter the service.

##Connect to the DLA service. You can find the account and password are in your inbox, and the service access point is on the service page.
[root]# mysql -u<your DLA username> -p<your DLA password> -h<your DLA service access point> -P10000

##Enter the DLA service and start testing.
mysql> show databases;
Empty set (0.09 sec)

You have now activated the service. Authentication and connection are successful.

Create a Sub-Account and Connect to the Database

Click here to open the admin page where you can manage your accounts.

Connect to the DLA service and enter the service:

Permission Model

DLA provides a two-layer authentication mechanism to ensure the security of your data:

Authentication and authorization at the DLA layer are defined and implemented based on MySQL syntax (Grant: https://dev.mysql.com/doc/refman/5.7/en/grant.html Revoke: https://dev.mysql.com/doc/refman/5.7/en/revoke.html Show Grants: https://dev.mysql.com/doc/refman/5.7/en/show-grants.html )
Authentication and authorization at the data source and RAM layer are implemented based on RAM access control (OSS, TableStore, and other cloud-native products): https://help.aliyun.com/product/28625.html or authentication based on data sources (such as RDS, which is a self-created account and therefore has a self-created permission system)
The user's SQL is sent to DLA for authentication and then forwarded to the back-end data source layer for RAM and data source authentication. Then, the user data can be accessed.

DLA-Layer Authentication Principles

DLA implements authentication based on the scope of users' operation objects from large to small, that is, from the global level (global permissions) to the schema level, to the table level, and finally to the column level (currently not supported). Authentication is successful only if it is successfully at all layers.

DLA-Layer Authorization Method

Basically, DLA-layer authorization is implemented with reference to MySQL's Grant/Revoke/Show Grants syntax:

##grant-related
GRANT {SELECT | SHOW | ALTER | DROP | CREATE | INSERT | UPDATE | DELETE | GRANT OPTION | ALL | ALL PRIVILEGES | USAGE }
ON {* | *.* | xxDb.* | xxDb.yyTable | yyTable }
TO { oa_1234546 | 'oa_123456' | 'oa_123456'@'1.2.3.4'}
[with grant option]

##revoke-related
REVOKE {SELECT | SHOW | ALTER | DROP | CREATE | INSERT | UPDATE | DELETE |GRANT OPTION |  ALL | ALL PRIVILEGES | USAGE}
ON {* | *.* | xxDb.* | xxDb.yyTable  | yyTable }
FROM { oa_1234546 | 'oa_123456' | 'oa_123456'@'1.2.3.4'}

##show grants-related
SHOW GRANTS 
[for [ current_user | current_user() | oa_123456 | 'oa_123456' | 'oa_123456'@'localhost'] ]

Description of key information:

Authorization personnel:

Only the DLA root account can grant permissions to other non-root accounts.

Non-root accounts cannot grant permissions to other accounts.

Authorization and permission revocation are not supported across cloud accounts.

Privilege list:

Permissions can be separated by commas (,), for example, SELECT, DELETE,UPDATE,INSERT,...

ALL or ALL PRIVILEGES indicates that all permissions are granted or revoked, regardless of other permissions. Note that Grant option is not included in ALL and must be explicitly granted or revoked.

Currently, other types of authorization are not supported.

SELECT is the query authorization.

SHOW is the show and use command authorization (different from MySQL logic).

ALTER is the alter and other change-type DDL authorization.

CREATE is the creation-type DDL authorization.

DROP is the drop-type DDL authorization.

INSERT is the insert-type DML authorization.

UPDATE is the update-type DML authorization.

DELETE is the delete-type DML authorization.

GRANT OPTION is the DCL authorization (related to grant and revoke). GRANT OPTION can be specified in Privilege, or authorized through the "with grant option" syntax fragment.

USAGE is null.

In ResourceType:

* indicates that the current connection uses xxDB and grants library-level permissions to xxDB.

. indicates global-level permissions are granted to all tables in all libraries.

xxDb. * indicates that library-level permissions are granted to xxDB.

xxDb.yyTable indicates that table-level permissions are granted to xxTable in xxDB.

yyTable indicates that the current connection uses xxDB. Table-level permissions are granted to xxTable in xxDB.

Currently, field-level authorization is not supported.

Definition of authorized user:

Username: oa_123456

User string: 'oa _ 123456'

Even if the IP address and host information are written, they will not be used for whitelist authentication.

Only the root user under the same cloud account can show grants to others.

Show Grants cannot be performed across different UIDs.

You must have SHOW and GRANT permissions to perform Show Grants, or you must execute ; for yourself.

SHOW GRANTS FOR 'jeffrey'@'localhost': IP addresses are not currently supported.

Permission Mapping between DLA and RAM

Because sub-accounts in DLA do not map to those in RAM, resource permissions in RAM do not directly map to library and table permissions in DLA. In DLA, You need to map resources and isolate permissions in a specially defined manner.

Currently, authorization units in DLA are schema-level. If the root account grants permissions for a library to a user account, the user account can access all the tables in this library. This means that all the resources mapped from the library to RAM can be accessed. Such access is not controlled by RAM sub-account permissions.

For example, let's look at a typical library creation statement (assuming that you can create a library in DLA):

CREATE DATABASE db_name 
with dbproperties (
  CATALOG = 'ots',
  LOCATION = 'https://test-hangzhou.ots.aliyuncs.com',
  INSTANCE = 'test'
)

If the root account grants permissions to a user account, the user account can access this library:

grant all on db_name.* to xxx_s1519122757;

All the preceding processes assume that system role authorization has been completed for the cloud account. The next section describes how to implement system role authorization, which allows DLA to access your data in other cloud products.

System Role Authorization

System role authorization is the process by which users grant authorization to DLA. This allows DLA to access user data in cloud services for the purpose of DLA association analysis or ETL through DLA. The data then reflows to the user library. The process is as follows: https://www.alibabacloud.com/help/doc-detail/53478.htm

Cross-Account Access

DLA supports cross-account access, which allows user A on DLA to access user B's OSS data for analysis and computing.

Permission Test (Using Table Store as an Example)

Prepare a Table Store Library and Table

Go to the OTS home page https://ots.console.aliyun.com/index and create a DLA library and table used for analysis.

After you create the library, create a table.

Insert a row of data.

Activate the Table Store Service, and Grant Permissions to DLA Cloud Service Roles

For more information about access control and role authorization, see: https://www.alibabacloud.com/product/ram

Return to the DLA home page: https://openanalytics.console.aliyun.com/overview

Once the button (Table Store, OSS, etc.) is greyed out, the corresponding data source has been successfully authorized; blue, on the other hand, indicates that authorization is needed. Please click the button, to authorize DLA to access Table Store as a data source.

Click Agree to authorize roles:

This option allows DLA to access data stored Table Store with proper permissions.

Status after authorization:

Check that role authorization was successful:

You can enter the RAM system for more detailed authorization information.

Create a Library and Table in DLA and Associate Them with the TableStore Library and Table.

Connect to DLA over the JDBC protocol by using the DLA root account (oa_xxx ). (For the account information, DLA access point, and JDBC connection method, see the previous chapters.)

╰─○ mysql -u<your DLA root account> -p<password of your DLA root account> -h<your DLA-jdbc access point> -P10000 

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 194631
Server version: 5.6.40-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Create a library, and associate it with the instance in TableStore:

mysql> select user();
+----------------+
| user()         |
+----------------+
| oa_xxxxxxxxxxx |
+----------------+
1 row in set (0.08 sec)

mysql> show databases;                                                                                                                                                                                      Empty set (0.02 sec)

mysql> create database ots_account_test 
with dbproperties(
  catalog = 'ots',
  location = 'https://for-account-test.cn-shanghai.ots-internal.aliyuncs.com',
 instance = 'for-account-test'
) comment 'test account and privileges';
Query OK, 0 rows affected (0.10 sec)

mysql> show databases;
+------------------+
| Database         |
+------------------+
| ots_account_test |
+------------------+
1 row in set (0.01 sec)

mysql> show create database ots_account_test;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Database         | Create Database                                                                                                                                                                                                       |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ots_account_test | CREATE DATABASE `ots_account_test`
WITH DBPROPERTIES (
    catalog = 'ots',
    location = 'https://for-account-test.cn-shanghai.ots-internal.aliyuncs.com',
    instance = 'for-account-test'
)
COMMENT 'test account and privileges' |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Create a table, associate it with the table in TableStore, and query the data (the results are consistent with the results in OTS):

mysql> use ots_account_test;
Database changed

mysql> show tables;
Empty set (0.03 sec)

mysql> create external table account_test (
    -> pk1 int not null primary key,
    -> name varchar(20) 
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> show create table account_test;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                  |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| account_test | CREATE EXTERNAL TABLE `account_test` (
    `pk1` int NOT NULL COMMENT '',
    `name` varchar(20) NULL COMMENT '',
    PRIMARY KEY (`pk1`)
)
COMMENT '' |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> select * from account_test;
+------+--------------+
| pk1  | name         |
+------+--------------+
|  123 | account-test |
+------+--------------+
1 row in set (1.61 sec)

Data association and data query are complete.

Grant Library and Table Permissions to Sub-Accounts

All the preceding operations are performed using the root account. The root account can manage all cloud resources under the cloud account. The DLA user account can access its corresponding libraries and tables only after being grated permissions by the root account.

Switch to the user account/sub-account connection page, and you will not see any library or table:

mysql> select user();
+------------------------+
| user()                 |
+------------------------+
| test_sxxxxxxxxxxxxxxxx |
+------------------------+
1 row in set (0.14 sec)

mysql> show databases;
Empty set (0.02 sec)

mysql> show grants ;
+------------------------------------------------+
| Grants for test_sxxxxxxxxxxxxxxxx              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_sxxxxxxxxxxxxxxxx' |
+------------------------------------------------+
1 row in set (0.03 sec)

Switch to the root account connection page, and grant permissions to the user accounts/sub-accounts:

mysql> select user();
+------------------------+
| user()                 |
+------------------------+
| oa_xxxxxxxxxxx |
+------------------------+
1 row in set (0.14 sec)

mysql> show grants for test_sxxxxxxxxxxxxxxxx;
+---------------------------------------------------------------+
| Grants for test_sxxxxxxxxxxxxxxxx                             |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_sxxxxxxxxxxxxxxxx'                |
+---------------------------------------------------------------+
1 rows in set (0.02 sec)


mysql> grant all on ots_account_test.* to test_sxxxxxxxxxxxxxxxx;
Query OK, 0 rows affected (0.05 sec)


mysql> show grants for test_sxxxxxxxxxxxxxxxx;
+---------------------------------------------------------------+
| Grants for test_sxxxxxxxxxxxxxxxx                             |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_sxxxxxxxxxxxxxxxx'                |
| GRANT ALL ON `ots_account_test`.* TO 'test_sxxxxxxxxxxxxxxxx' |
+---------------------------------------------------------------+
2 rows in set (0.03 sec)

Switch to the user account connection page and check that the accounts have been granted permissions and can read the data.

mysql> select user();
+------------------------+
| user()                 |
+------------------------+
| test_sxxxxxxxxxxxxxxxx |
+------------------------+
1 row in set (0.14 sec)


mysql> show grants ;
+---------------------------------------------------------------+
| Grants for test_sxxxxxxxxxxxxxxxx                             |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_sxxxxxxxxxxxxxxxx'                |
| GRANT ALL ON `ots_account_test`.* TO 'test_sxxxxxxxxxxxxxxxx' |
+---------------------------------------------------------------+
2 rows in set (0.02 sec)


mysql> show databases;
+------------------+
| Database         |
+------------------+
| ots_account_test |
+------------------+
1 row in set (0.02 sec)


mysql> select * from ots_account_test.account_test;
+------+--------------+
| pk1  | name         |
+------+--------------+
|  123 | account-test |
+------+--------------+
1 row in set (0.43 sec)

The sub-accounts have been granted access permissions.

Support for Cross-Account Access

Typically, most users use DLA in the following way: Cloud account A uses DLA to access the data of cloud account A in other cloud products. As described previously, a user can access the data source, create a library and table, and query data as long as the user chooses a specific data source (such as TableStore) in the DLA console and implements system role authorization for DLA.

However, in other scenarios, cloud account A may use DLA to access cloud account B's data in other cloud products (such as TableStore, as in the following example). This requires specialized role authorization:

Assume that the test account corresponds to cloud account A. In the following, we use TableStore and cloud account B (another real test cloud account for DLA) as an example to describe how account B grants cross-account permissions to allow account A to access its TableStore instance and how account A queries this data in DLA.

First, use account B to create a cross-account authorization role in "Access control (https://ram.console.aliyun.com )".

Open the RAM management page, click "roles", create a new role, authorize cloud account A to access account B's data via DLA.

Select a service role and template and quickly create a role:

You can choose from a list of pre-generated templates, follow the template to create a role with cross-account service access permissions.

Notice that this is just to start from a pre-created template, doesn't have to serve IoT purposes.

Return to the role management page and modify the role (change it to a template that supports DLA):

Once this is successfully configured, it should look like the follow figure.

Cross-account role creation and modification are now complete. The following describes how to configure the "role authorization policy", using TableStore as an example (the methods for other data sources are similar):

Cross-account role definition and authorization are complete, so we can start the actual DLA test. First, check the TableStore instances and tables of cloud account B:

Connect to DLA through MySQL-cli by using the DLA root account of cloud account A. Then, connect to and access the data of cloud account B:

mysql> select user();
+----------------+
| user()         |
+----------------+
| oa_xxxxxxxxxxx |
+----------------+
1 row in set (0.06 sec)

mysql> show databases;
+------------------+
| Database         |
+------------------+
| ots_account_test |
+------------------+
1 row in set (0.24 sec)

mysql> create database ots_cross_account_test  
with dbproperties(
  catalog = 'ots',
  location = 'https://test-sh.cn-shanghai.ots-internal.aliyuncs.com', -- TableStore instance of cloud account B
  instance = 'test-sh',
  cross_account_accessing_arn= 'acs:ram::1013xxxxxx:role/test-cross-account-accessing-role'  -- The Arn information generated when cloud account B performs cross-account role authorization for cloud account A @ cloud service DLA
);
Query OK, 0 rows affected (0.14 sec)

mysql> show databases ;
+------------------------+
| Database               |
+------------------------+
| ots_account_test       |
| ots_cross_account_test |
+------------------------+
2 rows in set (0.18 sec)

mysql> show create database ots_cross_account_test;
+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Database               | Create Database                                                                                                                                                                                                                                                                           |
+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ots_cross_account_test | CREATE DATABASE `ots_cross_account_test`
WITH DBPROPERTIES (
    catalog = 'ots',
    location = 'https://test-sh.cn-shanghai.ots-internal.aliyuncs.com',
    instance = 'test-sh',
    cross_account_accessing_arn = 'acs:ram::1013xxxxxx:role/test-cross-account-accessing-role'
)
COMMENT '' |
+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.19 sec)

mysql> use ots_cross_account_test;
Database changed

mysql> show tables;
Empty set (0.19 sec)

mysql> create external table test_table1 (
  id1 int not null primary key,
  col1 int 
);
Query OK, 0 rows affected (0.31 sec)

mysql> show tables;
+-------------+
| Table_Name  |
+-------------+
| test_table1 |
+-------------+
1 row in set (0.20 sec)

mysql> show create table test_table1;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                         |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------+
| test_table1 | CREATE EXTERNAL TABLE `test_table1` (
    `id1` int NOT NULL COMMENT '',
    `col1` int NULL COMMENT '',
    PRIMARY KEY (`id1`)
)
COMMENT '' |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.20 sec)

mysql> select * from test_table1;
+--------+------+
| id1    | col1 |
+--------+------+
|      0 | -111 |
| 111111 |  111 |
+--------+------+
2 rows in set (1.29 sec)

You should note that the common library creation process does not require the cross_account_accessing_arn parameter. A cloud account grants DLA cloud service access permissions for itself by default. When a library creation process uses the cross_account_accessing_arn parameter, this enables the cross-account service and the DLA library and its tables have cross-account access permissions.

You have now completed the whole cross-account access process. To connect to other cloud services, such as OSS, repeat the preceding process.

Alibaba Cloud Data Lake Analytics (DLA): https://www.alibabacloud.com/products/data-lake-analytics

Product documentation: https://www.alibabacloud.com/help/doc-detail/70378.htm

Community