[Fix][database] 支持加密字段获取API，Schema新增加密、敏感字段定义(TSG-22629)

7 files changed, 121 insertions, 2 deletions

diff --git a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/monitor_event.json b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/monitor_event.json
index b614ed50..3ad4db14 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/monitor_event.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/monitor_event.json
@@ -784,6 +784,10 @@
       "tunnel_endpoint_a_desc",
       "tunnel_endpoint_b_desc"
     ],
+    "sensitive_columns": [
+      "subscriber_id",
+      "phone_number"
+    ],
     "tunnel_type": {
       "$ref": "public_schema_info.json#/tunnel_type"
     },
@@ -2044,6 +2048,16 @@
       "label": "Subscriber ID",
       "doc": {
         "visibility": "enabled",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "subscriber_id_hmac",
+      "label": "Subscriber ID HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
@@ -2080,6 +2094,16 @@
       "label": "Phone Number",
       "doc": {
         "visibility": "enabled",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "phone_number_hmac",
+      "label": "Phone Number HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/proxy_event.json b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/proxy_event.json
index 2dbd3e99..bc4ec389 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/proxy_event.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/proxy_event.json
@@ -381,6 +381,10 @@
       "tunnel_endpoint_a_desc",
       "tunnel_endpoint_b_desc"
     ],
+    "sensitive_columns": [
+      "subscriber_id",
+      "phone_number"
+    ],
     "action_columns": {
       "intercept": [
         "proxy_pinning_status",
@@ -1640,6 +1644,16 @@
       "label": "Subscriber ID",
       "doc": {
         "visibility": "enabled",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "subscriber_id_hmac",
+      "label": "Subscriber ID HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
@@ -1676,6 +1690,16 @@
       "label": "Phone Number",
       "doc": {
         "visibility": "hidden",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "phone_number_hmac",
+      "label": "Phone Number HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/security_event.json b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/security_event.json
index e1bac814..140951e0 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/security_event.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/security_event.json
@@ -788,6 +788,10 @@
       "tunnel_endpoint_a_desc",
       "tunnel_endpoint_b_desc"
     ],
+    "sensitive_columns": [
+      "subscriber_id",
+      "phone_number"
+    ],
     "tunnel_type": {
       "$ref": "public_schema_info.json#/tunnel_type"
     },
@@ -2048,6 +2052,16 @@
       "label": "Subscriber ID",
       "doc": {
         "visibility": "enabled",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "subscriber_id_hmac",
+      "label": "Subscriber ID HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
@@ -2084,6 +2098,16 @@
       "label": "Phone Number",
       "doc": {
         "visibility": "enabled",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "phone_number_hmac",
+      "label": "Phone Number HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
diff --git a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/session_record.json b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/session_record.json
index b579f6bd..5f3f9244 100644
--- a/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/session_record.json
+++ b/config/nacos/config/fixed-127.0.0.1_8848-tsg_nacos/data/config-data-tenant/tsg/Galaxy/session_record.json
@@ -835,6 +835,10 @@
       "tunnel_endpoint_a_desc",
       "tunnel_endpoint_b_desc"
     ],
+    "sensitive_columns": [
+      "subscriber_id",
+      "phone_number"
+    ],
     "tunnel_type": {
       "$ref": "public_schema_info.json#/tunnel_type"
     },
@@ -2185,6 +2189,16 @@
       "label": "Subscriber ID",
       "doc": {
         "visibility": "enabled",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "subscriber_id_hmac",
+      "label": "Subscriber ID HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
@@ -2221,6 +2235,16 @@
       "label": "Phone Number",
       "doc": {
         "visibility": "enabled",
+        "is_encrypted": "true",
+        "ttl": null
+      },
+      "type": "string"
+    },
+    {
+      "name": "phone_number_hmac",
+      "label": "Phone Number HMAC",
+      "doc": {
+        "visibility": "hidden",
         "ttl": null
       },
       "type": "string"
diff --git a/src/main/java/com/mesalab/qgw/controller/DatabaseController.java b/src/main/java/com/mesalab/qgw/controller/DatabaseController.java
index 06f3cc38..e6d89cf1 100644
--- a/src/main/java/com/mesalab/qgw/controller/DatabaseController.java
+++ b/src/main/java/com/mesalab/qgw/controller/DatabaseController.java
@@ -2,6 +2,7 @@ package com.mesalab.qgw.controller;
 
 import cn.hutool.core.collection.CollectionUtil;
 import cn.hutool.core.util.NumberUtil;
+import cn.hutool.core.util.StrUtil;
 import com.alibaba.fastjson2.JSON;
 import com.google.common.collect.Sets;
 import com.jayway.jsonpath.JsonPath;
@@ -40,12 +41,17 @@ public class DatabaseController {
 
     @RequestMapping(value = "/table/{table_name}/schema", method = RequestMethod.GET, consumes = "application/x-www-form-urlencoded")
     @AuditLog("DatabaseController.getSchemaByTable")
-    public BaseResult getSchemaByTable(@PathVariable("table_name") String tableName) {
+    public BaseResult getSchemaByTable(@PathVariable("table_name") String tableName
+            , @RequestParam(value = "option", required = false) String option) {
         log.debug("get Schema api, params is: {}", tableName);
         if (StringUtil.isBlank(tableName)) {
             return BaseResultGenerator.success4Message("ok");
         }
-        return BaseResultGenerator.success(databaseService.getSchemaInfo(MetadataType.FIELDS.getValue(), tableName, true));
+        if (StrUtil.isBlankIfStr(option)) {
+            return BaseResultGenerator.success(databaseService.getSchemaInfo(MetadataType.FIELDS.getValue(), tableName, true));
+        }
+        List<String> tableEncryptedFields = databaseService.getTableEncryptedFields(tableName);
+        return BaseResultGenerator.success(tableEncryptedFields);
     }
 
     @PutMapping(value = "/table/{table_name}/schema", consumes = "application/json")
diff --git a/src/main/java/com/mesalab/qgw/service/DatabaseService.java b/src/main/java/com/mesalab/qgw/service/DatabaseService.java
index b811eb4e..03077881 100644
--- a/src/main/java/com/mesalab/qgw/service/DatabaseService.java
+++ b/src/main/java/com/mesalab/qgw/service/DatabaseService.java
@@ -19,6 +19,16 @@ public interface DatabaseService {
      */
     Map getSchemaInfo(String type, String name, boolean displayStorageSize);
 
+    /**
+     * Desc: Get table's encrypted fields
+     *
+     * @param table name
+     * @return {@link List<String>} encrypted fields list
+     * @created by wWei
+     * @date 2024/10/29 17:12
+     */
+    List<String> getTableEncryptedFields(String table);
+
     Map<String, List<Object>> getSchemaDataDict(String name);
 
     /**
diff --git a/src/main/java/com/mesalab/qgw/service/impl/DatabaseServiceImpl.java b/src/main/java/com/mesalab/qgw/service/impl/DatabaseServiceImpl.java
index 02c8736f..fc8f44be 100644
--- a/src/main/java/com/mesalab/qgw/service/impl/DatabaseServiceImpl.java
+++ b/src/main/java/com/mesalab/qgw/service/impl/DatabaseServiceImpl.java
@@ -41,6 +41,7 @@ import com.mesalab.qgw.service.DatabaseService;
 import com.mesalab.qgw.service.SQLSyncQueryService;
 import com.geedgenetworks.utils.StringUtil;
 import com.mesalab.services.common.property.SqlPropertySourceFactory;
+import com.mesalab.services.configuration.JobConfig;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.context.EnvironmentAware;
 import org.springframework.context.annotation.PropertySource;
@@ -115,6 +116,12 @@ public class DatabaseServiceImpl implements DatabaseService, EnvironmentAware {
     }
 
     @Override
+    public List<String> getTableEncryptedFields(String table) {
+        Map<String, Object> schema = getSchemaInfo(MetadataType.FIELDS.getValue(), table, false);
+        return JsonPath.read(JSONUtil.toJsonStr(schema), "$.fields[?(@.doc.is_encrypted == \"true\")].name");
+    }
+
+    @Override
     public Map<String, List<Object>> getSchemaDataDict(String tableName) {
         Map<String, Object> schema = getSchemaInfo(MetadataType.FIELDS.getValue(), tableName, false);
         Map<String, List<Object>> schemaDataDict = Maps.newHashMap();