Desiging roles - people or permissions

  • I have a question for those who use database roles. Fundamentally, the question is whether it is based on a set of users or a set of permissions, but it's a bit more complex than it first seems.

    I think most people who use roles would agree with my preference to use a role to define a set of permissions, and let a set of related users be defined in, say, AD, and then have the AD groups be members of the required roles.

    The complicating factor comes when we are talking about different SDLC phases, which mostly only applies to a limited set of business users (ie, developers and testers). Let's say I have defined a db_developer role to handle my developers.

    In the development environment, members of the development team have (let's say) dbo permission on the relevant databases.

    In the UAT environment, let's say developers have db_datareader permission.

    In production, they have select permission against a limited set of schemas or objects.

    Then,

    does my "db_developer" role exist on all instances, but represent a different set of permissions based on SLDC phase, or...

    does my "db_developer" role imply (let's say) dbo permission and therefore not exist in UAT or prod?

    I lean towards the latter (leave AD for users, use roles for permissions), but am interested in hearing arguments either way on the topic.

  • The role should define the set of permissions. With different permissions requirements in different environments, we just follow the documented roles (for audit purposes) and just make sure not to promote a role from one environment to another.

    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

  • Thanks for your response, but I'm still not quite clear on your method.

    If you had a role called "db_developer" which gave high privileges in the dev environment, would you create a role with the same name in UAT but assign a different set of permissions due to the different phase, or would you not have the db_developer role in UAT at all and instead have a db_tester (or something) role with the reduced permission set?

  • Hi,

    I think that you should only create the minimum number of roles you require in each environment and each role should have the minimum set of permission to function.

    In your example, I do not feel that you should create a db_developer role in your UAT environment since no development should take place there. So you would only have your db_tester role and that role may contain people who happen to be members of the db_developer role in the dev environment. This will prevent them from fixing issues in the UAT env and 'forgetting' to apply same changes to dev or (the deploy scripts).

    I hope this helps,

    B

  • Thanks, I expect this trend of everyone wanting roles to reflect permissions and not people (as is my preference as well) is going to continue. I was curious because I am getting some resistance to this approach, I found this rather odd but thought it would be wise to double check that I wasn't the odd one out here!

  • A small example might be useful.

    Few users can CREATE SPs in DEV environment. They are Developers. So you have a group of resources with set of permissions for them say ‘app_DEV’.

    You have another group of resource in QA environment and they just EXECUTE the SPs for verification. This is QA functionality so it’s a new role say ‘app_QA’.

    The group members of DEV team actually performs QA role (verification of SPs NOT creation) so they should be member of ‘app_QA’ role and should be removed from ‘app_DEV’ role on QA environment.

  • allmhuran (10/11/2011)


    Thanks for your response, but I'm still not quite clear on your method.

    If you had a role called "db_developer" which gave high privileges in the dev environment, would you create a role with the same name in UAT but assign a different set of permissions due to the different phase, or would you not have the db_developer role in UAT at all and instead have a db_tester (or something) role with the reduced permission set?

    It depends on how your policy is written. I have done it both ways due to policy. The developers could be a member of a different role in UAT that meets the permissions requirements. It's not necessary for that db_developer role to exist in UAT. However, if the policy said the role should be db_developer in UAT, but with reduced permission, then do it that way.

    I prefer to not have the db_developer role in UAT with a different set of permissions because it can be a bit more confusing - thus the need to rely on policy and documentation.

    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

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

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