complex SQL?

Question

complex SQL?

oliveraustin

SSC Veteran

Points: 236
More actions
January 23, 2014 at 11:42 pm

#75685

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 12 groups from a set of 12 groups.
A role can only have a particular group once.
I think a user may have more than one role, they probably shouldn't but they might. Example:
Role | Group(s)
Helpdesk | Password Reset
Security | Password Reset, Enable User
Reception | Enable User, Disable User
The data I have 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 see the users have the following roles User | Role
SmithA | Security
BloggsJ | Helpdesk
MouseM | Reception
I can modify the first table anyway that makes it more useful e.g split the groups over multiple rows such as the example below:
Role | Group(s)
Helpdesk | Password Reset
Security | Password Reset
Security | Enable User
Reception | Enable User
Reception | Disable User
I'm struglling with the SQL needed to build a list of User's Roles and have a horrible feeling I'm missing the obvious. I'm using MS Access and have tied myself in knots with joins and unions but they're going nowhere....this isn't a homework assignment 🙂 Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

You appear to be struggling with a common problem for new users - it is called "normalization" and determines the table structure. There is some background information you can check on the subject at WikiPedia or numerous other sites, but it is the foundation for relational databases, which includes both Microsoft Access and Microsoft SQL Server.

It seems to be more difficult for designers when the objects aren't physical things, which much of your data isn't. When you are dealing with Streets, and Streets have Houses on them, and you have People who live in the Houses, things are pretty obvious. But in your case the only physical thing is people. I would break your data down into three tables, one for People, one for Roles, and one for Groups. Thus a Person could belong to one or more Roles, and Roles can have one or more Groups. I think your current design is mixing the Roles and Groups together. (BTW, I assume your use of the term "LDAP" refers to the Lightweight Directory Access Protocol.)

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

oliveraustin SSC Veteran Points: 236 More actions · Answer 2

Hi Wendell,

Thanks for your reply.

The "table " structure can be manipulated in any way required. That's no problem,. The LDAP data is what it is though, 2 columns, user and group.

I work with simple DBs a lot and have studied the theory at graduate level (though a long time ago). The problem is that my mind is just blank when comes to how to tackle this problem.

Would you mind spending a moment to outline the solution you have in mind please?

Thanks

WendellB SSCrazy Eights Points: 8678 More actions · Answer 3

If the LDAP data is all you have, then how can you define a role? It would seem that there must be some sort of combinations of groups that define a role. How is that being determined? If it is based on a specific set of groups, then you could either create a separate linking table that says this role has this group, which would involve adding a fourth table with pointers between roles and groups. That is probably the most flexible arrangement. Another option would be to put 12 fields in the Roles table that point to the Groups that Role has, but that is less flexible.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

oliveraustin SSC Veteran Points: 236 More actions · Answer 4

Hi. I can structure the table of roles any way required. The thing i am struggling with is the actual sql that would give me a list of users and their roles from the ldap data. Is that something you could provide some pointers on please?

Thanks

WendellB SSCrazy Eights Points: 8678 More actions · Answer 5

You have the horse before the cart. You cannot write SQL that is useful until you establish the proper table structure. And the query builder in Access will do a very nice job of helping you with the SQL statement once you have the tables designed and some data in them. Also note that Access SQL is different from T-SQL which is different from ANSI SQL, so unless you take a course in writing Access SQL, you will find writing it in SQL mode much more difficult than using the designer.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

oliveraustin SSC Veteran Points: 236 More actions · Answer 6

Hi,

So as far as I can see it the only table I have "control" over is the one that links Groups to Roles and IMHO the most sensible way to structure that is two columns, ROLE, GROUP.

The LDAP is in the form USER, GROUP.

Please, if you know how the SQL to generate a list of user's roles would look I'd really appreciate it?

Don't worry about Access for now, I just put that in because it's the tool I was using in case it was relevant, I can do this in TSQL on MS SQL Server too, that's no bother.

Thanks

oliveraustin SSC Veteran Points: 236 More actions · Answer 7

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!