Jim Lawrence
accessd at shaw.ca
Sat Oct 15 08:51:32 CDT 2005
Thanks Shamil for all the information. I had been blissfully unawares of the controversy swirling around the subject. One point though and it seems to be the main argument used by the 'Parameterized' group in opposition to the 'Stored Procedure' security defense and that is that Stored procedures are just as prone to 'injection' attacks. That comment is supported by listing a SP or process that defeats the whole purpose of a SP. The following is a very tradition response: <quote> ... Actually, your argument is false. I can have a stored procedure which concatenates strings together and therefore open itself up to sql injection attacks... <unquote> All projects that I have previously worked on have been within offices and were only used by the staff members. Roles, Windows authentication and user groups have always been enough. For the first time I am working of a large web distributive project and realize that security will be more of an issue than ever. So every piece of 'real' information I can gather on the subject is important. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Saturday, October 15, 2005 12:27 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Data interface The best way Jim, Thanks for the link. I still think the following arguments of this article are getting obsolete: <<< - The best possible performance - Removes the SQL code from the other layers of the application - Prevents SQL injection attacks - Prevents casual table browsing and modifications >>> Or I'd better say they are getting less important because with Application Roles and modern technlogies like ADO.NET and N-tier solutions all that problems above(as well as related) have effective and secure solutions. Having secret passwords technique is quite different from Application Roles. Secret passwords have to have superuser(s) defined. Application Roles don't. I wouldn't want to start a "religious debate" here on "SP vs. Dynamic SQL" subject. Here is an interesting link on such debate - http://www.theserverside.net/news/thread.tss?thread_id=31953 - it's good enough to close the subject I guess? :) BTW, here is a good and free CRUD generator - http://www.microsoft.com/france/msdn/olymars/default.mspx for the developers who prefer CRUD SP to dynamic SQL or parameterized queries or other dynamic SQL techniques.... Shamil ----- Original Message ----- From: "Jim Lawrence" <accessd at shaw.ca> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Saturday, October 15, 2005 4:04 AM Subject: Re: [AccessD] Data interface The best way > Shamil, the other technique is to have secret passwords embedded in the > compiled FE (dll/executable) code and in theory that should eliminate > hostile attacks. > > Here is a good article on CRUD: > http://www.databasejournal.com/features/mssql/article.php/3082201 > > Jim > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil > Salakhetdinov > Sent: Friday, October 14, 2005 2:39 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Data interface The best way > > > So what special purpose is then served by the sproc? > IMO they are now getting obsolete for CRUD operations. > > You're right Charlotte, I think - in a modern N-tier architecture if one > gets Data Layer objects running on a well protected server then there is no > need in CRUD stored procedures. MS SQL database tables/views can be still > well protected and Data Layer objects will use Application Roles to do > whatever these Applications Roles are allowed to do with the database using > dynamic SQL.... > > And SQL injection attempts can be blocked on Business/Data Layer object > interfaces level... > > Shamil > > ----- Original Message ----- > From: "Charlotte Foust" <cfoust at infostatsystems.com> > To: "Access Developers discussion and problem solving" > <accessd at databaseadvisors.com> > Sent: Friday, October 14, 2005 11:55 PM > Subject: Re: [AccessD] Data interface The best way > > > > Swell, define dynamic SQL. When it is compiled into a dll, is it still > > dynamic? When your permissions to the back end are highly restricted > > and all the SQL is created in the dll, is it still dynamic? I > > understand the capabilites of sprocs. However, in an N-tier > > architecture, you can build some of that same capability into the middle > > tier and validate the data before it ever gets passed to the backend for > > handling. So what special purpose is then served by the sproc? > > > > Charlotte Foust > > > > > <<< tail trimmed >>> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com