Flink OpenSource SQL Jobs Using DEW to Manage Access Credentials

Scenario

When DLI writes the output data of Flink jobs to MySQL or GaussDB(DWS), you need to set attributes such as the username and password in the connector. However, information such as usernames and passwords is highly sensitive and needs to be encrypted to ensure user data privacy.

Data Encryption Workshop (DEW) and Cloud Secret Management Service (CSMS) joint form a secure, reliable, and easy-to-use privacy data encryption and decryption solution.

Users or applications can use CSMS to create, retrieve, update, and delete credentials in a unified manner throughout the secret lifecycle. CSMS can help you eliminate risks incurred by hardcoding, plaintext configuration, and permission abuse.

This section walks you through on how to use DEW to manage access credentials for Flink OpenSource SQL jobs.

Prerequisites

• A shared secret has been created on the DEW console and the secret value has been stored. For details, see Creating a Shared Secret.
• An agency has been created and authorized for DLI to access DEW. The agency must have been granted the following permissions:
  • Permission of the ShowSecretVersion interface for querying secret versions and secret values in DEW: csms:secretVersion:get.
  • Permission of the ListSecretVersions interface for listing secret versions in DEW: csms:secretVersion:list.
  • Permission to decrypt DEW secrets: kms:dek:decrypt

  For details about agency permission examples, see Customizing DLI Agency Permissions and Agency Permission Policies in Common Scenarios.

• DEW can be used to manage access credentials only in Flink 1.15. When creating a Flink job, select version 1.15 and configure the information of the agency that allows DLI to access DEW for the job.
• On the DLI management console, create an enhanced datasource connection and configure the network connection between DLI and the data source.

  For details, see Enhanced Datasource Connections.

Syntax

create table tableName(
  attr_name attr_type 
  (',' attr_name attr_type)* 
  (',' WATERMARK FOR rowtime_column_name AS watermark-strategy_expression)
)
with (
   ...
  'dew.endpoint'='',
  'dew.csms.secretName'='',
  'dew.csms.decrypt.fields'='',
  'dew.projectId'='',
  'dew.csms.version'=''
  
);

Parameter Description

Example

This example demonstrates how to configure Flink OpenSource SQL to manage access credentials using DEW by generating random data through a DataGen table and outputting it to a MySQL result table.

1. Create an enhanced datasource connection between DLI and MySQL.
2. Create an agency for DLI to access DEW and complete authorization. For details, see Customizing DLI Agency Permissions.
3. Create a shared secret in DEW. For details, see Creating a Shared Secret.
   1. Log in to the DEW management console.
   2. In the navigation pane on the left, choose Cloud Secret Management Service > Secrets.
   3. On the displayed page, click Create Secret. Set basic secret information.
   4. In this example, the MySQL credential value is configured as follows:
      • "MySQLUsername":"demo"
      • "MySQLPassword":"*******", where ******* indicates the password for accessing the MySQL database.
4. Enter a SQL statement in the editing pane. The following is an example:

   create table dataGenSource(
     user_id string,
     amount int
   ) with (
     'connector' = 'datagen',
     'rows-per-second' = '1', --Generate a piece of data per second.
     'fields.user_id.kind' = 'random', --Specify a random generator for the user_id field.
     'fields.user_id.length' = '3' --Limit the length of user_id to 3.
   );
   
   CREATE TABLE jdbcSink ( 
     user_id string,
     amount int
    ) 
   WITH (  
    'connector' = 'jdbc',  
     'url? = 'jdbc:mysql://MySQLAddress:MySQLPort/flink',--flink is the MySQL database where the orders table locates.
    'table-name' = 'orders',  
    'username' = 'MySQLUsername',  -- Shared secret in DEW whose name is secretInfo and version is v1. The key MySQLUsername defines the secret value. The value is the user's sensitive information.
    'password' = 'MySQLPassword',  -- Shared secret in DEW whose name is secretInfo and version is v1. The key MySQLPassword defines the secret value. The value is the user's sensitive information.
    'sink.buffer-flush.max-rows' = '1',
    'dew.endpoint'='kms.cn-xxxx.myhuaweicloud.com', --Endpoint information for the DEW service being used
    'dew.csms.secretName'='secretInfo', --Name of the DEW shared secret
    'dew.csms.decrypt.fields'='username,password', --The password field value must be decrypted and replaced using DEW secret management.
    'dew.csms.version'='v1'
   );
   
   insert into jdbcSink select * from dataGenSOurce;