January 7, 2009 at 2:50 pm
I have a group that is the support and administration for a major business application that is SQL based. This group is not trained in SQL, but do to the lack of quality admin tools within the application they occasionally run SELECT statements using Query Analyzer. Recently they have been trying UPDATE statements without IT's permission. How can I deny this group of users from running UPDATE or DELETE statements when they are working through Query Analyzer? They should only be doing updates or deletes through the application guid.
January 7, 2009 at 3:28 pm
If all they need to do is select data from tables, the only permission they should have in the database db_reader. What permissions do they currently have to the server and database(s)?
January 7, 2009 at 3:39 pm
This is the perfect case of only giving users select permissions (as the other reply said, db_reader) on tables and encapsulating all DML within stored procedures and having the application only call the stored procedures. By doing that they at least do not have the ability to run an update or delete statement through QA.
January 7, 2009 at 9:57 pm
Erin (1/7/2009)
I have a group that is the support and administration for a major business application that is SQL based. This group is not trained in SQL, but do to the lack of quality admin tools within the application they occasionally run SELECT statements using Query Analyzer. Recently they have been trying UPDATE statements without IT's permission. How can I deny this group of users from running UPDATE or DELETE statements when they are working through Query Analyzer? They should only be doing updates or deletes through the application guid.
crerate user with DB_READER Database role and give them..
January 8, 2009 at 6:51 am
create restricted user with limited right's like all guys tell u and give that user name and password to them and change other user's password admin right's user with you
Raj Acharya
January 8, 2009 at 7:32 am
They do not have any server roles. Database roles include public and a role setup by the application.
Adding db_denydatawriter will not stop them from making updates through the application guid...correct?
January 8, 2009 at 7:49 am
That depends on how the application is set up in terms of updating the tables. If it is based on Windows Authentication and the updates are being done as inline SQL (and not through stored procedures) then it will definitely stop them from being able to update through the app. If the application is set up to run the updates through stored procedures that the user has the right to run then this will not be a problem.
January 8, 2009 at 8:23 am
Unfortunately updates are being done as inline SQL. Perhaps a trigger that accounts for where the SQL command is coming from would work.
January 8, 2009 at 8:36 am
Erin (1/8/2009)
Unfortunately updates are being done as inline SQL. Perhaps a trigger that accounts for where the SQL command is coming from would work.
You certainly could create triggers for that sort of thing. It could end up being a tedious process depending on how many tables they update, how often new ones are added, etc...plus it would add some overhead (not a lot) but it is very much possible to view the process that is updating and rollback if the ProgramName <> 'WhateveryourAppNameIs'.
January 8, 2009 at 8:51 am
Are they domain admins or admins locally on the server? Are they connecting using Windows or SQL Authentication? Is Builtin\Administrators still allowed access to the SQL Server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 8, 2009 at 9:05 am
They are neither domain admins or local admins. Builtin\Administrators is allowed access to SQL Server. They are using Windows Authentication to connect.
January 8, 2009 at 10:29 am
What authentication/access is the app using? Is it different from what the users are using?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 10:39 am
Erin (1/8/2009)
They do not have any server roles. Database roles include public and a role setup by the application.Adding db_denydatawriter will not stop them from making updates through the application guid...correct?
Aha!
I see now what you are saying....
they are using the same account to access the application as they are to use QA... in other words there is no middle tier or anything like that.
This would be the route I would go if the situation were right:
http://msdn.microsoft.com/en-us/library/bb326598(SQL.90).aspx
Good luck, be careful!! test test test
~BOT
Craig Outcalt
January 8, 2009 at 11:47 am
Exactly....thanks for the link.
January 8, 2009 at 11:57 am
Erin: If you take this route, based on the App Name attached to the connection, you should be aware that this is just a barrier, it's not truly secure (because the client code can change the APP_NAME to whatever they want, if they know how).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply