February 21, 2010 at 9:44 pm
Hi,
I need to find a way to stop users from modify tables (db objects) from the backend.
What i mean is that, i have an app that is interacting with my database (sql server 2008).
What i want is that, no one should be able to run queries such as update,delete,select from the backend.
Can, some one out there help me achieve this?
thank you very much
Robert.
February 21, 2010 at 10:16 pm
Are you using a single generic account to authenticate users from the app to SQL?
You will have to define persmissions on the accounts.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 21, 2010 at 10:30 pm
If your application in OLTP then it will perform DML opertions which needs to work , hope you want this work
If you do want user's not to use SSMS against the database and run DML operations then it requires better planning as listed below.
Scenario
The application is web hosted then user's hitting the IIS\web server will be using default windows authentication to reach the server, from the application server to database server the portal will use its admin account only.
- In the above case the user will not be able to any tools from the machine to access the back-end unless they no 'sa' user password or have admin account privleages at database end.
- Check the security at database level and instance level , use one or two admin accounts for SQL server services this would tighten the security.
- In-case you are hosting other databases which developer's need access to then bind there rights to them only.
- The production database in instance should have access with two admin accounts only and there password should not be shared with user's
I hope your question is addressed , also please look at BOL for exact commands for implementing the above strategies.
Cheer Satish 🙂
February 21, 2010 at 10:30 pm
I am not using a single account to authenticate users.
All i want is, no one should modify or run queries from the backend.
All modifications should be done from the frontend(app).
thanks for you response, though.
February 21, 2010 at 11:20 pm
thanks alot
February 22, 2010 at 2:44 pm
jaroho (2/21/2010)
I am not using a single account to authenticate users.All i want is, no one should modify or run queries from the backend.
All modifications should be done from the frontend(app).
thanks for you response, though.
So, how are the users connecting to the database from the application? Are you using windows authentication - and setting up every user in the database?
If so, then you need to make sure the user accounts in SQL Server only have the privileges necessary to execute the stored procedures your application calls. If the users have direct access to tables and views - then they can pretty much use any tool to connect up to the database (e.g Access, Excel, SSMS, other query tools, etc...).
You can look at implementing a login trigger - but you need to be very careful about this.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 22, 2010 at 4:41 pm
Jeffrey Williams-493691 (2/22/2010)
...If so, then you need to make sure the user accounts in SQL Server only have the privileges necessary to execute the stored procedures your application calls. ...
I think that's the major point here.
But I'm also afraid the app isn't designed that way (meaning: communication via stored procedures only).
As to my knowledge a login trigger will only help if it's possible to differentiate between a login via app vs. login via e.g. SSMS. If the app cannot clearly be identified I think it's impossible to use a trigger for the requested purpose.
Best way to protect the db is communication via stored proc.
February 22, 2010 at 6:02 pm
jaroho (2/21/2010)
I am not using a single account to authenticate users.
All i want is, no one should modify or run queries from the backend.
All modifications should be done from the frontend(app).
I shudder to think about the security door opened with this approach, it is in effect no, none, nadda security -- hope your managers understand this ... if not good luck because your database will need it
February 22, 2010 at 10:24 pm
Thank you everyone for your help.
I want to point out something though. My app is an ASP.Net app connecting to the db via web.config (windows authentication).
I have created a table called 'Users' in db from which all App users credentials are stored.
When a user wants to log into the app, i establish a connection to db and check if he/she exists and then allow him to login.
All, I want is, Is there away i can block/stop users from running queries such as Delete,Select,Update on any of my db objects(tables,views etc) from the backend or using any other external tools OTHER THAN my app?
thanks
February 23, 2010 at 12:35 am
Like I stated before:
change the communication between your app and the db to stored procedures only and lock down everything else. Once you're done with it, add your users to designated roels and lock down the database to the minimum level required.
What would happen if you'd enter the following login to your app:
username'; update users set name = name + str(id); --
Also you should make yourself familiar with the risk of SQL injection (google those keywords and you'll find plenty of information...).
Edit: sample code modified to not destroy anything but to show the effect...
February 23, 2010 at 12:43 am
Thanks Lutz.
I take note of that.
thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply