October 31, 2011 at 8:52 am
I googled quickly but perhaps I don't have the right keywords. As always will appreciate pointer toward articles discussing this subject which I'm very sure has been hashed several times over.
The puritanical side of me wants to insist that we should be creating SQL Login/User for each physical user, though I'm told that a common setup for several web application is to create a single login/user and manage the users within the user table (and therefore within the application, rather than the server). I can see why web application would work that way, but I'm not so convinced same would work equally well for a desktop client application.
At least, I hope to know more about the caveats and downside of using either methods so I can make more informed decisions.
Thanks!
October 31, 2011 at 8:59 am
If you create a physical user for each login, you are asking for a lot of administrative work to be done, unless you expect the application to create the user, in which case, you have potential security issues.
If you use a single login, it becomes harder to determine which user is actually executing which code if you have issues, or need to remove a problematic user.
You can join with a user's table to better control data access, but that could be done either way. When you create an actual user, you can also easily add a user to a user's table and use that for controlling access.
The one thing that can help with a user's table is that you can better control access by users from things like Excel/Access since they likely would not have the name/password to connect to SQL. However it has to be in a config file or embedded in the app, and that can be decomposed, which might leave you with more security holes and be hard to change.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 31, 2011 at 10:02 am
How many physical users are you expecting to have to manage?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 31, 2011 at 10:21 am
Thanks for some pointers.
The current project I'm working on, it seemed that the application managing its own logins/users was the lesser of two evils, since there's several other routines that depends on auditing the users and tracking who did what. As seen in my recently solved thread regarding IMPERSONATE, using certificate user to provide the necessary permissions seems to help a lot with limiting the scope of how application can create its own logins/users.
I think the joining users table is essentially a given, since there are additional privileges we need to manage that are not a part of SQL security model but rather a part of the application (e.g. restricting the access to application's administrative section and doesn't just contain raw data stored in a SQL server table so no permission can be inferred). It's hard to conceive an application that could work with SQL Server security exclusively, I think.
I would be probably OK with storing password in the application or config file if the application was in-house, wasn't exposed to web, and phyiscal security was sufficient. But in this case, it's meant to be accessible across WAN so for that reason, I'd rather have users type in the password & authenticate themselves rather than risking the chance that the hidden password gets compromised. The only safe password is one that's not stored.
opc.three, I think for this specific project, no more than 25 users, though I've been stressed the importance of enabling users to be able to administer their database without calling us. I was also hoping to write a security subsystem that is generalized to work well whether we use SQL server login or Windows auth, so I would also hope we shouldn't have to use something different just because we had 1,000 users and not 10 users. Is my hope too optimistic?
October 31, 2011 at 12:56 pm
My preference is to have each physical user log in with a different server login. From a troubleshooting perspective I like to be able to trace runaway processes to specific people. That said, I have to balance that with any potential performance losses incurred when it comes to connection pooling for a large number of concurrent application users.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 31, 2011 at 1:04 pm
opc.three (10/31/2011)
My preference is to have each physical user log in with a different server login. From a troubleshooting perspective I like to be able to trace runaway processes to specific people. That said, I have to balance that with any potential performance losses incurred when it comes to connection pooling for a large number of concurrent application users.
+1000 on the trace problem with a single user.
I've had to jump through hoops to get that info.
What I have done in the end was to edit the create connection function & put the web login name in the application name.
Of course now you have to parse it out everytime you access a trace. It works but it's a little more annoying.
I would have preffered 1 sql user per client, but that would have meant 1000s of them. In the end we did the single web user with a logins table.
October 31, 2011 at 1:58 pm
I wonder if I'm missing something when people say they don't like having 1000 logins.
While opc.three made good point about concurrent users (thanks!), I'm thinking it's not that big deal if there's 1000 logins and the concurrent users # is sustainable with the given hardware. The way I look at it, we can just create jobs and/or other routines to inactivate/delete stale logins/users, and since we should be assigning permissions via roles, the number of logins seems to me largely inconsequential. I've already mentioned that I have routines that enables the database administrator to create new logins for their users so the administrative burden is reduced in my book.
Also, my biggest problem with a user table is that it boils down to a DIY security model, and of many DIY things out there, I do not consider security to be one thing that one should DIY. At least not without considerable expertise & resources to test everything properly.
Is there more to the story behind wanting to manage number of total logins/users?
October 31, 2011 at 2:03 pm
Banana-823045 (10/31/2011)
I wonder if I'm missing something when people say they don't like having 1000 logins.While opc.three made good point about concurrent users (thanks!), I'm thinking it's not that big deal if there's 1000 logins and the concurrent users # is sustainable with the given hardware. The way I look at it, we can just create jobs and/or other routines to inactivate/delete stale logins/users, and since we should be assigning permissions via roles, the number of logins seems to me largely inconsequential. I've already mentioned that I have routines that enables the database administrator to create new logins for their users so the administrative burden is reduced in my book.
Also, my biggest problem with a user table is that it boils down to a DIY security model, and of many DIY things out there, I do not consider security to be one thing that one should DIY. At least not without considerable expertise & resources to test everything properly.
Is there more to the story behind wanting to manage number of total logins/users?
I would have done it that way too if I had had the time. The 6 months project had tobe done in under 30 days and there was just no time to do that part right.
If you use sp with RUN AS and give access to that to only a handful of users you should be fine. Especially if you use roles.
This is where you just don't want to have dynamic sql with possible injection...
October 31, 2011 at 2:30 pm
Ninja's_RGR'us (10/31/2011)
What I have done in the end was to edit the create connection function & put the web login name in the application name.
You saved yourself the headache of managing 1000's of logins however your change had the same negating effect on re-using existing connections in the pool as using a different login, i.e. requests will not be issued an existing connection from the pool unless the application name in the connection string is the same.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 31, 2011 at 2:30 pm
Banana-823045 (10/31/2011)
Is there more to the story behind wanting to manage number of total logins/users?
Yeah, it's one ROYAL PITA. You're not just on the hook at 2AM for server problems, but user login issues, since the database is always guilty until proven innocent.
NT Login administration and group permission controls are your friend. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 31, 2011 at 2:36 pm
opc.three (10/31/2011)
Ninja's_RGR'us (10/31/2011)
What I have done in the end was to edit the create connection function & put the web login name in the application name.You saved yourself the headache of managing 1000's of logins however your change had the same negating effect on re-using existing connections in the pool as using a different login, i.e. requests will not be issued an existing connection from the pool unless the application name in the connection string is the same.
Thanks for the info.
Since this was a membership only site it probably made no difference. Only 100 users or so would order daily.
The 2nd side effect is that I could give access via gui to the login table and let the csr handle the logins. All the logins/ password were pre-created, they just had to activate them. They could edit them if needed.
October 31, 2011 at 2:59 pm
Evil Kraig F (10/31/2011)
Banana-823045 (10/31/2011)
Is there more to the story behind wanting to manage number of total logins/users?Yeah, it's one ROYAL PITA. You're not just on the hook at 2AM for server problems, but user login issues, since the database is always guilty until proven innocent.
NT Login administration and group permission controls are your friend. 🙂
Easier said than done. We actually originally started out using Windows auth but later down the road, it was deemed that it had to be hosted and therefore we had to use SQL Server auth.
That said, I have to ask - why is 1000 logins a PITA but 1000 Windows user isn't? At the end, they're just logins, Sure, it's simpler to re-use the credentials associated with the machine, but I guess if one really wanted to, one could create a authentication system in the application that checked the machine hardware and generated a password out of this, thus, a DIY Windows auth. (funny that I should say after me expressing misgivings about security DIY....). STILL, you have to grant the windows users/groups the permissions to the server/databases and if the domain adminstrator screws up, you're looking at compromised data.
October 31, 2011 at 3:08 pm
A 1000 SQL logins means more of a password management issue for the DBAs. A 1000 windows logins puts that effort on the Windows admins, or help desk. Also, if someone is less likely to forget their windows login as often as they will secondary logins.
It may not be much more effort, or it could be a lot more effort.
If you can create self service resets, then it's not an issue either with SQL logins or a Users table, but self service for the former is hard.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 27, 2011 at 8:21 am
I want to revisit the subject with a bit different take.
1) Would the architecture make any difference in whether the users' going to be in a user defined table or SQL Logins? For example, I have this in my head that it's OK for a web application where the code is not accessible and thus a bit more resilient to attacks compared to a desktop application where it could be dissembled or traced. What about applications that consumes ODBC data source, such as Excel?
2) After thinking about it some more, I think my biggest issue with an user table is that there's no good way to identify an user from the server-side if they can have more than one connections open. The closest one could get is probably to map an user to a client machine name and MAC address and that'll work as long as the network connection doesn't change and isn't otherwise obfuscated (NAT?). It was mentioned earlier that using Application Name to identify the user could be done but at risk of losing the advantage of pooling. Without a simple way to consistently identify the actual user, it becomes difficult to be able to stamp the changes with their usernames ensure they are mapped to the correct roles. But perhaps I'm not being imaginative and looking at it with a tunnel vision. So, is this a problem for anyone else and how did they address this?
TIA!
November 28, 2011 at 7:26 am
The thing I've seen some people do is add extra logging to client code that can be enabled when there's an issue. This can trigger something to allow you to trace the user on the server. Either adding a parameter to proc calls, or return/display the SPID on the client.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply