January 12, 2010 at 2:59 pm
Hello,
I know this is a big topic, so I will try to narrow down my question as much as possible.
I'm trying to plan our future security setup for SQL Server. We have mixed-mode authentication by default because we use some Unix/Linux servers. However, we also have created SQL logins in cases where it seems that it was easier than creating the equivalent Windows group, but not necessarily for any better rationale. :blush: I want to take the opportunity to make the new server setups more organized and less ad hoc and full of grunt work .
I would like to set up a manageable system. I think I understand the basics of using Windows groups and then adding Windows accounts to the groups or removing accounts from the groups. But I need help with the following questions:
1. Can we use user-defined roles in SQL Server as the equivalents to Windows groups to help organize permissions? In other words, is it better to work on making a system of roles and SQL logins comparable to Windows groups and accounts, or should I just minimize the use of SQL logins and use Windows groups as much as possible - and press for Windows accounts for consultants, etc.? One thing I just read when searching the forums is a comment from Steve Jones that if you use a Windows group, you potentially lose control over who is in that group (someone in the systems group can add or remove people without necessarily having to consult the DBA), so unexpected users might be given access when they shouldn't. I'm not sure if that helps answer my question above one way or the other.
2. Is there any methodology for correctly planning out security (whether for Windows groups or for roles) regarding how to most efficiently set them up? In other words, is it best practice to set up a Windows group for each db server, then groups for each db within the server, then groups for each type of access within the server, after which a given Windows account is added to the relevant groups to yield the correct permissions?
For example, if you want access to allow some people to be db owners, others to read and write, and still others only to read, does the method described above do that in the best way possible? Or are there other ways or other things to consider?
Thanks in advance for any help or pointers to books or web sites.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 13, 2010 at 7:53 am
There are pluses and minuses to both methods. I think form a pure security standpoint using Windows Users/groups is more secure for login reasons and also easier on users so they don't have to remember multiple passwords.
Steve is correct that you lose a little control using Windows Groups, but there should be a process in place for adding logins to Windows groups and at some point your Windows admins have to be trusted.
I like to use Windows Groups where I can for access to the SQL Server and then within databases have roles that mirror the Windows Groups and add the groups to the database roles. This allows for users to be added to the proper windows groups and get the needed database access, but also means it is flexible so windows logins or sql logins can be added to the server and added to the database roles as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 13, 2010 at 8:02 am
Hello Jack,
Thank you for your help. I will include your advice in my planning.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 13, 2010 at 10:48 pm
Jack Corbett (1/13/2010)
There are pluses and minuses to both methods. I think form a pure security standpoint using Windows Users/groups is more secure for login reasons and also easier on users so they don't have to remember multiple passwords.Steve is correct that you lose a little control using Windows Groups, but there should be a process in place for adding logins to Windows groups and at some point your Windows admins have to be trusted.
I like to use Windows Groups where I can for access to the SQL Server and then within databases have roles that mirror the Windows Groups and add the groups to the database roles. This allows for users to be added to the proper windows groups and get the needed database access, but also means it is flexible so windows logins or sql logins can be added to the server and added to the database roles as well.
Just to echo the sentiment, I too like to use AD Groups in my Roles. As long as controls, policy and processes are in place for adding a user to an AD group - there should be little conflict.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2010 at 8:02 am
**Note that we mostly deal with SSAS, and this pertains more towards data access**
One thing I really like about windows groups - if someone leaves and is deleted from AD, access is updated automatically.
There is also no login to get shared.
There is visibility to others in the organization, especially if AD groups give an indication of role or application being secured. For example, we secure sales regions and business units in our cube via groups. It's real easy for anyone to view and verify that the user is setup for the application, and the slice they are expected to see.
A drawback can be you may not have access to add / manage groups in AD, so changes may take longer to get done if others are involved.
There also may be some ability to manage along similar lines as network share access, although we find a lot of exceptions.
Some coordination is needed with AD group. You need to be on the same mindset as to how a specific groups to be used, or someone may nest one group inside another, and access is not what was intended by you.
So like Jack says - there are ++'s and --'s and you have to weigh them in your environment.
Good question. And even though you seem to be asking more about SQL, this will give you a bit of insight into what you might want to do if you use bubes.
Greg E
January 14, 2010 at 8:25 am
Thanks, Greg,
Your advice is useful even though we don't use cubes. Especially the considerations about exceptions. I think it's the exceptions that have caused me the greatest amount of extra work and messiness. In particular, cases where a login or a group needs read or write access to one or two tables in another database, but nothing more. Those can be simple in each particular case but hard to keep track of once they start to add up.
I'm going to try to take a moment to step back and analyze the exceptions - perhaps there's a way to group the preivous exceptions in a way where they can be put into some groups or even SQL database roles. Maybe exceptions under one approach can be better organized under another approach. Or, if that's not possible, at least I will have documented the exceptions better so we can have a way to clearly communicate what's out there.
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply