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 |
|
timgaunt
Posting Yak Master
115 Posts |
 Posted - 2005-04-27 : 12:13:53
|
| Hi guys,I've been developing on a shared enviroment now for quite some time and have recently updated to a dedicated server, one of the perks is that we now have complete control over our SQL Server, users etc which has brought with it questions :)I've got a simple one here I'm sure, with the exemption of developers/managers in-house, access to the SQL Server will only be through the web, so I was thinking to maximise security, should I create some sort of generic login for the server which only has certain access rather than connecting using ie "sa"? (I've seen people set access for ASP_NET for instance)If thats the case:-What privalidges should I give the user to allow him to use the Stored Procedures (Select/Insert/Update/Delete)-Would using one generic login for all db's be a big no-no?Security isn't a great concern but I think I should make some efforts to ensure the databases are fairly secure and I understand taht it will depend on the application at the end of the day whether or not to use individual logins for each db, but I'm just intrerested in your opinions. FYI the idea arose because a colleague was talking about a hacker being able to access the hard-coded connection string and get complete DB Admin access (which would be a pain hehe)ThanksTim |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-27 : 13:13:02
|
| Never use sa. Create the sa password write it on a piece of paper lock it in a safe and forget it.Use windows Administrator to run SQL server.make account groupings as you need them.JimUsers <> Logic |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-27 : 13:22:15
|
| "What privalidges should I give the user to allow him to use the Stored Procedures Select/Insert/Update/Delete)[i]"You should only need to allocate EXECUTE permission on the SProcs"[i]Would using one generic login for all db's be a big no-no?"Not necessarily. I like to have separate logins for each person, then that can be tracked if necessary.Each person can be a member of a specific Role, which could be the same on each DB - and then that Role would contain the physical permissions to the resources in the DB.For example: Our application has a login unique to the main database that it connects to, which has Role Name which is shared by all databases [which the application could connect to]. That Role could have different permissions for objects duplicated in the various databases.Often in Dev you don;t need much security, but you do need some accountability. For example, if someone leaves and everyone is sharing a single login then everyone needs to change to a new login/password and every database has to be changed. Each user having their own login, and sharing a Role, means that you just have to delete the specific user's account if they leave.Kristen |
|
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2005-04-27 : 13:40:34
|
| hehe whats that Jim? I should use sa? haha, sorry, I shouldn't have used that, in the past we've used something stupid like 0000001 which was given to us by the host.So is windows administration suitable for Web access too then?Many thanks also to your informative response Kristen, I'm not sure seperate users as far as the web front is concern is needed as alot of the time it just annonymous viewers getting the page content and thats about it but based on your response I think I will do the following:1. Have a "generic" account which has access to the majority of the databases it needs to via the roles you outlined above -ie WEB_USER2. Where data is more sensitive have a seperate user whom only has access to the information for his database and not allow any of the roles access to that database...3. For the permissions, in all cases I will only set EXECUTE.So having set that up *if* someone were to get access to our username/password/IP details they would only be able to do limited damage right? hehe <-- I will remove the "DELETE_ALL" SProc from the SQL Server hehe.Thanks for your advice btwTim |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-27 : 13:54:45
|
quote:
So is windows administration suitable for Web access too then?
I said Use the Windows Administrator account to run SQL(server) not the 'sa'.Actually I have been told you can use Windows authentification for VS.net web applications though I have never tried it myself as I am just starting to play with VS.JimUsers <> Logic |
|
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2005-04-27 : 14:00:30
|
| Sorry yes, I'm with you now, thats what we already do locally, but the issue is with the web applications accessing the data :) yep you can use Windows Auth to access SQL Server, I did that a long time ago in ASP, you just need to update your connection string :)Thanks again |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-27 : 14:10:06
|
| "I'm not sure seperate users as far as the web front"Yup, no problem with that. I was meaning Developers and other Maintenance "Crew"We have unique users, per database, that we use for Web (IIS under windows for us):User = XXX_IIS or YYY_IIS where the associated database names are XXX and YYYboth these users would use the role "WEB_IIS", within their specified database.We might allow XXX_IIS to have access to the YYY db when appropriate, but we would have to explicitly do that (it would be somewhat exceptional for us to need to do that, maybe common place in your application from what you indicate."Limited damage"That's the idea. Having permission to run a delete-one-row SProc makes it quite hard work deleting all the data!!We have a Scheduled Task that removes SysAdmin access from all "regular" developers [each night]. They have sufficient permissions to re-add it for any database, but this step prevents them accidentally mucking up a database they they had the need to work on yesterday [and had allocated themselves sysadmin] - its all too easy to select the wrong database from a drop down list, or whatever.Kristen |
|
|
|
|
|
|
|
|
Database Security Advice