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];

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

/* Create a Custom Database Role to deny access to specific columns */

/* 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];

/* 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];

/* 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];

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.


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

Custom Database Roles in SQL Server

In addition to the default Database-Level Roles in SQL Server (db_owner, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter and db_denydatareader), you can create custom roles at the database level.  One reason you might want to do this would be if you have different user accounts for the same functionality across multiple environments (e.g. Dev, QA, UAT, Production).  It might be easier for an application to use the same service user/password combination across all of these environments, but then your developers would know the production Password.  If you store any sort of sensitive information in your database, this makes you an easy target.  It is much more secure to use a dedicated service user for each application in each environment.

Another reason you might want to consider using custom roles would be if multiple users in the same department log directly into the database instead of going through a thread pool in the application layer.  If a new user is hired for accounting or maybe someone leaves the department, there is no need to search for which permissions must be added or removed.

With a little bit of work, you can assign all of the database permissions for a user (or group of users) to a custom role.  If you need to add/remove users, you only need to move them in and out of custom roles.  If you need a redacted copy of the Production database brought down to Dev, just swap out the Production service user for the Dev one.  In this example, the accounting application might have four service users (AcctApp_DEV, AcctApp_QA, AcctApp_UAT and AcctApp).  The same could hold true for the sales application, etc.

Continue reading

#custom-database-roles, #sql-server