标准-99, 服务器2008R2:

WITH ImpliedUserRoles
     AS
     (
      SELECT u."User", d.Dept, d.Task
        FROM UserRoles u
             INNER JOIN DeptTasks d
                ON u.Dept = d.Dept
     )
SELECT *,  omission  AS role_error
  FROM ImpliedUserRoles
EXCEPT
SELECT *,  omission  AS role_error
  FROM UserRoles
UNION
SELECT *,  inclusion  AS role_error
  FROM UserRoles
EXCEPT
SELECT *,  inclusion  AS role_error
  FROM ImpliedUserRoles;

我从你的问题和数据中立即推断出,你的表格设计不当,造成含糊不清和错误(授予特权或不当授予特权),而不应有。

然而,与你合作:

Missing Privileges ("Roles")

   SELECT d.User, d.Dept, d."Should Have" as "Missing"
     FROM (   SELECT DISTINCT udr.User, udr.Dept, dt.Task AS "Should Have"
                FROM UserDeptRole udr
           LEFT JOIN DeptTask dt
                     ON udr.Dept = dt.Dept) d
LEFT JOIN UserDeptRole udr2
          ON udr2.Dept = d.Dept AND udr2.Role = d."Should Have"
    WHERE udr2.Role is NULL;

产出:

+------+---------+---------+
| User | Dept    | Missing |
+------+---------+---------+
| Jane | Manager | Inquire |
| Jane | Manager | Delete  |
| Fred | Clerk   | Add     |
+------+---------+---------+

Improperly Granted Privileges ("Roles")

   SELECT DISTINCT udr.User, udr.Role AS "Improper"
     FROM UserDeptRole udr
LEFT JOIN DeptTask dt
          ON udr.Dept = dt.Dept AND udr.Role = dt.Task
    WHERE dt.Task IS NULL

产出:

+------+----------+
| User | Improper |
+------+----------+
| Fred | Modify   |
+------+----------+

A Better Question

我鼓励你在另一个问题上询问SO社区,你如何能够改变你的表象,以避免这种情形(任由或不当的角色)。

从您的用户桌开始,外人就坐到您在深度和广度、深度和作用、任务等方面的作用桌旁。 这样,如果使用者不应享有这一权利,则该使用者将不再享有这一权利。

样本:

select u.User, case when d.Role is null then 0 else 1 end as Allowed
from User as U
left outer join Role as d
on U.Dept = d.Dept and U.Role = d.Task

下面的询问不会对大型数据集进行!

你可以确定所有可能性,相互融合,然后,当权利齐头并进时,只能关注失踪人员。

(以下为T-SQL,其表变量代表了实例数据)

select allTasks.[User], allTasks.[Dept], allTasks.Task as MissingTask
from @users u
    right outer join (
        select u.[User], d.Dept, d.Task
        from @depts d
            cross join (select distinct [User], Dept as [Dept] from @users) u
        where d.Dept = u.Dept) allTasks on allTasks.[User] = u.[User] and allTasks.Dept = u.Dept and allTasks.Task = u.Role
where u.[User] is null

成果:

User       Dept       MissingTask
---------- ---------- -----------
Fred       Clerk      Add
Jane       Manager    Delete
Jane       Manager    Inquire

(3 row(s) affected)

这样,人们就会发现,他们可能承担以下任务:

select u.[User], u.Dept, u.Role as ExtraRole
from @users u
    left outer join @depts d on d.Task = u.Role and d.Dept = u.Dept
where d.Dept is null

成果:

User       Dept       ExtraRole
---------- ---------- ----------
Fred       Clerk      Modify

(1 row(s) affected)