資料庫密態等值查詢概述及操作

語言: CN / TW / HK

目錄

1. 密態等值查詢概述

2. 使用gsql操作密態資料庫

操作步驟

3. 使用JDBC操作密態資料庫

連線密態資料庫

呼叫isValid方法重新整理快取示例

執行密態等值查詢相關的建立金鑰語句

執行密態等值查詢相關的建立加密表的語句

執行加密表的預編譯SQL語句

執行加密表的批處理操作


1. 密態等值查詢概述

隨著企業資料上雲,資料的安全隱私保護面臨越來越嚴重的挑戰。密態資料庫將解決資料整個生命週期中的隱私保護問題,涵蓋網路傳輸、資料儲存以及資料執行態;更進一步,密態資料庫可以實現雲化場景下的資料隱私許可權分離,即實現資料擁有者和實際資料管理者的資料讀取能力分離。密態等值查詢將優先解決密文資料的等值類查詢問題。密態等值查詢目前支援客戶端工具gsql和JDBC。接下來分別介紹如何使用客戶端工具執行密態等值查詢的相關操作。

2. 使用gsql操作密態資料庫

操作步驟

  1. 以作業系統使用者omm登入主節點。
  2. 執行以下命令開啟密態開關,連線密態資料庫。

    gsql -p PORT postgres -r -C

    這裡,PORT需要替換為實際值。

  3. 建立客戶端主金鑰CMK和列加密金鑰CEK。建立CMK的語法請參考CREATE CLIENT MASTER KEY、建立的CEK的語法請參考CREATE COLUMN ENCRYPTION KEY

    --建立客戶端加密主金鑰(CMK)
    openGauss=# CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value1", ALGORITHM = RSA_2048);
    openGauss=# CREATE CLIENT MASTER KEY ImgCMK WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value2", ALGORITHM = RSA_2048);
    openGauss=# CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
    CREATE COLUMN ENCRYPTION KEY
    openGauss=# CREATE COLUMN ENCRYPTION KEY ImgCEK WITH VALUES (CLIENT_MASTER_KEY = ImgCMK, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
    CREATE COLUMN ENCRYPTION KEY

    查詢儲存金鑰資訊的系統表結果如下。

    openGauss=# SELECT * FROM gs_client_global_keys;
     global_key_name | key_namespace | key_owner | key_acl |        create_date
    -----------------+---------------+-----------+---------+----------------------------
     imgcmk1         |          2200 |        10 |         | 2021-04-21 11:04:00.656617
     imgcmk          |          2200 |        10 |         | 2021-04-21 11:04:05.389746
    (2 rows)
    openGauss=# SELECT column_key_name,column_key_distributed_id ,global_key_id,key_owner  FROM gs_column_keys;
     column_key_name | column_key_distributed_id | global_key_id | key_owner
    -----------------+---------------------------+---------------+-----------
     imgcek1         |                 760411027 |         16392 |        10
     imgcek          |                3618369306 |         16398 |        10
    (2 rows)

  4. 建立加密表。

    openGauss=# CREATE TABLE creditcard_info (id_number    int, name         text encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC),
    credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id_number' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE

    查詢表的詳細資訊如下,Modifiers值為encrypted則表示該列是加密列。

    openGauss=# \d creditcard_info
            Table "public.creditcard_info"
       Column    |       Type        | Modifiers
    -------------+-------------------+------------
     id_number   | integer           |
     name        | text              |  encrypted
     credit_card | character varying |  encrypted

  5. 向加密表插入資料並進行等值查詢。

    openGauss=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');
    INSERT 0 1
    openGauss=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033');
    INSERT 0 1
    openGauss=# select * from creditcard_info where name = 'joe';
     id_number | name |     credit_card
    -----------+------+---------------------
             1 | joe  | 6217986500001288393
    (1 row)
    注意:使用非密態客戶端檢視該加密表資料時是密文
    openGauss=# select id_number,name from creditcard_info;
     id_number |                                                                         name
    -----------+------------------------------------------------------------------------------------------------------------------------------------------------------
             1 | \x011aefabd754ded0a536a96664790622487c4d366d313aecd5839e410a46d29cba96a60e4831000000ee79056a114c9a6c041bb552b78052e912a8b730609142074c63791abebd0d38
             2 | \x011aefabd76853108eb406c0f90e7c773b71648fa6e2b8028cf634b49aec65b4fcfb376f3531000000f7471c8686682de215d09aa87113f6fb03884be2031ef4dd967afc6f7901646b
    (2 rows)

  6. (可選)對加密表進行alter和update操作。

    openGauss=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ImgCEK, ENCRYPTION_TYPE = DETERMINISTIC);
    ALTER TABLE
    openGauss=# \d creditcard_info
            Table "public.creditcard_info"
       Column    |       Type        | Modifiers
    -------------+-------------------+------------
     id_number   | integer           |
     name        | text              |  encrypted
     credit_card | character varying |  encrypted
     age         | integer           |  encrypted
    openGauss=# ALTER TABLE creditcard_info DROP COLUMN age;
    ALTER TABLE
    openGauss=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy';
    UPDATE 1
    openGauss=# select * from creditcard_info  where name = 'joy';
     id_number | name |    credit_card
    -----------+------+-------------------
             2 | joy  | 80000000011111111
    (1 row)

