Shamil Salakhetdinov
shamil at users.mns.ru
Sat Oct 15 15:01:53 CDT 2005
> <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> Jim, IMO this is a bad contr-argument of "Parameterized" group. I'd not buy on it if I'd have been from 'CRUD SPs" group. If CRUD SPs approach is used then of course there should not be any stored procedures, which build SQL expressions on the fly - they just do not make sense. The argument is wrong and it shows that the one who uses it doesn't understand well MS SQL security features: - in CRUD SP design approach access to all the tables and views etc. is usually REVOKED for MS SQL ordinary users and roles - they are only allowed to run certain SPs. And if there is an SP, where there is a dynamic SQL against tables/views, which a user/role isn't allowed to access then such SP fails of course... 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 5:51 PM Subject: Re: [AccessD] Data interface The best way > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com