Using AD Groups for Security

Question

Using AD Groups for Security

david.c.holley

SSCrazy

Points: 2423
More actions
July 23, 2010 at 6:51 am

#225369

We're building an ASP.NET application where the various ASP.NET pages will utilize the user's Windows User Id to grab & update data from the database.
Obviously, we can use a AD group to grant everyone access to the data with appropriate limited permissions. Is this the preferrred method for a situation like ours?
If a user belongs to the general group (which has only limited permissions) as well as another group (which has additional permissions) will the extended permissions be granted to the user that belongs to both groups?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sudeepta SSCommitted Points: 1666 More actions · Answer 1

Using Active directory group to assign permission is a preferred way.

If an user belongs to two groups, one with public access and another with additional access rights, the user will have all the rights assigned to both the group.

Hope, this may help.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 2

As Sudeepta stated AD groups is the best control method for access, especially once you get a high number of users.

Edit: I have worked with applications that used the .NET application roles and then those in SQL Server. It tends to be a pain to troubleshoot things when it gets into the .NET roles, but again it is strictly based on preference and policy I think.:-D

With regards to multiple group membership this is a good BOL to read: Permission Check Algorithm. It shows the steps it goes through in checking permissions on a user/login that might help you. Basically it is going to first check if a DENY exist, this will overwrite any other inheritable permission from another group. (DENY is a very strong word in SQL compared to REVOKE. It can cause many headaches in troubleshooting permissions.) If you general group has a DENY to an object, that will trump if your "other group" is supposed to have access.

Hope this helps.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Sudeepta SSCommitted Points: 1666 More actions · Answer 3

Thanks Shawn, Forget to add info about the DENY command

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly

david.c.holley SSCrazy Points: 2423 More actions · Answer 4

Yeah, I was thinking that it'd be an AD group. I keep having the pleasure of having new things thrown at me quite unexpectedly and wanted to have some idea as to which direction to start out on as opposed to doing something and then spending more work to fix it later.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

I seem to miss something here. You only require the user name to check what permission they have and use that user name for logging purpose?

Please keep in mind that the more users (group) added the SQL Server, the bigger is the security risk. Also are you planning to have an application pool that will be running from the IIS?

-Roy

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

when using AD groups in this situation you'll need to check with your Windows Admins to make sure that 'Kerberos Delegation' is turned on otherwise you will end up in the 'double hop' authentication nightmare.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

david.c.holley SSCrazy Points: 2423 More actions · Answer 7

Roy Ernest (7/26/2010)
I seem to miss something here. You only require the user name to check what permission they have and use that user name for logging purpose?
Please keep in mind that the more users (group) added the SQL Server, the bigger is the security risk. Also are you planning to have an application pool that will be running from the IIS?

I've got several things going on at the moment so the heads spinning a bit. Since the application is something that all employees need access (one component is a Contact Directory on steroids), the thought was to add the AD group to which everyone belongs and then lock down permissions for that specific group. A second AD group would be created for Admins granting us the permissions that we need.