DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Creating SM4-Encrypted Tables in GBase 8a and Managing Encryption Keys

#gbase #database #数据库 #security

GBase 8a MPP Cluster supports the SM4 national cipher for transparent data encryption at the storage level. To create an SM4-encrypted table, you follow a three-step process: pick the algorithm, create a certificate, and define the table. The encryption key is managed through a centralized certificate and distributed as files across all cluster nodes.

Step 1: Choose the SM4 Algorithm

Set the cluster-wide encryption algorithm to SM4. Once set, it cannot be changed.

SET GLOBAL gbase_encrypt_new_mode = 'SM4';
Enter fullscreen mode Exit fullscreen mode

Step 2: Create an Encryption Certificate

Certificates can be plain or ciphered. Ciphered certificates require a password and offer stronger security.

CREATE ENCRYPTION CERTIFICATE my_sm4_cert
    TYPE = CIPHERED
    PASSWORD = 'YourStrongPassword123!';
Enter fullscreen mode Exit fullscreen mode

The certificate file is automatically distributed to every node's $GBASE_HOME/config/ directory.

Step 3: Create the Encrypted Table

Use the ENCRYPT keyword for table-level or column-level encryption.

-- Table-level: encrypts all columns
CREATE TABLE encrypted_whole_table (
    id INT,
    sensitive_data VARCHAR(200),
    create_time DATETIME
) ENCRYPT;

-- Column-level: encrypts only specific columns
CREATE TABLE customer_info (
    user_id INT,
    name VARCHAR(100),
    id_card_no VARCHAR(20) ENCRYPT,
    phone VARCHAR(20) ENCRYPT,
    email VARCHAR(50)
);
Enter fullscreen mode Exit fullscreen mode

Important: Encryption must be defined at table creation time; it cannot be added or removed via ALTER TABLE.

Using Ciphered Certificates

Before running DML on encrypted columns, you must open the certificate:

OPEN ENCRYPTION CERTIFICATE my_sm4_cert PASSWORD = 'YourStrongPassword123!';
Enter fullscreen mode Exit fullscreen mode

Closing the certificate will cause all subsequent DML on encrypted columns to fail.

Key Management and Storage

  • Key generation & storage: The key is generated when the certificate is created and stored inside the certificate file. A copy of that file lives on every cluster node.
  • Certificate types: Plain (no password) or Ciphered (password-protected). You can convert between them.
  • Management tasks: Backup the certificate file, change the password, query certificate status via information_schema.encryption_certificate_status, and open/close the certificate as needed.
  • Security model: One cluster‑wide certificate. For ciphered certificates, the password is the user's responsibility — the system does not store or recover it. Protect the certificate file with OS permissions (e.g., chmod 600) and ensure the password meets the cluster's password policy.

Important Restrictions

  • Once the encryption algorithm is set, it cannot be changed.
  • Encrypted tables do not support DBLink, and encrypted columns cannot be used as distribution keys.
  • The performance overhead is minimal — typically less than 5%.

This design gives you strong, transparent encryption for your gbase database while keeping key management clean and compliant with enterprise security requirements. If you're storing sensitive data in GBASE's MPP platform, SM4 encryption is a powerful tool to have in your security toolkit.

Top comments (0)