Sharding-Proxy library and table and data encryption usage scenario analysis

Sharding-Proxy library sub-table and data encryption

We will mainly summarize and share the experience of using shardingshpere-proxy in actual projects.

Use scenarios

• The company plans to develop two SaaS platforms for new government affairs media and digital villages as new profit growth points. Taking into account the number of users and data in the future, it is decided to divide the database and table according to the tenant (contracted customers). For some sensitive data, such as ID number, mobile phone number, etc., use database-level encryption and decryption, and do not store plain text data.
• After examining some existing database middleware and database splitting solutions on the Internet, the company decided to use itSharding-ProxyAs a database middleware for sub-repository and data encryption. The main reason is that it is very invasive to code and developers don't need to pay attention to it, reducing learning costs and DBA-friendly. On the other hand, ShardingSphere has entered the Apache Incubator, which is completely open source and free, the community is also very active, and the version iteration is also very fast.
• ShardingSphere-5.0 used this time

Configuration file explanation

• resources folder, under the conf folder
• Mainly include registration center configuration, login connection configuration and basic configuration

mode:
  type: Cluster  # Cluster  repository:
    type: ZooKeeper  # Use zookeeper    props:
      namespace: governance_ds  
      server-lists: 192.168.1.100:2181
      retryIntervalMilliseconds: 50000
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 50000
  overwrite: true 

• : ClusterUsing cluster configuration, a single deployment can also be set toCluster, no effect
• Configure storage method, you can choose to use itZooKeeper
• , configure the loading method, whether the local configuration covers the configuration center configuration. true is overridable, the local configuration shall be synced to zookeeper; false, the zookeeper shall be zookeeper shall be
• There are some basic configurations below, whether to print SQL, etc., which are temporarily defaulted

• resources folder, under the conf folder
• schemaName Database connection, database name
• dataSources Data Sources
• rules
• !SHARDING library and table rules
• tables table
• actualDataNodes is actually for the library table
• databaseStrategy library partition strategy none no library partition
• defaultDatabaseStrategy
• defaultTableStrategy default table strategy
• defaultKeyGenerateStrategy
• shardingAlgorithms custom sharding algorithm
• keyGenerators primary key generation strategy

• resources folder, under the conf folder
• schemaName Database connection, database name
• dataSources Data Sources
• rules
• !ENCRYPT Data encryption
• encryptors encryption policy, you can choose AES or MD5, and the encryption policy can be selected in the specific fields below.
• aes_encryptor, aes can be configured with salt
• tables table
• columns field s
• id_number logical field
• plainColumn Original field
• cipherColumn Encryption Field
• encryptorName encryption policy
• queryWithCipherColumn Whether to use encrypted fields when querying

other

• You can download the source code or download the program to see. The functions inside have configuration file cases, which are commented out, a PostgreSQL, and a MySQL
• These two functions are currently used. Other functions have not been studied for the time being, so I won't say much
• When actually using it, the partition database and data encryption are used together, so only one configuration file is used, all of which are placed under the rules.
• I'll post my configuration file later

Usage

• The government new media SaaS platform is temporarily using only the library and table
• Digital rural SaaS platform uses database and tables and data encryption
• The database is divided into different tables, mainly based on tenants, and some tables are divided into different tables, and some business fields are divided into different tables.
• Table sub-table strategy, default 8 table writing method algorithm-expression: monitor_record_${media_id % 8}
• We divide the database based on the hash of the tenant, but for some tenants, we want to specify the database, which requires custom library division and table division strategy.
• It requires that the library supports hashing and specification. I wrote a custom library policy class and there is a static map. When parsing and executing SQL, I first get it from the map. If I cannot get it, I will get it based on the hash.
• Data encryption is mainly about adding encrypted fields and processing historical data.
• You can write a static method to process existing data

Summarize

• sharding-proxy is fully supported for database and tables according to tenants and data encryption, which is sufficient for us to use.
• It's very easy to use, just download the latest stable version to install
• If there is no custom library and table policy requirement and only use the existing policy, you only need to modify the configuration file deployment
• If you need to customize the library and table division strategy, it is not complicated. Write the class and package it well, and put it in ext-lib
• Example of configuration file

schemaName: digital_village
dataSources:
  ds:
    url: jdbc:postgresql://192.168.:5432/digital_village?currentSchema=public&serverTimezone=UTC&useSSL=false
    username: postgres
    password: xxxxxx
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 120
    minPoolSize: 1
  ds_0:
    url: jdbc:postgresql://192.168.:5432/digital_village_0?currentSchema=public&serverTimezone=UTC&useSSL=false
  ds_1:
    url: jdbc:postgresql://192.168.:5432/digital_village_1?currentSchema=public&serverTimezone=UTC&useSSL=false
    password: xxxxx
rules:
  - !SHARDING
    tables:
      # The table that needs to be divided into the library, and the library is divided into the library according to the tenant id      cms_basic_info:
        actualDataNodes: ds_${0..3}.cms_basic_info
      cms_column:
        actualDataNodes: ds_${0..3}.cms_column
      cms_content:
        actualDataNodes: ds_${0..3}.cms_content
      cms_content_text:
        actualDataNodes: ds_${0..3}.cms_content_text
      cms_menu_column_bind:
        actualDataNodes: ds_${0..3}.cms_menu_column_bind
      cms_message_board:
        actualDataNodes: ds_${0..3}.cms_message_board
      # Tables that do not require library and table division, all stored in the ds data source      auth_cfg_catalog_data_permission:
        actualDataNodes: ds.auth_cfg_catalog_data_permission
        databaseStrategy: 
          none:
      auth_cfg_column_data_permission:
        actualDataNodes: ds.auth_cfg_column_data_permission
        databaseStrategy:
      
    # Default library policy    defaultDatabaseStrategy:
      standard:
        shardingColumn: customer_id  #Separation Field        shardingAlgorithmName:  customer_id_inline #Distribution rules:    defaultTableStrategy:
      none:
    # Default primary key policy    defaultKeyGenerateStrategy:
      column: id
      keyGeneratorName: snowflake
    # Custom sharding algorithm    shardingAlgorithms:
      customer_id_inline:
        type: CLASS_BASED
        props:
          strategy: standard
          algorithmClassName: 
    # Primary key generation strategy    keyGenerators:
      snowflake:
        type: SNOWFLAKE
          worker-id: 123
  - !ENCRYPT
    encryptors:
      aes_encryptor:
        type: AES
          aes-key-value: xxxwwaS213123SAD
      md5_encryptor:
        type: MD5
      party_position:
        columns:
          mobile:
            plainColumn: mobile
            cipherColumn: mobile_cipher
            encryptorName: aes_encryptor
      village_population:
          id_number:
            plainColumn: id_number
            cipherColumn: id_number_cipher
    queryWithCipherColumn: true

This is the article about Sharding-Proxy library sub-table and data encryption. For more related Sharding-Proxy library sub-table content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!

Latest Comments