CODEWITHSUNDEEP
sql
JhPalmer
JhPalmer

Reputation: 1

SQL to list missing items or items that are not valid

I have a list of users and a list of roles. I'd like to extract reports of roles that users should have but don't and roles that they do have but shouldn't. For example

User,   Dept,  Role
Jane, Manager, Add
Jane, Manager, Modify
Fred, Clerk, Modify
Fred, Clerk, Inquire

Dept, Task
Manager, Add
Manager, Modify
Manager, Delete
Manager, Inquire

Clerk, Inquire
Clerk, Add

My query should indicate that Jane is missing delete and inquire and Fred should not have modify. Currently I'm getting many duplicates as whenver I say something like manager's task <> role's task it's including records such as Add <> Modify.

Any help would be appreciated

Upvotes: 0

Views: 149

Answers (4)

pilcrow
pilcrow

Reputation: 58619

My immediate inference from your question and data is that your tables are misdesigned, permitting ambiguity and error (missing or impoperly granted privileges) where there should be none.

However, working with what you have:

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;

Output:

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

Output:

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

A Better Question

I encourage you to ask the SO community in a separate question how you can refactor your schema to avoid this very circumstance (missing or improper roles).

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

Standard SQL-99, tested on SQL Server 2008 R2:

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;

Upvotes: 1

Austin Salonen
Austin Salonen

Reputation: 50225

The query below will not be performant on large data sets!!

You can determine all the possibilities with a cross join and then, with the right join, only care about those that are missing.

(Below is T-SQL with table variables that represent the example data)

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

Results:

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

(3 row(s) affected)

This query will then find the rows that have a task they likely shouldn't:

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

Results:

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

(1 row(s) affected)

Upvotes: 0

Dustin Geile
Dustin Geile

Reputation: 43

Starting with your user table, left outer join to your roles table on Dept -> Dept and Role -> Task. That way, if the user is not supposed to have that right, it will return null.

sample:

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

Upvotes: 0

Related Questions