January 15, 2008 at 4:43 pm
Does Anyone know of a good tool that will provide an alternative to SQL 2000 Query analyzer?
I like being able to provide a tool for a user to query data or develop reports, but I need something that will also provide a level of security within the tool.
For example, I have a user who has write access to a database. For this user to perform his or her job functions within the application, they need write access. But when provided with a Query tool, there is nothing to stop them from maliciously or accidentally running a "delete from blah" statement and wiping out a table thus creating way more work for me.
Any Ideas would be awesome.
Thank you.
January 15, 2008 at 5:35 pm
My take on this is that it doesn't really doesn't have to do with Query Analyzer. The ability to query and change data is wholly dependent on the object permissions granted to the user rather than the tool being used.
In your example, if you granted SELECT and INSERT on the table to the user but didn't grant DELETE, he wouldn't be able to delete any data. You didn't specify how you granted write access, but if you made the user a member of the db_datawriter database role, he would get permission to insert, delete, and update.
Greg
January 15, 2008 at 7:53 pm
The security described initially would require some sort of smart text parsing which could be overcome and perhaps break otherwise good queries...
The best thing would be to create a new user in your database and have them use that user instead. The new user would have read only access to the DB.
Is the user account on SQL Server a Windows account or a SQL account?
January 15, 2008 at 8:09 pm
You can revoke the delete permissions from the required tables. Just grant them insert and update, this will solve your purpose. If they will use any other tool also there also, they can execute delete. In case you dont want to modify the security permissions you can have batch files which will run on command prompt which accepts queries with delete as exception.
January 18, 2008 at 10:42 am
thanks to everyone for their responses.
Unfortunately the application they use and the positions they are in with in the organization requires them to also have delete rights. This is fine with in the confines of the application. I could require them to use a alternative logon that restricts their right alter or delete data, but with query analyzer it is too easy to just use their trusted logon and have unrestricted access to the data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply