background

Apache ShardingSphere parses the SQL entered by users and rewrites the SQL according to the encryption rules provided by users, so as to encrypt the original data and store the original data (optional) and ciphertext data in the underlying database at the same time. When a user queries data, it only retrieves ciphertext data from the database, decrypts it, and finally returns the decrypted original data to the user. Apache ShardingSphere automates & transparently the data encryption process, allowing users to use encrypted data as normal data without paying attention to the implementation details of data encryption. In addition, Apache ShardingSphere can provide a relatively complete solution for both the encryption transformation of the existing online business and the use of encryption function of the new online business.

The solution

Refer to the official documentation for two scenarios

  • Newly launched services
  • Online services

This article first introduces the new operation of the new online service, which is much simpler than the online service

Newly launched services

Version information

  • SpringBoot 2
  • ShardingSphere 5
  • MySQL 8

Introduction of depend on

 <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.0.0 beta -</version>
</dependency>
Copy the code

Configuration file:

spring:
  profiles:
    include: common-local
  shardingsphere:
    datasource:
      names: write-ds,read-ds-0
      write-ds:
        jdbcUrl: jdbc:mysql://mysql.local.test.myallapp.com:23306/test?allowPublicKeyRetrieval=true&useSSL=false&allowMultiQueries=true&s erverTimezone=Asia/Shanghai&useSSL=false&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior =convertToNull
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: nicai
        connectionTimeoutMilliseconds: 3000
        idleTimeoutMilliseconds: 60000
        maxLifetimeMilliseconds: 1800000
        maxPoolSize: 50
        minPoolSize: 1
        maintenanceIntervalMilliseconds: 30000
      read-ds-0:
        jdbcUrl: jdbc:mysql://mysql.local.test.read1.myallapp.com:23306/test?allowPublicKeyRetrieval=true&useSSL=false&allowMultiQueries= true&serverTimezone=Asia/Shanghai&useSSL=false&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBe havior=convertToNull
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: nicai
        connectionTimeoutMilliseconds: 3000
        idleTimeoutMilliseconds: 60000
        maxLifetimeMilliseconds: 1800000
        maxPoolSize: 50
        minPoolSize: 1
        maintenanceIntervalMilliseconds: 30000
    rules:
      readwrite-splitting:
        data-sources:
          glapp:
            write-data-source-name: write-ds
            read-data-source-names:
              - read-ds-0
            load-balancer-name: roundRobin # Load balancing algorithm name
        load-balancers:
          roundRobin:
            type: ROUND_ROBIN ROUND_ROBIN ROUND_ROBIN ROUND_ROBIN
      encrypt:
        encryptors:
          mobile-encryptor:
            props:
              aes-key-value: 123456abc
            type: AES
        tables:
          t_cipher_new:
            columns:
              mobile:
                cipher-column: mobile # encrypt column name
                encryptor-name: mobile-encryptor # encryption algorithm name (name cannot be underlined)
                # plain-column: mobile #
        queryWithCipherColumn: true # whether to use an encrypted column for query. In the case of the original column, you can use the original column for query

Copy the code

I’m mixing the read/write separation and encryption configurations together, and I’ll really only focus on the ENCRYPT node section for this article

There are a few points to note on configuration:

  • Don’t use an underscore in the key name, for examplemobile-encryptorThis custom name, before looking for a long time reason, forget the rule.
  • Since this section demonstrates a new business, the encrypted and textual columns are represented as one column (mobile), as is the logical column.

Construction sentences:


CREATE TABLE `t_cipher_new` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  `pwd` varchar(100) DEFAULT NULL,
  `mobile` varchar(100) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Copy the code

test

Insert a piece of data through the service interface:

You can see that the mobile field is automatically encrypted. The plaintext is 1234567 and the ciphertext is FGPDcFbE1uWPwPUOeRpKbw==

Let’s query this data again through the business interface

{
    "code": 100000."msg": ""."data": {
        "id": 1440855970338058241."name": "hello"."pwd": "123"."mobile": "1234567"."createTime": "The 2021-09-23 09:50:09"."updateTime": "The 2021-09-23 09:50:09"}}Copy the code

As you can see, the mobile field is 1234567 after decryption

reference

  • Shardingsphere.apache.org/document/5….