Data Masking

If terms like these (PCI DSS, HIPAA, SOX, etc.) cause you to wake up in a cold sweat, data masking might just be what the doctor ordered. Previews of SQL Server 2016 tout a new feature called dynamic data masking, which will allow you to house and control sensitive data in your database; only users with UNMASK privileges can see the real values. As data breaches occur with increasing frequency, I would hope that this feature would be available across the board.  I have yet to find mention, however, of which edition(s) this feature will be available in, so I would prepare for the sticker shock of Enterprise Edition (just in case).

If you can’t wait for the general release of SQL Server 2016 or do not want your production environment to lose any fingers on the bleeding edge of technology, a quick search will turn up multiple reputable companies that offer persistent and/or dynamic data masking products.  You also have the option to add many of these features with functionality that has been around since at least SQL Server 2000 (Why are you still on SQL 2000?).

For this example, we will look at the Sales.CreditCard table in AdventureWorks2008R2.  The CardNumber field shows the full credit card number to anyone with basic SELECT privileges on the Sales schema.  You could create a view (named CreditCard_NonSensitiveFields or some such), but that only partially addresses the issue.  What I propose is to create a Custom Database Role and use it to deny privileges to its members.

USE [AdventureWorks2008R2];
GO

/* Create a view for "limited" users */
CREATE VIEW [Sales].[CreditCard_NonSensitiveFields]
AS
SELECT CreditCardID
, CardType
, CardNumber = RIGHT(REPLICATE('X', LEN(CardNumber)) + RIGHT(CardNumber, 4), LEN(CardNumber))
, ExpMonth = 1
, ExpYear = 1900
, ModifiedDate
FROM Sales.CreditCard;
GO

/* Create a Custom Database Role to deny access to specific columns */
CREATE ROLE [db_CUSTOMROLE_DenyPCI] AUTHORIZATION [dbo]
GO

/* You can deny all you want */
DENY SELECT ON [Sales].[CreditCardID].[CardNumber] TO [db_CUSTOMROLE_DenyPCI];
DENY INSERT ON [Sales].[CreditCardID].[CardNumber] TO [db_CUSTOMROLE_DenyPCI];
DENY UPDATE ON [Sales].[CreditCardID].[CardNumber] TO [db_CUSTOMROLE_DenyPCI];
DENY DELETE ON [Sales].[CreditCardID].[CardNumber] TO [db_CUSTOMROLE_DenyPCI];
DENY SELECT ON [Sales].[CreditCardID].[ExpMonth] TO [db_CUSTOMROLE_DenyPCI];
DENY INSERT ON [Sales].[CreditCardID].[ExpMonth] TO [db_CUSTOMROLE_DenyPCI];
DENY UPDATE ON [Sales].[CreditCardID].[ExpMonth] TO [db_CUSTOMROLE_DenyPCI];
DENY DELETE ON [Sales].[CreditCardID].[ExpMonth] TO [db_CUSTOMROLE_DenyPCI];
DENY SELECT ON [Sales].[CreditCardID].[ExpYear] TO [db_CUSTOMROLE_DenyPCI];
DENY INSERT ON [Sales].[CreditCardID].[ExpYear] TO [db_CUSTOMROLE_DenyPCI];
DENY UPDATE ON [Sales].[CreditCardID].[ExpYear] TO [db_CUSTOMROLE_DenyPCI];
DENY DELETE ON [Sales].[CreditCardID].[ExpYear] TO [db_CUSTOMROLE_DenyPCI];
GO

/* Adding Role members by Active Directory Security Groups */
ALTER ROLE [db_CUSTOMROLE_DenyPCI] ADD MEMBER [adventure-works\SG_Application_Specialists];
ALTER ROLE [db_CUSTOMROLE_DenyPCI] ADD MEMBER [adventure-works\SG_Benefits_Specialists];
GO

/* Adding Role members one at a time */
ALTER ROLE [db_CUSTOMROLE_DenyPCI] ADD MEMBER [adventure-works\barbara1];
ALTER ROLE [db_CUSTOMROLE_DenyPCI] ADD MEMBER [adventure-works\mike0];
GO

In most cases, it is better to deal with groups when addressing security (database users, roles, etc.), but you can also add individuals.

If you don’t want to be in a position where you HAVE to trust your DBAs (for whatever reason), then hardware security modules or tokenization appliances might be a better fit.  Still, there are some fairly decent options available now and the future looks bright.

Advertisements

#custom-database-roles, #data-masking, #sql-server, #sql-server-2016