Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Encrypting SQL Code Expand / Collapse
Author
Message
Posted Friday, April 10, 2009 4:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, 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
Post #694717
Posted Friday, April 10, 2009 5:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,067, Visits: 1,148
Like being pregnant or dead, either you are, or you aren't.

have you heard of Schrödinger's cat???
Post #694732
Posted Friday, April 10, 2009 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 1, 2010 8:40 AM
Points: 24, Visits: 18
Almost no one cares about this.

People by applications and use them.

Post #694743
Posted Friday, April 10, 2009 5:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #694744
Posted Friday, April 10, 2009 5:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, 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???


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
Post #694749
Posted Friday, April 10, 2009 6:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:17 PM
Points: 1,891, Visits: 481
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
Post #694751
Posted Friday, April 10, 2009 6:18 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 7:34 AM
Points: 6,804, Visits: 1,933
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
Post #694765
Posted Friday, April 10, 2009 6:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,067, Visits: 1,148
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...
Post #694767
Posted Friday, April 10, 2009 6:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #694769
Posted Friday, April 10, 2009 6:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 705, Visits: 1,779
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!)
Post #694779
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse