By: Paul Schuliger
What is Column Level Security?
Column level security (CLS) is a useful tool for limiting access to specific data elements (or database columns) through data obfuscation. Data obfuscation masks the underlying data elements to avoid PII/PHI data from being available to data consumers. Typical examples include showing only the last four digits of a customer’s social security number or a phone number in the results of a query or report.
CLS must be a priority in any data warehouse environment in order to secure data elements that are considered personally identifiable information (PII) or protected health information (PHI). Some examples of PII are full name, data of birth, Social Security number, address, and IP address. Some examples of PHI are lab results, doctor notes, and billing records.
(Related post: How to Set Up Row Level Security (RLS) in Microsoft Power BI)
How to Implement Column Level Security in SQL Server
A data warehouse that maintains PII/PHI information must take specific action to ensure the security of its information. Most data consumers in a data warehouse, including the development team, do not need to have access to sensitive data.
SQL Server provides a highly valuable tool called dynamic data masking (DDM) that can be used in obfuscating column level data.
Dynamic Data Masking
It’s important to note that the dynamic data masking functionality does not protect the underlying data at rest but masks the results of any query results using that masked column. Therefore, the result set that includes the social security number column shows the SSN as XXX-XX-1111 instead of the full nine digit number.
DDM is easy to implement. The functionality requires an ALTER TABLE on an existing table so that the data mask can be applied on a specific column. The default data mask obfuscates all information in the column. There are options to allow partial masking, as in the case of showing the last four digits of the social security number. And the tool can be implemented on date fields and numeric fields in addition to the character based fields.
You can learn about the specific functionalities of DDM and how to implement them on Microsoft’s website.
Dynamic data masking has a couple of side benefits that are helpful in creating obfuscated data:
- Using SELECT INTO or INSERT INTO to copy data from a masked column into another table results in masked data in the target table.
- Dynamic data masking is applied when running SQL Server Import and Export. A database containing masked columns will result in an exported data file with masked data (assuming it is exported by a user without UNMASK privileges), and the imported database will contain statically masked data.
These side benefits could be used to provide obfuscated data to the non-production environments, to provide test data for other internal initiatives, or to provide data to a partner organization that does not have direct access to the data warehouse.
In closing, know that column level security is only a part of an effective data security program. If used in conjunction with other data security best practices like data encryption, database permissions, row level security, and so on, then CLS can improve a data security program and lower the risk of improper access to sensitive data.
Questions?
Thanks for reading. We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, (Star Wars), or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
Keep Your Business Intelligence Knowledge Sharp by Subscribing to our Email List
Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!
Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.