Complex SQL to see roles from groups? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Complex SQL to see roles from groups?

Author

Topic

olly
Starting Member

5 Posts

Posted - 2014-01-25 : 03:08:51

Hi,
First post.... I'm trying to map roles to LDAP group membership and hoped I could get some help with how to analyse this please?

A role consists of between 1 and x groups from a set of x groups.
A role can only have a particular group once.
A user may have one role pr more roles (or no role because the groups they have don't describe any role).

Simple sample data:

Role | Group
Helpdesk | Password Reset
Security | Password Reset
Security | Enable User
Reception | Enable User
Reception | Disable User

The data from the LDAP is of the form

User | Group
SmithA | Password Reset
SmithA | Enable User
BloggsJ | Password Reset
MouseM | Enable User
MouseM | Disable User

From that you can calculate the users have the following roles

User | Role
SmithA | Security
BloggsJ | Helpdesk
MouseM | Reception

I'm struggling with the SQL needed to build a list of User's Roles and have a horrible feeling I'm missing the obvious. I've tied myself in knots with joins and unions but they're going nowhere.

P.S. I've asked this question on two other forums and so far nobody has been able to (or has chosen not to) give a proper answer.
If the question's not clear please do say.

Thanks

olly
Starting Member

5 Posts

Posted - 2014-01-25 : 04:23:59

Solved it....I think...amazing what hot water on your head in the shower does.
a) count the number of rows for each role type from the roles table.
b) do the join on group between the role and LDAP table.
c) count the number of times a distinct user appears per role.
d) if that count is the same as the count of groups per role then it's a match.

I'll code it up later and see if it works, got to tile a roof now.

Part of the problem is that I was trying to do something impossible and/or unwarranted which was to isolate the "biggest" role a user had, by that I mean if they were a member of a role who's groups were a superset of another role then I was trying to only extract the superset role....not required!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-25 : 08:35:40

try this


select	ur.[User], ur.[Role]
from
(
	select	ug.[User], rg.[Role], Role_Cnt, 
		rn = row_number() over ( partition by ug.[User] order by count(*) desc, Role_Cnt )
	from	user_group ug
		inner join 
		(
			select	[Role] , [Group], Role_Cnt = count(*) over ( partition by [Role] )
			from	role_group 
		) rg			on	ug.[Group]	= rg.[Group]
	group by ug.[User], rg.[Role], rg.Role_Cnt
) ur
where	ur.rn	= 1

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-27 : 07:27:01

[code]
SELECT ug.User,rg.Role
FROM (SELECT DISTINCT user FROM user_group)ug
CROSS JOIN role_group rg
LEFT JOIN user_group ug1
ON ug1.user = ug.user
AND ug1.Group = rg.Group
GROUP BY ug.User,rg.Role
HAVING SUM(CASE WHEN ug1.user IS NULL THEN 1 ELSE 0 END) =0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources