March 16, 2018 at 1:12 pm
Generally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.
March 16, 2018 at 1:16 pm
RenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.
A while back I worked for a company where they had with encryption on every single stored proc. Of course, it is possible to break that encryption, but certainly made it a lot harder for customers to steal your code.
Ben
March 16, 2018 at 1:24 pm
bkubicek - Friday, March 16, 2018 1:16 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.A while back I worked for a company where they had with encryption on every single stored proc. Of course, it is possible to break that encryption, but certainly made it a lot harder for customers to steal your code.
Ben
Rumor has it that SQL Prompt does it automatically. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2018 at 1:51 pm
Jeff Moden - Friday, March 16, 2018 1:24 PMbkubicek - Friday, March 16, 2018 1:16 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.A while back I worked for a company where they had with encryption on every single stored proc. Of course, it is possible to break that encryption, but certainly made it a lot harder for customers to steal your code.
Ben
Rumor has it that SQL Prompt does it automatically. 😉
I have SQLPrompt and I just tried it. It for sure works. I create a test stored proc with encryption. You just need to have sysadmin rights. So much for using with encryption.
Ben
March 16, 2018 at 1:56 pm
RenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.
Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2018 at 2:00 pm
Jeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Perhaps some legal stuff in your software agreement should exist. Something like you will not steel our code and call it your own.
Ben
March 16, 2018 at 2:03 pm
bkubicek - Friday, March 16, 2018 2:00 PMJeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Perhaps some legal stuff in your software agreement should exist. Something like you will not steel our code and call it your own.
Ben
Yes but the burden of proof would be on you. Were those folks dumb enough to actually put their theft in writing? Maybe in a phone call that's recorded (we record everything with or without warning to the person on the other end of the line)?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2018 at 2:06 pm
I have never worked in a shop that had a true DBA--it was always a network admin who set it up the SQL Server instance and the developer setting up the databases, permissions, yada. In 2002 was the first time I worked at a shop that had SQL Server and it was wasn't properly set up or utilized the best (Access front-end with in-line SQL, they wanted replication but things were set up so poorly that a contractor couldn't get things replicated, etc.) Never having used SQL Server, I bought myself a SQL Server programming book and learned about stored procedures for the application that I had been tasked to develop and I've been using them ever since.
Fast forward 16 years--I still never held a job with "DBA" in my title but I'm still the one who gets to do the fun stuff in SQL Server--setting up the databases, creating the SSIS packages, everything. The guy who has "/DBA" in his title deploys my changes to the production SQL Server but I'm the one who scripts everything out, etc. I've got developers who I manage who try to slip in in-line SQL but I force them to use stored procedures--easier to maintain, reuse, and trouble-shoot, in addition to being more secure.
March 16, 2018 at 2:18 pm
bkubicek - Friday, March 16, 2018 2:00 PMJeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Perhaps some legal stuff in your software agreement should exist. Something like you will not steel our code and call it your own.
Ben
Of course such legal stuff exists, but when you are dealing with customers in other countries, some of which with questionable legal standards, it is tough.
March 16, 2018 at 2:21 pm
Jeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Yes, but we didn't keep it all in SQL. Instead we went to simpler CRUD style queries, and performed the proprietary logic itself in application code.
March 16, 2018 at 2:27 pm
RenoChris - Friday, March 16, 2018 2:21 PMJeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Yes, but we didn't keep it all in SQL. Instead we went to simpler CRUD style queries, and performed the proprietary logic itself in application code.
Anything done to keep people from attaching a debugger to your running code and gleaning from that your proprietary logic? As they say, where there is a will there is a way.
March 16, 2018 at 4:24 pm
Lynn Pettis - Friday, March 16, 2018 2:27 PMRenoChris - Friday, March 16, 2018 2:21 PMJeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Yes, but we didn't keep it all in SQL. Instead we went to simpler CRUD style queries, and performed the proprietary logic itself in application code.
Anything done to keep people from attaching a debugger to your running code and gleaning from that your proprietary logic? As they say, where there is a will there is a way.
Another aspect that needs to be considered... How many sales have you lost as a result of that derision?
I'm pretty sure i'm not the only DBA who will automatically cast a "NO" vote for any 3rd party application that either encrypts stored procs, uses ORMs and/or hides database level logic in the application tier.
It has nothing to do with wanting to steal code and everything to do with efficiency trouble shooting performance issues when they arise. I don't want to be responsible for admining a database that was intentionally designed to prevent me from admining it.
March 16, 2018 at 4:52 pm
RenoChris - Friday, March 16, 2018 2:21 PMJeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Yes, but we didn't keep it all in SQL. Instead we went to simpler CRUD style queries, and performed the proprietary logic itself in application code.
What did the application do and how was the performance?
It can be one hell of a conundrum.... sometimes it comes down to keeping proprietary processes secret or having them work the same week they're called.
For example, I had a team approach me with some performance problems for a small company that I was trying to help for free. They had done similar (basic CRUD with all logic in the App) and couldn't understand why their proprietary application code was running so very slow in a certain area. It turns out that they were generating (in a proprietary fashion) 225,000 wide rows and sending it all to the database using one INSERT/VALUES statement per row. As an experiment, I took their proprietary logic and refactored it as a stored procedure... it ran in about 2 seconds.
Not sure what they ended up doing with that problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2018 at 5:00 pm
Jason A. Long - Friday, March 16, 2018 4:24 PMLynn Pettis - Friday, March 16, 2018 2:27 PMRenoChris - Friday, March 16, 2018 2:21 PMJeff Moden - Friday, March 16, 2018 1:56 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.Agreed and you make an outstanding point. It's real tough to keep sysadmins from viewing anything.
Shifting gears a bit, it's actually not difficult to RE application code to get the SQL anymore. Sometimes, everything you need can be gleaned from a Profiler or Extended Events run.
Yes, but we didn't keep it all in SQL. Instead we went to simpler CRUD style queries, and performed the proprietary logic itself in application code.
Anything done to keep people from attaching a debugger to your running code and gleaning from that your proprietary logic? As they say, where there is a will there is a way.
Another aspect that needs to be considered... How many sales have you lost as a result of that derision?
I'm pretty sure i'm not the only DBA who will automatically cast a "NO" vote for any 3rd party application that either encrypts stored procs, uses ORMs and/or hides database level logic in the application tier.
It has nothing to do with wanting to steal code and everything to do with efficiency trouble shooting performance issues when they arise. I don't want to be responsible for admining a database that was intentionally designed to prevent me from admining it.
It's a good point, but I don't think we have lost any. It's my understanding that our main competitor does encrypt their stored procedures. We've dumbed them down instead. This actually makes database tuning easier, since there is less to worry about. Also, for performance we can scale out at the application tier, but this is harder to do at the database layer. Much of this logic was very complex and probably not best described as data logic in the first place.
March 16, 2018 at 8:05 pm
bkubicek - Friday, March 16, 2018 1:16 PMRenoChris - Friday, March 16, 2018 1:12 PMGenerally I like stored procedures too for many of the reasons already mentioned. One real world problem we had however, that caused us to move all or business logic back up into code, was the time a client stole our code and told us they didn't need our product anymore. This would have been more difficult for them to do if so much functionality hadn't been exposed in the stored procedures.A while back I worked for a company where they had with encryption on every single stored proc. Of course, it is possible to break that encryption, but certainly made it a lot harder for customers to steal your code.
Ben
This sounds more like a client problem than a stored procedure problem. Don't you have legal agreements for such issues? Shouldn't your attorneys handle this for you?
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply