Exporter & anonymiser des données [Résolu]

[CPI_en_mousse]

Bonjour à tous,

J'ai besoin de faire un dump de ma base de données tout en anonymisant certaines données, notamment des noms. Malheureusement, je ne vois pas comment je peux faire pour y inclure dans la requête de dump.

L'anonymisation peut être de différente forme, soit une séquence (nom1, nom2, nom3, ...) ou une randomisation (Mr dupigniouf deviendrai Mr ddfgshdfkjhe) ou toutes autres idées.

merci pour vos réponses

[vmolines]

Code :

UPDATE LATABLE SET NOM = 'NOM_' + ID

Puis vous faites le dump.

[CPI_en_mousse]

Envoyé par vmolines

Code :

UPDATE LATABLE SET NOM = 'NOM_' + ID

Puis vous faites le dump.

Oui mais non . Il s'agit d'une table de production, il est hors de question d'effectuer des insert, update ou tout autre commande modifiant les tables.

J'ai oublié de le préciser, toutes mes excuses.

[vmolines]

Ok super variante alors :

- Dumper votre base de prod
- Remontez le dump sur une base temporaire
- Appliquer la procédure initiale à la base temporaire



Vous pouvez même faire un script qui fait tout ça si vous devez faire la manip régulièrement.

[CPI_en_mousse]

Ok merci bien pour ta réponse.
Encore une petite question : comment faire une requête de randomisation de nom?
Mr dupigniouf deviendrai Mr ddfgshdfkjhe.

merci

[vmolines]

Si vous savez développer, vous pouvez faire une fonction utilisateur (UDF) sur votre SGBD qui génère une chaine aléatoire. Il suffira de l'appeler dans votre requête update pour valoriser le nom.

[skuatamad]

C'est mieux de randomiser les colonnes qui participent aux filtres dans l'application, ça permet de conserver la distribution des données entre la dev et la prod.

Envoyé par CPI_en_mousse

Encore une petite question : comment faire une requête de randomisation de nom?
Mr dupigniouf deviendrai Mr ddfgshdfkjhe.

Donc comme je le disais ça peut être intéressant de réaffecter aléatoirement de vrais valeurs à d'autres lignes de la table générant ainsi de fausses données.
C'est faisable en une requête, tout dépend du SGBD utilisé (déjà pour la fonction random mais aussi en fonction des fonctionnalités offertes), par exemple sur oracle :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
merge INTO usr
USING (   WITH t AS (
        SELECT row_number() over (ORDER BY id) AS rn_id,       
               id, nom, prenom
          FROM usr
       ),
               rand_nom AS (
        SELECT row_number() over (ORDER BY dbms_random.value) AS rn_rand_nom,
               id, nom, prenom
          FROM usr
       ),
               rand_prenom AS (
        SELECT row_number() over (ORDER BY dbms_random.value) AS rn_rand_prenom,
               id, nom, prenom
          FROM usr
       )
        SELECT t.id, t1.nom, t2.prenom
          FROM t
          JOIN rand_nom t1 ON t1.rn_rand_nom = t.rn_id
          JOIN rand_prenom t2 ON t2.rn_rand_prenom = t.rn_id
      ) vrand
   ON (usr.id = vrand.id)
 when matched then UPDATE
  SET usr.nom = vrand.nom,
      usr.prenom = vrand.prenom

A noter que j'ai utilisé une vue par colonne à randomiser (rand_nom et rand_prenom) mais c'est spécifique à oracle 11g qui cache le résultat de dbms_random.value, avec 2 vues je le force à randomiser les 2 colonnes.
Ci dessous le jeu de test :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
SQL> CREATE TABLE usr AS
  2  SELECT rownum AS id,
  3         'nom_'||mod(rownum,40) AS nom,
  4         'prenom_'||mod(rownum,15) AS prenom
  5    FROM dual
  6  connect BY level <= 50
  7  /
 
TABLE created.
 
SQL> SELECT * FROM usr
  2  /
 
        ID NOM             PRENOM
