Post reply

Procedure Encryption

  • June 2, 2011 at 10:45 am

    #389649

    HI All,

    I've about 50 Procdures in my Db,in which each Procedure has certian columns that are encrypted with Symmetric Key.Now I've to write script to decrypt that columns to normal plain text columns.

    Presently i'm doing like this

    Alter Procedure

    --------

    Open symmentric key

    by certificate

    -------

    Decrypting that particular column

    Now i've write a batch script for all the 50 Procedure so that the batch script would be deployed in QA as well as Prod.Could you Please help me is any other way i could be able to approach this

  • June 2, 2011 at 12:03 pm

    #1333275

    It sounds like the process you have thought up will work...are you not happy with some aspect of it?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 2, 2011 at 12:07 pm

    #1333277

    I'm looking for is their any Best and Easy possible way of it.

  • June 2, 2011 at 12:23 pm

    #1333288

    I guess I'm unclear what you are asking. You have 50 or so sprocs that use columns that are encrypted with a particular cert. The code that accesses those columns does or does not currently contain the open/decrypt logic? So what question are you really asking. Sorry for my confusion.

    CEWII

  • June 2, 2011 at 1:03 pm

    #1333316

    gordon.davis (6/2/2011)

    I'm looking for is their any Best and Easy possible way of it.

    I think you have all the bases covered but you'll have to be more specific about what you mean by "Best and Easy" as that will depend on what things you're trying to avoid/achieve.

    Some possible problems with the sequence you described:

    1) Alter Procedure

    2) Open symmentric key by certificate

    3) Decrypting that particular column

    If anyone tries to use the procedure between when step 1 completes and when step 3 completes they'll be blocked from accessing the data or they'll get incorrect results.

    Will you have an idle system to work on when deploying the changes? Is the process taking too long for your liking? Is it too disruptive on other databases users/processes?

    I am just trying to find out what you think is wrong with the process the way you have designed it. We can't recommend a solution unless the problem is clearly stated.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 5 posts - 1 through 4 (of 4 total)

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