Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to analyze the Data Lake Analytics account and authority system. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Introduction of Data Lake Analytics

Based on the analysis of the data lake, we can achieve big data association analysis across a variety of heterogeneous data sources without doing any pre-processes such as ETL and data relocation, thus greatly saving costs and enhancing user experience.

The following figure shows the simple architecture of DLA (_ _ MPP computing engine + storage computing separation + elastic high availability + heterogeneous dataset sources, etc.) _ _:

Cdn.nlark.com/lark/0/2018/png/103052/1541757933088-c409a27e-3db2-45bb-9228-98f37d52550c.png ">

Second, set up your own account

At present, DLA provides services in the form of MySQL protocol, and users need to connect to DLA services through JDBC. The accounts and passwords within DLA are self-built (different from RAM). The corresponding account and password information will be notified to you by internal messages, emails and SMS messages when users activate the DLA service.

You may wonder why DLA does not use RAM or AK accounts for authentication and authorization, but needs to build its own account. Here, let's make some brief introductions:

DLA is a database. When the client establishes a connection (requires authentication), accesses the database, tables and fields (requires authentication), a large number of cross-service access to the RAM system will put great pressure on the RAM system, and may affect the DLA service delay.

DLA is a database, which needs to be compatible with the MySQL permission model, and it is difficult for domain objects such as libraries, tables and fields to be mapped to the RAM system one by one; at the same time, the permissions of resource objects under RAM can be coarse or fine, and tend to manage the life cycle of data rather than the detailed data level.

Users are used to Grant, Revoke, Show grants and other logic, which are directly mapped to the libraries, tables and fields of the traditional database.

With reference to the design of other database products on Ali Cloud and industry cloud service platform, there are the same considerations.

Third, three account models

Root/Service account: when RAM main account activates DLA service in a certain Region, the system automatically creates the first DLA account, and notifies RAM main account via internal message, SMS and email. There is only one Root account and cannot be deleted.

User/ sub-account: the new DLA User account created on Console by the RAM main account (note, it is not created by the Root account). Cloud account users can create or delete User accounts, or change their passwords, etc.

Product account: when other cloud products (such as DBS) interact with DLA, the user authorizes the cloud product in RAM, and DLA automatically generates and dispatches the account to the cloud product.

Root account and User account, and the associated RAM uid are the corresponding cloud accounts, so both Root and User accounts have the opportunity to access all the resources of the cloud account (of course, these are all within the authorization management)

4. Actual operation of the account a) activate and initialize the service

Find the service:

Purchase:

Initialize services for different Region:

After initialization, you get a Root account:

Configure service access points

B) connect to the database and be authenticated

Connect to the DLA service and enter the service

# # to connect to the DLA service, the account number and password are in your inbox, and the service access point is on the service page [root] # mysql-u-p-h-P10000access # to enter the DLA service and start the test journey mysql > show databases;Empty set (0.09 sec)

Connect to the DLA service and enter the service, and it will work properly.

5. Authority model

There are two layers of permission verification mechanism in DLA to ensure that your data is accessed securely:

DLA layer authorization and validation, 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)

Data source and RAM layer authorization and verification, implemented based on RAM access control (such as OSS, TableStore and other cloud native products): https://help.aliyun.com/product/28625.html, or permission verification based on the data source itself (such as RDS, which is a self-built account, so it also has a self-built permission system)

The user's SQL is sent to DLA, and after the permission verification of DLA is completed, it is forwarded to the backend data source layer, where the permission verification of the RAM layer and the data source is performed, and finally the user's data is actually accessed.

A) the principle of DLA layer check

DLA validates permissions "from large to small" according to the range of objects that users operate on, from Global level (global permissions), to Schema level, to Table level, and finally to Column level (not currently supported). If any layer has permission to verify successfully, but does not have permission at the last layer, the verification fails:

B) DLA layer authorization method

Basically refer to MySQL's Grant/Revoke/Show Grants syntax to implement:

# # grant related GRANT {SELECT | SHOW | ALTER | DROP | CREATE | INSERT | UPDATE | 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 | ALL PRIVILEGES} ALL PRIVILEGES {* | *. * | 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']] key information description:

Authorized person:

Authorization can only be granted to other non-Root accounts by DLA's root account.

It is not supported for non-Root accounts to authorize other accounts.

Cannot authorize and reclaim permissions across cloud accounts

Privilege list:

Can be connected with',', such as SELECT,DELETE,UPDATE,INSERT,...

If you have ALL or ALL PRIVILEGES, you will authorize or recycle all of them. Ignore other Privilege;, but you must note that grant option itself is not included in ALL. It must be explicitly authorized or recycled.

Other types of authorization are not supported for the time being

SELECT is authorized for Query

SHOW is authorized by show and use commands (the logic here is quite different from that of mysql)

ALTER is a ddl license for alter or other variants

CREATE is a ddl license of create type

DROP is a ddl license of Drop type

INSERT is an insert type dml license

UPDATE is an update type dml license

DELETE is delete type dml authorization

GRANT OPTION is dcl authorization (grant is related to revoke); GRANT OPTION can be specified in Privilege, or grant authorization can be done through statement fragment with grant option

USAGE actually has nothing, which means it's empty.

In ResourceType:

* indicates that a library xxDB is used for the current connection, and then authorization is made for xxDB, with library-level permissions

. Represents all table permissions for all libraries, Global-level permissions

