I am currently working on a project to mask and de-identify data and wanted to pass my findings through a sanity check (you guys).

Is it better to create a new view which houses masked data and limits source table access to admins, or is it better to create a trigger / stored procedure to apply the masking realtime after query results have been fetched?

With the view approach I would have to make some code changes, as the code base is accessing tables through a traditional ORM. But this would mask data regardless of access (app, cli, after a snapshot).

Is it possible through triggers / stored procedures to intercept a request against a sensitive table, and apply the mask before returning the results? This would avoid having to make any code changes.

Or would it be better to implement a hybrid solution, to add a trigger on a sensitive table, save the data as given, but then apply a stored procedure to store the same (but masked and de-identified) data in the view.

Thanks in advance.

  • 1
  • VIEW probably has less performance impact than TRIGGER. All non-admin accesses should go through an app API anyway. That is, you could do the filtering/masking there (as another option). – Rick James Jan 16 at 6:11
  • @RickJames but what about cli DB access? I've read several articles that it's good practice to LOCK down the source tables as tightly as possible, but create views with looser access and available to the app API you mention. – Mike Purcell Jan 16 at 17:56
  • 1
    @MikePurcell - Sure. There is a wide spectrum of possibilities. I think that Triggers are not the right tool for access control. "Masking" -- Well, triggers don't apply to SELECTs. Views or APIs could refuse to show, say, a password. But perhaps only an API could mask a pwd with "xxxxx". Which do you want? Not having the column, or "masking" the data in the column? – Rick James Jan 16 at 20:37

Your Answer

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

Browse other questions tagged or ask your own question.