In this article we will explore in depth the concept of Dynamic Data Masking in Ms SQL Server
SQL Server 2016 helps in protecting sensitive data from being exposed to non- privileged users by masking the data; this is known as Dynamic Data Masking (DDM). It allows the user to simplify the design and coding of application security. DDM allows users to decide what amount of sensitive data should be revealed. This is done to limit unauthorized access to critical or sensitive data. DDM allows for configuration of sensitive data over databases in the form of results to queries, on designated database fields without bringing any changes in the database. DDM can easily be used with existing applications as it requires rules to be applied only in query results. Sensitive data can be masked by majority applications without causing many changes in existing queries.
DDM works in the following way
- It consists of a central data masking policy which directly acts on sensitive fields inside the given database.
- It decides who are the privileged users and have access to sensitive data.
- It offers features like Full masking and Partial masking along with Random mask for numeric data.
- Simple commands like Transact SQL are used for the purpose of defining and managing masks.
A developer is allowed to query production environments, only for troubleshooting, that too without causing any violations in compliance regulations. Appropriate data mask should be used for the protection of Personally Identifiable Information (PII) data
The purpose of DDM is to restrict the exposure to sensitive data by disallowing unprivileged users from gaining access to it. However it does not restrict users from gaining access to databases for conducting exhaustive queries that yield sensitive data as results. DDM feature is in addition to various other security features in SQL Server 2016. It is suggested to use the DDM feature along with other security features to ensure better protection of sensitive data.
DDM has been designed to simplify the development of application by restricting exposure to data in a set of pre-defined queries which is used by the application. One of its additional uses also involves protection from accidental exposure to sensitive data while directly accessing databases for production. However there is a possibility that unprivileged users with rights to ad-hoc query permissions can apply such techniques to gain access to the actual data. Incase there is a need of providing ad-hoc access, proper auditing should be done to monitor overall database activity to reduce the damage.
Although DDM is a security feature it should not be used in isolation, as there is a possibility of a user with ad-hoc access to guess the sensitive data just by gaining some insight of the actual values.
DDM is suitable for preventing accidental exposure to sensitive data, but will not protect against any possible malicious intent to infer the underlying data. The most important thing is to efficiently manage the permissions on the said database, and to always adhere to the principle of minimal required permissions. Make sure you have enabled auditing for the purpose of tracking all activities occurring on the database.
It is always better to remain prepared for contingencies such as a SQL Crash while working on Ms SQL Server Application
While SQL Crashes cannot be predicted, we can always remain prepared for them in advance by investing in a powerful sql server recovery tool like DataNumen SQL Recovery application. If in case the underlying SQL data file gets corrupted, this incisive tool can jump into action and bring back each data record that you have stored in the database with ease. Further it can recover the data irrespective of the media storage format.
Author Introduction:
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/
Leave a Reply