Using JDBC to Operate an Encrypted Database

Configuring the JDBC Driver

1. Obtain the JDBC driver package. For details about how to obtain and use the JDBC driver, see "Application Development Guide > Development Based on JDBC" and "Application Development Guide > Compatibility Reference > JDBC Compatibility Package" in Developer Guide.

   The encrypted database supports the gscejdbc.jar, gaussdbjdbc.jar, and gaussdbjdbc-JRE7.jar JDBC driver packages.

   • gscejdbc.jar (for EulerOS only): The main class name is com.huawei.gaussdb.jdbc.Driver. The URL prefix of the database connection is jdbc:gaussdb. This driver package contains the dependent libraries related to encryption and decryption that need to be loaded to the encrypted database. You are advised to use this driver package in encrypted scenarios. The Java code examples in this section use the gscejdbc.jar package by default.
   • gaussdbjdbc.jar: The main class name is com.huawei.gaussdb.jdbc.Driver. The URL prefix of the database connection is jdbc:gaussdb. This driver package does not contain the dependent libraries related to encryption and decryption that need to be loaded to an encrypted database. You need to manually configure the LD_LIBRARY_PATH environment variable.
   • gaussdbjdbc-JRE7.jar: The main class name is com.huawei.gaussdb.jdbc.Driver. The URL prefix of the database connection is jdbc:gaussdb. The gaussdbjdbc-JRE7.jar package is used in the JDK 1.7 environment. This driver package does not contain the dependent libraries related to encryption and decryption that need to be loaded to an encrypted database. You need to manually configure the LD_LIBRARY_PATH environment variable.
   

   Other compatibility: The encrypted database also supports other compatible JDBC driver packages: gsjdbc4.jar and opengaussjdbc.jar.

   • gsjdbc4.jar: The main class name is org.postgresql.Driver, and the URL prefix of the database connection is jdbc:postgresql.
   • opengaussjdbc.jar: The main class name is com.huawei.opengauss.jdbc.Driver, and the URL prefix of the database connection is jdbc:opengauss.
2. Configure LD_LIBRARY_PATH.

   Before using the JDBC driver package in encrypted scenarios, you need to set the environment variable LD_LIBRARY_PATH.

   • When the gscejdbc.jar driver package is used, the dependent library required by the encrypted database in the gscejdbc.jar driver package is automatically copied to the path and loaded when connecting to the database with the encrypted database function enabled.
   • When using gaussdbjdbc.jar, gaussdbjdbc-JRE7.jar, opengaussjdbc.jar, or gsjdbc4.jar, you need to decompress GaussDB-Kernel_Database version number_OS version number_64bit_libpq.tar.gz to a specified directory, and add the path of the lib folder to the LD_LIBRARY_PATH environment variable.
     

     To use the JDBC driver package in the full-encryption scenario, you must have the System.loadLibrary permission as well as the read and write permissions on files in the first-priority path of the environment variable LD_LIBRARY_PATH. You are advised to use an independent directory to store the full-encryption dependent library. If java.library.path is specified during execution, the value must be the same as the first-priority path of LD_LIBRARY_PATH.

When gscejdbc.jar is used, JVM that loads class files depends on the libstdc++ library of the system. If the encrypted database function is enabled, gscejdbc.jar automatically copies the dynamic libraries (including the libstdc++ library) on which the encrypted database depends to the LD_LIBRARY_PATH path set by the user. If the version of a dependent library does not match that of the existing system library, only the dependent library is deployed during the first running. After the dependent library is called again, it can be used normally.

Executing SQL Statements

Before running the SQL statements in this section, ensure that the master key has been generated and the parameters for accessing the master key are clear.

This section uses a complete execution process as an example to describe how to use the encrypted database syntax, including three phases: DDL statement execution, DML statement execution, and cleanup.

For details about JDBC development operations that are the same as those in non-encrypted scenarios, see "Application Development Guide > Development Based on JDBC" in Developer Guide.

• Connection parameters of an encrypted database

  enable_ce: string type. If enable_ce is set to 0, the full encryption function is disabled. If enable_ce is set to 1, the basic capability of encrypted equality query is supported. If enable_ce is set to 3, the memory decryption emergency channel is supported based on the encrypted equality query capability.

  // The following uses the gscejdbc.jar driver as an example. If other driver packages are used, you only need to change the driver class name and the URL prefix of the database connection.
  // gsjdbc4.jar: The main class name is org.postgresql.Driver, and the URL prefix of the database connection is jdbc:postgresql.
  // opengaussjdbc.jar: The main class name is com.huawei.opengauss.jdbc.Driver, and the URL prefix of the database connection is jdbc:opengauss.
  // gscejdbc.jar: The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb.
  // gaussdbjdbc.jar: The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb.
  // gaussdbjdbc-JRE7.jar: The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb.
  
  public static void main(String[] args) {
      // Driver class.
      String driver = "com.huawei.gaussdb.jdbc.Driver";
      // Database connection descriptor. If enable_ce is set to 1, the encrypted equality query basic capability is supported.
      String sourceURL = "jdbc:gaussdb://127.0.0.1:8000/postgres?enable_ce=1";
      // Set the username and password in the environment variables USER and PASSWORD, respectively.
      String username = System.getenv("USER");
      String passwd = System.getenv("PASSWORD");
      Connection conn = null;
      try {
          // Load the driver.
          Class.forName(driver);
          // Create a connection.
          conn = DriverManager.getConnection(sourceURL, username, passwd);
          System.out.println("Connection succeed!");
          // Create a statement object.
          Statement stmt = conn.createStatement();
  
         // Set the parameters for accessing the master key.
          // Two methods are provided here. Select either of them.
          // Authentication mode 1: AK/SK authentication (For details about how to obtain parameters, including the project ID, AK, and SK, see the master key generation phase.)
          conn.setClientInfo("key_info", "keyType=huawei_kms, kmsProjectId={Project ID}, ak={AK}, sk={SK}");
          
          /* Example:
               conn.setClientInfo("key_info", "keyType=huawei_kms,kmsProjectId=0b59929e8100268a2f22c01429802728," + 
                   "ak=XMAUMJY******DFWLQW, sk=ga6rO8lx1Q4uB*********2gf80muIzUX,");
          */
          // Authentication mode 2: Account and password authentication (For details about how to obtain related parameters, including the IAM server address, IAM username, IAM user password, account name, and project, see the master key generation phase.)
          conn.setClientInfo("key_info", "keyType=huawei_kms," +
              "iamUrl={IAM server address}," +
              "iamUser={IAM username}," +
              "iamPassword={IAM user password}," +
              "iamDomain={Account name}," +
              "kmsProject={Project}");
          /* Example:
          conn.setClientInfo("key_info", "keyType=huawei_kms," +
                 "iamUrl=https://iam.example.com/v3/auth/tokens," +
                  "iamUser=test," +
                  "iamPassword=*********," +
                  "iamDomain=test_account," +
                  "kmsProject=xxx");
          */
  
          // Define the master key. cmk1 is the master key name, which can be customized.
          // For details about how to obtain the following parameters, including KMS server address and key ID, see the master key generation phase.
          int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = huawei_kms , KEY_PATH = '{KMS server address}/{Key ID}', ALGORITHM = AES_256);");
          
          /* 
               Example of KEY_PATH: https://kms.cn-north-4.myhuaweicloud.com/v1.0/0b59929e8100268a2f22c01429802728/kms/9a262917-8b31-41af-a1e0-a53235f32de9
               Explanation: In the master key generation phase, the KMS has generated and stored the master key. Running this syntax only stores the master key information in the database for future access.
               Note: For details about the KEY_PATH format, see "SQL Reference > SQL Syntax > CREATE CLIENT MASTER KEY" in Developer Guide.
          */
  
          // Define a column encryption key: The column key is encrypted by the master key created in the previous step. For details about the syntax, see "SQL Reference > SQL Syntax > CREATE COLUMN ENCRYPTION KEY" in Developer Guide.
          int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AES_256_GCM);");
          // Define an encrypted table.
          int rc3 = stmt.executeUpdate("CREATE TABLE creditcard_info (id_number int, name varchar(50) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC),credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));");
          // Insert data.
          int rc4 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');");
          // Query the encrypted table.
          ResultSet rs = null;
          rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';");
          // Delete the encrypted table.
          int rc5 = stmt.executeUpdate("DROP TABLE IF EXISTS creditcard_info;");
          // Delete the CEK.
          int rc6 = stmt.executeUpdate("DROP COLUMN ENCRYPTION KEY IF EXISTS ImgCEK1;");
          // Delete the CMK.
          int rc7 = stmt.executeUpdate("DROP CLIENT MASTER KEY IF EXISTS ImgCMK1;");
          // Close the statement object.
          stmt.close();
          // Close the connection.
          conn.close();
      } catch (Exception e) {
          e.printStackTrace();
          return;
      }
  }

  

  • When JDBC is used to perform operations on an encrypted database, one database connection object corresponds to one thread. Otherwise, conflicts may occur due to thread changes.
  • When JDBC is used to perform operations on an encrypted database, different connections change the encrypted configuration data. The client calls the isValid method to ensure that the connections can hold the changed encrypted configuration data. In this case, the refreshClientEncryption parameter must be set to 1 (default value). In a scenario where a single client performs operations on encrypted data, the refreshClientEncryption parameter can be set to 0.

