Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If I use ENCRYPTBYPASSPHRASE to encrypt the full table then how can I decrypt full table easily by using DECRYPTBYPASSPHRASE?

Actually I have to use DECRYPTBYPASSPHRASE ('passphrase', column_name) for every column to decrypt it. So it becomes so lengthy command. If I want to decrypt all columns then I have to specify this statement for all columns. Instead of that, can I use DECRYPTBYPASSPHRASE in queries so that I can use it only once and it decrypts all the columns?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

No. Scalar operators cannot magically transform themselves into relational operators. You use the ENCRYPTBYPASSPHRASE to encrypt a column (there is no way to 'encrypt a table') and you use DECRYPTBYPASSPHRASE to decrypt a column.

Using passphrases to encrypt the data directly is a very very poor choice. A proper key hierarchy is needed where data is encrypted with a set of symmetric keys and the symmetric keys are in turn encrypted with a certificate. Encrypting the data directly with a passphrase requires expensive size-of-data operation to rotate the keys.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.