Oracle Data Masking с использованием случайных имен из временной таблицы
Нам нужно замаскировать некоторую личную информацию в нашей базе данных 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 и т. Д.) В надежде, что это облегчит объяснение.
Любой вклад / совет / помощь будет принята с благодарностью:)
Благодарю.
1 ответ
Одна из сложностей заключается в том, что одна и та же фамилия может появляться под разными person_id в таблице PERSON. Возможно, вам лучше использовать отдельную вспомогательную таблицу, содержащую разные фамилии (например, вы можете заполнить ее, выбрав разные фамилии из ЛИЦ).
Настроить:
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;
CTAS для создания PERSON_TRANSFER:
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: область действия была расширена - теперь необходимо обновить surname в оригинальной таблице (PERSONS). Это может быть лучше всего сделано с merge оператор и запрос соединения (под) я продемонстрировал ранее. Это работает лучше всего, когда PERSONS таблица имеет ПК, и, действительно, ОП сказал реальный стол PERSONS имеет такой ПК, состоящий из person_id столбец и дополнительный столбец, date_from, В приведенном ниже сценарии я опускаю persons и воссоздать его, чтобы включить этот дополнительный столбец. Затем я показываю запрос и результат.
Примечание - mask_names стол еще нужен. Заманчивой альтернативой было бы просто перемешать фамилии, уже присутствующие в persons поэтому не было бы необходимости в вспомогательном столе. Увы, это не сработает. Например, в тривиальном примере persons имеет только один ряд. Чтобы запутать фамилии, НЕОБХОДИМО придумать фамилии, которых нет в исходной таблице. Более интересно, предположим, каждый person_id имеет ровно две строки с разными фамилиями, но в каждом случае эти фамилии - "Джон" и "Мэри". Это не помогает просто перемешать эти два имени. Нужна таблица "помощник", как 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.