0

Our app slightly obfuscates text before insertion to the database. This is done by the application (C#) and not the database. (This is not encryption but just to stop people who are looking at the data in Management Studio; not really preventing shoulder surfing, more like "can't read text even with correct permissions")

Here's a simplified example.

  • In the website I input HELLO into a textbox and click save.
  • The save method then replaces each character with the next in the alphabet, so HELLO becomes IFMMP.
  • The save/update command is then called and IFMMP is saved into the database.
  • When the data is read back from the database, each character is replaced with the one before, so IFMMP becomes HELLO and gets displayed in the textbox.

IFMMP won't be much good for fulltext searching. We have hundreds of existing sprocs & triggers which we don't want to update for this.

The question is, how can I "un-obfuscate" the data before it gets added to the Fulltext catalogue?

Update: Looks like we need a custom WordBreaker or IFilter. Will update if get to a solution.

  • 2
    Not seeing how obfuscate before sending to the database stops shoulder surfing. – paparazzo Apr 17 '18 at 11:48
  • Its if someone happens to look at the data in management studio, so not really shoulder surfing, more like "cant read text even with correct permissions" – DomBat Apr 17 '18 at 12:35
3

The full text catalog will only reflect the contents of a table's data.

Since it sounds like you're not using full text search yet, and you just want to start, you basically have 2 ways to do this:

Option 1: add columns with the de-obfuscated data, and use full text search on those. Say you have a column dbo.Customers.SavedText that is obfuscated - you add a dbo.Customers.SavedTextClear column with the original text. You can populate it and keep it up to date with triggers on insert and update. (Triggers usually have a bad reputation - rightfully so, you wanna minimize the work performed in them - but you asked for a solution that didn't require modifying the original queries inserting/updating into the table, and this is what it looks like. When you tie my hands, my artwork isn't quite as good, ha ha ho ho.)

Option 2: do full text search in something like Elasticsearch. Build an ETL process that exports the fields you want to search, de-obfuscates them, and puts them in a database that's tuned for full text search, like Elastic. You can read about how Stack Overflow approached this problem, although when you read it, I bet modifying your original queries is probably going to sound like a better solution. The only reason I mention it is that if you really need to scale out, this can be a less expensive approach long term.

  • I like option1. If I dont get anywhere with a custom WordBreaker I might do something similar. – DomBat Apr 17 '18 at 16:33
3

Given that the actual goal is:

It's if someone happens to look at the data in Management Studio, so not really shoulder surfing, more like "can't read text even with correct permissions"

then the way to fix this is to "un-obfuscate" the data on its way into SQL Server. Or, to state that more directly: stop this ineffective practice. What the app code is doing is not only not achieving the stated goal, but it is proving to be a detriment to the company as it prevents you from using the data within SQL Server. It might as well be binary data. But "slight" obfuscation of the data does little good when someone can take that data and try to reverse engineer that obfuscation at home (which shouldn't be difficult given that it is just "slightly" obfuscated, especially if the app is using something simple such as ROT13, and even more especially if the person has read access to the source code and can look at the algorithm being used to do the obfuscation). So the current practice is providing nothing more than a false sense of security, not any amount of actual security. Or, as I was told in my early days when trying stuff like this: "security through obscurity" isn't security!

What you should do is:

  1. Stop wasting time / resources on this "slight" obfuscation
  2. Don't give users permissions that they shouldn't have
  3. Get rid of employees that cannot be trusted
  4. Implement actual security with one or more of the mechanisms provided by SQL Server:
    1. Give access to a View that does not SELECT any sensitive column, and prevent access to the base Table
    2. Use column-level permissions (so that certain users cannot SELECT that column, maybe some can INSERT only, but allow the application user to SELECT it)
    3. Look into Always Encrypted (not sure if this prevents usage by Full Text Search or not)
    4. Possibly other mechanisms
  5. Use Full Text Search as intended
  • Unfortunatley this won't happen as it's a really old & large system written for Oracle by a large team who no longer exist, but good advice for the rewrite! – DomBat Apr 17 '18 at 16:19
1

I totally agree with Brent and Solomon's answers, but wanted to provide an additional option. If you can't implement Solomon's, this may be the next best thing.

Dynamic Data Masking

A lot of people dislike Dynamic Data Masking (available on 2016+), because it isn't the most secure thing, but this may be an okay use case for it.

It works by dynamically masking specified columns of data for specified users, but does not affect the actual storage of the data. You can dictate which users should see obfuscated data, but let your applications interact with the data as normal. This allows you to still be able to utilize full text search on the data.

For example, you can only show the first two characters of a varchar field:

HELLO becomes HExx which disguises both the end of the string as well as how many characters there actually are.

So why isn't this used much? If I am a user subjected to masked data, and I have a hunch at the value, I can still query

SELECT * FROM users WHERE name = 'Hello'

and it will work fine, even though my result set will contain the obfuscated 'Hexx'. Obviously this doesn't cut it when real security is required, but I think it has a place for internal usage when just trying to not display data, but not have to encrypt everything either.

Or, you could just buy some screen filters and call it a day.

  • Dynamic Data Masking doesn't help the over-the-shoulder situation - the data is still clearly visible when the user enters it (which is what the question focused on), and when they're putting it into search boxes. – Brent Ozar Apr 17 '18 at 16:07
  • 2
    @BrentOzar I understood the question in line with Solomon's edit - that this was more about SSMS based data viewing and that the detailed explanation was just describing the mechanics of their current obfuscation. – LowlyDBA Apr 17 '18 at 16:13
  • 1
    @BrentOzar for context: the O.P. clarified their situation in a comment on the question well after you had answered. I updated the question with that new info, which somewhat negated the original request (i.e. the "so not really shoulder surfing" part). – Solomon Rutzky Apr 17 '18 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.