May 17, 2010 at 8:54 am
Steve Jones - Editor (5/17/2010)
My argument is encryption of stored procedures isn't worth doing. It's a PIA and doesn't provide protection. It can be broken.I see two arguments back.
- Customers mess with stored procs so vendors need it.
- Competitors can steal your code if you don't do this
I think both are invalid, for different reasons.
We are not talking about data encryption.
I get the argument Steve.
It seems to me that implicit in your second bullet seems to be that there is no point is stealing other peoples code because there is no value in the design, development and testing process and therefore no-one will do it. I don't think that this is true.
Tim
.
May 17, 2010 at 8:57 am
If that's what I'm implying, I don't mean to. I mean that it's not that secret. What you can do with SQL is fairly limited and there aren't necessarily a lot of tricks there.
Your code is covered by copyright. A competitor can't use it, and if they do, wouldn't you want it visible on people's systems for legal reasons?
May 17, 2010 at 9:12 am
Steve Jones - Editor (5/17/2010)
If that's what I'm implying, I don't mean to. I mean that it's not that secret.
We can all agree with that. It's very easy to decrypt.
Steve Jones - Editor (5/17/2010)
What you can do with SQL is fairly limited and there aren't necessarily a lot of tricks there.
There you go again! There are loads of things you can do that are innovative and useful, some of which are quite difficult to create and get right. They have a value - even if the value is only that someone else tested it to destruction and it works.
I would particularly apply that to code of general interest, e.g. innovative use of DMVs and sql internals.
Steve Jones - Editor (5/17/2010)
Your code is covered by copyright. A competitor can't use it, and if they do, wouldn't you want it visible on people's systems for legal reasons?
Absolutely right. Which is why at the end of the day I don't encrypt anything. But I might take a different view if it was unbreakable!
🙂
Tim
.
May 17, 2010 at 9:37 am
You know, part of what I don't like about Steve's argument is some protection even easily broken is worth some value. For example: A chain link fence. No one believes this chain link fence will keep someone out that really wants to get it. It will keep some people out. Some people will decide it is wrong to jump the fence, etc.
Some people will see the stored procs are encrypted and leave it alone. My experience has been that several of my clients have wanted access to our source code and couldn't get it because the stored procs were encrypted. Also, some of our clients have asked to see a specific proc un encrypted, which I have sent to them with the understanding that they would not change it etc.
So again, something that you are calling worthless and not useful, is in fact for us useful and is doing something. It may not be doing much, but it is a fence that is keeping some people out. That is worth something to us.
Ben
May 17, 2010 at 9:40 am
Fair enough, perhaps you can do something worth protecting in SQL Server. I'm not sold, but I'll stipulate I could be wrong.
In terms of a fence, perhaps there is some value, but I'm not sure this can't be handled with a EULA as the fence.
May 17, 2010 at 9:49 am
Steve, a EULA is not going to keep your common DBA from opening up a stored proc and messing with it. If there are having problems most likely the DBA will try to fix it anyway they can. It is also just as likely they wouldn't come clean when things really blow up. Like I said before, for some DBA's they will hop the fence. Of course, I don't fear them since they hopefully know what they are doing. Still there are many more DBA's that won't hop the fence and in the end are probably better off for it.
Ben
May 17, 2010 at 9:54 am
Steve Jones - Editor (5/17/2010)
I have worked with encryption and the arguments you give just don't make sense.If the software is unencrypted, you can easily run a hash against the code to see if things are changed. If DBAs want to look at it, why is it your job to stop them. They might learn something, or they might find a bug, or they might improve the code used in joins. In almost every third party system I've seen, the indexing is horrible. Plus, viewing the code doesn't change anything.
This doesn't protect you. The encryption is easily broken. You might claim it is, but it isn't. The reason Litespeed isn't copied is because of laws against copyright. If someone brings out a product, you can get an injunction against their code, ESPECIALLY if you can see it because it's not encrypted.
The reality is that you aren't providing protection from competitors. They have the resources and time to easily decrypt it.
For customers, you can just write this into future EULAs. Not current ones, but you tell them that support requires that they don't change a proc.
Nah... we have two subjects going on here. The first subject is based on your fine article... my take on it is code IS worth protecting and I've stated the reasons why. The second subject that came up in the discussion is CAN you protect it? I've already stated that I don't use encryption and there's only one reason for that... it's not worth doing because a 5 year old can break the encryption because the product just doesn't do a good job of it all. Shoot... Microsoft can't even protect their compiled code which is why they have you agree to a EULA saying that you agree to not reverse engineer the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2010 at 10:09 am
Encryption is worth it for the reasons folks have stated. The largest percentage of folks that would take advantage of your work or mess with system integrity are detered by simple measures such as encryption and locks. Against a pro, (which there few that would resort to such labor), little is safe.
😎
May 17, 2010 at 10:12 am
bkubicek (5/17/2010)
Steve, a EULA is not going to keep your common DBA from opening up a stored proc and messing with it. If there are having problems most likely the DBA will try to fix it anyway they can. It is also just as likely they wouldn't come clean when things really blow up. Like I said before, for some DBA's they will hop the fence. Of course, I don't fear them since they hopefully know what they are doing. Still there are many more DBA's that won't hop the fence and in the end are probably better off for it.Ben
It doesn't protect them from messing with it, but that's not the point. It protects you from having to support their breakage for free. You can charge for fixing it, and that is part of what support does.
May 17, 2010 at 10:23 am
I understand the EULA protects us from having to fix it for free. My point is for several of our clients the fence (encryption) keeps us from having to fix their mistakes. Honestly, I don't care how much they would have to pay, I would rather not have to fix something they messed up. I have enough on my plate fixing and added new functionality. Besides nothing helps your relationship with your client like shoving a EULA in their faces and telling how much money they are going to owe you. We prefer to have good relationships with our clients. The EULA is still there just in case. The fence is there to keep out the people it will keep out.
Ben
May 17, 2010 at 11:24 am
I am not particularly fond of encrypting stored procedures. One particular reason why is that it only obscures what is being done until you have to unencrypt it.
One example I can think of is when I was evaluating indexes on a database. There was a table that had three indexes named differently but were all the same. We had somebody who thought all stored procs needed to be encrypted. I didn't unencrypt all of the procs to see if any lame index hints were in use and removed two of the offending duplicate indexes. I broke the app due to the use of an index hint in one of the procs that was encrypted. Why was it encrypted - to hide the use of the index hint. There was nothing special in the code - just a select. I could have spent all week unencrypting the procs to examine all of them, but it seemed futile. Needless to say, the fix was easy enough. It was just a stupid reason to encrypt procs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 17, 2010 at 11:38 am
From the article (he gives an excuse that might be used)
“Well, certain encryption algorithms might need to be encrypted in stored procedures so that people don’t discover and break them.”
The best encryption algorithms have PUBLIC SOURCE. As Schneier and others have pointed out, a good encryption algorithm is not compromised by publication, indeed that's how it gets validated. A 'secret algorithm' is quite likely to have fatal weaknesses.
...
-- FORTRAN manual for Xerox Computers --
May 17, 2010 at 1:19 pm
chrisn-585491 (5/17/2010)
Encryption is worth it for the reasons folks have stated. The largest percentage of folks that would take advantage of your work or mess with system integrity are detered by simple measures such as encryption and locks. Against a pro, (which there few that would resort to such labor), little is safe.😎
No labor at all... just tried it on SQL Server 2008 and it took less than 10 minutes to decrypt any sp... I just need to do a "for-each-object". Don't kid yourelves believing that the encryption is encryption. It's at best obfuscation and I'm not even sure that hiding stuff under the admin port is to obfuscate.
There are even posts at SQLServerCentral linking to resources on how to do it and you really don't need to be a DBA to get it done.
In my mind it should go away because of the fact that it is not really encryption, maybe call it something else but loose the name encryption and replaced with some real encryption if there is a need for it by the users.
I'm pretty much buddy with Steve on this one. I liked the hashed value suggestion to void support 😉
K
May 17, 2010 at 5:36 pm
I believe the merit of stored procedure encryption is that it presents a basic layer of "obfuscation" against competitors seeing your business logic.
I can see Jeremy Pischka's original point that when he has to support slow-performing software from ISV's, the encryption is just preventing him from improving what sounds like poorly written code.
But there are scenarios where it can be useful. At my company, for example, we do government contracting. Sometimes we share larger contracts with our competitors. A fact of life in our business is that when the contracts are being renewed, everyone begins competing for the all the work on the next round and tries to kick their former business partners off the contract.
A SQL Server database is distributed with our software, and without stored procedure encryption, it would be easy for our competitors to see our business logic. Our business logic is valuable because it incorporates the work from our on-site subject matter experts.
Yes, the competing company could take the time to get into our code if they really wanted to. But this small layer of security makes it more of an annoyance and a cost to them, and thus makes it less likely they will succeed.
May 19, 2010 at 5:46 pm
Pardon me, but shouldn't we be asking the question: given that M$ HAS encryption, should they provide a strong encryption for SPs - one that really does work ?
Given that the encryption currently isn't really encryption, this would make the arguments a lot more straightforward.
Funny, but I can't help the creeping suspicion that maybe they borrowed the 'encryption' algorithm from MS Access. Anyone compared them ?
It's just a straight bytewise XOR for Access AFAIK.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply