November 18, 2003 at 8:21 am
Despite protest from the DBAs at my organization, management requires query analyzer be installed on all client machines. Does anyone know of a query analyzer tool that will not run UPDATE, DELETE, INSERT, DROP, ALTER, etc queries and also has a timeout feature?
Brian
November 18, 2003 at 9:10 am
Is security set up so that users have write access to the actual tables? (db_datawriter or db_owner) If so can this be changed so that all write access id via the stored procs? If your applcation doesn't use stored procs then this is a bad situation. Even if the users only have access to SELECTs what's to stop them from running some large cross JOIN statement and bringing production to a snail's pace (for which you'll be blamed) If these users absolutely need direct access to the tables whay not create a read only version of the production database and periodically copy data (once a day after hours?)
If these users only want to report why not get a report tool (Impromptu, Inteliview etc)
You may want to check whether you are licenses to install QA on every machine. Anyone know if Client tools requires a licence? If it does mention this to management as a discouragement.
Francis
November 19, 2003 at 5:43 am
If the application requires individual logins, as apposed to an application login, you could assign each login with only the database role db_datareader and utilize a Database Application Role with the application. The application role takes precedent over the individual login permissions. Therefore, the application role will allow them to use the application to perform INSERT, UPDATE, DELETE, and SELECT and the db_datareader database role will allow them to utilize Query Analyzer for SELECT only.
As a second note, if you don't want to maintain individual workstations with Query Analyzer, you could use MS Query. It is part of MS Office.
November 19, 2003 at 9:00 am
The only way, really, to restrict the use of the statements you mention is via the security model in SQL Server. Just don't assign the users the permission to take those actions on the tables and they won't be able to.
Regarding a tool... well, as dwilliams@ddpmi.com said, Office comes with MS Query (I use it to populate Excel files sometimes) and Access has that functionality as well. However, I agree with fhanlon that users should not generally be allowed direct access to the tables, even read access, unless there is a specific need and they are trained regarding how some queries can impact a server. You need to be especially worried about anyone that wants to be a SQL programmer, thinks they're a SQL programmer, but still needs a couple of years to ripen; IE: watch out for cursor death. 😉
I don't know how hard you've fought to keep QA off of people's desks, but if anyone external uses an application that requires access to your servers, you might play the stability and security card. This works particularly well if you're storing sensitive information that might be legally restricted in terms of who in an organization is allowed to view it.
Matthew Galbraith
November 19, 2003 at 9:10 am
Thanks to all for your replies.
I understand the ability to setup security within our systems. All of our users are to login with a read only user. However, I fear someone learning a more powerful login and causing damage to our data. All of our users are internal.
I am hoping to find a tool that regardless of security rights, will not allow some queries to be executed.
November 19, 2003 at 1:53 pm
It's a hack, and avoids your security issues (sounds like physical security is a problem, or forced frequent changing of passwords on non system accounts), but...
What about writing a silly little app in your favorite 4GL and throwing an error if any of the key words are found in the text before executing it on SQL server?
To be honest though, the rest of these people are right in that security is the way to really address this. Anyone can go out and install any ISQL client on a machine and presto.
November 19, 2003 at 4:36 pm
Set up the correct permissions and there will be no problem.
We are trying to disseminate information not hinder it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply