August 24, 2011 at 2:21 pm
I am trying to create a view that will select fields from a table that has column-level encryption implemented. How do I best open my symmetric key and certificate without getting an error? Specifically, where do I open the key/certificate? Below is what I currently have and I get an error before I even try to run it.
[font="Courier New"]CREATE VIEW custView AS (
OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE Cert1
SELECT custID, name, CONVERT(VARCHAR(12), DECRYPTBYKEY(SSN)) AS SSN, CONVERT(VARCHAR(50), DECRYPTBYKEY(ADDR )) AS ADDR
FROM customer
);[/font]
How can I decrypt my data from inside a view?
Thanks.
August 24, 2011 at 2:28 pm
What error are you getting?
I have always done this within stored procedures, not views.
Is there a reason you are using a view instead of a sproc to do this?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 24, 2011 at 2:29 pm
You will have to assume the key is open before the view is queried. A view can only contain a single select statement, nothing else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2011 at 2:30 pm
Marios Philippopoulos (8/24/2011)
What error are you getting?
Msg 156, Level 15, State 1, Procedure custView, Line 2
Incorrect syntax near the keyword 'OPEN'.
Msg 102, Level 15, State 1, Procedure custView, Line 7
Incorrect syntax near ')'.
Because the OPEN is not permitted inside a view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2011 at 2:31 pm
I get a syntax error. Doesn't like where I placed the 'OPEN'.
August 24, 2011 at 2:38 pm
flora M (8/24/2011)
Specifically, where do I open the key/certificate? Below is what I currently have and I get an error before I even try to run it.[font="Courier New"]CREATE VIEW custView AS (
OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE Cert1
SELECT custID, name, CONVERT(VARCHAR(12), DECRYPTBYKEY(SSN)) AS SSN, CONVERT(VARCHAR(50), DECRYPTBYKEY(ADDR )) AS ADDR
FROM customer
);[/font]
How can I decrypt my data from inside a view?
Thanks.
Typically you would open the key before querying the view.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2011 at 2:43 pm
Thanks for all the responses. I'll look for an option to do this from a stored procedure.
August 24, 2011 at 2:55 pm
flora M (8/24/2011)
Thanks for all the responses. I'll look for an option to do this from a stored procedure.
why??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2011 at 3:01 pm
Perry Whittle (8/24/2011)
flora M (8/24/2011)
Thanks for all the responses. I'll look for an option to do this from a stored procedure.why??
Because:
1) the capacity that this view will serve is way simple than I'm trying to make it and opening the key before the function will actually not be welcomed by the person that I'm doing this for
2) the request was more of a want than a need
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply