By: Alan Cranfield | Comments (2) | Related: 1 | 2 | > Encryption
Problem
In Part 1, we looked out how to identify PII data in your databases. In this tip we look at how to lock down PII data using SQL Server security. Protecting your data is very important and the recent publicity over the theft of Sony PSN data underscores both the economic and ethical importance of protecting personal data. The PSN breach is estimated to have cost SONY $170 million and that doesn't include legal action taken against them by users.
Solution
The U.S Department of Commerce defines Personally Identifiable Information (PII) as any information about an individual that can be used to distinguish or trace an individual's identity, such as name, social security number, date and place of birth, mother's maiden name, or biometric records; and any other information that is linked or linkable to an individual, such as medical, educational, financial, and employment information
In Part 1, I provided a quick way to identify potential PII information in your databases. In Part 2, we will build on this information to lockdown access to the sensitive PII data while still providing existing read only users access to the non PII columns.
We will achieve this by creating a DenyPII database role and explicitly DENYING SELECT on the PII columns to this role. To restrict users from accessing the PII columns we can now add their relevant user accounts or groups to this role.
In my experience the majority of user access to data is granted through the db_datareader fixed database role.
Create the DenyPII database role
The simple script below will generate CREATE ROLE statements for all your databases (set Results to Text for best formatting)
-- this script will generate the statements to create the DenyPII role
select 'USE '+name+char(10)+'CREATE ROLE [DenyPII] AUTHORIZATION [dbo]'+char(10)
from sys.databases
where state_desc = 'ONLINE'
and name not in ('msdb','tempdb','master','model')Edit the output as required and then execute the output to create the new DenyPII database role:
DENY SELECT to the DenyPII database role
The script below will iterate through all online databases and build a DCL statement that will effectively DENY SELECT access to the PII columns to the DenyPII database role.
You can edit the script further to suit the nature of your own data. You can also generate am emergency rollback script by replacing the 'DENY" with 'REVOKE'. (set Results to Text for best formatting)
-- the script examines all fields for PII sounding names and generates DENY statements
EXEC sp_msforeachDB '
USE [?]
SELECT "USE "+''[?]''+char(10)+
''DENY SELECT ON [''+s.name+ ''].[''
+object_name(o.object_id)+'']([''+c.name+''])''+'' TO [DenyPII]''+char(10)+
''GO''+char(10)
from sys.columns c join sys.objects o on c.object_id = o.object_id
join sys.schemas s on o.schema_id = s.schema_id
where o.type IN (''U'',''V'')
and (c.name like ''%mail%''
or c.name like ''%first%name%''
or c.name like ''%last%name%''
or c.name like ''%birth%''
or c.name like ''%sex%''
or c.name like ''%address%''
or c.name like ''%city%''
or c.name like ''%region%''
or c.name like ''%state%''
or c.name like ''%postal%code%''
or c.name like ''%phone%''
or c.name like ''%social%''
or c.name like ''%ssn%''
or c.name like ''%gender%'')
and db_name() not in (''msdb'',''tempdb'',''master'',''model'')'Edit the output as required and then execute the output to grant the DENY SELECT to the DenyPII database role:
Confirm Changes
To confirm the changes we can run sp_helprotect.
Or we can check the database role properties in SSMS by right clicking the DenyPII database role -> selecting Properties -> Securables -> Schema -> Explicit -> Column Permissions as shown below.
Verification and Validation
All that is remaining is to add the relevant database users to the DenyPII database role in each database in order to restrict access. Once this is done and the user tries to SELECT from a restricted column they will receive the following message:
Summary
Using database roles is a quick and effective way to DENY access to PII data while still providing access to other operational data through the usual fixed database role methods. PII data should be regularly reviewed. Is the PII data still required to meet the business objectives? If not, then old PII data should be destroyed.
Access management of PII data held in SQL Server is just one of the many areas of concentration in a well defined information security program.
Next Steps
- Explore the possibility of encrypting PII data
- Make sure your database backups are secure
- Review these other great tips on securing SQL Server data:
About the author
Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
