mascaras - sql 2016 dynamic data masking functions

Ofuscar/ocultar/información personal

Question

Estoy buscando una manera local de codificar datos de producción para su uso en desarrollo y prueba. He creado un par de scripts que crean números aleatorios de la seguridad social, cambian las fechas de nacimiento, codifican los correos electrónicos, etc. Pero me he topado con una pared tratando de mezclar los nombres de los clientes. Quiero conservar los nombres reales para que podamos usarlos o buscarlos de manera que la generación de letras al azar esté fuera. Lo que he intentado hasta ahora es construir una tabla temporal de todos los apellidos en la tabla y luego actualizar la tabla de clientes con una selección aleatoria de la tabla temporal. Me gusta esto:

DECLARE @Names TABLE (Id int IDENTITY(1,1),[Name] varchar(100))
/* Scramble the last names (randomly pick another last name) */
INSERT @Names SELECT LastName FROM Customer ORDER BY NEWID();
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer)
UPDATE [Customer ORDERED BY ROWID] SET LastName=(SELECT [Name] FROM @Names WHERE ROWID=Id)

Esto funcionó bien en la prueba, pero se empantana completamente al tratar con grandes cantidades de datos (> 20 minutos para 40K filas)

Todo eso para preguntar, ¿cómo codificarías los nombres de los clientes mientras mantienes los nombres reales y el peso de los datos de producción?

ACTUALIZACIÓN: nunca falla, intenta poner toda la información en la publicación y olvida algo importante. Esta información también se usará en nuestros entornos de venta y demostración que están disponibles públicamente. Algunas de las respuestas son lo que intento hacer para "cambiar" los nombres, pero mi pregunta es, literalmente, ¿cómo codificar en T-SQL?

Answer 1

¿Por qué no usar un generador de nombres aleatorios?

Answer 2

Cuando hago algo así, generalmente escribo un pequeño programa que primero carga muchos nombres y apellidos en dos matrices, y luego simplemente actualiza la base de datos usando nombres / apellidos aleatorios de las matrices. Funciona muy rápido incluso para conjuntos de datos muy grandes (más de 200.000 registros)

Answer 3

Francamente, no estoy seguro de por qué esto es necesario. Sus entornos de desarrollo / prueba deben ser privados, detrás de su firewall y no accesibles desde la web.

Se debe confiar en sus desarrolladores, y usted tiene un recurso legal contra ellos si no cumplen con su confianza.

Creo que la verdadera pregunta debería ser "¿Debería codificar los datos?", Y la respuesta es (en mi opinión) "no".

Si lo envía fuera del sitio por alguna razón, o tiene que tener acceso a sus entornos a través de la web, o si está paranoico, implementaría un cambio aleatorio. En lugar de crear una tabla temporal, ejecute los interruptores entre cada ubicación y una fila aleatoria en la tabla, intercambiando una pieza de datos a la vez.

El resultado final será una tabla con todos los mismos datos, pero con la reorganización aleatoria. También debería ser más rápido que tu tabla temporal, creo.

Debería ser lo suficientemente simple para implementar Fisher-Yates Shuffle en SQL ... o al menos en una aplicación de consola que lea el archivo db y lo escriba en el destino.

Editar (2): Respuesta fuera del manguito en T-SQL:

