Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ann06
Posting Yak Master
171 Posts |
 Posted - 2010-04-25 : 03:21:02
|
| I have inherited a web application that is developed using asp.net, it uses windows authentication in IIS and also in sqlserver.the problem is the application works by providing the domain users explicit read and write attribute in the sql server security to gain access for reading or writing data.i.e this the user to possibly make sql server registration and would gain access to the sql server so he can browse my tables and also may mess up with the dataisn't this a bad application design? what is the better way of achieving the same requirements without compromising my system for a possible sabotage?thanks for sharing your ideas |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-25 : 12:58:39
|
quote:
Originally posted by tkizer
The best solution is for the users to only be granted EXEC on stored procedures. Then they can't browse anything, they can only execute the stored procedure.
Or, use impersonation (under ASP.NET, not SQL Server) to impersonate a user that has access to the database. The web-user connects to the website, the website impersonates the database users in behalf of the web-user, and connects to the database. The more users that have direct access to the database, the more security holes you leave open.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-25 : 14:58:58
|
| Well I was only referring to the account that has access to the database. What we do here is application security where users are just a row in a table. The users have no access to the database directly, only through the application. The one account that does have access to the database only has EXEC on stored procedures where no dynamic SQL is used. Dynamic SQL opens up direct table access, so that's a big no no.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-04-25 : 16:09:22
|
quote:
Originally posted by tkizer
Well I was only referring to the account that has access to the database. What we do here is application security where users are just a row in a table. The users have no access to the database directly, only through the application. The one account that does have access to the database only has EXEC on stored procedures where no dynamic SQL is used. Dynamic SQL opens up direct table access, so that's a big no no.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
You can get around that requirement by using EXECUTE AS. You can also give this application login access to select from the tables - as long as the login does not have direct access to insert/update/delete. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2010-04-26 : 02:19:43
|
| thanks all of you much appreciated |
|
|
|
|
|
|
|
|
Please Advice for Authenticating Users