Post reply

Single Column Encryption On Sensitive Data-Unabale to use Join

  • July 26, 2013 at 8:29 am

    #296862

    Hi All,

    My issue is regarding Single Column Encryption on Primary Key Column , what happens if this Primary Key or Unique Key column contains sensitive data, and also used for joins. between two tables.?

    what are the options to encrypt this data , so that they can remain same , even in encrypted state and can be used for joins.

    e.g.

    in table A there are two columns.

    Member_Id(PK) City

    1234 London

    6789 Paris

    7777 NewYork

    in table B there are two columns

    Member_Id(FK) Bank_account

    1234 RBC

    1234 ScotiaBank

    7777 BOB

    When I applied single column encryption, to save these sensitive data, it worked perfactly, but, it gives me different encrypted number for same Member_Id(both in table A and B) everytime.

    e.g.

    1234(pk) -> AES_128 -> HASDHAS829092)(jsadkljdhnasd

    1234(fk) -> AES_128 -> NCBASJEUHHKLS893298()JSLHA

    so I can not use this fields to join this tables any more,!!!!

    is there any option to solve this.?

    I already used "DataMasking" , which works fine.

    Now, I also want to try with encryption too., Please help me..

    Thanks..

  • July 26, 2013 at 1:03 pm

    #1636297

    The native encryption methods in SQL server use a "randomizer" seed to make it difficult to infer values from a large set of values. That is why you are seeing the different encryption results for the same value.

    You have several choice to work around this issue. My suggestion would be to have a master ID table that contains a pure integer ID (identity) column as a primary key, then the encrypted from of the actual user ID. I would also maintain a hash of the unencrypted ID with an index so I could look up the "real" ID. This table should have a clustered index on the PK and the hash, and a non-clustered index on the hash with the ID column included. This will be a narrow table and optimized for efficient look-ups via either entity.

    Then all the other tables have their FK back to the integer ID in the master table and thus, your queries will always join to the master table. Sometimes this sort of architecture is referred to as star schema. It provides for efficient partitioning of the data in narrower tables especially for transactional environments.

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 2 posts - 1 through 1 (of 1 total)

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