Traditional approaches exist to protecting sensitive data held within tables of relational databases, such approaches include the utilisation of:

𝐄𝐧𝐜𝐫𝐲𝐩𝐭𝐢𝐨𝐧: Sensitive information can be stored in an encrypted form whereby underlying applications handle the encryption/decryption of data, however, this limits the processing and filtering of encrypted data at the database level which would also prevent us from leveraging any optimisation performed by database engines and would place more load on the underlying application.

𝐓𝐨𝐤𝐞𝐧𝐢𝐳𝐚𝐭𝐢𝐨𝐧: A non-sensitive token can be utilised in place of sensitive information, however, this also shares many of the downsides of encryption but it can be a more suitable alternative when the underlying application does not process the token in any way and may simply pass it across to a third party such as the case with payment card processing.

𝐕𝐢𝐞𝐰𝐬: Direct access to underlying tables with sensitive columns can be only granted to a view which would only allow for the retrieval of a subset of columns based on the sensitivity of data held within each column.

𝐀𝐧𝐨𝐧𝐲𝐦𝐢𝐳𝐚𝐭𝐢𝐨𝐧: Sensitive information can be anonymized prior to its storage making it untraceable however this approach could only be of use when no traceability requirements exist for stored data.

The approaches described above have shared disadvantageous such as implementation overhead, complexity and the lack of fine-grained protection.

Thankfully a relatively new technique known as dynamic data masking exists which helps alleviate the disadvantages of traditional approaches.

𝐃𝐲𝐧𝐚𝐦𝐢𝐜 𝐃𝐚𝐭𝐚 𝐌𝐚𝐬𝐤𝐢𝐧𝐠: First introduced by SQL Server 2016 and later adopted within several Azure SQL offerings.

This approach allows for sensitive information to be stored in the database in its raw format however it can only be viewed in its raw/unmasked format by authorized users (usually an application).

Unauthorised users who attempt to access data protected by masking policies will be presented with data in masked formats such as XXXXXX instead of underlying data.

Masking policies can apply at various levels from a column level to an entire database, these policies allow for fine-grained control providing an alternative to unconditionally encrypting an entire column for all users.

The appearance of masked data to unauthorised users can also be configurable.

The native support of data masking by underlying infrastructure reduces implementation overhead and complexity, developers will no longer have to implement an encryption/decryption mechanism and have performance suffer due to introduced overhead and limitations.