PolarDB:EncJDBC の統合

Step 1: Configure KMS access permissions

1. Obtain an AccessKey pair: Obtain the AccessKey ID and AccessKey secret of a RAM user. This allows your application to obtain the Master Encryption Key (MEK) that is managed by KMS.

   1. If you already have a suitable RAM user to access KMS, you can use that user.

   2. If you do not have a suitable RAM user, go to the RAM console. In the left-side navigation pane, choose [Identity Management] > [Users]. Click Create User and follow the on-screen instructions to create a user.

2. Grant permissions to the RAM user:

   1. Go to the RAM console. In the left-side navigation pane, choose [Permission Management] > [Policies]. Click Create Policy. Switch to the script editor, copy the following content, and click [OK]. Enter a policy name to create the policy.

      {
          "Version": "1",
          "Statement": [
              {
                  "Effect": "Allow",
                  "Action": "KMS:Decrypt",
                  "Resource": "*"
              }
          ]
      }

   2. Return to the RAM console. In the left-side navigation pane, choose [Identity Management] > [Users]. Find the target RAM user, click Add Permissions in the [操作] column, and grant the newly created access policy to the user. This allows the client application to decrypt data by using KMS.

3. Obtain the KMS instance endpoint:

   • VPC endpoint (default): By default, keys in a KMS instance can be accessed only from within a VPC. You can go to the KMS instance management page, select the region where the instance is located, find the target KMS instance, and click [詳細] in the [操作] column. Then, on the [基本情報] tab, view the VPC endpoint of the instance.

   • Public endpoint: To access the key over the internet, enable public access on the RD Multi-Account tab. After you enable public access, you can view the [パブリックエンドポイント] on the [基本情報] tab.

Step 2: Add dependencies

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

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

Step 3: Configure connection parameters

You can configure MEK by using JDBC properties, a file, or a URL. If your JDBC is configured with two or more methods, the priority order is as follows: JDBC properties configuration > File configuration > URL configuration.

// Prepare connection information, such as the endpoint (hostname), port, database name (dbname), username, and password.
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";

// Get the 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 you use a temporary STS access credential to read the KMS key, you must also specify the obtained security token.
// String stsToken = "yourSecurityToken";

// Specify the KMS instance endpoint. Use the public endpoint for internet access or the VPC endpoint for access within a VPC.
String kmsEndpoint = "your-kms-endpoint";

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);

// The following is the connection URL format for a MySQL database: "jdbc:mysql:encdb://%s:%s/%s".
String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s", hostname, port, dbname);

// Load the EncJDBC driver for a MySQL database.
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");

// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, props);

// ... Execute a query ...

// Prepare connection information, such as the endpoint (hostname), port, database name (dbname), username, and password.
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";

// Get the 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 you use a temporary STS access credential to read the KMS key, you must also specify the obtained security token.
// String stsToken = "yourSecurityToken";

// Specify the KMS instance endpoint. Use the public endpoint for internet access or the VPC endpoint for access within a VPC.
String kmsEndpoint = "your-kms-endpoint";

// The following is the connection URL format for a MySQL database.
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);
// Use an 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 a MySQL database.
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");

// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, username, password);

// ... Execute a query ...

Step 4: Query data

After you connect to the database, you can perform database operations as you would with a regular JDBC query. EncJDBC automatically decrypts data in encrypted columns and returns it as plaintext.

Example

// Create a query statement.
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table_name");

// Iterate over the result set.
while (resultSet.next()) {
    for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
        System.out.print(resultSet.getString(i + 1));
        System.out.print("\t");
    }
    System.out.print("\n");
}

Appendix: Code example

import java.sql.*;
import java.util.Properties;

public class EncryptedColumnAccess {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // Replace the following connection details with your instance information, such as hostname, port, dbname, username, and password.
        String hostname = "your-hostname";
        String port = "your-port";
        String dbname = "your-database-name";
        String username = "your-username";
        String password = "your-password";

        // Get the 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 you use a temporary STS access credential to read the KMS key, you must also specify the obtained security token.
        // String stsToken = "yourSecurityToken";

        // Specify the KMS instance endpoint. Use the public endpoint for internet access or the VPC endpoint for access within a VPC.
        String kmsEndpoint = "your-kms-endpoint";

        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);

        // The connection URL format for a MySQL database is "jdbc:mysql:encdb://%s:%s/%s".
        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");

            // Iterate over 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 the resources.
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Step 1: Generate an MEK

MEK: The client securely transmits the MEK to the database server using envelope encryption, ensuring the key is never exposed in plaintext during transit.

Value range: A 16-byte, 32-character hexadecimal string. Example: 00112233445566778899aabbccddeeff.

Common generation methods: Use a password generation tool or a random function in a programming language.

Example

• On a Linux system, use the built-in OpenSSL tool. Run the openssl rand -hex 16 command to generate a key.

  # Use OpenSSL to generate a secure random key
  openssl rand -hex 16
  # Example output: 11ce9a9489fab7355cf710837cea5d5b

• On a Windows system, install the OpenSSL package.

Step 2: Add dependencies

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

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

Step 3: Configure the MEK database connection

You can configure the MEK by using JDBC properties, a file, or a URL. If you use two or more methods, the following priority order applies: JDBC properties > File > URL.

// Prepare connection information, such as the endpoint (hostname), port, database name (dbname), username, and password.
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";

// The customer master key.
String mek = "00112233445566778899aabbccddeeff"; 

Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
props.setProperty("MEK", mek);

// The connection URL format for a MySQL database is "jdbc:mysql:encdb://%s:%s/%s".
String dbUrl = String.format("jdbc:mysql:encdb://%s:%s/%s", hostname, port, dbname);

// Load the EncJDBC driver for a MySQL database.
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");

// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, props);

// ... Execute a query ...

MEK=00112233445566778899aabbccddeeff

// Prepare connection information, such as the endpoint (hostname), port, database name (dbname), username, and password.
String hostname = "your-hostname";
String port = "your-port";
String dbname = "your-database-name";
String username = "your-username";
String password = "your-password";

 // The customer master key.
String mek = "00112233445566778899aabbccddeeff";

// The connection URL format for a MySQL database is "jdbc:mysql: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 a MySQL database.
Class.forName("com.aliyun.encdb.mysql.jdbc.EncDriver");

// Get the database connection.
Connection connection = DriverManager.getConnection(dbUrl, username, password);

// ... Execute a query ...

Step 4: Query data

After you connect to the database, you can perform database operations as you would with a regular JDBC query. EncJDBC automatically decrypts data in encrypted columns and returns it as plaintext.

Example

// Create a query statement.
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table_name");

// Iterate over the result set.
while (resultSet.next()) {
    for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
        System.out.print(resultSet.getString(i + 1));
        System.out.print("\t");
    }
    System.out.print("\n");
}

Appendix: Code example

import java.sql.*;
import java.util.Properties;

public class EncryptedColumnAccess {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // Replace the following connection details with your instance information, such as hostname, port, dbname, username, and password.
        String hostname = "your-hostname";
        String port = "your-port";
        String dbname = "your-database-name";
        String username = "your-username";
        String password = "your-password";
        
        // This is an example key. We recommend that you 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);
        
        // The connection URL format for a MySQL database is "jdbc:mysql:encdb://%s:%s/%s".
        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");

            // Iterate over 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 the resources.
            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}