October 3, 2012 at 1:46 pm
Not everyone knows how to do these things. And for each of the items you mentioned there are additional steps that can be taken to mitigate those risks.
If I have what you are saying right you're advocating using Windows Groups for all Windows-based database access. Correct?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 2:16 pm
For the most part. The only exception I've suggested is with respect to user accounts that function as a "service account." However, I've run into cases recently where it would have been a lot better had Windows groups been used. This isn't so much an issue on the database side, but on the file system where you have millions of objects and you want to change service accounts. You can script it, but it'll run for days and consume lots of CPU to accomplish.
K. Brian Kelley
@kbriankelley
October 3, 2012 at 2:17 pm
Also, I would add that it's relately simple to audit for Windows group changes. This is even easier in 2008 AD and above with event forwarding. An organization is hopefully doing this anyway.
K. Brian Kelley
@kbriankelley
October 3, 2012 at 2:41 pm
I agree that service accounts, depending on what level of permissions they have, may not be good candidates for Windows Group membership. That exception is not unlike the only exception I have brought up which was for sysadmin Role membership. While you raise some good points I would have to stick by that and continue to manage those outside of as individual SQL Server Logins (preferably based on Windows Users).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 4, 2012 at 1:49 am
K. Brian Kelley (10/3/2012)
Also, I would add that it's relately simple to audit for Windows group changes. This is even easier in 2008 AD and above with event forwarding. An organization is hopefully doing this anyway.
But it is somewhat difficult to determine who made a change if it is made through a login for a windows group, unless there happened to be only one member of that group logged in to windows at the time. For some data, security requires being able to attribute changes (updates/deletions/insertions) to individual people.
Personally I am inclined to use groups only for limited privilege access - individual logins for anything less privileged. Of course limited privilege access may be access that comes in through a specific app with a user interface that limits what can be done - eg a website - and it may be that that application identifies users securely and tags changes w, so again a a windows individual will be used despite this being limited privilege access.
Tom
October 5, 2012 at 10:24 am
It seems pretty straight forward to me. If someone leaves the company or moves to another department and no longer should have access to resources they used before, what would you rather do?
1) Go in into AD and edit the items on the account's Member Of tab?
2) Use some other way of hunting down every resource the person has their login on, verify whether or not is it still appropriate and making the needed changes, all of which are more complex, prone to issues and are more time consuming.
October 5, 2012 at 10:36 am
dan-572483 (10/5/2012)
It seems pretty straight forward to me. If someone leaves the company or moves to another department and no longer should have access to resources they used before, what would you rather do?1) Go in into AD and edit the items on the account's Member Of tab?
2) Use some other way of hunting down every resource the person has their login on, verify whether or not is it still appropriate and making the needed changes, all of which are more complex, prone to issues and are more time consuming.
If someone leaves the company you disable the account. Access is denied to that account on all resources going forward regardless of whether it was granted via the account or a group. Nothing changes there. When it comes time to delete that account (if it is company policy to ever do that) then you could see some orphaned permissions out there if it was granted to an account. That cleanup needs to be part of the account deletion process.
And no one is saying to only use Windows Accounts to grant permissions and to never use Windows Groups. All that is being said is that there are reasons why one might not use a Group to grant access to an environment resource in some specific cases.
edit: spelling
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2012 at 11:45 am
When I came onto this job and looked at the SSMS Security node for the instances & DBs I was now reposonsible for, I saw long lists of logins I didn't recognize, and spent a bunch of time asking around to find out who didn't still work there and who did & did not need access. Even when an AD account is disabled, it still clutters up the security config for the objects where the account exists. I still find it much easier & cleaner to use groups whenever possible.
October 5, 2012 at 12:03 pm
dan-572483 (10/5/2012)
When I came onto this job and looked at the SSMS Security node for the instances & DBs I was now reposonsible for, I saw long lists of logins I didn't recognize, and spent a bunch of time asking around to find out who didn't still work there and who did & did not need access. Even when an AD account is disabled, it still clutters up the security config for the objects where the account exists.
That's where sp_validatelogins comes in handy.
I still find it much easier & cleaner to use groups whenever possible.
So do I. It's the 'whenever possible' that gets tricky.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply