Debunking nonsense about Stored Procedures.
-- Steven Black
- This topic began in November 2001 as a result of statements made by John Petersen which were (are) demonstrably false.
- Restored on June 2 2002 to the version saved November 28 2001 by John Ryan, with the good contributions at the end, after the topic got trashed by some well-meaning folks, but mostly by an anonymous person between May 28 and June 2nd 2002.
(Please let me know if there is something you've added between May 28 and June 2nd that you feel I should fetch from the archive)
When presented with an apparently definitive argument, check those citations. Nonsense begets more nonsense until it's called.
The Root Statement: "The use of SP's is considered to be a best practice is far as C/S development is concerned"
Reply: the statement "FWIW, the use of SP's is considered to be a best practice is far as C/S development..." is unsubstantiated and, in my opinion, not supported by observable fact.
When asked for supporting evidence of this, the reply is a series of citations that at best can be described as Bogus Science
Bogus Science: I would direct you to pages 36-37 of Inside SQL Server by Kalen Delany. Also, one of the tips for optimizing ADO involves the use of Stored Procedures.
Reply: Let's see, a book on SQL Server by Que recommends using SPs? That's a weak citation. It certainly doesn't say what you claim. How does this citation support your claim?
Bogus Science: A good link on Dr. Dobbs: http://www.ddj.com/articles/2000/0065/0065e/0065e.htm?topic=database
Reply: This link lists advantages of stored procedures (Stored procedures are only parsed once, Users can be granted different permissions on stored procedures than the permissions on the underlying tables, Stored procedures can encapsulate business rules, Stored procedures can reduce network use, Stored procedures can return record sets that are sorted views, and Stored procedures can return record sets that do not represent any physical table). The article does not support your assertion that "FWIW, the use of SP's is considered to be a best practice is far as C/S development...". Sorry.
Furthermore this article is talking about ASP pages, comparing access to SQL data using SQL via ASP and comparing that to SP. Extrapolating an easily crackable scripting environment to "C/S development" as known by most of us, is remarkable.
Since VFP does not natively use ADO, comparing C/S access via ADO is irrelevent. Not a useful citation.
Bogus Science: A link from the IBM website: here
Reply: This article names 3 advantges of SPs (Reduced network traffic, Improved performance of server intensive work, and access to features that exist only on the database server). It does not support your assertion that SP's are considered best practice in the blind, general quasi-heuristic way you imply it to be: "FWIW, the use of SP's is considered to be a best practice is far as C/S development...". Sorry.
Furthermore, the mentioned advantages are proposed as "possible" advantages by the quoted article, not "definite" advantages ... and for DB2, not for all C/S development.
As for the proposed advantages, review them...
Commands to list directories on the server (such as LIST DATABASE DIRECTORY and LIST NODE DIRECTORY) can only run on the server.
The stored procedure may have the advantage of increased memory and disk space if the server computer is so equipped.
Additional software installed only on the database server could be accessed by the stored procedure.
Hardly the most compelling reasons I've ever seen, even for those who are willing to write SP for DB2. It would be inappropriate to quote this as an argument for SP across the board.
Bogus Science: A good article from the old MIND magazine from MS: http://www.microsoft.com/mind/defaulttop.asp?page=/mind/0399/sql/sql.htm&nav=/mind/0399/inthisissuecolumns0399.htm
Reply: I just read the whole article. It does not say that. What am I missing? Also, I can't resist pointing out that you cite an article by Microsoft that relates to data. Microsoft has published more garbage related to data over the years...
Furthermore, the quoted article dates from 1999, discusses SQL Server 6.5, and is again an "Internet" ASP article. How does this assist people wanting to develop C/S using VFP in 2001?
Bogus Science: There are a lot of resources out there to lead one to the conclusion that the use of Stored Procedures is considered a best practice. Certainly, it is highly recommended. Unsubstantiated? I think not...
Reply: You forget to cite the SQL Server marketing materials. These actually say what you are desperately trying to substantiate. More to the point, why did you send me on a wild goose chase to articles that don't support your claim?
It seems that the argument is an appeal to authority, not an appeal to reason- justifications are not required because "the illuminati" know what is best. And you are either an enlightened disciple or a "po dunker". Ah, if only life were so simple.
31/05/2002: Trevor Smythe
What I will try to do here is bring all of the above back into perspective with a personal real-world example.
I am an analyst programmer for a multi-national finance group. I don't claim to be a specialist in either SP, SQL or OO, but perhaps that gives me some perspective. For a development that I have been involved with recently, the emphasis has been on speed and accuracy of the end product, and basically we had a full year and a large budget to create and implement this. It was good =)
Any way I have just completed a project with a (mainly) 3-tier architecture: a mainly-OO based front-end, communicating via xml (for flexibility, as the client's display and interface is not hard-coded, instead using a set of primitives, with the interface cached on the client until any updates are sent through) to our application server: The client-side interface is entirely OO, with several hot-pluggable modules (with scheduled activation times) that contain the business logic. Data retrieval is via SPs to the back-end as we ran various tests and SP's allowed the database to create a more-efficient execution plan. For this project, our back-end was a MS SQL Server 2000 database with XSP (Extended Stored Procedures) written in OO style C++ for all interfaces to the Mainframe systems.
Why did we choose this architecture? A couple of reasons:
We had to interface to several legacy systems, hence the XSPs.
We wanted to have a consistant interface for the application server to the back-end, so any interfaces that could not be handled by SQL server directly, an XSP was written.
Performance. The SP's written by our DBAs were far faster then even optomized Dynamic SQL queries. The reason for this is the excellent execution plan compilier included with MS SQL Server, which, when it viewed the entire SP, was able to optimize in ways that minimized traffic between the servers, and minimized query-execution time. "BUT WAIT!" you cry, "That would mean business logic on the backend server!" to which I reply: Yes, so what? When you are talking about servers which need to handle billions of gigs of data flowing through them, you need to separate the most of the logic: This means separating the application server and backend server, in this instance to separate physical machines, and occasionally to reduce latency and overhead; this means including some bits of the business logic in the backend.
Perhaps there MAY be other ways to do this that achieve equivalent performance and flexibility, and perhaps even better performance. However, according to our benchmarking, this turned out to be the best combination of technologies and techniques for speed and accuracy that we had available to us at the time.
I think the bottom line here is that you should not take everything you read in the trade rags at face value. Mileage may vary, etc. I for one would not start designing or implementing SP's UNLESS the requirements called for it as Trev's project obviously did. We just finished re-engineering an app to use SQL Server. This was the first time I implemented a SQL Server app in a production environment. (I have done other C/S apps, however, using Oracle and other tools where we leveraged server side views heavily, but I digress...) The requirements in this case, although calling for speed and flexibility, did not warrant moving all data access to SP's. However, we had 2 specific areas that were well served by leveraging SP's. The first is that we decided to maintain our legacy method of assigning primary keys on the front end RV's. The framework we use supports a sp_NewID SP out of the box within the data class - very handy and working very well. This saved us a ton of time in migrating the data model, etc. The second SP we use does 4 different counts on the CHECKS table to show # of checks and type that still need to be printed as administrators exit the program (this table is approaching 400 thousand rows at present). Because the criteria is primarily based on bit fields, these queries turned out to execute much faster in an SP. Again, we are not noticing any bottlenecks or experiencing any limitations that would cause us to even vaguely consider an all SP solution. But, that does not mean we WON'T ever use them if the requirements are better suited to them.
I'm still curious about what John Petersen was espousing, but I guess it's not important - some good content came out of it whatever it was. -- Randy Jean
Ah. John Petersen said all the things prefixed by Bogus Science. I'm saying whatever is in Red, and I believe Blue is John Ryan.-- Steven Black
If you ask me, this whole topic is bogus, as is its counterpart. Why? Because on one side we have the "SP's are the best, any other way is bad" crowd and on the other side we have "Never use SP's, they stink" crowd, when in fact the REAL answer is that they both have their place and what is best practice in one case may not be best practive in another. There's a reason Don Knuth's books are titled "The ART of Programming", it's not an exact science. I'm sure there are any number of people in either camp that can provide benchmark examples showing their was is better. I'm sure those same people can show examples where their preferred method shows easier maintainability as well.
Take some of the bullet points above. The one about Inside SQL Server recommending SP's. Does this mean SPs are the end all be all? No. But it also does not make the point for the other side, which dismisses it off-hand because it's published by Que (although one of the erased posts mentioned it's actually MS Press, I believe) and it's called "Inside SQL Server" the opinion presented therein can not possibly be valid. What kind of argument is that?
The bottom line is that either side could fill a book with VALID reasons why they are correct, and this issue will NEVER be resolved. Perhaps both of these topics which have generated so much inflammatory content could be replaced by a single item which has a list of pros and cons for RV's, SPT, and SP's based on FACT and/or actual practice and not some academic malarky which usually fails to hold water in the real world, or unsubstantiated opinion or rumor. -- Randy Rinker
You misunderstand. This is not a two-sided discussion. It's not about whether SP's are great or not. This topic simply says and, correct me if I'm wrong, exactly what you're saying: that whomever says that SP's are always to be recommended is full of nonsense. Also, in case it's not clear enough, I personally chased down all those references, and it was a wild goose chase that took nearly an hour. Those citations are nonsense. Look, the central point is this: Just because someone gives a citation supporting an argument, it doesn't mean the citation says what this person says it says. And you shouldn't be surprised that the person who started the goose chase with really shallow arguments and bogus citations is being, once again, called to the red carpet for it. -- Steven Black
The "bogus science" you claim above does not seem that bogus. When you get down to it, it is hard to find the exact cites for anything with the exact words that dictate it is a "best practice". Sometimes, it boils down to common sense. As far as SP's are concerned, I think we can agree that they are part of the data layer. They can control inserts, updates, deletes, queries, etc. Which makes more sense: binding SQL statements in the application client or binding the logic in the database? If the DBA will not allow you to send in client-side SQL because she has select, insert, update, and delete privledges turned off for the application login, then you don't have a choice. However, this is not a technical reason why SP's would be better. Rather, it is more of a policy and procedure reason. A good technical argument would be founded on something like "SP's are a good way to go because they keep data-related logic with the database and thus, is independent of a specific client."
Are stored procedures always better? One cannot say this for the same reason that a line of code can never be proven to be bug-free; all of the possible combinations can never be tested. Are SP's likely to be better? I think this is a safer statement on grounds of performance, security, and control. The ADO cite above is definitely on the mark; SP's make a big difference. Rendering client-side SQL that involves complex parameters and SQL gets messy. A SP provides a cleaner interface. You may not agree for one reason or another. To me, it appears you have a personal beef going on here as opposed to a difference of technical opinion. THe cites offered above do support the idea that SP's are a good idea. Whether it is a best practice or not, that is a matter of opinion that people should be free to express. To simply disagree and label it as bogus science simply because you don't agree or because you don't like somebody is not being intellectually honest.
I'm getting increasingly irritated by this anonymous smack from the Philly area. When someone provides a bogus citation supporting an argument, not only is it bogus science, it's unethical. Where ethics are concerned, I'm sorry, there is zero room for slack. Whoever you are, get a grip.-- Steven Black
Please justify the statement that "Rendering client-side SQL that involves complex parameters and SQL gets messy. A SP provides a cleaner interface." This does not seem to me to by the reason to use SP- probably the opposite, in fact. Is this one of the "slogans" John Ryan talks about?
I remind you that this is a software engineering issue and I would appreciate it be treated as such. One cannot look at the strong points of something, and conclude that because it has some good points, that it therefore is a best practice. Moreover one cannot say "The use of SP's is considered to be a best practice is far as C/S development is concerned" and then provide citations that don't say or even support this. Nobody is saying SP's don't have their place. What's objectionable on an engineering basis is the assertiuon that "The use of SP's is considered to be a best practice is far as C/S development is concerned". That's horseshit, and providing citations that don't clearly support the statement is bogus. -- Steven Black
Puh-lease. You do take yourself seriously don't you? For what its worth, this has to do with database design. It is not a software "engineering" issue. The fact is, most of the "truths" that people hold onto in software development are nothing more than acecdotal evidence. They are rules that can be broken down as neatly as F=MA. Who the heck you to remind anybody of anything anyway? It is for people to decide for themselves what the issue is. Just because you say something is nonsense and bogus does not make it so. Put that in your engineering pipe and smoke that.
You know, that's a really interesting point of view. For years I've believed that database design was one of the core elements of software engineering. I'm happy you've taken the time to clear that up. It might be a really great thing for your career to write that up in an article, assuming you don't do it anonymous ly, and submit it to, say, IEEE or maybe even Dr Dobbs. If you used FoxPro examples to show that database design is a trivial thing, and not an engineering issue, that would be a great thing for us all.-- Steven Black
What if when you inserted data into a table, you needed to perform summary calculations and store those results in either the same table or somewhere else? In a SP, you can block the code in one place in a single transaction, providing a single interface. On the client, you would have to perform multiple operations.
What if the SQL statement was large, perhaps involving Unions or lots of joins? What if instead, it was more effcient to use a temp table to filter out results? How could you do this on the client? You could if you made multple requests to the server.
Complex parameters involve delimited strings that get sent to a stored procedure for the purpose of inserting records into a temp table for the purpose of filtering data in a result set via an inner join. How could you do this on the client? Several ways. One, you could make multple requests to the server, but this defeats some of the benefits of having a server. Alternatively, you could bring multiple results to the client and filter on the client. This too defeats the advantages that servers bring to the table.
If you have lots of parameters, coupling that with a potentially large and/or complex SQL statement can get difficult to manage on the client.
As far as program/data independence is concerned, if you change the structure of a database element, your client MUST be re-compiled. If however, you use stored procedures, you can keep the interface constant and roll out the changes to and recompile the client when you are ready.
These are all valid reasons to use SP's and definitely qualify as strengths. Bigger issues involve security. Smaller issues involve performance. i.e., for a big security pay-off, a ms or two of a performance hit is a good price to pay. However, if you rely on client-side alternatives where you may have to make multiple passes and/or bring larger result sets to the client for complex filtering, SP's, more likely than not, will yield better performance.
Whenever you render client-side SQL, there is a compilation process that must take place. Having a cached execution plan is a big benefit to SP's. Also, if you need to boost performance with index hints, while you can do this in client-side code, using a SP allows you to do this more seamlessly. As your data grows, you may find that a new index is required or you may have a data-driven search mechanism that requires a new index. If you render client-side SQL, recompilation of the client is required.
SP's have enough of an up-side to warrant their use being a default rule as opposed to the exception. Of course, there may be issues that preclude the use of SP's. From a technical standpoint, there are many more upsides than downsides. Most downsides are perceived becuase for many, it is a new experence and a new way, a somewhat disconnected way of dealing with data. Therefore, many of the downsides are really more of a matter of understanding how to apply SP's. When people complain of performance, it is usually because more work is placed on the server than before but the resources of the server have not been adaquately increased. If you take what was formally distributed between multiple clients and centralize operations to a server and you don't increase the resources of the server, it should not come as a surprise that performance could tank. This is not a weakness of SP's howerver. Rather, it is an issue of an under-capacity server.
Hopefully, this adds some clarity.
(Replaced after overwritten by wiki contention) Now we're talking "what ifs"? OK, what if less than 10% of development fell into any of the above "what ifs"? Would you still advocate SP's as the defacto way of doing things? I can tell you NONE of my current development falls into the "what ifs" above. Again, back to my statement about R-E-Q-U-I-R-E-M-E-N-T-S dictating what is best practice. -- Randy Jean
Everything is all about what if this or that, right? Nowhere in the preceeding passage will you find an absolute. Rather, there are a bunch of factors to consider whether SP's are a good choice or not. I happen to be of the opinion that SP's are the way to go and I have to have clear and convincing reasons to go the other way. Others have the opposite opinion - which is cool. What is not cool is all the name calling and simply shurgging off what are decent and reasonable grounds to support a point of view. It would appear that a few here have egos the size of the rock of gibralter and cannot cope when people disagree with them. It is kind of like the kid who has the toy and when he gets upset, he likes to take his toy home. In this case, the equivalent is to simply ignore and delete the material one does not like. Those are the folks that need a serious attitude adjustment and in need of growing up.
For what it is worth, what is labeled as myths here are not. The only thing that is clear is that there are some major personal issues between some people.
No, this amplifies but does not justify the assertion you made re multiple/complex parameters and a cleaner interface. The minority examples you cite are unconvincing as a "proof" that SP should be preferred by default. Statements like "As your data grows, you may find that a new index is required or you may have a data-driven search mechanism that requires a new index.
If you render client-side SQL, recompilation of the client is required" are FUD nonsense.
This was in the context of using index hints. You neglected to quote the full context.
Others like "When people complain of performance, it is usually because more work is placed on the server than before but the resources of the server have not been adaquately increased" are so circular- amounting to "SP is better because you have to beef up your server to match RV performance"- that I am wondering whether you are a Troll.
Why is it circular? As you place more stress on a server, you need to beef up the resources. What is illogical about that?
What is illogical is to turn this truism into an assertion that "SP is faster because it places more stress on the server and you need to beef up the resources".
Assertions like "Whenever you render client-side SQL, there is a compilation process that must take place." are no longer correct for SQL server; the online docs make it clear that stereotyped SQL queries are compiled the first time and can be just as quick as SP after that.
Can be and always as fast are different. If a client-side rendered query has the same characteristics that are to be used again, it is true that SQL will create a cache for that. But, you cannot rely on the mechanism being there as the case with SP's.
You do not mention at all that using SP binds you to a particular backend, not always the backend you might choose. That may be dandy for you but might cause loss of business for others. You cannot say.
Making use of the particular features of a back-end is usually a good idea. This is precisely why you don't want to bind database specific logic in the client. For instance, relying on the results of @@identity, @@rowcount, etc. binds you to SQL Server. These are powerful features that are particular to SQL Server and to avoid the use of these items is foolish. If you bind the use of these items on the client, your client has to change based on the particular backend you are using. If you bind the use to the back-end itself through SP's, the only thing your client knows is that it is connected to a backend and makes a SP procedure call. The client will not care what particular back-end it is. If the market for the business is such that writing versions of the database in different back-ends, the cost will yield a return and make the decision prudent.
I use SP when I see a need whether that be my judgement or a requirement from without. Arbitrary selection of SP as "the way" is poor logic IMHO and does not reflect well on those espousing such simplistic argument.
There is nothing "arbitrary" about picking SP's as a default. Just because somebody disagrees with you, it does not necessarily make them a troll.
Did anybody say that? No. My reference to Troll came from the unsustainable argument that SP is better and faster because if it is not, you need a bigger server. The non sequitur is blindingly obvious. Surely what you mean is that if you choose SP you may need more server power than with other options? Some people might not see that as a positive. Some people might perceive that your SP is only "faster" because you get a bigger box in which it is the box to be thanked, not the SP.
That is not the argument. Rather, the argument goes against those who go from client-side SQL to SP's. If slower performance occurs, it is most likely due to the extra load on the server where before, the load was dispersed. One might say that SP's are the cause and are slower than client-side rendered SQL. This misses this issue of the benefit of having work spread accross multiple clients. In reality, the issue is that placing extra work on a machine does not come for free. If you want the same performance when the load was lighter, you will need to beef up the RAM and perhaps the disk storage capacity as well. In this day and age where RAM and disk space are cheap as dirt, it is a small price to pay for the benefits that SP bring to the table. I bascially said this in the first place.
I challenge you to correlate the preceeding paragraph with your earlier assertion that SP are faster. It seems you are saying that SP are faster because you can upgrade your server for a few dollars and make it so. Of course we all expect upgrading the server to improve performance, but to attribute this improvement to SP is not helpful. To me it seems a shroud over the *fact* that all else being equal, SP may be *slower* because of extra server load. Converting that to a benefit "because you can upgrade the server" strikes me as a Troll argument.
Take a scenario with 100 users rendering SQL to a server and take those same 100 users and instead, have them run a SP instead. I will grant you that it is quite possible that the performance could be the same. It is such an insigificant point, I will cede it to you because the flexibility and security gains you get with SP's tip the scale in favor of SP's. You may counter that SP's tie you into a specific back end. I counter that if you ever take advantage of even 1 proprietory aspect of a back-end, whether on the client or server, it ties you to a back-end. The only difference is that if you concentrate the backend specifics to the backend as opposed to your application, the flexibility advantage of SP becomes immediately apparent. This is anything but a troll argument.
Well I was not the one quoting performance as an advantage one way or the other.
Now you say that flexibility and security are the reasons why SP is better. I reject the security suggestion. In healthcare, 75% of data misuses involve IT staff. Only 5% is "hackers" and only 5% doctors and nurses sneaking. None of the data is misused because of use of SPT rather than SP. It is caused by sloppy people security. Your SP is no defence against the unaccountable person with the sa password.
You say SP is more flexible. Please explain. If you are saying that there are some areas where SP do better, we all agree. There are also some places where SP are not as suitable. This is not an argument for one over the other, it is a circumstantial judgement that needs to be made every time.
Nothing can defend against the sa account. < s >.
The fact that you use SPT is not what protects your data.
No, it is you saying SP is more secure. Nobody is saying SPT is more secure or that it protects data better.
Rather, it is the fact that your application controls what SQL gets passed to the server. It is poor protection.
FUD. You cannot justify that.
The issue is where that SQL should reside; on the client or the server. If you knew that you would never have other clients that require that same logic, then it is a tough question. However, the fact that the SQL resides on the client, it means the client is rendering the SQL.
And your point is...?
Unless you were granular in granting column privledges, one could select in query analyzer or any other client for that matter.
You need to update your understanding of backend security. Of course we do not want users accessing data via Excel. A competent shop will ensure that users cannot access data except via the app, whether it be RV, SP or SPT. That's why users do not know the database password.
So basically, if a user finds out your database password, your defense mechanism is defeated. I think you need to update YOUR understanding of how backend security works. Of course, you can establish logins for a particular application, but the "effacacy" of this mechanism is diminished if you allow clients to render SQL to the server with something other than sa. SP's provide an extra layer that allows you to dictate, in code, precisely who and which applications can execute the proc. You can assign permissions through the grant statement as well. If however, the logic is more complex, SP's do allow you to fall back to writing code if necessary.
It is really a matter of just how much you want to lock down your data and how committed you are to security and safeguarding your data.
If you used SP's, not only do you control which SQL is run, it can be utilized by any client without rewrites. SP's, being an interface to data provides an additional layer of security that is best kept with the data on the server. If a hacker go hold of the login you use for SQL, the hacker could go to Query Analyzer and start poking around.
One might just as well say if a hacker got hold of the sa password... Or quote a SP that "reverses" charges as an example where access to SP would allow a hacker to derive benefit far more easily than if he/she had to update multiple tables. We can all choose examples, all it proves is that we can find examples that support our point of view. Also known as anecdote. Not a proof.
If on the other hand, the only thing the login could do is run SP's, then whether they are on query analyzer or any other client for that matter, that is all they could do. No rouge selects, deletes, inserts, etc.
If you follow this example, then you will need SP that can provide all the features of a normal app... inserts, edits, deletes, issue credits/refunds, change interest rates, record payments... accessing these may be far quicker for a hacker than managing transacted multiple table edits to achieve the same end. So your argument only holds if "the hacker" does not know how to utilise these SPs. As 75% of data misuse is done by IT staff, the assumption is not a good one. I'm sure you could "hack" data in your own work if you can get passwords, whether you used SP or SPT.
Equally importantly, it is far easier for a "hacker" to get a user password than a database password, in which case the hacker can work on data just the same whether it be SP or SPT.
You should have SP's that can handle inserts, deletes, payments, credits or whatever. The data interface classes in your app should do nothing more than broker access to those procs through OLE-DB or ODBC.
In a SP, you can limit which applications have the ability to run them. Therefore, if you have a rogue user trying to access/invoke through something like query analyzer, the first line of SP code can check the app_name() value. This of course assumes you populate the variable correctly in the connection string.
From a tx standpoint, because all processessing occurs at the server and is handled/mangaged at the server, it is far more cleaner than trying to manage the tx context from the client.
As far as the 5% hacker number is concerned, that equates to 1 out of 20 people attaching to your database. That is a big number considering hackers do most of the damage,
No, 5% of *data misuse*, not 5% of users. The amount of data misuse by people apart from IT staff is very small. We need to include ourselves and our colleagues when we talk about security. Most IT security I see is managed by IT people imposing limits on others while they themselves retain unfettered access to data and are largely unsupervised. Who will guard the guards themselves? Preferring SP is false comfort, whatever access method is used we need to be implementing People Security that limits access to database passwords, reduces the risk of user passwords going astray, ensures that access is only available via the application.
How does one innocently "mis-use" data? SP's don't provide false comfort. Rather, they provide an additional layer that prevents the problems you speak of. Of course, if a user has access to the raw tables, there is not much you can do. This is precisely why making SP's the sole way applications access data is a preferrable alternative. If you are somebody who is preforming maint. operations, direct access to tables makes sense. However, it should be a valid assumption that whomever is doing this is not acting malicously, knows what he is doing, has made a backup, and is working in off-production hours.
"You assume too much!"
This whole argument condenses down to "if a hacker can access the data you have a potential security breach". How will you prevent that access? You *have* to keep both user and database passwords secure.
Chances are, a hacker is not using your application. Therefore, if you use SP's to qualify by application, then your data will be more secure.
Consider for a moment who might have access to the database password. Not users, obviously. Will you agree it is people in a department with "IT" on the door. If so, I contend that somebody who knows the database password also knows the sa password... or at least knows that the source code for your SP is backed up on a CD in that cupboard over there.
You need to focus on people security, anything else is wasted without people security.
It ultimately boils down to people. That is a blinding flash of the obvious. But assume for a moment that somebody DOES get the logon and password the app uses to connect to the database. If you use SP's that marshall access based on a specific application, hackers, people who want to misuse the data and anybody else will not be able to access/corrupt your data. This scenario locks your data down in a much more comprehensive fashion that relying on the application layer. i.e., the database is the last line of defense. It makes sense to lock your security down there. Otherwise, for every application that hits the database, you have to make sure your security model goes along with it.
If somebody has the sa password, in a word, your database is screwed. In that case, having complete backups is your only prayer. Of course, backups don't stop the malicious use of data.
( Topic last updated: 2002.06.07 01:34:53 PM )