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 example
mobile-encryptor
This 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….