---------- --------------- -----------------------------------------------
         1 nom_1           prenom_1
         2 nom_2           prenom_2
         3 nom_3           prenom_3
         4 nom_4           prenom_4
         5 nom_5           prenom_5
         6 nom_6           prenom_6
         7 nom_7           prenom_7
         8 nom_8           prenom_8
         9 nom_9           prenom_9
        10 nom_10          prenom_10
        11 nom_11          prenom_11
        12 nom_12          prenom_12
        13 nom_13          prenom_13
        14 nom_14          prenom_14
        15 nom_15          prenom_0
        16 nom_16          prenom_1
        17 nom_17          prenom_2
        18 nom_18          prenom_3
        19 nom_19          prenom_4
        20 nom_20          prenom_5
        21 nom_21          prenom_6
        22 nom_22          prenom_7
        23 nom_23          prenom_8
        24 nom_24          prenom_9
        25 nom_25          prenom_10
        26 nom_26          prenom_11
        27 nom_27          prenom_12
        28 nom_28          prenom_13
        29 nom_29          prenom_14
        30 nom_30          prenom_0
        31 nom_31          prenom_1
        32 nom_32          prenom_2
        33 nom_33          prenom_3
        34 nom_34          prenom_4
        35 nom_35          prenom_5
        36 nom_36          prenom_6
        37 nom_37          prenom_7
        38 nom_38          prenom_8
        39 nom_39          prenom_9
        40 nom_0           prenom_10
        41 nom_1           prenom_11
        42 nom_2           prenom_12
        43 nom_3           prenom_13
        44 nom_4           prenom_14
        45 nom_5           prenom_0
        46 nom_6           prenom_1
        47 nom_7           prenom_2
        48 nom_8           prenom_3
        49 nom_9           prenom_4
        50 nom_10          prenom_5
 
50 rows selected.
 
SQL> merge INTO usr
  2  USING (   WITH t AS (
  3          SELECT row_number() over (ORDER BY id) AS rn_id,
  4                 id, nom, prenom
  5            FROM usr
  6         ),
  7                 rand_nom AS (
  8          SELECT row_number() over (ORDER BY dbms_random.value) AS rn_rand_nom,
  9                 id, nom, prenom
 10            FROM usr
 11         ),
 12                 rand_prenom AS (
 13          SELECT row_number() over (ORDER BY dbms_random.value) AS rn_rand_prenom,
 14                 id, nom, prenom
 15            FROM usr
 16         )
 17          SELECT t.id, t1.nom, t2.prenom
 18            FROM t
 19            JOIN rand_nom t1 ON t1.rn_rand_nom = t.rn_id
 20            JOIN rand_prenom t2 ON t2.rn_rand_prenom = t.rn_id
 21        ) vrand
 22     ON (usr.id = vrand.id)
 23   when matched then UPDATE
 24    SET usr.nom = vrand.nom,
 25        usr.prenom = vrand.prenom
 26  /
 
50 rows merged.
 
SQL> SELECT * FROM usr
  2  /
 
        ID NOM             PRENOM
---------- --------------- -----------------------------------------------
         1 nom_12          prenom_2
         2 nom_36          prenom_11
         3 nom_24          prenom_2
         4 nom_32          prenom_3
         5 nom_4           prenom_5
         6 nom_9           prenom_3
         7 nom_13          prenom_10
         8 nom_25          prenom_11
         9 nom_2           prenom_6
        10 nom_34          prenom_13
        11 nom_5           prenom_1
        12 nom_7           prenom_1
        13 nom_22          prenom_11
        14 nom_29          prenom_9
        15 nom_1           prenom_2
        16 nom_3           prenom_1
        17 nom_30          prenom_12
        18 nom_6           prenom_10
        19 nom_11          prenom_5
        20 nom_8           prenom_8
        21 nom_23          prenom_4
        22 nom_31          prenom_8
        23 nom_10          prenom_3
        24 nom_10          prenom_14
        25 nom_2           prenom_13
        26 nom_7           prenom_6
        27 nom_37          prenom_5
        28 nom_26          prenom_8
        29 nom_20          prenom_7
        30 nom_35          prenom_12
        31 nom_27          prenom_9
        32 nom_17          prenom_7
        33 nom_9           prenom_9
        34 nom_5           prenom_0
        35 nom_33          prenom_10
        36 nom_3           prenom_13
        37 nom_28          prenom_1
        38 nom_19          prenom_5
        39 nom_18          prenom_3
        40 nom_15          prenom_6
        41 nom_38          prenom_4
        42 nom_0           prenom_0
        43 nom_4           prenom_2
        44 nom_16          prenom_14
        45 nom_39          prenom_7
        46 nom_14          prenom_4
        47 nom_1           prenom_0
        48 nom_8           prenom_12
        49 nom_21          prenom_4
        50 nom_6           prenom_14
 
50 rows selected.

Je pense que c'est plus ou moins facilement adaptable pour sqlserver, db2 et peut être aussi postgre.
C'est totalement impossible à faire en une requête sur mysql....

[CPI_en_mousse]

Merci pour ta réponse, je vais essayer ça. je suis sur oracle 10

[pacmann]

Salut,

Faut faire attention quand même Skuat, ce genre de décision doit se faire valider par des RSSI et autres...

Même si les libellés sont redistribués de manière incohérente, ça peut divulger le nom d'un client, des numéros de téléphones, des libellés d'opérations (genre bancaire), ... je suis pas sûr qu'il soit safe de laisser extraire la liste des numéros de téléphones des clients de ta boite

Bref, dbms_random.string, dbms_random.value, pour randomiser juste ce qui est nécessaire.

