SQL Server data masking

  • Admingod

    SSCertifiable

    Points: 5771

    Can you able to mask data in sql server 2012? If yes, would it be easy to mask few tables or the complete database?

     

  • Jeffrey Williams

    SSC Guru

    Points: 88350

    There isn't any native way to mask data - you either have to build it yourself of purchase a product.  You should take a look at: https://www.red-gate.com/products/dba/data-masker/

    I would also recommend reviewing this article: https://www.red-gate.com/hub/product-learning/data-masker/masking-data-practice

    Whether or not masking is worth the time and effort you spend on it will depend on your systems and what actually needs to be masked - and whether or not it can actually be masked without breaking the system.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996061

    Maybe the native Dynamic Data Masking feature first made available in 2016 would be a good enough reason to update 2012 to 2019 sooner than later?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Admingod

    SSCertifiable

    Points: 5771

    I was looking at native option. So you saying using sql server native method data masking can be done from SQL server 2016 onwards?

  • Jeffrey Williams

    SSC Guru

    Points: 88350

    Jeff Moden wrote:

    Maybe the native Dynamic Data Masking feature first made available in 2016 would be a good enough reason to update 2012 to 2019 sooner than later?

    From what I have read - this is more of a security setting.  You define - for each column - the masking function and any users that do not have the UNMASK permission will only see the masked data.

    This does not change the actual data - just masks the data on output.  I guess it depends on what you mean by masking the data...

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996061

    Absolutely agreed.  To me, the term "mask" or "data masking" actually has nothing to do with changing the underlying data.  It's a poorly chosen term (IMHO) except when it's used like (for example) ***-**-1234 to "mask" a social security number from view on a GUI.

    To me, changing the underlying data is "Desensitizing through wholesale replacement" (which isn't masking to me) and I'm not talking about the feeble attempts that people make with the equivalent of "magic decoder rings" that a lot of people use in an improper attempt to desensitize things like SSNs,.

    What's really bad (and sad) is when people think they've encrypted data when all they've really done is mask it.

    And so that does bring up the question... what does the OP mean by "mask"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996061

    Admingod wrote:

    I was looking at native option. So you saying using sql server native method data masking can be done from SQL server 2016 onwards?

    If you look at my definition of data masking in my previous post above, yes.  I don't, however, recommend the use of such data masking if the data is actually sensitive as PII or anything else.   Reportedly, a proverbial first year cadet can break it.

    What do you intend to do with what you're calling "data masking"?  Do you really mean "data encryption"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Grant Fritchey

    SSC Guru

    Points: 396384

    Admingod wrote:

    I was looking at native option. So you saying using sql server native method data masking can be done from SQL server 2016 onwards?

    When you say "mask the data" what do you mean? That is a big determining factor for whether or not something within SQL Server can get the job done for you or not.

    Let's say you want to mask information within your production server such that only certain roles have access. That is what the native Dynamic Data Masking is all about. It hides data based on security. What this doesn't do, is hide data in less secure environments.

    So, let's say you want to hide data from your developers because of the GDPR or similar compliance requirements. If you use Dynamic Data Masking in your non-production servers, and the people there have elevated privileges, which they most certainly will in most cases, they'll be able to see the "masked" data. This is why, when talking about masking in non-production environments, the term gets overloaded. It means, change the data because you can no longer simply hide the data.

    If you clarify your goals, it'll be easier to point you to a viable solution.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply