Create a view to view encrypted columns from a table

Question

Create a view to view encrypted columns from a table

SSRS Newbie

Hall of Fame

Points: 3244
More actions
March 17, 2017 at 2:12 pm

#321770

Hello All,
I have a table that has 5 encrypted columns. I'm using Symmetric Key for the encryption. Usually when I run the query I Open the Symmetric Key and then run my select statement. How do I achieve this using Views?
Below is the sample select code:
use TestDB GO OPEN SYMMETRIC KEY Enc_Key DECRYPTION BY CERTIFICATE Cert_TestDB GO
SELECT TOP 1000 [enc_Name1] , CONVERT(char,DECRYPTBYKEY([enc_Name1])) 'DecryptedName1' FROM [TestDB].[dbo].[Order_Det]
CLOSE SYMMETRIC KEY Enc_Key

Viewing 10 posts - 1 through 9 (of 9 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

SSRS Newbie - Friday, March 17, 2017 2:12 PM
Hello All,
I have a table that has 5 encrypted columns. I'm using Symmetric Key for the encryption. Usually when I run the query I Open the Symmetric Key and then run my select statement. How do I achieve this using Views?
Below is the sample select code:
use TestDB GO OPEN SYMMETRIC KEY Enc_Key DECRYPTION BY CERTIFICATE Cert_TestDB GO
SELECT TOP 1000 [enc_Name1] , CONVERT(char,DECRYPTBYKEY([enc_Name1])) 'DecryptedName1' FROM [TestDB].[dbo].[Order_Det]
CLOSE SYMMETRIC KEY Enc_Key

DECRYPTBYKEY is a scalar function. Drop all of the decryption code into a UDF and reference it in your view.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Seems a bit crazy to do it in a view because then you have to protect the view and all that goes with it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 3

Jeff,
Can you suggest an alternative to views? Need help with this.

Thanks

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

You'd have to revoke privs to use the view from everyone except the group of people that are authorized to view the decrypted data. Keep in mind that (IIRC) that won't keep people with sysadmin privs from viewing the decrypted data. That brings on the larger subject of "Is your system actually secure"? For example, do you have ANY applications or non-DBA users that have sysadmin or DBO privs (just to start) and do you have things like password complexity enforcement at the Windows and SQL Server levels?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

You still need the key open, so anyone that can't open the key will get NULLs in the view.

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 6

I do not have any applications or non-DBA users that have sysadmin or DBO privs. How can we perform CRUD operations on the encrypted table? Once I get the view working for reading the encrypted records, I plan to use it for update, delete and insert. Any suggestions to perform CRUD operation on encrypted table are welcome? Thanks for responding

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

What's the purpose of the view? In other words, what are you trying to accomplish?

If you use decryptbykey() in the view, you can't insert data into that field. It's the result of a function. Inserts/updates/deletes against a view only work on one table, but the columns are needed in the view, which means you'd need:

create view myview as select mypk , decrypteddata = decryptbykey(myencyryptedcolumn) , myencryptedcolumn from mytable

SSRS Newbie Hall of Fame Points: 3244 More actions · Answer 8

Steve,
The idea behind creating a view is to allow programmers to perform CRUD operations using views. My table has firstname, lastname and e_firstname, e_lastname columns (encrypted columns using orginal firstname, lastname columns) Can you suggest any other alternative?

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

Use two views. One for CRUD, one for display that decrypts data.