Group By problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group By problem

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-07-08 : 14:20:42

Having a problem with a group by clause, and the case statement(using max and min). Basically I have a permissions table holding permissions, the problem I'm having is making permissions for individuals take precedence over group rights(a user with access should always have access if that user that rights specified in the usergroupright table). Here are the tables:

UserGroupRight (RightAllow 1 is allow, -1 is deny)


UserID   RightID   RightAllow
------   -------   ----------
1        1         1
1        2         1
1        3         -1
1        4         -1
2        1
2        2
2        3
2        4

UserGroup (UserType 1 is a group, 0 is a user)


UserID   Name    UserType
-----    -----   --------
1        John    0
2        Admin   1

UserGroupLine (GroupID relates to UserID in UserGroup)


UserID   GroupID
------   -------
1        2

AreaRight


RightID   RightName
-------   ---------
1         Access
2         Create
3         Modify
4         Delete

Here's the query without the precedence logic(this is just with a permissions returning -1 if the least is -1)

select ar.RightID,
ar.RightName,
RightAllow = (case when min(ugr.RightAllow) =-1 then -1 else max(ugr.RightAllow) end)
into #tmpright
from usergroupright ugr,
arearight ar
where ugr.RightID = ar.RightID and
(ugr.UserID in (select groupID from usergroupline where UserID = @UserID) or
(ugr.Userid = @UserID))
group by
ar.RightID, ar.RightName

any help?

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-07-08 : 17:22:08

Not exactly understanding your data-model but here's an idea:

SELECT MAX(u.maxRight)
FROM users INNER JOIN
(SELECT userId, userRight AS maxRight FROM users
UNION
SELECT userId, groupRight AS maxRight FROM groups INNER JOIN usersGroups ON usersGroups.groupId = groups.groupId) AS u
ON users.userId = u.userId
WHERE users.userId = ?

The idea is to create a combined list of users direct rights and indirect rights (inherited from group) then select the maximum right for a given user.

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-07-09 : 13:24:18

is there a way to have user rights take precedence over group rights(if group rights exist?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources