pros and cons of using stored procedures for VB app ?

Hi,
Just wondering what sort of problems and advantages people have found using
stored procedures. I have an app developed in VB6 & VB.NET and our
developers are starting to re-write some of the code in stored procedures
(im advocating encryption of them). When deploying an application however
stored procedure seem to add another level of complexity to installation. In
future we also plan to have an basic ASP app with some of the functionality
of the VB app.
What are the pros and cons of using stored procedures in the situation?
PRO
- can call same stored procedure for ASP pages which saves us writing again
CON
- more complex deployment (i.e sending a bug fix will involve shipping DLL
and SQL script for stored procedure).
Thanks for any input
Scott
pros and cons of using stored procedures for VB app ?
Performance, security, maintainability (db can be maintained
independently of app changes) are some of the advantages. Your
colleagues are right. ALL data access for updates, inserts, deletes and
selects should be done through procs unless you have exceptional
reasons not to. This is the design pattern followed by all good SQL
developers. See:
http://msdn.microsoft.com/library/de...un_1a_6x45.asp
http://www.sql-server-performance.co...procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/archi.../17/38095.aspx
A potential disadvantage if you are a software vendor is that the code
in the DB is exposed to the customer. The protection offered by the
WITH ENCRYPTION option is pretty worthless from a security perspective
- it can easily be circumvented. I don't see this as a major issue
myself because the best protection for your intellectual property ought
to be a licence agreement, not a software obstruction that hampers the
customer's DBA in doing his job.
David Portas
SQL Server MVP
|||many thanks for the reply David. That helps clear a few things up.
You suggested that the "ENCRYPTION" can easily be reversed. My main worry is
customer/users amending deployed stored procedures - hence we thought the
"with enctyption" method would be the best way to protect against this.
Would you use a licence agreement to stop this also ?
cheers
scott
|||Even without decrptying it, the WITH ENCRYPTION option won't stop the
DBA replacing procs. Some decryption solutions are here by the way:
http://www.planetsourcecode.com/vb/s...d=505&lngWId=5
http://www.securiteam.com/tools/6J00S003GU.html
"Users" (non administrators) should not have ddladmin role so should
never be able to modify procs, unless you are talking about a single
user setup where the end user has total control.
You could verify the existence of each proc or even recreate them at
install time for each release. You might also verify them as part of
your support diagnostics. In principle you could store and verify their
CHECKSUMs based on the text in the syscomments table. Be cautious about
how you interpret any checksum though. I can imagine legitimate acts of
scripting and re-creating proc could easily introduce extra line breaks
and whitespace that would change the checksum.
David Portas
SQL Server MVP
|||thanks again. this quote kind of says it in a nutshell...
"since encrypting something that can be decrypted without user interaction
(i.e., entering of a password) isn't anything else than the infamous
security by obscurity."
thanks for your help
Scott