database - Oracle Data Masking 使用临时表中的随机名称

Question

我们需要在 Oracle 10g 数据库中屏蔽一些个人身份信息。我正在使用的过程基于我们用于 Sybase 的另一个屏蔽脚本(它工作正常)，但由于 Oracle 和 Sybase 数据库中的信息完全不同，我遇到了一些障碍。

该过程是将 PERSON 表中的所有数据选择到 PERSON_TRANSFER 表中。然后，我们使用随机数从 PERSON_TRANSFER 表中选择一个随机名称，然后使用该随机名称更新 PERSON 表。这在 Sybase 中运行良好，因为在 PERSON 表中每个人只有一行。

我遇到的问题是，在 Oracle 数据库中，每个人有多行，并且每一行的名称可能不同，也可能不同，例如

|PERSON|
:-----------------:
|PERSON_ID|SURNAME|
|1        |Purple |
|1        |Purple |
|1        |Pink   | <--
|2        |Gray   |
|2        |Blue   | <--
|3        |Black  |
|3        |Black  |

PERSON_TRANSFER 是此表的副本。该表有数百万行，所以我在这里只给出一个非常基本的例子:)

我目前使用的逻辑只是将所有行更新为与该 PERSON_ID 相同，例如

|PERSON|
:-----------------:
|PERSON_ID|SURNAME|
|1        |Brown  |
|1        |Brown  |
|1        |Brown  | <--
|2        |White  |
|2        |White  | <--
|3        |Red    |
|3        |Red    |

但这是不正确的，因为该 PERSON_ID 的不同名称需要以不同方式屏蔽，例如

|PERSON|
:-----------------:
|PERSON_ID|SURNAME|
|1        |Brown  |
|1        |Brown  |
|1        |Yellow | <--
|2        |White  |
|2        |Green  | <--
|3        |Red    |
|3        |Red    |

我如何让脚本分别更新不同的名称，而不是仅仅根据 PERSON_ID 更新它们？我的脚本目前看起来像这样

DECLARE
    v_SURNAME    VARCHAR2(30);

BEGIN

    select pt.SURNAME
    into  v_SURNAME
    from   PERSON_TRANSFER pt
    where   pt.PERSON_ID = (SELECT PERSON_ID FROM
                            ( SELECT PERSON_ID FROM PERSON_TRANSFER
                            ORDER BY dbms_random.value )
                            WHERE rownum = 1);
END;

这会导致错误，因为为该随机 PERSON_ID 返回的行太多。

1) 是否有更有效的方法来更新 PERSON 表以便随机分配姓名？2) 我如何确保 PERSON 表被正确屏蔽，因为对于任何一个 PERSON_ID，不同的姓氏保持不同(或者相同，如果它们都相同)？

我希望这是足够的信息。我对它做了一些简化(该表有更多的列，例如名字、DOB、TFN 等)，希望它能使解释更容易。

任何输入/建议/帮助将不胜感激:)

谢谢。

Answer 1

复杂性之一是相同的姓氏可能出现在 PERSON 表中不同的 person_id 下。您最好使用一个单独的辅助表来保存不同的姓氏(例如，您可以通过从 PERSONS 选择不同的姓氏来填充它)。

设置:

create table persons (person_id, surname) as (
  select 1, 'Purple' from dual union all
  select 1, 'Purple' from dual union all
  select 1, 'Pink'   from dual union all
  select 2, 'Gray'   from dual union all
  select 2, 'Blue'   from dual union all
  select 3, 'Black'  from dual union all
  select 3, 'Black'  from dual
);
create table mask_names (person_id, surname) as (
  select 1, 'Apple'  from dual union all
  select 2, 'Banana' from dual union all
  select 3, 'Grape'  from dual union all
  select 4, 'Orange' from dual union all
  select 5, 'Pear'   from dual union all
  select 6, 'Plum'   from dual
);
commit;

创建 PERSON_TRANSFER 的 CTAS:

create table person_transfer (person_id, surname) as (
select ranked.person_id, rand.surname
from   ( select person_id, surname, 
                dense_rank() over (order by surname) as rk
         from   persons
       ) ranked 
       inner join 
       ( select surname, row_number() over (order by dbms_random.value()) as rnd
         from   mask_names
       ) rand
              on ranked.rk = rand.rnd
);
commit;

结果:

SQL> select * from person_transfer order by person_id, surname;

 PERSON_ID SURNAME
---------- -------
         1 Pear
         1 Pear
         1 Plum
         2 Banana
         2 Grape
         3 Apple
         3 Apple

应 OP 的要求添加:范围已扩展 - 现在的要求是更新原始表 (PERSONS) 中的 surname。这可以最好地通过 merge 语句和我之前演示的连接(子)查询来完成。这在 PERSONS 表有 PK 时效果最好，实际上 OP 说现实生活中的表 PERSONS 有这样的 PK，由 person_id 列和附加列 date_from。在下面的脚本中，我删除了 persons 并重新创建它以包含此附加列。然后我显示查询和结果。

注意 - 仍然需要一个 mask_names 表。一个诱人的替代方案是将 persons 中已经存在的姓氏打乱，这样就不需要“助手”表了。唉，那是行不通的。例如，在一个简单的例子中，persons 只有一行。为了混淆姓氏，必须想出原始表中没有的姓氏。更有趣的是，假设每个 person_id 恰好有两行，具有不同的姓氏，但在每种情况下这些姓氏都是“John”和“Mary”。只是将这两个名字混在一起并没有帮助。确实需要一个像 mask_names 这样的“助手”表。

新设置:

drop table persons;

create table persons (person_id, date_from, surname) as (
  select 1, date '2016-01-04', 'Purple' from dual union all
  select 1, date '2016-01-20', 'Purple' from dual union all
  select 1, date '2016-03-20', 'Pink'   from dual union all
  select 2, date '2016-01-24', 'Gray'   from dual union all
  select 2, date '2016-03-21', 'Blue'   from dual union all
  select 3, date '2016-04-02', 'Black'  from dual union all
  select 3, date '2016-02-13', 'Black'  from dual
);

commit;

select * from persons;


 PERSON_ID DATE_FROM  SURNAME
---------- ---------- -------
         1 2016-01-04 Purple
         1 2016-01-20 Purple
         1 2016-03-20 Pink
         2 2016-01-24 Gray
         2 2016-03-21 Blue
         3 2016-04-02 Black
         3 2016-02-13 Black

7 rows selected.

新查询和结果:

merge into persons p
  using (
          select ranked.person_id, ranked.date_from, rand.surname
          from ( 
                 select person_id, date_from, surname, 
                        dense_rank() over (order by surname) as rk
                 from   persons
               ) ranked 
          inner join ( 
                 select surname, row_number() over (order by dbms_random.value()) as rnd
                 from   mask_names
               ) rand
          on ranked.rk = rand.rnd
        ) t
  on (p.person_id = t.person_id and p.date_from = t.date_from)
when matched then update
  set p.surname = t.surname;

commit;

select * from persons;

 PERSON_ID DATE_FROM  SURNAME
---------- ---------- -------
         1 2016-01-04 Apple
         1 2016-01-20 Apple
         1 2016-03-20 Orange
         2 2016-01-24 Plum
         2 2016-03-21 Grape
         3 2016-04-02 Banana
         3 2016-02-13 Banana

7 rows selected.