Decrypting the Encrypted Equality Ciphertext

A decryption API is added to the PgConnection class to decrypt the encrypted equality ciphertext of the fully-encrypted database. After decryption, the plaintext value is returned. The ciphertext column corresponding to the decryption is found based on schema.table.column and the original data type is returned.

// After the ciphertext is obtained through non-encrypted connection or logical decoding, this API can be used to decrypt the ciphertext.
import com.huawei.gaussdb.jdbc.jdbc.PgConnection;
import com.huawei.gaussdb.jdbc.jdbc.clientlogic.ClientLogicDecryptResult;

// conn is an encrypted connection.
// Call the decryptData method of PgConnection to decrypt the ciphertext, locate the encrypted column to which the ciphertext belongs based on the column name, and return the original data type.
ClientLogicDecryptResult decrypt_res = null;
decrypt_res = ((PgConnection)conn).decryptData(ciphertext, ciphertext.length(), schemaname_str,
        tablename_str, colname_str);
// Check whether the decryption of the returned result class is successful. If the decryption fails, obtain the error information. If the decryption is successful, obtain the plaintext, length, and original data type.
if (decrypt_res.isFailed()) {
    System.out.println(String.format("%s\n", decrypt_res.getErrMsg()));
} else {
    System.out.println(String.format("decrypted plaintext: %s size: %d type: %s\n", decrypt_res.getPlaintext(),
        decrypt_res.getPlaintextSize(), decrypt_res.getOriginalType()));
}

Precompiling an Encrypted Table

// Create a prepared statement object by calling the prepareStatement method in Connection.
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO creditcard_info VALUES (?, ?, ?);");
// Set parameters by triggering the setShort method in PreparedStatement.
pstmt.setInt(1, 2);
pstmt.setString(2, "joy");
pstmt.setString(3, "6219985678349800033");
// Execute the precompiled SQL statement by triggering the executeUpdate method in PreparedStatement.
int rowcount = pstmt.executeUpdate();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();

Batch Processing on Encrypted Tables

// Create a prepared statement object by calling the prepareStatement method in Connection.
Connection conn = DriverManager.getConnection("url","user","password");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO creditcard_info (id_number, name, credit_card) VALUES (?,?,?)");
// Call the setShort method for each piece of data, and call addBatch to confirm that the setting is complete.
int loopCount = 20;
 for (int i = 1; i < loopCount + 1; ++i) {
      pstmt.setInt(1, i);
      pstmt.setString(2, "Name " + i);
      pstmt.setString(3, "CreditCard " + i);
      // Add row to the batch.
      pstmt.addBatch();
}
// Execute batch processing by calling the executeBatch method in PreparedStatement.
int[] rowcount = pstmt.executeBatch();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();