The Proliferation of Roles

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716619

    Comments posted to this topic are about the item The Proliferation of Roles

  • Gary Varga

    SSC Guru

    Points: 82166

    Whenever dealing with permissions on a database or domain basis then it has always been easiest when using roles/groups. People move jobs but should not retain permissions from their previous post, people swap into a post and immediately should have the same access levels as the previous postion holder. All this adds up to the abstraction that permissions are required to perform a job which forms part, or even all, of the duties of a position.

    People perform the duties of the position so it is natural that the permissions reside with the role. This has always been the way. Even before computers. The bearer of the seal of office has permissions because they have the seal which symbolises the role not the person.

    On a more pragmatic level, positions are usually more static than people.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Lawrence Moore

    Ten Centuries

    Points: 1203

    I tend to simply add Windows AD groups as logins and don't bother with any associated role.

    I'm curious what value a role adds?

    (I'm aware the main thrust of your editorial was avoiding individual logins - which always makes sense in my opinion)

  • Japie Botma

    SSCrazy

    Points: 2931

    What if the SQL Section is too small to handle the change in personnel AND database access is not allowed to AD administrators? Then you have to use SQL authentication with security control per system on the front-end. Now that is turning "normal thinking" upside down.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Eric M Russell

    SSC Guru

    Points: 125032

    In my last two jobs, which covers a span of ten years, we've used AD domain group authentication. For example MYCORP\DBA_Production, MYCORP\StoreOpsQA, MYCORP\MarketingBI. As employees are hired (or switched between teams), then let HR and executive management maintain group membership. You don't want the DBA in a large organization to be pestered with daily changes in (organizational) role membership.

    Also, unless you're trying to protect highly sensitive data (ie: customer contacts or payments), then it's best not to get too granular. For example even if Jane Smith is the only person in DevOps who needs a particular PowerBI report against the production server, then grant permissions to all her peers in the DevOps team, because you never know when someone else may need to cover for her while she's out on vacation. The more groups and roles the DBA must manage, then more effort it requires, and the greater the chance for mistakes. When you have a database server with hundreds of group or individual logins, it's too easy for things to get out of hand.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • roger.plowman

    SSChampion

    Points: 10174

    I create roles in the database that always match AD roles. That makes it child's play for the AD admins to create security groups the DBA needs, then have the DBA match the application's roles to AD security groups.

    That way you don't need AD admins to have database access.

  • ZZartin

    SSC-Dedicated

    Points: 30390

    Gary Varga (5/13/2016)


    Whenever dealing with permissions on a database or domain basis then it has always been easiest when using roles/groups. People move jobs but should not retain permissions from their previous post, people swap into a post and immediately should have the same access levels as the previous postion holder. All this adds up to the abstraction that permissions are required to perform a job which forms part, or even all, of the duties of a position.

    People perform the duties of the position so it is natural that the permissions reside with the role. This has always been the way. Even before computers. The bearer of the seal of office has permissions because they have the seal which symbolises the role not the person.

    On a more pragmatic level, positions are usually more static than people.

    The value of roles in the database is that you can define a role and assign that role to multiple AD groups or logins, those might have multiple roles that aren't all shared between the two.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716619

    Lawrence Moore (5/13/2016)


    I tend to simply add Windows AD groups as logins and don't bother with any associated role.

    I'm curious what value a role adds?

    (I'm aware the main thrust of your editorial was avoiding individual logins - which always makes sense in my opinion)

    Can't always guarantee Windows logins. If you had to add a SQL auth login, what would you do?

    Not only that, but it's possible that multiple Windows groups map to permission sets. Ideally you use roles to assign a group of users (AD group) to a group of permissions (db role)

  • Japie Botma

    SSCrazy

    Points: 2931

    Roger, If you use AD then AD administrators have access. 🙂

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • roger.plowman

    SSChampion

    Points: 10174

    Japie Botma (5/13/2016)


    Roger, If you use AD then AD administrators have access. 🙂

    That's what audits are for... 😀

  • Gary Varga

    SSC Guru

    Points: 82166

    ZZartin (5/13/2016)


    Gary Varga (5/13/2016)


    Whenever dealing with permissions on a database or domain basis then it has always been easiest when using roles/groups. People move jobs but should not retain permissions from their previous post, people swap into a post and immediately should have the same access levels as the previous postion holder. All this adds up to the abstraction that permissions are required to perform a job which forms part, or even all, of the duties of a position.

    People perform the duties of the position so it is natural that the permissions reside with the role. This has always been the way. Even before computers. The bearer of the seal of office has permissions because they have the seal which symbolises the role not the person.

    On a more pragmatic level, positions are usually more static than people.

    The value of roles in the database is that you can define a role and assign that role to multiple AD groups or logins, those might have multiple roles that aren't all shared between the two.

    Thanks. Good point.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • ZZartin

    SSC-Dedicated

    Points: 30390

    Gary Varga (5/13/2016)


    ZZartin (5/13/2016)


    Gary Varga (5/13/2016)


    Whenever dealing with permissions on a database or domain basis then it has always been easiest when using roles/groups. People move jobs but should not retain permissions from their previous post, people swap into a post and immediately should have the same access levels as the previous postion holder. All this adds up to the abstraction that permissions are required to perform a job which forms part, or even all, of the duties of a position.

    People perform the duties of the position so it is natural that the permissions reside with the role. This has always been the way. Even before computers. The bearer of the seal of office has permissions because they have the seal which symbolises the role not the person.

    On a more pragmatic level, positions are usually more static than people.

    The value of roles in the database is that you can define a role and assign that role to multiple AD groups or logins, those might have multiple roles that aren't all shared between the two.

    Thanks. Good point.

    Hmm.... the forums quoted the wrong post 😛

    It should have quoted the guy below you.

    I tend to simply add Windows AD groups as logins and don't bother with any associated role.

    I'm curious what value a role adds?

    (I'm aware the main thrust of your editorial was avoiding individual logins - which always makes sense in my opinion)

  • Larry Aue

    Ten Centuries

    Points: 1027

    For me, the benefit of using Roles is the layer of abstraction. Roles are contained within the database (vs. a user which is tied to a login which is not within a database). Across environments (dev, test, and prod) you typically are granting different people access to objects. With roles, the security for the objects are set and then in environments you can decide who gets the Roles. For example, I would never give a business user access to a dev database.

    Another benefit to Roles is that it doesn't matter if they are SQL-authenticated or Windows. Whoever is in the role has the permission.

  • David.Poole

    SSC Guru

    Points: 75199

    A side effect of having DB roles is that you have a common frame of reference to discuss business roles and how the business wishes to organise itself.

    It's a useful abstraction for getting business people to think about security.

    You can ask whether someone has access because it's a coping strategy for poor organisation.

  • Gary Varga

    SSC Guru

    Points: 82166

    ZZartin (5/13/2016)


    Gary Varga (5/13/2016)


    ZZartin (5/13/2016)


    Gary Varga (5/13/2016)


    Whenever dealing with permissions on a database or domain basis then it has always been easiest when using roles/groups. People move jobs but should not retain permissions from their previous post, people swap into a post and immediately should have the same access levels as the previous postion holder. All this adds up to the abstraction that permissions are required to perform a job which forms part, or even all, of the duties of a position.

    People perform the duties of the position so it is natural that the permissions reside with the role. This has always been the way. Even before computers. The bearer of the seal of office has permissions because they have the seal which symbolises the role not the person.

    On a more pragmatic level, positions are usually more static than people.

    The value of roles in the database is that you can define a role and assign that role to multiple AD groups or logins, those might have multiple roles that aren't all shared between the two.

    Thanks. Good point.

    Hmm.... the forums quoted the wrong post 😛

    It should have quoted the guy below you.

    I tend to simply add Windows AD groups as logins and don't bother with any associated role.

    I'm curious what value a role adds?

    (I'm aware the main thrust of your editorial was avoiding individual logins - which always makes sense in my opinion)

    Still was a good point :hehe:

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 15 posts - 1 through 15 (of 17 total)

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