Encrypting confidential data including foreign keys

From: Tom (Tom_at_nospam.com)
Date: 10/28/05

  • Next message: Scott Allen: "Re: Encrypting confidential data including foreign keys" 
    Date: Fri, 28 Oct 2005 21:29:31 +0100

    Hi,

    I am currently working on a project for a client. The project is to create
    an intranet site to maintain confidential employee data. There is a master
    employee table, containing name, date of birth etc. However, the table also
    links to some look-up tables, such as grades, ethnicity, citizenship etc.

    I was wondering what was the best solution for encrypting the data in SQL
    Server 2000, so that someone with database access cannot read the
    confidential data. Encrypting the free text data is fairly simple, but I am
    not sure how to encrypt the foreign keys while preserving referential
    integrity.

    For example, if an employee has citizenship = 5, it is quite easy to workout
    the citizenship from the look-up table. Even if 5 is encrypted to ABC, it is
    quite easy to see the pattern on the table.

    Also, the client wants to do some reporting on the tables, so run queries
    with filters (such as Citizenship=UK) on encrypted data.

    We looked at xpcrypt, but it appears to create automatically a view
    containing the decrypted data for the duration of the SQL session, so I am
    not sure how appropriate it is for a web application, maintaining pretty
    much a constant connection/session to the DB.

    Any advice welcome!

    Thanks,
    Tom

  • Next message: Scott Allen: "Re: Encrypting confidential data including foreign keys"

    Relevant Pages

    • Re: Analyzing/Normalizing Database
      ... and other attributes of the employee - for example: ... Now, as I understand it, a contract is arranged with a client (may be ... If each employee has multiple possible pay rates, ...
      (microsoft.public.access.tablesdbdesign)
    • Re: Remoting and Events with VB.NET
      ... EventNotifier that it registers itself with. ... receive events has an EventListener that is registers itself with. ... Client loads and attaches to server ... Employee and all child classes are checked out. ...
      (microsoft.public.dotnet.framework.remoting)
    • Re: Slightly OT, and a cautionary tale
      ... I've been here over 21 years, first as an employee of the client ... employer and found another Oracle DBA job at another company in the ... You can lead a horse to water, but you can't make him drink. ...
      (comp.databases.oracle.server)
    • Re: talking to boss
      ... > some tenured professors where the tenure is good (there is some tenure ... >> 2 clients, especially if you have one client who throws multiple ... My point is - you need to be doing this while working as an employee also. ... The only thing uglier than a government that sucks up to labor unions ...
      (sci.research.careers)
    • RE: problems with displaying "duplicate vlookup values" in same co
      ... Set ws1 = Worksheets("AHN Client Roster") ... I created the sheet with CASE-ID, LASTNAME as you suggested, however when I ... Set ws1 = Worksheets("JNNR Client Roster") ... 'Employee Roster'! ...
      (microsoft.public.excel.misc)