Access plaintext of encrypted columns in RDS or PolarDB databases using EncJDBC - Data Security Center

After configuring column encryption for sensitive data columns in RDS MySQL, RDS PostgreSQL, PolarDB MySQL, or PolarDB PostgreSQL database tables, use the EncJDBC driver to connect to the database if you want your Java application to access the plaintext of these encrypted columns. This topic describes how to use EncJDBC to connect to a database and access plaintext data from encrypted columns.

Prerequisites

Enable column encryption for the target database and set the ciphertext permission for the target database account to Ciphertext Permission (JDBC Decryption). For detailed instructions on configuring database column encryption and account permissions, see Configure database column encryption.
Obtain the encrypted database connection information: endpoint, port, database name, database account, and password.

Background information

The column encryption feature lets you apply dynamic output protection to specific sensitive columns in your database, improving data security. After enabling this feature, the system controls query results based on the database account's access policy:

Accounts with Plaintext Permissions can view raw data directly.
Accounts configured with Ciphertext Permission (JDBC Decryption) receive encrypted ciphertext but can automatically restore it to plaintext using Alibaba Cloud’s always-confidential JDBC driver (EncJDBC) and providing a master encryption key (MEK) that matches the encryption policy.
Accounts configured with Ciphertext Permission (No Decryption Permission) can only see ciphertext and cannot decrypt it in any way.

This mechanism ensures sensitive data is effectively protected at the output stage. Even if data is exported or intercepted, unauthorized parties cannot read it.

Generate an MEK

When you enable column encryption using the Local key method for a column, the DSC column encryption gateway encrypts sensitive fields using a controlled key before returning query results to the client. Except for accounts with Plaintext permission, all other accounts receive encrypted ciphertext when querying that column.

To access plaintext later using an account with Ciphertext permission (JDBC decryption), provide and record your own MEK during initial column encryption setup. Only an MEK matching the encryption policy allows the EncJDBC driver to correctly decrypt ciphertext fields in query results on the client side.

Value range: A 16-byte hexadecimal string, exactly 32 characters long.
Role of the MEK: The MEK is the root credential that authorizes the EncJDBC client to decrypt ciphertext fields in query results. It protects the actual key used to encrypt query results.
Security responsibility: Alibaba Cloud does not store, back up, or host your MEK. Safeguard it using a secure key management solution such as KMS.
Important warning: If you lose your MEK, all historical query results encrypted under this policy become permanently undecryptable.

Based on the Encryption Method selected in your database column encryption configuration, obtain a KMS Key or generate a Local Key to use as your MEK for decrypting the corresponding database.

KMS key

Important

Ensure the KMS service is available when using a KMS key. Otherwise, the always-confidential client driver EncJDBC cannot function.

Obtain the endpoint of the KMS instance that owns the KMS key selected in your database column encryption configuration, along with the AccessKey ID and AccessKey secret of the Alibaba Cloud account or RAM user (which must have KMS decryption permission) to read this KMS key from the client. Follow these steps:

Log on to the console using an Alibaba Cloud account or a RAM user.
If using a RAM user, grant the RAM user KMS decryption permission.
1. Create a custom policy. Use the following policy content:
```
{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "KMS:Decrypt",
            "Resource": "*"
        }
    ]
}
```
2. Attach the created custom policy to the specified RAM user. For details, see Manage RAM user permissions.
Obtain the KMS instance endpoint.
- By default, keys in a KMS instance allow access only from VPC networks. On the KMS instance management page, find your target KMS instance, click Actions, then Details, and view the VPC endpoint on the Basic Information tab.
- To access keys over the public network, enable public network access and then view the public endpoint. For details, see Enable public network access.
Obtain access credentials.

Save the AccessKey ID and AccessKey secret when creating the AccessKey for your Alibaba Cloud account or RAM user. For details, see Create an AccessKey.

Local key

When the Encryption Method in your database column encryption configuration is set to Local Key, generate an MEK. For example: 00112233445566778899aabbccddeeff.

Common generation methods include password generators or random functions in programming languages.

For example:

On Linux, use the built-in OpenSSL tool by running openssl rand -hex 16 to generate a key.
On Windows, install the OpenSSL software package.

Client integration instructions

Important

Use JDK 1.8 or later for Java.

On the client side, switch the database connection driver to EncJDBC, update the database connection URL, and specify the MEK to access plaintext from encrypted database columns.

1. Install dependencies

Add the following dependency to your Maven project configuration file pom.xml.

<dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>aliyun-cls-jdbc</artifactId>
    <version>1.0.10-3</version>
</dependency>

2. Configure`MEK` to connect to the database

The following methods describe how to configure the MEK: JDBC properties configuration, file configuration, and URL configuration. If you configure more than one method simultaneously, the priority order is: JDBC properties configuration > file configuration > URL configuration.

Note

In URL configuration, separate multiple parameters with &.
In all configurations and connection methods below, the MEK is processed locally on the client and securely sent to the server using envelope encryption to prevent MEK leakage.

Choose to connect to the database using either a local key or a KMS key based on the Encryption method in your database column encryption configuration.

Connect to the database using a KMS key

Important

If you use STS temporary credentials to retrieve a KMS-managed MEK, use the STS SDK to obtain the temporary credential STS token. For STS SDK examples, see STS SDK overview.
Do not hard code access credentials (AccessKey ID and AccessKey secret) directly in your application code. This example uses system environment variables to manage access credentials. For details, see Configure environment variables on Linux, macOS, and Windows.

JDBC properties configuration

Standard JDBC lets you set custom properties using Properties during connection. The following example shows how to configure JDBC properties and run JDBC:

// Prepare connection information such as hostname, port, database name, username, and password.
// ...
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";
// Retrieve access credentials (AccessKey ID and AccessKey secret) from environment variables.
String accessKeyId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
String accessKeySecret = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
// If using STS temporary credentials to read the KMS key, also provide the obtained STS token.
// String stsToken = "yourSecurityToken";
// KMS instance endpoint. Use the public endpoint if public network access is enabled. Use the VPC endpoint for VPC access.
String kmsEndpoint = "kms.cn-hangzhou.aliyuncs.com";
Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
props.setProperty("ALIBABA_CLOUD_ACCESS_KEY_ID", accessKeyId);
props.setProperty("ALIBABA_CLOUD_ACCESS_KEY_SECRET", accessKeySecret);
props.setProperty("ALIBABA_CLOUD_KMS_ENDPOINT", kmsEndpoint);
// props.setProperty("ALIBABA_CLOUD_STS_TOKEN", "stsToken");
// Connection URL format for MySQL: "jdbc:mysql:encdb://%s:%s/%s".
String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s", hostname, port, dbname);
// Load the EncJDBC driver for MySQL.
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");
// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, props);
// ... Execute queries ...

URL configuration

You can embed parameters for retrieving the KMS key directly in the URL, as shown below:

// Prepare connection information such as hostname, port, database name, username, and password.
// ...
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";
// Retrieve access credentials (AccessKey ID and AccessKey secret) from environment variables.
String accessKeyId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
String accessKeySecret = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
// If using STS temporary credentials to read the KMS key, also provide the obtained STS token.
// String stsToken = "yourSecurityToken";
// KMS instance endpoint. Use the public endpoint if public network access is enabled. Use the VPC endpoint for VPC access.
String kmsEndpoint = "kms.cn-hangzhou.aliyuncs.com";
// Connection URL format for MySQL.
String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s?ALIBABA_CLOUD_ACCESS_KEY_ID=%s&ALIBABA_CLOUD_ACCESS_KEY_SECRET=%s&ALIBABA_CLOUD_KMS_ENDPOINT=%s", hostname, port, dbname, accessKeyId, accessKeySecret, kmsEndpoint);
// With STS token.
// String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s?ALIBABA_CLOUD_ACCESS_KEY_ID=%s&ALIBABA_CLOUD_ACCESS_KEY_SECRET=%s&ALIBABA_CLOUD_KMS_ENDPOINT=%s&ALIBABA_CLOUD_STS_TOKEN=%s", hostname, port, dbname, accessKeyId, accessKeySecret, kmsEndpoint, stsToken);
// Load the EncJDBC driver for MySQL.
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");
// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, username, password);
// ... Execute queries ...

Connect to the database using a local key

JDBC properties configuration

Standard JDBC lets you set custom properties using Properties during connection. The following example shows how to configure JDBC properties and run JDBC:

// Prepare connection information such as hostname, port, database name, username, and password.
// ...
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";
// Master encryption key.
String mek = "00112233445566778899aabbccddeeff"; 
Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
props.setProperty("MEK", mek);
// Connection URL format for MySQL: "jdbc:mysql:encdb://%s:%s/%s". For PostgreSQL, use "jdbc:postgresql:encdb://%s:%s/%s".
String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s", hostname, port, dbname);
// Load the EncJDBC driver for MySQL. For PostgreSQL, use "com.aliyun.encdb.postgresql.jdbc.EncDriver".
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");
// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, props);
// ... Execute queries ...

File configuration

You can import parameters such as the required MEK through a configuration file.

Note

File configuration applies only to local key MEKs.

You can set a property named encJdbcConfigFile in your project and set its value to the configuration file path (by default, the file encjdbc.conf is used). The content of the configuration file is as follows:

MEK=00112233445566778899aabbccddeeff

You can place the configuration file in one of two locations:

Put the file in the resources directory of your project, as shown below:
```
src
  main
    java
    resources
      encjdbc.conf
```
Put the file in the project root directory (the program’s runtime directory).

After setting up file configuration, no additional configuration is needed in your code, as shown below:

// Prepare connection information such as hostname, port, database name, username, and password.
// ...
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";
// Connection URL format for MySQL: "jdbc:mysql:encdb://%s:%s/%s". For PostgreSQL, use "jdbc:postgresql:encdb://%s:%s/%s".
String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s", hostname, port, dbname);
// Load the EncJDBC driver for MySQL. For PostgreSQL, use "com.aliyun.encdb.postgresql.jdbc.EncDriver".
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");
// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, username, password);
// ... Execute queries ...

URL configuration

You can embed parameters such as the MEK directly in the URL, as shown below:

// Prepare connection information such as hostname, port, database name, username, and password.
// ...
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";
 // Master encryption key.
String mek = "00112233445566778899aabbccddeeff";
// Connection URL format for MySQL: "jdbc:mysql:encdb://%s:%s/%s?MEK=%s". For PostgreSQL, use "jdbc:postgresql:encdb://%s:%s/%s?MEK=%s".
String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s?MEK=%s", hostname, port, dbname, mek);
// Load the EncJDBC driver for MySQL. For PostgreSQL, use "com.aliyun.encdb.postgresql.jdbc.EncDriver".
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");
// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, username, password);
// ... Execute queries ...

3. Query plaintext data from encrypted columns

After successfully connecting to the database, operate it just like a standard JDBC query. EncJDBC automatically decrypts encrypted columns and returns plaintext data.

Sample code:

// Execute the query.
// Create a query statement.
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table_name");
// Traverse the result set.
while (resultSet.next()) {
    for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
        System.out.print(rs.getString(i + 1));
        System.out.print("\t");
    }
    System.out.print("\n");
}

Complete code example

Using JDBC properties to configure a local key MEK, this example queries plaintext data from encrypted columns in an RDS MySQL database using a database account with Ciphertext Permission (JDBC Decryption).

For information about the database configuration in the following example, see Verify column encryption results in the RDS MySQL database column encryption example.

Note

This example uses Maven version 3.9.9 and the development tool IntelliJ IDEA Community Edition 2024.1.2.

import java.sql.*;
import java.util.Properties;
public class EncryptedColumnAccess {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // Update the following connection information (hostname, port, database name, username, password) with your instance details.
        String hostname = "rm-******.mysql.rds.aliyuncs.com";
        String port = "3306";
        String dbname = "sddp_em_db";
        String username = "sddp_em03";
        String password = "******";
        // Example only. Use a more complex key in production.
        String mek="00112233445566778899aabbccddeeff";
        Properties props = new Properties();
        props.setProperty("user", username);
        props.setProperty("password", password);
        props.setProperty("MEK", mek);
        String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s", hostname, port, dbname);
        // Load the EncJDBC driver.
        Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");
        // Get the database connection.
        Connection connection = DriverManager.getConnection(dbUrl, props);
        // Execute a query.
        try {
            // Create a query statement.
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
            // Traverse the result set.
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("username");
                String phone = resultSet.getString("phone");
                // Process other fields based on your table schema.
                System.out.println("ID: " + id + ", Name: " + name + ", Phone: " + phone);
            }
            // Close resources.
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Sample output:

ID: 1, Name: username008808, Phone: 15xxx95
ID: 2, Name: username187643, Phone: 15xxx81

Prerequisites

Background information

Generate an MEK

KMS key

Local key

Client integration instructions

1. Install dependencies

2. ConfigureMEK to connect to the database

Connect to the database using a KMS key

JDBC properties configuration

URL configuration

Connect to the database using a local key

JDBC properties configuration

File configuration

URL configuration

3. Query plaintext data from encrypted columns

Complete code example

2. Configure`MEK` to connect to the database