My Rights table has the following structure:
RightKey CategoryKey RoleKey UserKey RightName RightCode AllowDeny
1 1 NULL NULL Add User sys_add_user 1
2 1 NULL NULL Edit User sys_edit_user 1
3 1 NULL NULL Remove User sys_remove_user 1
4 1 NULL NULL Lock User sys_lock_user 1
5 1 2 NULL Add User sys_add_user 1
6 1 2 NULL Edit User sys_edit_user 1
7 1 2 NULL Remove User sys_remove_user 1
8 1 2 NULL Lock User sys_lock_user 1
9 1 2 3 Remove User sys_remove_user 0
The rows with NULL in RoleKey and UserKey are default rights assigned to new roles. When a user assigned to a role, he/she
gets all rights assigned to the role, as in RoleKey 2. Finally, individual user rights can be assigned, as in user 3 on row 9.
I'm trying to pull the rights for a user, but as you can see, the Remove User row is appearing twice. The result set should overwrite
the Role level rows with User level rows.
Here's what I have:
SELECT rt.RightKey, rt.CategoryKey, rc.CategoryName,
rt.RoleKey, rt.UserKey, rt.RightName, rt.RightCode, rt.AllowDeny
FROM sec_Rights rt
LEFT JOIN sec_RightCategories rc ON rc.CategoryKey = rt.CategoryKey
WHERE (rt.UserKey = @UserKey OR
rt.RoleKey =
(SELECT RoleKey
FROM sec_Users
WHERE UserKey = @UserKey))
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people