SQL Best Practice: Use Windows groups instead of indiv windows logins question
-
I am currently working on a SQL Server project to bring out security up to best practice standards. One of the best practices that i have found is to assign permissions/roles to windows groups as opposed to individual windows logins. Then assign users to the windows groups. I am trying to map out how I am going to do this. I am struggling with figuring out how to split up the groups and what kind of a naming convention to use. Do I create a group that performs a certain function (has data reader & data writer permissions) and put everyone that has these permissions into it or should I split it up by teams within our organization? I would like to hear from others that have already done this to see what you did. Any input would be appreciated.
Thanks!
John
-
Approach it from a role-based security perspective. Build the Windows groups around business roles (system administrator, legal secretary, sales rep, etc.). This will allow those groups to be reused in different areas of the infrastructure.
Within each database, build user-defined database roles matching the roles for the application you are supporting. Put the Windows groups into these roles. Assign permissions to these database roles.
Role-Based Access Control (Role-Based Security)
K. Brian Kelley
@kbriankelley -
Also, give the group names an alias name so that you can refer to the alias in the script rather than the full domain workgroup name.
-
Thanks for the suggestions guys! And thanks for the links Brian. I have a lot of reading to do.
John
-
The biggest issue to keep in mind is that if you have database roles and assign a Windows group to multiples or have permissions on the Windows Group itself the most restirctive will apply. Grant is consider more restrictive than Revoked, Deny is more restrictive than Grant. So keep that in mind as you can cause yourself issues trying to troubleshoot issues the more Roles you assign a group to.
Personally I suggest no permissions be assigned to to the Windows group in a database but definently use roles. This will help for any potential future movement of the database. However it does sound like that is where you are headed.
-
Actually, that is exactly what I was planning on doing. Now that I look at my post, I may have said otherwise. I will be creating roles and then assigning the roles to the windows group.
I did have another question I forgot to ask. How is ownership of objects handled if you use windows groups? Does it still consider your windows login which is part of the group the owner or does the windows group become owner of the objects? I think that it is the first but I haven't had time to test it out yet.
John
-
The owner is the database user. A Windows group is technically a login to the SQL Server. That gets mapped into the database as a single database user.
K. Brian Kelley
@kbriankelley -
So any object that is created by anyone within a database will have the same windows group owner? Doesn't that mean that anyone within the group can then alter any objects that are owned by the group. So someone could delete someone else's table if they are in the same group. I don't know if I like that or not. Did I misinterpret your response?
Thanks for the help!
John
-
I recommend against using roles if you have control over the domain. Windows groups are much more efficient. Roles are most useful when you don't control the domain in which you have to work. If you create roles, and then assign groups to them, you will be creating two steps where one would have been necessary. Where you would have created a role, create a group instead.
-
I was wondering about this situation. My production sql servers reside in a remote location, remote from our office, and hence they are in their own domain. We have production webservers in the same location that reside in yet a different domain. The webservers connect to the sql servers via odbc and I use sql logins for these connections. I too am trying to upgrade the security to best practices and I was wondering what roles are best to use. I would like to create a role for the web connections only. Then a role for those people who need intermitent connection to the production servers. I am leaning towards creating a fixed server role for the people from our office and a database role for each of the web connections.
What are your thoughts on this.
Thanks
Gary
-
Best practice is still to create roles. You never know when you might suddenly need two groups that need that access instead of one. Been there, done that. Recommendation from MS.
Windows account into Windows group >> Windows group granted Server login rights to SQL Server >> Windows group login mapped to database user >> database user placed into user-defined database role.
This is especially true if you're using row-level security based on Window groups. Multiple groups through the use of a role may be given permission against a database object (say you have to support ad-hoc querying). Row-level security is based on their Windows group to actually restrict the data displayed.
K. Brian Kelley
@kbriankelley -
While I would normally defer to one much wiser in the ways of SQL Server than me (really, I mean that
) I have spent a lot of time with the security aspects, and I can also say been there done that in reverse. Had a chance to secure the db using windows authentication, and first created roles because I didn't understand the process thoroughly. When I was done, I realized I created an unnecessary layer. I took the trouble to fix it to eliminate the roles, and in two years, I have not had to undo this security system.I don't take MS recommended best practices lightly, but what I said also has it's published promoters. One day I may eat my words, as I have on one or two occasions, but in this case I stand by my original recommendation.
-
I guess in order to put in all the provisos: really this depends on the size of your organization, the amount of change that occurs, and the likelihood of B2B or acquisitions/mergers, etc.
An organization that undergoes a lot of change would be best suited for using roles. For instance, a B2B comes in and suddenly there is a trusted AD forest. They have a Windows group that needs access, etc. Same thing would be true in an acquisitions/merger situation. Or, if you're like my org, where you've got a side-by-side AD migration (meaning groups in both domains), permissions against roles means saving a ton of work.
K. Brian Kelley
@kbriankelley -
Excellent point. I work with one domain over which I have complete control. Therefore creating a role is tantamount to creating a group with an extra layer.
Based on what you added, I would modify my general rule of not using groups when control over the domain exists to include the caveat that if multiple domains exist for which multiples groups could be covered under one role, I would use roles. The loss of the one to one correlation would argue for the use of roles.
-
Yup. Same reason for users into groups. No one-to-one relationship, so a group is recommended. Another example that could come up in even a single domain is you have two different sets of application support groups. Application changes hands but for a short time users in both application support groups need access. The overall Windows group for each application support team has access to a lot of areas, areas beyond what is needed to support just the one app.
Ideally this isn't a big deal. There was a Windows group just for the app support. Reality is a lot of places don't do that, because you can imagine the numbers of groups, etc. During that time of transition, the role saves a lot of extra work.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply