Encrypting confidential data including foreign keys

Discussion in 'ASP .Net' started by Tom, Oct 28, 2005.

  1. Tom

    Tom Guest

    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
     
    Tom, Oct 28, 2005
    #1
    1. Advertising

  2. Tom

    Scott Allen Guest

    On Fri, 28 Oct 2005 21:29:31 +0100, "Tom" <> wrote:

    > but I am
    >not sure how to encrypt the foreign keys while preserving referential
    >integrity.
    >


    Hmm, I doubt you'll find any encryption product that can encrypt a
    foreign key column.

    --
    Scott
    http://www.OdeToCode.com/blogs/scott/
     
    Scott Allen, Oct 29, 2005
    #2
    1. Advertising

  3. Hi,
    well try our software for SQL server side encryption for that , visit and
    refer :
    http://database-encryption.com/ it will serve your purpose

    Regards
    --
    Andy Davis
    Activecrypt Team
    --------------------------------------------SQL Server Encryption Software
    http://www.activecrypt.com



    "Tom" wrote:

    > 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
    >
    >
    >
     
    =?Utf-8?B?QW5keSBEYXZpcw==?=, Nov 29, 2005
    #3
  4. Tom

    Mike C# Guest

    http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart1.asp

    You can't really encrypt foreign key columns to any useful degree, since
    they'll have to be encrypted on the referenced table as well and once again
    you've got an easily visible link... What you can do is encrypt the
    descripition data in the referenced table to make it unreadable. So instead
    of storing "NATIVE AMERICAN" in plain text on the ethnicity table, you could
    encrypt that description and store it.

    "Tom" <> wrote in message
    news:...
    > 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
    >
     
    Mike C#, May 18, 2006
    #4
  5. you can use built in database security to accomplish your goal
     
    Alexander Kuznetsov, May 18, 2006
    #5
  6. Tom

    Mike C# Guest

    What SQL 2000 function is that?

    "Alexander Kuznetsov" <> wrote in message
    news:...
    > you can use built in database security to accomplish your goal
    >
     
    Mike C#, May 18, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. H5N1
    Replies:
    0
    Views:
    449
  2. Harry George
    Replies:
    9
    Views:
    723
    sonal
    Jun 13, 2006
  3. 2K
    Replies:
    0
    Views:
    454
  4. dfa_geko
    Replies:
    3
    Views:
    394
    dfa_geko
    Apr 6, 2007
  5. paratge
    Replies:
    0
    Views:
    86
    paratge
    Apr 14, 2007
Loading...

Share This Page