Search code examples
sql-server-2008securityactive-directoryrow-level-security

SQL Server row level security - many to many

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)

Solution

  • 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