SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encrypting SQL Code


Encrypting SQL Code

Author
Message
Jason Miller-476791
Jason Miller-476791
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 694
Most of the work I do here supports "internal" clients. The parts that support external clients is only accessible via web based reports (CE). The work for the internal clients is rarely encrypted. BUT, there have been instances where encryption was warranted. Mostly to protect the end-users from hurting themselves. And by that I mean, garnering the wrath of our team for their experiments.

This was implemented after one very nice, and well meaning accountant saw what I was typing and decided it would be nice to report on income and positions, but didn't join the information correctly. The resulting Cartesian set (20M x 21M) brought our production server to a standstill. (These were fairly wide tables, 350+ columns. They've been reworked in the years since.)

This problem has been mitigated by SQL training classes, and by a more friendly relationship with the departments. Though I still have the reputation as a hostile, petulant @rsehole, my coworkers actually come over occasionally to say hello and ask a question such as "how do I...". They've come up with a nickname that I actually like... I'm Mister Black & White. This came about during a meeting in which I took the position that X is either right or wrong. "Like being pregnant or dead, either you are, or you aren't."

I agree with the previous posts, for the most part it is unnecessary, but there exist specific circumstances where it is desirable. Not so much to stop a hacker, but to stop the well intentioned coworker. I guess this is analogous to the dead bolt on my doors, they'll slow the intruder. But a determined hacker/intruder can circumvent the protection.



Best wishes for Passover, Good Friday, NichLactemyer, or whatever holiday it is that you celebrate or not...

Honor Super Omnia-
Jason Miller
Andy sql
Andy sql
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2943 Visits: 1323
Like being pregnant or dead, either you are, or you aren't.

have you heard of Schrödinger's cat??? :-P
Larry Brister-297916
Larry Brister-297916
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 18
Almost no one cares about this.

People by applications and use them.
Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 758
At previous employers I worked in Oracle and we used the "Wrap" function to obfuscate (love that word) the code. "Wrap"ped code was only readable by the Oracle runtime engine, I have not seen a hack of it, but there might be. Anyway ... we wrapped or code because we were in a very competitive "new-idea" market. Letting our competitors see how we accomplished what we did would place us at a dis-advantage. Don't now if that was really true, but that was our story.

<><
Livin' down on the cube farm. Left, left, then a right.
Jason Miller-476791
Jason Miller-476791
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 694
AndyD (4/10/2009)
Like being pregnant or dead, either you are, or you aren't.

have you heard of Schrödinger's cat??? :-P


I have, but it was 20 years ago. My progression through physics only took me through 1-4, Modern, and a few that bordered on engineering (Statics, Dynamics, Modeling of Linear and Dynamic Systems, etc)


With regard to quantum mechanics, I tend to think along the same lines as Einstein. The quantum guys say there is no way to predict the flip of a coin. Einstein would argue there is. IFF you knew all of the forces and assumptions, you could predict the result. I interpret that as saying, we simply don't have enough information, yet.

It's a similar discussion as the multiple universe, infinitive universe argument.

Honor Super Omnia-
Jason Miller
bkubicek
bkubicek
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4024 Visits: 842
We encrypt our sql stored procs. One of the main reasons is so that we don't have a support nightmare. For the causal client, they might try to change our stored procs to "improve" them, only to really mess things up. This way we can be relatively sure that what we have in source control for their version is most likely what they have on their database. So when tracking down bugs or issues for clients at least we don't have to worry about if they messed with our stored proc code.

Ben
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25297 Visits: 2746
I wish encryption did work. Not to prevent against updates, because in most cases I think it's having an app built on stored procs that gives it extra value to the client - they CAN change it if needed, or tweak performance, but to give us the ability to protect intellectual property. I'll agree that real IP in a proc is rare, but maybe it's rarer than it should be because we don't have the encryption. Same for DTS/SSIS.

I get the pain it causes from the DBA side, but as someone who has built a thing or two, it would be nice to be able to secure a few key ideas. More often the IP gets loaded on the app side of things, and as you point out it's hard even there to keep secrets.

It's not on my top 10 list of things I wish worked better, but there are people who legitimately need to protect IP and I wish we had a way to give it to them.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Andy sql
Andy sql
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2943 Visits: 1323
The quantum guys say there is no way to predict the flip of a coin. Einstein would argue there is. IFF you knew all of the forces and assumptions, you could predict the result. I interpret that as saying, we simply don't have enough information, yet.

I think it is a little more interesting than this. The Quantum Guy says the coin is both heads and tails at the same time; only when someone looks at the coin does it change from an indeterminate state to either heads or tails.

Now, how does this relate to encrypting our SQL code? hmm...
Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 758
Jason Miller (4/10/2009)
Best wishes for Passover, Good Friday, NichLactemyer, or whatever holiday it is that you celebrate or not...



Ok, I'll bite, not even Google knows what "NichLactemyer" is.

Happy Good Friday.

<><
Livin' down on the cube farm. Left, left, then a right.
chrisn-585491
chrisn-585491
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 2564
We encrypt to keep clients from shooting themselves in the foot and causing support nightmares. (Which in some determined cases they still manage to do!)
w00t
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search