John Kelley
 |
| 09/18/2001 12:20 PM |
 Quote
 Reply
 Alert
|
| I have an application that uses SQL Server 2000 as its database. In order for a user to use the application, the administrator for the application must logon to the application and add each user and assign whatever rights the user needs. Here is my problem: when you add a user, the APPLICATION will create a login on the SQL SERVER, give the user Server role Security Administrator and Database Roles db_owner & MedicsUser for the database (regardless of what rights the user is given in the application). The vendor claims that the Security Administrator and db_owner roles are necessary for functionality. I asked the vendor if I could remove the Security Administrator and db_owner roles and leave only the MedicsUser role (they said to leave as is). What kind of problems does this leave the door open to? |
|
|
|
|
Chip Andrews
|
| 09/18/2001 12:54 PM |
Quote
Reply
Alert
|
| John,
There are many problems with the level of privilege they are using. For one, if a user can simply connect to the database using Query Analyzer or any other SQL Server client then they can view/alter/delete any objects in the database in which they have db_owner privs. In addition, since they are a Security Administrator they can add/delete/change other users - with certain exceptions (they cannot add/delete System Administrator privileges). This keeps them from gaining access to the xp_cmdshell extended stored procedure but your database is already fully exposed so they already have the goods.
I would require the application developer to explain _WHY_ those level of privileges are needed. I would expect you will not like the answers - if you ever get any. Start looking for another vendor if they are not forthcoming with an explanation and a solution.
Chip
|
|
|
|
|
John Kelley
|
| 09/18/2001 2:18 PM |
Quote
Reply
Alert
|
| Thanks Chip! The users do not have access to any of the SQL Server tools (Enterprise Manager, Query Analyzer, etc) but they would have access to Microsoft Access 2000 - would the same problems exist that you mentioned? I have sent an email to the vendor this morning to explain why there application requires such access. |
|
|
|
|
Chip Andrews
|
| 09/18/2001 7:44 PM |
Quote
Reply
Alert
|
| Yes - the same problems exist with Access. Also, if the users can access SQL Server through ADO and just about any scripting language so please don't feel that keeping the tools away is the answer.
|
|
|
|
|