SQL Server行级安全性-多对多 [英] SQL Server row level security - many to many

问题描述

我们正在使用SQL Server 2008，并在Active Directory（AD）中设置了权限。核心表中的每一行仅应由拥有查看每一行权限的用户查看。我可以在每行上设置行级安全性（每行包含一个与AD组对齐的角色），但是我遇到以下问题：-

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 :-

• 因为这些角色代表的国家/地区，我有几百个角色。此外，我有一些用户可以访问多个角色...并且给定用户可以访问的几个角色没有统一地分组（即存在重叠）：因此，用户角色是多对多关系

在这种情况下（为了实现灵活的模型），我的第一个直觉是在每个国家/地区创建一个广告组，然后在数据库角色中创建分组（以将每个国家/地区分组）。但是，这将产生维护费用-例如，许多员工可以访问所有组，因此，当一个国家/地区上线时，会创建一个新的AD组，这意味着我将不得不要求将这些用户作为成员添加到一个新的AD组（以及需要向所有组添加一个新的全局用户）-我宁愿拥有一个可以全面访问的全局组。

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.

有任何人遇到这种问题。基本上，如果我只有非常细粒度的AD组，那么我可以容纳数据库角色级别中的许多对象，但是例如，我宁愿拥有细粒度的AD组（针对那些需要细化权限的用户），以及针对那些细粒度的AD组。需要访问所有行的用户（我只需要添加具有全局访问权限的用户）

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)

推荐答案

我完成了什么这样做是为了：-

What I finished up doing was to:-

• 创建每个国家/地区的数据库组（大约100个）


• 创建一个每个分组（全球，欧洲，亚太地区，拉丁美洲，北美洲，英国等）的数据库组


• 创建包含所需级别用户的AD组（仅需要12个用户）
  
• 将AD组添加为相应数据库角色的成员


• 在各个国家和所属组之间创建数据库角色的父子关系；例如。欧洲的英国（注意这里允许多对多）


• 为组中的组创建数据库角色父子关系（注意这里允许多对多）

• 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)

一些注意事项：-

• 此处添加的每个数据库角色都需要成为数据库用户


• 在创建数据库角色关系时，所有相关的数据库角色和AD组也都将加入该关系中


• 现在我能够根据需要在AD组级别或单个国家AD组级别添加用户


• 数据库角色和所有关系均已编写脚本


• IS_MEMBER子句在各个国家/地区级别

• 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

这篇关于SQL Server行级安全性-多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！