Francisco H Tapia
my.lists at verizon.net
Fri Jun 11 11:56:18 CDT 2004
Jim Lawrence (AccessD) wrote On 6/10/2004 3:46 PM: >Hi All: > >As to all these issues I would like to dump some of my own comments. At the >risk of saying 'I told you so', I firmly believe that users should: > >1. Always (or should I say only), use windows authentication and judiciously >distribute security access, to your SQLxx, to only those who need it. >Appropriate passwords and time limits. > > Both Windows Authentication and Sql Server Authentication are viable platforms. If either pwd is weak, neither authentication will protect against dictionary attacks. I find that Auditing is a good tool to use for helping to protect such systems. >1. Never allow access to the data except through your program. That means NO >to ODBC drivers only ADO-OLE. > > Unrealistic. When working in an Enterprise network, data must be shared. Our system which went from an Access Database to a Sql Server database is undergoing this transition, where the servers are sharing data. This is increasingly painful to monitor because you must "trust" your other dba's but that's why the dba must take the responsibility to always keep eye on their servers. >2. In 99% of cases, control is handled through SPs. > > there's SPs, Triggers and Functions >3. And the biggy never, never use bound forms to access SQLxx data. I have >been whining about that for years, a position that M$ has also fully >supported. > > bound forms have their place. unfortunately, I don't really like the way ADP's handle the 3 connection pooling to the server, that and the fact that any report that can possibly run over 30 seconds times out :| >4. You, as the programmer, should design your program to carefully validate >all requests and data before they are posted. > > :|, no, your input devices (SPs, Triggers, and Functions) must help validate data too, otherwise when someone needs to hook into your database, data will not be in par w/ what is expected. >I may be already talking to the converted so you can ignore this; if >not..Get on the program. > >Seeing we at in the midst of an election campaign, a good stump, political >speech is the expected. >Jim > > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of >Francisco H Tapia >Sent: Thursday, June 10, 2004 1:55 PM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Difference between views and queries > > >Andy, > SQL Server is not Access on steriods, it's a diffrent engine, and thus >requires a new level of thinking towards your database engine. It's NOT >that you couldn't, you can do anything, heck if you wanted to you can >set up your SQL Server with a blank SA password or SA as the password. >For all that matters, and avoiding all SQL Server authentication, just >use NT authentication and enable guest users :). > >There have been quite a few white papers circulating on why you >"wouldn't" use dynamic sql w/ your sql server (check out >www.sqlservercentral.com, a fine resource) but the 2 very critical >factors include performance and also quite possible damage to your data. > >It is entirely possible for your sql statement to read in this manner, >lets's say that you are Selecting Data from a table and you have a >combobox to help choose data, so your SQL Statment looks like this: > >"Select CompanyName, CompanyAttribute1, pKey FROM tblCompany WHERE >CompanyAttribute1 = '" & me.cboMyBOX & "'", If I was a malicious user on >your system and I have direct access to tables, and if you're doing >statements like the above it is entirely plausible that you also have >"INSERT" statements thus you are giving more than just simple SELECT >access to these tables., thus with some malformed selection I can add >this in the select > >'; DELETE FROM tblCompany; SELECT ' > >your final statement would look like this > >Select CompanyName, CompanyAttribute1, pKey FROM tblCompany WHERE >CompanyAttribute1 = ''; DELETE FROM tblCompany; SELECT '' > >Now your entire COMPANY table has been wiped out, while it is completely >possible to restore your db up to the minute, you've still lost some >downtime given that you had ONE bad apple in the bunch. > >Besides the sql injection threats, you also suffer from a >NON-pre-compiled statement, thus your data could conceptually be >returned a lot faster if you let it, simply by creating a view or stored >procedure. By the way just because you are using a stored procedure >does not make you completly excempt of sql injections, if you are using >dynamic sql within that procedure you are still open to these kind of >attacks and your stored procedure is always re-comiled and thus suffers >from the same performance deficits. > > >Andy Lacey wrote On 6/10/2004 1:27 PM: > > > >>But, Francisco, if I was porting to SQL Server my Access app which builds >>SELECT statements dynamically all of the time for many and various >>situations are you saying I couldn't, or shouldn't or something? >> >>-- Andy Lacey >>http://www.minstersystems.co.uk >> >> >> >> >> >>>-----Original Message----- >>>From: dba-sqlserver-bounces at databaseadvisors.com >>>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf >>>Of Francisco H Tapia >>>Sent: 10 June 2004 20:58 >>>To: dba-sqlserver at databaseadvisors.com >>>Subject: Re: [dba-SQLServer] Difference between views and queries >>> >>> >>>jwcolby wrote On 6/10/2004 9:33 AM: >>> >>> >>> >>> >>> >>>>Can anyone explain the difference between a view and a query? Views >>>>use a query, plus the view keyword. I have a couple of books that I >>>>have read the chapter on Views, but I so far haven't managed >>>> >>>> >>>> >>>> >>>to "get" >>> >>> >>> >>> >>>>why you wouldn't just use the query itself instead of >>>> >>>> >>>> >>>> >>>turning it into a >>> >>> >>> >>> >>>>view. >>>> >>>> >>>> >>>> >>>> >>>> >>>A query is a request for an Access Database, however for Sql >>>Server you >>>would either use a View or Stored Procedure to return the data you >>>wanted... you are also able to use dynamic SQL to retrieve the >>>information you need. ANY request given to the SQL Server engine is >>>managed by the engine, unless you are running Remote servers (iirc). >>> >>>In Sql Server, it is TABOO, nay, GENERALLY bad practice to >>>use dynamic >>>sql because of the implication of SQL INJECTION attacks, this poses a >>>"real" security threat to your database. and your server. >>> >>>another reason to use a VIEW over dynamic sql is that it is >>>pre-optimized by the SQL Server Engine and thus runs faster and more >>>efficient. Additionally if you use Dynamic SQL then your individual >>>users who access the server will need EXPLICIT "SELECT" >>>permissions by >>>you, which is another 'bad' practice. In SQL Server you make data >>>available to your users via VIEWs and Stored Procedures or some other >>>secure way in order to protect your tables and it's data. >>> >>>ya get wot I mean? >>> >>>-- >>>-Francisco >>> >>> >>> >>> > > -- -Francisco