Post reply

Decrypted Data Using Views

  • February 24, 2010 at 5:46 pm

    #211733

    I have successfully encrypted the data on few columns in one table for testing. I have used Symmetric Key with Certificate..

    Create Master Key Encryption By Password = 'XXXX'

    Create Certificate MyCertificate Encryption by password = 'zzzz'

    with subject = 'yyyy'

    Create Symmetric Key MySymmetricKey

    With Algorithm = Triple_des Encryption By Certificate MyCertificate

    -- Open Symmetric Key

    Open Symmetric Key MySymmetricKey

    Decryption By Certificate MyCertificate with password = 'zzzz'

    Then I created a view for users to see the data by decrypting it as

    Convert( nvarchar(60), DecryptByKey(encrypt_col)) decrypt_col

    In order to view the rows, users must open the symmetric key..

    What I want is, user just query the view without opening the symmetric key..

    How can I avoid opening the key by creating a view, that include the open as well?

    Or How can I run open command for the users every time when they log in, so that view works just fine..?

    Without opening the symmetric key, results are just null.

    Any help is highly appreciated.

    -Shihab

  • February 24, 2010 at 6:21 pm

    #1124695

    i don't believe you can; the whole purpose of encryption is to hide the data....if the data was encrypted, but exposed as unencrypted in a view, it defeats the purpose, right? a view can only have a select statement, no commands like open key etc.

    you could use a stored procedure to return the decrypted results; so if you were really just trying a proof of concept, you could use openrowset to loop back to your own server, just to put the results of a procedure in a view.

    bet it's slow as heck, but this might work:

    CREATE VIEW dbo.DefeatThePurposeOfEncryption

    AS

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.MyDecryptionProcedure')

    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!

  • February 24, 2010 at 7:14 pm

    #1124701

    It make sense. Now, I don't use Symmetric Key and I just use Certificate and Encrypt/DecryptByCert function help me to achieve what I want. I know this is not optimal way of doing it. But this is just a start for me to present to my superiors, later I have to convince them to use Symmetric and Asymmetric keys to protect the data. That requires code change at the development engineering. In this way, I by pass that hassle.

    Thank you so much for the quick response as always.

    "I spend much time to research, at the end, I turn to SSC (SQL Server Central) and you guys help me to move forward quick and confident"

  • April 12, 2011 at 9:41 pm

    #1310788

    😀 That is a good name for a view

  • April 12, 2011 at 10:34 pm

    #1310792

    This will do what you want, although I would suggest protecting the certificate private key with the database master key instead of a password, since the password has to be in the view if you use a password.

    create view MyView

    as

    select

    Convert(nvarchar(60),

    DecryptByKeyAutoCert(cert_ID('MyCertificate'),'zzzz',a.MyCypherText)

    ) as MyClearText

    from

    MyTable a

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

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