declare @name varchar (50) configure @name = (SELECT lastName de la persona donde personID = (número de identificación aleatorio) Update person set lastname = @name WHERE personID = (person id of current row)

Envuelva esto en un bucle, y siga las pautas de Fisher-Yates para modificar las restricciones de valor aleatorio, y se establecerá.

Answer 4

Una solución muy simple sería ROT13 el texto.

Una mejor pregunta puede ser ¿por qué sientes la necesidad de codificar los datos? Si tiene una clave de cifrado, también podría considerar ejecutar el texto a través de DES o AES o similar. Sin embargo, esto tendría posibles problemas de rendimiento.

Answer 5

Yo uso generatedata . Es un script php de código abierto que puede generar todo tipo de datos ficticios.

Answer 6

Estoy trabajando en esto en mi empresa en este momento, y resulta ser algo muy complicado. Desea tener nombres que sean realistas, pero no debe revelar ninguna información personal real.

Mi enfoque ha sido primero crear un "mapeo" aleatorizado de apellidos a otros apellidos, luego usar ese mapeo para cambiar todos los apellidos. Esto es bueno si tiene registros de nombres duplicados. Supongamos que tiene 2 registros "John Smith" que representan a la misma persona real. Si cambió un registro a "John Adams" y el otro a "John Best", ¡entonces su única "persona" ahora tiene 2 nombres diferentes! Con un mapeo, todas las ocurrencias de "Smith" se cambian a "Jones", por lo que los duplicados (o incluso los miembros de la familia) aún terminan con el mismo apellido, manteniendo los datos más "realistas".

También tendré que codificar las direcciones, números de teléfono, números de cuentas bancarias, etc. ... y no estoy seguro de cómo los abordaré. Mantener los datos "realistas" mientras lucha es ciertamente un tema profundo. Esto debe haberlo hecho muchas veces muchas compañías, ¿quién lo hizo antes? ¿Qué aprendiste?

Answer 7

Use una tabla temporal en su lugar y la consulta es muy rápida. Corrí en 60K filas en 4 segundos. Voy a usar este en el futuro.

DECLARE TABLE #Names
(Id int IDENTITY(1,1),[Name] varchar(100))

/ * Codificar los apellidos (elegir al azar otro apellido) * /

INSERT #Names
SELECT LastName
FROM Customer
ORDER BY NEWID();
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer)
UPDATE [Customer ORDERED BY ROWID]
SET LastName=(SELECT [Name] FROM #Names WHERE ROWID=Id)
DROP TABLE #Names

Answer 8

El siguiente enfoque funcionó para nosotros, digamos que tenemos 2 tablas Clientes y productos:

CREATE FUNCTION [dbo].[GenerateDummyValues]
(
@dataType varchar(100),
@currentValue varchar(4000)=NULL
)
RETURNS varchar(4000)
AS
BEGIN
IF @dataType = ''int''
BEGIN
Return ''0''
END
ELSE IF @dataType = ''varchar'' OR @dataType = ''nvarchar'' OR @dataType = ''char'' OR @dataType = ''nchar''
BEGIN
Return ''AAAA''
END
ELSE IF @dataType = ''datetime''
BEGIN
Return Convert(varchar(2000),GetDate())
END
-- you can add more checks, add complicated logic etc
Return ''XXX''
END

La función anterior ayudará a generar diferentes datos según el tipo de datos que ingrese.

Ahora, para cada columna de cada tabla que no tenga la palabra "id", use la siguiente consulta para generar más consultas para manipular los datos:

select ''select ''''update '''' + TABLE_NAME + '''' set '''' + COLUMN_NAME + '''' = '''' + '''''''''''''''' + dbo.GenerateDummyValues( Data_type,'''''''') + '''''''''''' where id = '''' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, '' + table_name + '' where RIGHT(LOWER(COLUMN_NAME),2) <> ''''id'''' and TABLE_NAME = ''''''+ table_name + '''''''' + '';'' from INFORMATION_SCHEMA.TABLES;

Cuando ejecuta la consulta anterior generará consultas de actualización para cada tabla y para cada columna de esa tabla, por ejemplo:

select ''update '' + TABLE_NAME + '' set '' + COLUMN_NAME + '' = '' + '''''''' + dbo.GenerateDummyValues( Data_type,'''') + '''''' where id = '' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, Customers where RIGHT(LOWER(COLUMN_NAME),2) <> ''id'' and TABLE_NAME = ''Customers'';
select ''update '' + TABLE_NAME + '' set '' + COLUMN_NAME + '' = '' + '''''''' + dbo.GenerateDummyValues( Data_type,'''') + '''''' where id = '' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, Products where RIGHT(LOWER(COLUMN_NAME),2) <> ''id'' and TABLE_NAME = ''Products'';

Ahora, cuando ejecuta las consultas anteriores, obtendrá consultas de actualización final, que actualizarán los datos de sus tablas.

Puede ejecutar esto en cualquier base de datos de SQL Server, sin importar cuántas tablas tenga, generará consultas para usted que se pueden ejecutar más.

Espero que esto ayude.

Answer 9

Otro sitio para generar conjuntos de datos falsos con forma, con una opción para salida de T-SQL: https://mockaroo.com/

Answer 10

Aquí hay una forma de usar ROT47 que es reversible, y otra que es aleatoria. Puede agregar un PK a cualquiera de los enlaces para volver a las versiones "no codificadas"

declare @table table (ID int, PLAIN_TEXT nvarchar(4000))
insert into @table
values
(1,N''Some Dudes name''),
(2,N''Another Person Name''),
(3,N''Yet Another Name'')
--split your string into a column, and compute the decimal value (N)
if object_id(''tempdb..#staging'') is not null drop table #staging
select
substring(a.b, v.number+1, 1) as Val
,ascii(substring(a.b, v.number+1, 1)) as N
--,dense_rank() over (order by b) as RN
,a.ID
into #staging
from (select PLAIN_TEXT b, ID FROM @table) a
inner join
master..spt_values v on v.number < len(a.b)
where v.type = ''P''
--select * from #staging
--create a fast tally table of numbers to be used to build the ROT-47 table.
;WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
--Here we put it all together with stuff and FOR XML
select
PLAIN_TEXT
,ENCRYPTED_TEXT =
stuff((
select
--s.Val
--,s.N
e.ENCRYPTED_TEXT
from #staging s
left join(
select
N as DECIMAL_VALUE
,char(N) as ASCII_VALUE
,case
when 47 + N <= 126 then char(47 + N)
when 47 + N > 126 then char(N-47)
end as ENCRYPTED_TEXT
from cteTally
where N between 33 and 126) e on e.DECIMAL_VALUE = s.N
where s.ID = t.ID
FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 0, '''')
from @table t
--or if you want really random
select
PLAIN_TEXT
,ENCRYPTED_TEXT =
stuff((
select
--s.Val
--,s.N
e.ENCRYPTED_TEXT
from #staging s
left join(
select
N as DECIMAL_VALUE
,char(N) as ASCII_VALUE
,char((select ROUND(((122 - N -1) * RAND() + N), 0))) as ENCRYPTED_TEXT
from cteTally
where (N between 65 and 122) and N not in (91,92,93,94,95,96)) e on e.DECIMAL_VALUE = s.N
where s.ID = t.ID
FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 0, '''')
from @table t