[estofilo]

Mais quel peut être l'intérêt de remplacer un nom par une chaine aléatoire au lieu d'une chaine vide ou d'omettre carrément la colonne, si la chaine aléatoire est de toute manière inexploitable pour quoi que ce soit?
Il me semble que la technique de remplacement d'une valeur par une autre n'est utile que si c'est fait par une fonction au sens mathématique du terme, c'est-à-dire que si x=y alors f(x)=f(y) ce qui n'est pas le cas dans une technique à base de random(). Ce serait le cas en revanche pour une fonction de cryptage.
De cette manière les éventuelles relations entre colonnes sont préservées.

[punkoff]

Bein pour des environnements de test par exemple, c'est quand même agréable d'avoir des données autre que vide.

[pacmann]

Le cryptage effectivement (à partir du moment où la clef est bien gardée) ça serait l'idéal !

Après, ce qui reste vrai dans tous les cas, c'est qu'il faut faire du cas par cas avec les règles de gestion de chaque donnée.
(Genre randomiser ou crypter n'importe comment les numéros de téléphone, ça peut faire un carnage.
On s'est aperçu chez nous que la déperso faisait sauter tous les caractères spéciaux. Du coup, c'est en pré-prod que ça claque, parce que dans les environnements de tests tous les cas particuliers avaient été squizzés )

[skuatamad]

Ok pacmann, c'est sûr que c'est pas le développeur qui chosit.
En fait j'avais mal lu son poste à la base et je pensais que c'était ce qu'il voulait.

Sinon pour avoir une valeur absurde il suffit peut être de translate les caractères :

Code :

1
2
3
4
5
6
7
8
SQL> SELECT translate('dupigniouf','abcdefghijklmnopqrstuvwxyz','ijklmnopqrstuvwxyzabcdefgh')
  2    FROM dual;
 
TRANSLATE(
----------
lcxqovqwcn
 
SQL>

La distribution des données est préservée mais c'est bijectif et facilement décodable.

[pacmann]

Je pense justement qu'il faudrait que ce soit pas facielment décodable

[skuatamad]

Alors peut être mieux coupler TRANSLATE avec DBMS_RANDOM :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  WITH letter AS (
SELECT row_number() over (ORDER BY dbms_random.value) AS rd,
       chr(level+96) AS lettre
  FROM dual
connect BY level <= 26
),
       rand_letter AS (
SELECT max(REPLACE(sys_connect_by_path(lettre,'/'),'/','')) AS str
  FROM letter
  start WITH rd = 1
connect BY prior rd + 1 = rd
)
SELECT translate('dupigniouf','abcdefghijklmnopqrstuvwxyz',rl.str)
  FROM dual
 CROSS JOIN rand_letter rl
 
TRANSLATE('DUPIGNIOUF','ABCDEFGHIJKLMNOP
----------------------------------------
xgdjnbjagr

Mais là la distribution des données est flinguée.

[pacmann]

Ben tu coup tu pouvait te contenter de dbms_random.string ?

[skuatamad]

Doh, je l'avais pas vu...

[SQLpro]

L'anonymisation mal faite peut avoir des conséquences catastrophiques suivant ce que vous envisagez de faire sur la base anonymisée après.

Par exemple si vous voulez faire un audit, une anonymisation conduit assez systématiquement à des erreurs d'interprétation du fait de la transformation des données (impact sur le volume de données, les index;;etc).

Une technique classique d'anonymisation qui respecte les données est de décaler les colonnes. Par exemple de décaler le nom et le prénom de n lignes.

A +

[CPI_en_mousse]

Bonjour,

je reviens vers vous après un week end prolongé.

Donc dans l'idée, je résume les différentes étapes :

• copier la table table_1 à anonymiser dans une nouvelle table temporaire table_temp
• anonymiser les données voulues avec dbms_random
• exporter le schéma sans la table_1
• supprimer la table temporaire table_temp
• puis importer le dump sur l'autre base
• renomer la table table_temp en table_1

dites moi si je me trompe.
Peut on exporter un schéma en excluant certaines tables?

[CPI_en_mousse]

Envoyé par SQLpro

L'anonymisation mal faite peut avoir des conséquences catastrophiques suivant ce que vous envisagez de faire sur la base anonymisée après.

Par exemple si vous voulez faire un audit, une anonymisation conduit assez systématiquement à des erreurs d'interprétation du fait de la transformation des données (impact sur le volume de données, les index;;etc).

Une technique classique d'anonymisation qui respecte les données est de décaler les colonnes. Par exemple de décaler le nom et le prénom de n lignes.

A +

Bonjour,

le but est de fournir des données réelles et récentes pour le développements d'évolutions et assurer la maintenance. Même si tous cela est assuré en interne, il est important d'anonymiser certaines données notament tous ce qui permet d'identifier les personnes.