Comprehensive Solution for Data Masking with Apache ShardingSphere (Part 2)

The article continues the discussion of ShardingSphere's built‑in data‑masking capabilities, focusing on practical implementation for both newly launched services and legacy systems that already store plaintext data.

1. New Business Scenario – For a fresh project, only logical columns need to be defined; the physical ciphertext column is added via a simple YAML configuration using an AES encryptor. The logical column can be the same as the ciphertext column, allowing developers to keep existing SQL unchanged.

encryptRule:
  encryptors:
    aes_encryptor:
      type: aes
      props:
        aes.key.value: 123456abc
  tables:
    t_user:
      columns:
        pwd:
          cipherColumn: pwd
          encryptor: aes_encryptor

Encrypt‑JDBC automatically writes plaintext to the logical column while storing the encrypted value in the ciphertext column, satisfying security‑audit requirements without modifying application code.

2. Existing Business Migration – Legacy databases contain large amounts of plaintext data. The solution proposes a phased migration: first, add a ciphertext column (e.g., pwd_cipher) and a plain column ( pwd) in the table; then configure ShardingSphere so that new writes are encrypted, and historical data are batch‑encrypted manually. The query.with.cipher.column flag controls whether queries use the ciphertext column.

encryptRule:
  encryptors:
    aes_encryptor:
      type: aes
      props:
        aes.key.value: 123456abc
  tables:
    t_user:
      columns:
        pwd:
          plainColumn: pwd
          cipherColumn: pwd_cipher
          encryptor: aes_encryptor
      props:
        query.with.cipher.column: false

During migration, both plaintext and ciphertext coexist; after verification, the flag is switched to true and the plaintext column can be dropped, enabling a seamless cut‑over without changing business SQL.

3. Post‑Migration Cleanup – Once the system stabilises, the plaintext column is removed. Because the application continues to reference the logical column, Encrypt‑JDBC maps it to the ciphertext column, eliminating the need for code changes.

Advantages of ShardingSphere’s Middleware Masking – Automatic and transparent data masking, multiple built‑in and third‑party strategies, API for custom strategies, switchable policies, and the ability to store both plaintext and ciphertext simultaneously for smooth migration.

Applicable Scenarios – Java‑based projects, backend databases such as MySQL, Oracle, PostgreSQL, SQLServer, requiring column‑level encryption without altering existing SQL.

Limitations – Users must handle historical data cleaning themselves; certain SQL features (e.g., ORDER BY, BETWEEN, LIKE, aggregation functions) are not supported on encrypted columns.

The article concludes by noting that ShardingSphere has evolved from a simple sharding tool to a full‑stack distributed database middleware offering data‑masking, sharding, read/write splitting, distributed transactions, and monitoring, with various client options like Encrypt‑JDBC and Encrypt‑Proxy.