Flink OpenSource SQL Jobs Using DEW to Manage Access Credentials

Scenario

When DLI writes the output data of Flink jobs to MySQL or DWS, you need to set sensitive parameters such as the username and password in the connector. Storing this information in plaintext poses significant security risks. To safeguard user data privacy, you are advised to encrypt these credentials.

Data Encryption Workshop (DEW) and Cloud Secret Management Service (CSMS) offer a secure, reliable, and easy-to-use solution for encrypting and decrypting sensitive data. By hosting database account details (for example, username and password) as managed secrets within CSMS, you can securely reference these credentials in your Flink jobs. This ensures that sensitive information is retrieved through a secure channel during runtime.

Additionally, CSMS offers comprehensive lifecycle management for credentials, enhancing both security and efficiency. It effectively mitigates risks associated with hardcoding sensitive information or storing it in plaintext configurations, thereby preventing unauthorized access and potential business disruptions.

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

Notes and Constraints

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.

Prerequisites

• A shared secret has been created on the DEW console and the secret value has been stored.
• 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
• On the DLI management console, create an enhanced datasource connection and configure the network connection between DLI and the data source.

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 a shared secret in DEW.
   1. Log in to the DEW management console.
   2. In the navigation pane on the left, choose Cloud Secret Management Service > Secrets.
   3. Click Create Secret. On the displayed page, configure basic secret information.
      • Secret Name: Enter a secret name. In this example, the name is secretInfo.
      • Secret Value: Enter the username and password for logging in to the RDS for MySQL DB instance.
        • The key in the first line is MySQLUsername, and the value is the username for logging in to the DB instance.
        • The key in the second line is MySQLPassword, and the value is the password for logging in to the DB instance.
        Figure 1 Setting the secret values
        
   4. Set other parameters as required and click OK.

2. Configure DEW to manage access credentials in the Flink OpenSource SQL job.

   Ensure that an enhanced datasource connection between DLI and MySQL has been created.

   Ensure that an agency has been created for DLI to access DEW and authorization has been completed.

   Here is an example configuration for a Flink OpenSource SQL job:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

   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'='endpoint', --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 username and password field values must be decrypted and replaced using DEW secret management. 'dew.csms.version'='v1' ); insert into jdbcSink select * from dataGenSOurce;