Post reply

Security Model with ROLES across DEV QA and PROD environments

  • April 20, 2010 at 5:06 am

    #217987

    To accomodate ROLE based security using WINDOWS Groups in our SQL 2008 environment, we'd like to:

    - create multiple WINDOWS Groups -- Examples:

    - OurDomain\GroupAdmin (with 'sa' access)

    - OurDomain\GroupReadOnly (with 'db_datareader' access)

    - OurDomain\GroupReadWrite (with 'db_datawriter' access)

    - OurDomain\GroupExecute (with execute permissions on Procs)

    - Connect individual Windows UserIDs to these Windows Groups via our Windows Administrator person

    - Create SQL Server 2008 ROLEs to accomodate different permission Levels and add the Windows Groups to the respective ROLE's -- Example Roles:

    - RoleADMIN

    - RoleReadOnly

    - RoleReadWrite

    - RoleExecute

    - etc..

    Question is: Any recommendation on this setup across environments: DEV, QA, PROD ??

    For example, if UserID OurDomain\ourDBA is added to Windows Group OurDomain\GroupAdmin and we create ROLE in our SQL Server 2008 DEV, QA, and PROD environments called RoleAdmin -- then add OurDomain\GroupAdmin to this ROLE -- the development DBA now has 'sa' privelege across all 3 environments. We don't want our Dev DBA to have 'sa' access in PROD

    Are others creating different ROLE names by environment? different Windows Groups by environment?

    thx in advance

    BT

  • April 21, 2010 at 8:50 am

    #1155391

    Using application or business function names is better. For instance you could use

    Production - and additionally ProdAdmin/DevAdmin/QAAdmin

    QA - and additionally QATester/QAWriter etc if necessary

    Development

    Consider your writer AD group. Do you really want all the same users to have write permissions on all three environments - no of course not. But if you use a QA group then you can assign that group to your readonly role in development, and to your readwrite role in qa.

    Just to recap, use the same rolenames in all three environements that are relevant to SQL permissions - like you posted, but use application or function names for the AD groups. Then just place those groups in the different roles depending on the environement.

Viewing 2 posts - 1 through 1 (of 1 total)

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