best way to grant access to sensitive data

  • 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.

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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
  • Thanks Grant. Unfortunately Azure is not an option for us.

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

  • 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.

  • 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!

  • 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
  • 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
  • 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!

  • 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.

  • 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.

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

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

  • 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

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

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