XxDb.* means to authorize the xxDB library with library-level permissions.

XxDb.yyTable means to authorize the xxTable of the xxDB library with table-level permissions.

YyTable indicates that a library xxDB is used for the current connection, which is authorized for the xxTable of the xxDB library and has table-level permissions.

Field level authorization is not supported for the time being

Authorized user definition:

Write the user name directly: oa_123456

User string to represent: 'oa_123456'

Even if the ip and host information is written, it will not be used for whitelist verification of the connection.

Only Root users under the same cloud account can show grants for others.

Cannot execute show grant across different uid

You must have show and grant privileges to execute show grants, or execute _ _; _ _ for yourself

SHOW GRANTS FOR 'jeffrey'@'localhost': currently does not support ip addresses

C) permission mapping between DLA and RAM

Because the sub-accounts in DLA do not correspond to the sub-accounts in RAM one by one, the permissions of resources in RAM and the permissions of database tables in DLA are not naturally mapped, but need to be mapped and isolated in a special way in DLA.

Currently, the authorized unit in DLA is at the Schema level, which means that if the Root account authorizes the permissions of a library to a User account, the User account can access all the tables under the library, which means that it can access all the resources mapped to the RAM. These access is not controlled by the permissions of the RAM sub-account.

For example, let's look at a typical library-building statement (assuming that users can already create related libraries in DLA):

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

If a Root account is assigned to a User account and the Grant operation is performed, the User account can access the library:

Grant all on db_name.* to xxx_s1519122757

The above process assumes that the system role authorization of the cloud account has been completed. In the next section, we describe how to complete the system role authorization first to allow DLA to access your data in other cloud products.

D) system role authorization

System role authorization refers to: users authorize DLA to allow DLA to access data in user-related cloud services, so as to achieve the goal of DLA association analysis of user data, or to achieve ETL through DLA to flow the data back to the user's database. The specific process can be referred to: https://help.aliyun.com/document_detail/53478.html

E) Cross-account access

DLA supports cross-account, allowing A users to connect data on user B's OSS on DLA for analysis and calculation. However, some operations need to be done. Subsequent documents will introduce and guide users graphically:

After the library is built, go and build the table.

Insert a row of data

Click agree to authorize:

Check that role authorization has been successful:

C) create libraries and tables in DLA and associate TableStore libraries and tables

We return to the DLA Root account (oa_xxx) and connect to DLA through the JDBC protocol (account information, DLA access point, JDBC connection method, please refer to the previous document)

╰─○ mysql-u-p-h-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 194631Server version: 5.6.40-log Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Start creating a library to associate the instances in TableStore:

Mysql > select user (); +-+ | user () | +-+ | oa_xxxxxxxxxxx | +-+ 1 row in set (0.08 sec) mysql > show databases Empty set (0.02sec) mysql > create database ots_account_test with dbproperties (catalog = 'ots' Location = 'https://account-test.cn-shanghai.ots-internal.aliyuncs.com', instance =' 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://account-test.cn-shanghai.ots-internal.aliyuncs.com', Instance = 'account-test') COMMENT' test account and privileges' | + - -+ 1 row in set (0.03 sec)

Start creating tables, disassociate tables in TableStore, and query data (the results are the same as those in OTS):

Mysql > use ots_account_test;Database changedmysql > 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)

So far, we have successfully completed the process of data association and data query!

D) authorize access to libraries and tables to sub-accounts

All appeals are made by the Root account. From the previous analysis, we can see that the Root account can operate all the cloud resources corresponding to the cloud account, but the User account of DLA cannot. Only by giving Grant permission to the user account through the Root account can DLA allow a User account to access the corresponding database and table:

Switch to the User account / sub-account connection page, and no database tables can be seen:

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 we authorize the previous account:

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.05sec) 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 the User account connection page and re-query it. You already have permission 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)

At this point, the sub-account can authorize access!

E) support cross-account access

In general, when most users use DLA, cloud account A uses DLA to access the data of cloud account An in other cloud products. From the previous analysis, we can know that as long as users select a specific data source (such as TableStore) on the console of DLA to authorize DLA for system role authorization, they can get through the data source, create database tables and query data.

However, there is another scenario where cloud account A uses DLA to access the data of cloud account B in other cloud products (TableStore below), which requires special role authorization to work properly:

Assuming that the cloud account corresponding to the above test account is A, take TableStore and another cloud account B (another real test cloud account in DLA) as examples to demonstrate that account B authorizes account A to cross-account for an instance in TableStore, and A completes the query process in DLA.

First, you need to go to the B account and create a cross-account authorization role in "access Control (https://ram.console.aliyun.com)":

Re-use the DLA Root account of cloud account A, connect to DLA through MySQL-cli, and then connect 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 instance of cloud account B = 'test-sh', cross_account_accessing_arn=' acs:ram::1013xxxxxx:role/test-cross-account-accessing-role'-- Arn information when cloud account B authorizes cross-account roles of 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 changedmysql > 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 |-111111 | 111111 | +-+-+ 2 rows in set (1.29 sec)

By the way, the cross_account_accessing_arn parameter is not required in the general database building process, which means that by default, the cloud account has enabled DLA access to the cloud service. With the cross_account_accessing_arn parameter, the cross-account service is enabled. The libraries in this DLA and the tables under the library have cross-account access permissions!

After reading the above, do you have any further understanding of how to analyze the Data Lake Analytics account and authority system? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.