Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Locking Down PII Data in SQL Server (Part 2 of 2)


By:   |   Last Updated: 2011-06-24   |   Comments (2)   |   Related Tips: 1 | 2 | More > 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:

creating a denyPll 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:

execute the output to grant the deny select to the denyPll database role

Confirm Changes

To confirm the changes we can run sp_helprotect.

to confirm 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.

check the database role properties in ssms

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:

verify relevent database users

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


    Last Updated: 2011-06-24


    next webcast button


    next tip button



    About the author
    MSSQLTips author Alan Cranfield Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

    View all my tips




    Post a comment or let the author know this tip helped.

    All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

    *Name    *Email    Email me updates 


    Signup for our newsletter
     I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



        



    Wednesday, March 07, 2012 - 4:23:44 PM - Mauricio Crespo Back To Top

    Great Job. Excellent approach.

     

    Mauricio

    Senior DBA at Darden Restaurants

     


    Friday, June 24, 2011 - 3:12:27 PM - Abhi Back To Top

    Thanks for sharing, good one


    Learn more about SQL Server tools