时间戳

首页 > 解决方案 > 在 JOIN 查询中生成匿名 ID

mysql - 在 JOIN 查询中生成匿名 ID

问题描述

任务是从敏感系统中提取客户和订单数据。数据存储在 MySQL 数据库中。

一个客户可以与许多订单相关联。一个简单的 LEFT JOIN 正是我所需要的：

---------------------------------------------------------
| customer_id | order_id | order_quantity | order_value |
---------------------------------------------------------
|      1      |    100   |       3        |    100.00   |
|      1      |    105   |      12        |    400.00   |
|      2      |    103   |       2        |     75.00   |
---------------------------------------------------------

但是，在生成的摘录中，我不允许透露.customer_id或order_id. 相反，这些 id 需要被数据导出时生成的随机匿名标识符替换。

客户与其订单之间的关系仍需要在生成的提取数据导出中维护。

期望的结果：

-------------------------------------------------------------------
| anon_customer_id | anon_order_id | order_quantity | order_value |
-------------------------------------------------------------------
|         xyz      |      abc123   |       3        |    100.00   |
|         xyz      |      def567   |      12        |    400.00   |
|         pqr      |      hij890   |       2        |     75.00   |
-------------------------------------------------------------------

有没有办法生成anon_customer_id并anon_order_id作为SELECT我正在运行以构建数据结果的一部分？

标签： mysqlsql

解决方案

一种选择是使用 MySQL 的本机加密方法，例如SHA1orSHA2并创建一个 VIEW 供您查询和加入。
我选择使用 SHA 512 是因为不同数据生成相同散列的概率非常低。

CREATE VIEW Table1_VIEW AS (
   SELECT 
      <table>.*
    , SHA2(<table>.customer_id, 512) AS anon_customer_id
    , SHA2(<table>.order_id, 512) AS anon_order_id
   FROM 
    <table>
 )

查询和结果

SELECT 
 *
FROM 
 Table1_VIEW

| customer_id | order_id | order_quantity | order_value | anon_customer_id                                                                                                                 | anon_order_id                                                                                                                    |
| ----------- | -------- | -------------- | ----------- | -------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------- |
| 1           | 100      | 3              | 100         | 4dff4ea340f0a823f15d3f4f01ab62eae0e5da579ccb851f8db9dfe84c58b2b37b89903a740e1ee172da793a6e79d560e5f7f9bd058a12a280433ed6fa46510a | 643c30f73a3017050b287794fc8c5bb9ab06b9ce38a1fc58df402a8b66ff58f69bf0a606ae17585352a0306f0e9752de8c5c064aed7003f52808b43ff992a603 |
| 1           | 105      | 12             | 400         | 4dff4ea340f0a823f15d3f4f01ab62eae0e5da579ccb851f8db9dfe84c58b2b37b89903a740e1ee172da793a6e79d560e5f7f9bd058a12a280433ed6fa46510a | 03d25c7071bce10d6b462d53854b969d9f61b982e3aee8771bdcca1ecb70495574e6929042f52e859ee9a253b58f776514180ff16e1338f5505e86c7ff328f72 |
| 2           | 103      | 2              | 75          | 40b244112641dd78dd4f93b6c9190dd46e0099194d5a44257b7efad6ef9ff4683da1eda0244448cb343aa688f5d3efd7314dafe580ac0bcbf115aeca9e8dc114 | 947de04bfae0bf062a66fc055d4c284c9779793d9bd58833ee7549fde1ff1effaf7aefdbc6c90ed0ac86c0acc82329e7c057d900c28ea7ed4724486f717ee38d |

演示

ps 也可以SHA2()直接在JOIN课外直接使用。

示例查询

SELECT 
      table11.*
    , SHA2(table11.customer_id, 512) AS anon_customer_id
    , SHA2(table11.order_id, 512) AS anon_order_id 
FROM 
 Table1 table11
LEFT JOIN
 Table1 table12
ON
 table11.customer_id = table12.customer_id

演示

仅限 MYSQL 5.7+

如果您至少有 MySQL 5.7+，那么您有一个更好的选择。
哪个是生成的列

CREATE TABLE Table1 (
  `customer_id` INTEGER,
  `order_id` INTEGER,
  `order_quantity` INTEGER,
  `order_value` INTEGER,
  anon_customer_id VARCHAR(255) AS ( SHA2(Table1.customer_id, 512) ) VIRTUAL,
  anon_order_id VARCHAR(255) AS ( SHA2(Table1.order_id, 512) ) VIRTUAL
);

演示

由于路易斯的评论而编辑

我的观点是，有人将能够在散列后提取敏感的客户 ID。只需计算所有可能或可能的客户 ID 的哈希值并查看哪些是相同的。如果客户 ID 不是具有可预测范围的递增数字，而是一些随机分配的非常大的数字或确实是长随机字符串，则可能会更好

这是非常真实的，你可以做的是向哈希添加更多的熵，这样真实的 id 就不会那么容易暴力破解。在这种情况下，您添加至少 52 个字符（datetime(6) 和相反的一个）作为熵，这应该足以在未来（一些）年内防止暴力破解。

CREATE VIEW Table1_VIEW_more_entropy AS (
   SELECT 
      Table1.*
    , SHA2(CONCAT_WS(':', Table1.id, Table1.date_created, REVERSE(Table1.date_created), Table1.customer_id), 512)
    , SHA2(CONCAT_WS(':', Table1.id, Table1.date_created, REVERSE(Table1.date_created), Table1.order_id), 512)   
   FROM 
    Table1
 );

看演示

推荐阅读