We are using SQL Server 2008 and have our permissions setup in Active Directory (AD). Each row in a core table needs to be only viewable by those that have permission to view each row. I can setup row level security on each row (each row contains a single role that lines up with an AD group), however I have the following problem :-
- because the roles represent countries I have several 100 roles. Further I have some users who have access to several roles...and the several roles given users have access to, are not grouped consistently (i.e. there is overlap) : hence role to user is a many to many relationship
This being the case (to enable a flexible model) my first instinct is to create one AD group per country, then create the groupings within database roles (to group each country). However this will be a maintenance overhead - for example there are many staff members who have access to all groups and hence when a country comes online, a new AD group is created which means I will have to request these users be added as members to the new AD group (as well a new global user needs to be added to all groups) - I would rather have a global group that has access across the board.
Has anyone come across this kind of issue. Basically I can accommodate the many to many within the database role level if I have very granular AD groups only, however I would rather for example have granular AD groups (for those users that require granular permissions) as well as a global AD group for those users that need access to all rows (that I only need to add users, who have global access)
Based on what you described, if you have a large number of users to manage, this would be a good situation to create a third-party management tool or use Identity Management tool to Manage your AD groups and the corresponding SQL row level security.
As for the AD groups, what you have thought is what I would do also, i.e.
- 100 roles base on the countries
- 1 role for all country => contain all other AD country groups
Create a tool (ideally Identity Management tool if you have one in place),
- tool allow you to manually assign users to their countries
- tool maps the country to their corresponding AD group (Identity Management would have this out of box)
- tool maps the country to their corresponding SQL row security
- tool regularly sync the country against AD group and SQL security (this is to prevent System Admin manually assigns a user group to the user, again Identity Management would have this out of box)
What I finished up doing was to:-
- create a database group per country (around 100)
- create a database group for each grouping (GLOBAL, EUROPE, APAC, LATINAMERICA, NORTHAMERICA, UK, etc)
- create AD groups that contain users at the required level (there was only 12 required)
- add the AD group as a member of the corresponding database role
- create database role parent-child relationships between the individual countries and groups they are in; eg. UK in EUROPE (note many to many are allowed here)
- create database role parent-child relationships for groups in groups (note many to many are allowed here)
A few notes:-
- each database role added here needs to be a database user
- when a database role relationship is created all relevant database roles and AD groups are also brought into the relationship
- now I am able to add users at AD group level or individual country AD group level depending on the need
- the database role and any relationships are scripted
- the IS_MEMBER clause is at the individual country level
