May 12, 2016 at 9:51 pm
Comments posted to this topic are about the item The Proliferation of Roles
May 13, 2016 at 12:23 am
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!!!
May 13, 2016 at 12:27 am
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)
May 13, 2016 at 12:52 am
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
May 13, 2016 at 7:28 am
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
May 13, 2016 at 8:43 am
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.
May 13, 2016 at 8:52 am
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.
May 13, 2016 at 8:54 am
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)
May 13, 2016 at 8:55 am
Roger, If you use AD then AD administrators have access. 🙂
5ilverFox
Consulting DBA / Developer
South Africa
May 13, 2016 at 9:00 am
Japie Botma (5/13/2016)
Roger, If you use AD then AD administrators have access. 🙂
That's what audits are for... 😀
May 13, 2016 at 10:06 am
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!!!
May 13, 2016 at 10:12 am
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)
May 13, 2016 at 10:20 am
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.
May 13, 2016 at 11:25 am
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.
May 13, 2016 at 3:29 pm
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 16 total)
You must be logged in to reply to this topic. Login to reply