As a DBA or developer, we have all come across sensitive data, especially if you work in the financial industry. Whether it is something simple as a last name or birthday to something more complicated such as your account or social security number, there is an obligation to protect this information being viewed without authorization.
One native solution? Microsoft's Dynamic Data Masking (DDM).
First introduced in SQL Server 2016, this masks data at the database layer from unauthorized users. There are four types of data masking Microsoft currently offers, Default, Email, Random and Custom.
Default provides different forms of masking depending on what your column type is. For example, if it's a string it will show 'XXXX' or less if you are masking less than 4 characters, for any numeric value, it will show '0'. If the column type is a datetime value, it will show up as '01.01.1900 00:00:00.0000000' and for binary data types, it uses a single byte of ASCII value 0.
Email provides straight forward masking, only showing the first character following 'X's', @ and .com (no matter what the value is at the end of the email).
Random enables you to specify a range of integers to replace the numeric value of the column.
Custom is similar to Email masking where it shows the first and last characters of the string while masking the rest but if the string is too short, it will mask the entire value.
Below is an example of what each coded value looks like to an authorized and unauthorized user:
As shown, when the select query is executed as a user with limited select rights, DDM does its job and hides sensitive information.
However there are limitations to how well DDM can perform as it is not the perfect answer to all solutions. I'd love to hear about what types of encryption/data masking you prefer to employ! Feel free to leave me a comment or drop me an email at adang@dbaondemand.us.
Comments