3. 使用JDBC操作密態資料庫

連線密態資料庫

連線密態資料庫需要使用驅動包gsjdbc4.jar,具體JDBC連線引數參考基於JDBC開發章節介紹。JDBC支援密態資料庫相關操作,需要設定enable_ce=1,示例如下。

public static Connection getConnect(String username, String passwd)
    {
        //驅動類。
        String driver = "org.postgresql.Driver";
        //資料庫連線描述符。
        String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres?enable_ce=1";
        Connection conn = null;
        
        try
        {
            //載入驅動。
            Class.forName(driver);
        }
        catch( Exception e )
        {
            e.printStackTrace();
            return null;
        }
        
        try
        {
             //建立連線。
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("Connection succeed!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        }
        
        return conn;
    };

 說明:

  • 【建議】使用JDBC操作密態資料庫時,一個數據庫連線物件對應一個執行緒,否則,不同執行緒變更可能導致衝突。

  • 【建議】使用JDBC操作密態資料庫時,不同connection對密態配置資料有變更,由客戶端呼叫isvalid方法保證connection能夠持有變更後的密態配置資料,此時需要保證引數refreshClientEncryption為1(預設值為1),在單客戶端操作密態資料場景下,refreshClientEncryption引數可以設定為0。

呼叫isValid方法重新整理快取示例

// 建立客戶端主金鑰
Connection conn1 = DriverManager.getConnection("url","user","password");

// conn1通過呼叫isValid重新整理快取
try {
 if (!conn1.getConnection().isValid(60)) {
  conn1.getFileWriter().writeLine("isValid Failed for connection 1");
 }
} catch (SQLException e) {
 conn1.getFileWriter().writeLine("isValid Failed with error");
 e.printStackTrace();
}

執行密態等值查詢相關的建立金鑰語句

// 建立客戶端主金鑰
Connection conn = DriverManager.getConnection("url","user","password");
Statement stmt = conn.createStatement();
int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = gs_ktool, KEY_PATH = \"gs_ktool/1\", ALGORITHM = AES_256_CBC;"); 
int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = localkms, KEY_PATH = \"key_path_value\" , ALGORITHM = RSA_2048);

 說明: 建立金鑰之前需要使用gs_ktool工具提前生成金鑰,才能建立CMK成功。

// 建立列加密金鑰
int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);");

執行密態等值查詢相關的建立加密表的語句

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));");
// 插入資料
int rc4 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');");
// 查詢加密表
ResultSet rs = null;
rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';");
// 關閉語句物件
stmt.close();

執行加密表的預編譯SQL語句

// 呼叫Connection的prepareStatement方法建立預編譯語句物件。
PreparedStatement pstmt = con.prepareStatement("INSERT INTO creditcard_info VALUES (?, ?, ?);");
// 呼叫PreparedStatement的setShort設定引數。
pstmt.setInt(1, 2);
pstmt.setString(2, "joy");
pstmt.setString(3, "6219985678349800033");
// 呼叫PreparedStatement的executeUpdate方法執行預編譯SQL語句。
int rowcount = pstmt.executeUpdate();
// 呼叫PreparedStatement的close方法關閉預編譯語句物件。
pstmt.close();

執行加密表的批處理操作

// 呼叫Connection的prepareStatement方法建立預編譯語句物件。
Connection conn = DriverManager.getConnection("url","user","password");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO batch_table (id, name, address) VALUES (?,?,?)");
// 針對每條資料都要呼叫setShort設定引數,以及呼叫addBatch確認該條設定完畢。
int loopCount = 20;
 for (int i = 1; i < loopCount + 1; ++i) {
      statemnet.setInt(1, i);
      statemnet.setString(2, "Name " + i);
      statemnet.setString(3, "Address " + i);
      // Add row to the batch.
      statemnet.addBatch();
}
// 呼叫PreparedStatement的executeBatch方法執行批處理。
int[] rowcount = pstmt.executeBatch();
// 呼叫PreparedStatement的close方法關閉預編譯語句物件。
pstmt.close();