best way to grant access to sensitive data

Question

best way to grant access to sensitive data

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
May 12, 2015 at 3:16 am

#300617

Hi
I have a large table with email column. I need to grant select access to this email column to many users but the actual email should not be visible to those users. I thought of following options:
1.Create an indexed view with that column encrypted and then grant access to users.While searching by email, their search text will also be encrypted and then matched with view column.
Problem with this is that I have to create indexed view because most searches will be on Email column and table size is pretty big,20 m records. I don't prefer indexed view in general.
2. Add another column to table with encrypted email and grant access to that column to users. Problem with this is that it will increase table size and i'll need one more index.
Is there any better way to achieve this? thanks for reading..
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Your best bet with the current product is probably option #2. If you can move your database to Azure, there is dynamic data masking[/url] that will do exactly what you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

An index on an encrypted column is useless. Encryption is a non-deterministic algorithm, encrypt the same value twice and you won't get the same output.

What you can do is hash the email and index the hashed column and search on that, then you have the encrypted email that can be decrypted for display for those who have permission. The unencrypted value should not be stored in the table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 3

Thanks Grant. Unfortunately Azure is not an option for us.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 4

Hi Gail

Thanks for your input. We currently have unencrypted values stored in this table but only authorized users have access to that.

So I can add another column with hashed values and grant permission on that to required users. But I think index on hashed column won't work. I ran a query like :

select * from mytable where email='a@a.com' and it resulted in index scan and took hell lot of time.

Any solution to overcome this? or did I interpret your statement wrongly?

thanks

GilaMonster (5/12/2015)
An index on an encrypted column is useless. Encryption is a non-deterministic algorithm, encrypt the same value twice and you won't get the same output.
What you can do is hash the email and index the hashed column and search on that, then you have the encrypted email that can be decrypted for display for those who have permission. The unencrypted value should not be stored in the table.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Lowell SSC Guru Points: 323518 More actions · Answer 5

i tried putting together an example, but i keep getting a table scan, maybe becaus emy sample table is small.

does this look right to everyone?

--DROP TABLE tempdb.dbo.MyEncryptedTable

With MYFirstNames(FName) AS

(

Select 'Leonardo' UNION ALL

Select 'Brad' UNION ALL

Select 'Arnold' UNION ALL

Select 'Mark' UNION ALL

Select 'Matt' UNION ALL

Select 'Bruce'

),

MyLastNames(LName) AS

(

Select 'DeCaprio' UNION ALL

Select 'Pitt' UNION ALL

Select 'Schwarzenegger' UNION ALL

Select 'Wahlberg' UNION ALL

Select 'Damon' UNION ALL

Select 'Willis'

)

SELECT

A.FName,

B.LName ,

EncryptByPassPhrase('Sekret',A.FName + '.' + B.LName + '@gmail.com') As EncryptedEmail,

HASHBYTES('SHA1', CONVERT(VARBINARY,A.FName + '.' + B.LName + '@gmail.com')) As EmailHash

INTO tempdb.dbo.MyEncryptedTable

FROM MYFirstNames A

CROSS JOIN MyLastNames B

--ORDER BY NEWID()

CREATE INDEX IX_TheHashedValue ON tempdb.dbo.MyEncryptedTable(EmailHash)

SELECT *,convert(varchar(200),DecryptByPassPhrase('Sekret',EncryptedEmail)) As Val FROM tempdb.dbo.MyEncryptedTable WHERE EmailHash = HASHBYTES('SHA1', CONVERT(VARBINARY,'Arnold.Wahlberg@gmail.com') )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

S_Kumar_S (5/12/2015)
But I think index on hashed column won't work.

It does work.

select * from mytable where email='a@a.com'

Well that's not a search on the hashed column, that's a search on the unencrypted email column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Lowell (5/12/2015)
i tried putting together an example, but i keep getting a table scan, maybe becaus emy sample table is small.
does this look right to everyone?

Yup.

With a tiny table and a non-covering index it's easy to get a table scan rather than an index seek.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 8

GilaMonster (5/12/2015)
Lowell (5/12/2015)
i tried putting together an example, but i keep getting a table scan, maybe becaus emy sample table is small.
does this look right to everyone?
Yup.
With a tiny table and a non-covering index it's easy to get a table scan rather than an index seek.

thanks Gail, as usual, your help makes me better.

if i tweaked my query and index a bit, i got an index seek that i was expecting:

CREATE INDEX IX_TheHashedValue ON tempdb.dbo.MyEncryptedTable(EmailHash) INCLUDE (EncryptedEmail)

SELECT convert(varchar(200),DecryptByPassPhrase('Sekret',EncryptedEmail)) As Val FROM tempdb.dbo.MyEncryptedTable WHERE EmailHash = HASHBYTES('SHA1', CONVERT(VARBINARY,'Arnold.Wahlberg@gmail.com') )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 9

Thanks For putting up the example. My table is with 20 million records and I too get table scan with same query. I even tried to use index hint but still the index on hash column is not used.

I actually created an indexed view and when I run your query on my view, the query uses the index created on original table even when I am giving an index hint to use the index created on view on hash column. No idea why so happening 🙁

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 10

Sorry, I didn't see this answer before my last post. Let me check on this if it works

Lowell (5/12/2015)
GilaMonster (5/12/2015)
Lowell (5/12/2015)
i tried putting together an example, but i keep getting a table scan, maybe becaus emy sample table is small.
does this look right to everyone?
Yup.
With a tiny table and a non-covering index it's easy to get a table scan rather than an index seek.
thanks Gail, as usual, your help makes me better.
if i tweaked my query and index a bit, i got an index seek that i was expecting:
CREATE INDEX IX_TheHashedValue ON tempdb.dbo.MyEncryptedTable(EmailHash) INCLUDE (EncryptedEmail)
SELECT convert(varchar(200),DecryptByPassPhrase('Sekret',EncryptedEmail)) As Val FROM tempdb.dbo.MyEncryptedTable WHERE EmailHash = HASHBYTES('SHA1', CONVERT(VARBINARY,'Arnold.Wahlberg@gmail.com') )

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 11

thanks all. I was able to get an index seek.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 12

I'm assuming you want to allow users to query Customers by Email Address, but you don't want to allow them to query Customer email addresses in bulk, and you'd rather not increase the size of your table. One approach is to create a non-persisted computed column based on a checksum of EmailAddress. This column can then be indexed, and then a regular non-indexed view can provide select access to only those columns (including the email checksum) you allow.

create table MyCustomerContacts

(

CustomerID int not null primary key,

EmailAddress varchar(180) not null,

EmailChkSum as checksum(EmailAddress)

);

GO

deny select on MyCustomerContacts to public;

GO

create index ix_EmailChkSum

on MyCustomerContacts( EmailChkSum );

GO

create view v_MyCustomerContacts as

select CustomerID, EmailChkSum

from MyCustomerContacts;

GO

grant select on v_MyCustomerContacts to public;

GO

The user now selects from the view using a checksum on the specified email address.

select * from v_MyCustomerContacts

where EmailChkSum = checksum('johndoe29@emailserver.com');

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho