-2

I have got a customers database and when I am using it in a presentation and to be on the safe side I would like to Obfuscate some sensitive data. What is the best way of doing this.

I can always write a script to update a column with NewID() or something like that but is there a better way of doing this.

I was researching online and found that Dynamic Data Masking is one way of doing it but unfortunately it is not available on SQL Server Express.

Any thoughts greatly appreciated.

Abe
  • 1,879
  • 2
  • 24
  • 39
  • Why would you ever use a product table in a live presentation to people you don't want to have the data (or period really) – Mason Stedman May 01 '18 at 10:11
  • This is a classic example of answering a question with another question. My team has imporved performance issue which the customer was having and we are having a scrum retrospective and we would like to show what improvements have been made and need to use this database. There is possibility that some stakeholders might join in so just trying to be on the safe side by obfuscating the data. – Abe May 01 '18 at 10:30
  • So use benchmarks as a showcase, rather than actual raw database returns. Or just mask the final results in your output, but showcase the data retrieval speed. Or clone the database you have and write a 1 time data scrambler on the new one. I mean you ask a silly question w\ insufficient data to answer regarding your needs in the presentation and wonder why you get half ass replies. – Mason Stedman May 01 '18 at 10:52
  • 2
    @MasonStedman, actually the use case presented is legitimate. The application contains some sensitive data. The developer would like to showcase the data, but not with the _actual_ values. So, the data could be scrambled (in a set of views) for the demo. – SSteven May 01 '18 at 13:40
  • Only way to do it, if you're not willing to just hide your endpoints is to create a staging DB and scramble the data ahead of time. You don't want to use functions at runtime if you're trying to showcase efficency – Mason Stedman May 01 '18 at 14:01
  • Also depends, are you agrgating data (ie using count) or returning individual records? – Mason Stedman May 01 '18 at 14:03

2 Answers2

0

Depending on the data held, you might not need anything more complicated than, for example:

UPDATE CUSTOMER
SET Name = 'David D Davidson', Address = 'House 1, ...';
Aaron
  • 115
  • 1
  • 9
  • It will not be ideal if you were to have several hundred tables and also if you would like to keep the data. What would you do if you would like to get the original data back. – Abe May 01 '18 at 10:35
  • 1
    I would use a staging database for demo purposes rather than the live database, so updating fields as above won't result in any data loss. – Aaron May 01 '18 at 11:09
0

You can create a view on the table. Include only non-sensitive columns in the view. Don't include sensitive columns in the view.

Do this for all tables in your database.

Make the demo using the views rather than the base tables.

Don't update the data, since that would modify the data.

You can preserve the views, for whenever required.

If all columns in tables are sensitive, alternatively, the views you create could include columns that scrambled the data, using (user-defined) database functions. For example, if you have numeric columns, the view could use a function that produces different data (eg: f(x) = 2x + 5). You could also use the same technique for date columns as well as string columns. For the latter, you could simply scramble the data, by writing a database function that mixes the characters at random.

SSteven
  • 733
  • 5
  • 17