Post reply

Global Groups or Local Groups

  • January 10, 2005 at 10:06 am

    #108923

    Can anybody advise on the best way to set up permissions to Groups in SQL Server.

    I have read on Microsoft to use the following:

    • Add Windows 2000 network users to Windows 2000 groups.

    • Create a single SQL Server 2000 login account for the Windows 2000 group.

    • Create a user account in the database for the SQL Server 2000 login account.

    • Add the user account to a user-defined database role.

    • Grant permissions on views and stored procedures to the role.

    so this means:

    Create Global Group, Create SQL login for the Group and assign db permissions to the login, add user account to user defined roles and grant permissions to the role.

    I have also read the following:

    • Users in each domain are assigned to Windows global groups
    • The Windows global groups from the various domains are placed into a Windows local group
    • The Windows local group is granted rights to log in to SQL Server 2000
    • The Windows local group is granted access rights to the appropriate databases.  This Windows local group may not be the same one as was used from granting rights in step 3.  Therefore steps 1 and 2 are often repeated to group the users by access permissions required.
    • The Windows local group is assigned permissions on the specific database objects

    So if you are using database roles you need only apply global group access but you can use local groups to assign permissions to the objects directly.

    Which one is more secure and easiest to administer?  I guess the Global group is easier to administer but is it more secure than the local group access?

     

    Regards

     

    Carl

     

     

     

     

     

     

     

     

  • January 10, 2005 at 10:25 am

    #536702

    The answer: it depends.

    When I was at J.D. Edwards, literally over a thousand Windows servers, 7k+ users, 500+ SQL Servers, etc. We had four domains that were being collapsed into 1.

    I have always worked with global groups because it was simpler for me and a db recovery to another server means I don't have to setup any local groups in recovery, one less step.

    The Windows admins, who I thought were pretty sharp, had a couple reasons for using local groups and always set them up with a global inside the local.

    I think it depends on your company standard. To me just globals is easier, but if the windows admins do the other, I'd just go with them.

  • January 10, 2005 at 11:09 am

    #536710

    I prefer global but I've seen it both ways. My preference for global is two fold:

    1) As a DBA, if I have to restore the database to a different server, the logins and their SIDs would mismatch with local groups. Not so with global groups. From a recovery standpoint it's not hard to script around this, but it takes time.

    2) As a security pro, if I have all my security being determined at the domain level, that's the only place I have to look. If I have local groups on the servers I have to look both places. After all, if I have a rogue employee, not only do I need to make sure said employee is out of the global group, but I now have to go in and check the local group to see if said user somehow got inserted there.

    K. Brian Kelley
    @kbriankelley

  • January 10, 2005 at 3:02 pm

    #536735

    I was always told in Windows NT courses to think Globally act Locally still applied. Meaning put your users in your domains into Global groups then put those global groups into a local group at the server. Then from there you put the local group into SQL Server and apply via roles. So now when you remove domian or add them you have a global groups for the folks (try to seperate based on function and not just a sql global group unless that is all that matters), apply to the relative local group (or remove as needed) and your local group since shoudl already have permission asigned to the local group by role should handle the rest.

  • January 10, 2005 at 3:36 pm

    #536737

    That's the scenario still communicated by MS as their preferred practice. However, with the ability to nest groups more than 2 levels in AD, etc., with the # of servers admins and security personnel are having to watch over, the simplest security model is to put and manage the groups completely at the domain level. Having gone through SAS 70 and SysTrust audits, our auditors have agreed with this approach, especially as we're migrating to a role-based security model in AD.

    K. Brian Kelley
    @kbriankelley

  • January 11, 2005 at 2:42 am

    #536762

    Thanks for all your replies.

    I think I'll go down the Global Groups option if there is no advantage to security by adding local groups.

Viewing 6 posts - 1 through 5 (of 5 total)

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