Encrypting SQL Code

  • "Encryption besides being annoying and pointless, it can also be dangerous if you lose the source code (a malicious attack by a disgruntled employee about to leave). Also I always thought that there must be some performance overhead of encryption/decryption"- Quote from athurgar

    I am in full agreement with this! Chance of increasing your workload and potential dangers totally outweigh any possible benefit.

  • It kind of reminds me of several systems I've looked at that try to protect their "IP" by naming tables and columns like tbl1.col12 and not using any declarative integrity. Again, annoying and not just pointless, but downright stupid. I agree with Steve, when it comes to database design, there is no real IP involved and anytime someone thinks they've come up with some new spiffy way of doing things, it always turns out to be a rehash of a bad idea.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • The best advice I ever got about encryption was this:

    "fvlsdbjawdygexdvnsdfyeqrmcnbsdfsdofglkfbnsdkfasgsfd;zdjfhasdsjbc"

    😀

  • 😀

  • stevet (4/10/2009)


    This from the same guy that made the windows login password scheme so complex that users are putting their passwords on post-it notes on their monitors so that they can remember them.

    This is one of my favorite things. That and the accountants not locking their PC's when they go to meetings or lunch.

  • Jack Corbett (4/10/2009)This is one of my favorite things. That and the accountants not locking their PC's when they go to meetings or lunch.

    This is only a problem when they also have the spreadsheet with all the financial information for the company open also - but, of course they want all that data encrypted :w00t:

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • terrance.steadman (4/10/2009)


    AndyD (4/10/2009)

    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...

    This is a little 2 sided now isn't it? Just to add a bit of Oh No's to this, we do live in a 3D world, not a 2D world. So, a coin *technically* does have a third option other than heads or tails. It also has an edge side. It just happens to be the rarest option to be hit, but still it is possible.

    Yes, I would also like to ask what this has to do with encrypting SQL code? Now, I have to add that I never heard someone say when asked: "Heads or tails?" "Oh no, I'll choose the edge."

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • I think that the general consensus is that this doesnt' really provide a lot of security, and it's a pain.

    If you think it reduces CS calls, you're probably looking at the problem the wrong way.

    If you think your IP is being protected, you're in denial, in a couple ways. Your IP isn't that great, and it's not. Those that would steal the IP can get the code.

  • This is an interesting thread, but you all seem to work on commercial project. Let's say your system is tracking the location of solders on the field of battle. Of course, you will have have layers of secuirty to prevent the other sidefrom hacking into the system. You might consider encrypting the data, code, table names, columns names just to make it more difficult for the hacker. Yes, he is already in the system, but you want to slow him down, so the auditing tools detect him (her) before your guys in the field are targeted.

    I work on communication applications sold to the US military and they require that we encrypt the stored procedures. They also require a great number of other secuirty checks and settings. Look into the SQL security requirements of the DOD's gold disk.

    You think your systems are secure, just check out the public version of gold disk and find out for sure.

  • Here's a better solution. Have the developer send you the code in identifiable releases (4.1, 4.2, etc.). Have the pertinent block of code/query/etc. prefaced with something like -- DO NOT MODIFY, V 4.2 STABLE -- and close the block with something like -- V 4.2 STABLE END --. The developer can hash/checksum that block of code, and both the developer and DBA are happy.

    The Developer can verify his code is the same by comparing checksums later on, but the DBA can examine it and figure out if something, say a RBAR, is responsible for the slow execution.

    It's not foolproof, but transparency and trust are maintained to a certain extent. As long as the DBA has a record of the checksums/hashes on file, he or she can be sure the developer isn't trying to pull a fast one saying the code was changed.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • This is a topic I have been struggling with at my new job with a software company that hasn't exactly had a DBA before and works very hard to please ALL of its clients (impossible I know, and I am working on changing that culture). Our customers are typically very small health care agencies with limited to NO real IT staff. Anyway...

    I don't think encryption/obfustication does the product any good and will not server clients well in the end. As software vendors we want to create and deliver a good product, but in the end we want the customer to own his environment and be aware of good database practices like backups, index maintenance, predicting space requirements, etc. etc. In order for the customer's DBA to be aware of indexing needs and in turn the performance of their environment, I feel they need the ability to see what they need to index and to become a little familiar with the SP at least. Data evolves, so indexing needs to be revisited (at least). I 100% am against customers changing any stored procedure, but I have nothing against them seeing the code (as long as the proper legal documents are in place for the proprietary knowledge, etc). I am always open to constructive criticism whether it come from a colleague or a customer.

    I am simplifying this next part, but you get the gist...

    The thing I do to ensure we don't end up with a support nightmare because the customer 'tweaked" soemthing in the database is to schema compare and hash compare SP what we delivered against what the customer currently has first. If that checks out, we continue... If it doesn't check out, we stop and will usually offer to assist at professional services rate and make it known that current support agreements are not valid until the schemas and SP are sorted out (at professional services costs).

    You need to trust your relationship with your customer and if your sales team does a good job up front and describes expectations and consequences, you have a solid procedure for support and know your own schemas, yuor support issues should be kept to real support issues.

  • Steve,

    I did some work a while back where this was a concern - the issue was less in them stealing the code and more aorund supporting something they botched up.

    Answer was if they touched it the warranty was null and void.

    After about 2 mos something stopped working and they wanted help, used RedGate to compare with what we gave em, found the changes and called them on it - ended up fixing it at time and materials, but redgate saved us some $$$ and aggrevation!

    Turned out one of thier employees messed something up in a couple of sprocs.

    Mark

  • So what does the edge of the coin have to do with encryption? It would seem nothing at all, other than to consider that the chances of getting the edge are slim to none for most situations and people. It is only when the conditions are such that even the slightest chance for compromise of a database system is cause for alarm that you really should think about it.

    Most people here talk about systems that are written to be used by other customers on the customers own systems and ran by the customers own IT staff. The programmers will want to protect their logic a bit to either hide their shortcomings or to keep others from taking their brilliance. Others just don't want the customer to be sticking their nose and sticky keyboards into places they don't belong. In most of these cases, what is the primary thing that is lost if the customer does get into the database? Money.

    Some companies write their code to be ran on their own systems or to be ran by a customer on a dedicated system that must do one thing - work good. For this, there is little need to worry about the customer getting into your code. The higher concern now is the outsider that should not be there in the first place, commonly called a hacker. This person actually can be a problem to consider in the previous paragraph too. But in the end, what must be considered is what could be lost if a hacker did get into the system. What are you trying to protect? In most cases, the end result is still money.

    One person did show that there is a case when all possible attempts to protect the data must be taken. This could be done with encryption of the data, encryption of the stored procedures, generalized naming of tables and columns, etc. What could be so valuable that it is worth to do all you can and even consider the most remote chances possible (the edge of the coin)? How about when the final cost of data compromise may not be counted in money, but be counted in human lives? Where the hackers trying to get into your system are not just some smart kids having some fun but somebody who is determined to find out any weakness and exploit it in a most deadly way.

    I do agree that too many companies and too many people inflate the importance of their code and the importance of their data to justify their extreme encryption efforts. But for some situations, enough just isn't enough. The cost of data compromise through code or data itself is just too high.

  • Mark Horninger (4/13/2009)


    Steve,

    I did some work a while back where this was a concern - the issue was less in them stealing the code and more aorund supporting something they botched up.

    Answer was if they touched it the warranty was null and void.

    After about 2 mos something stopped working and they wanted help, used RedGate to compare with what we gave em, found the changes and called them on it - ended up fixing it at time and materials, but redgate saved us some $$$ and aggrevation!

    Turned out one of thier employees messed something up in a couple of sprocs.

    Mark

    SQL Compare (and other like products) is definitely a lifesaver. We had a similar issue with a developer. They would send us scripts for the QA environment then for production. At some point it got out of sync and they and our own developers could not figure it out. Enter SQL Compare, and it proved that they had sent us an incorrect version of the production environment.

    In the long run, this may be the most doable way to insure your code, as a developer, has not changed.

    Gaby

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • John,

    I think you might have hit the exception that proves the rule. Slowing someone down in that case might be a good use for encryption, or it might not be. The code in 2000 can be broken very quickly with modern hardware.

Viewing 15 posts - 46 through 60 (of 69 total)

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