Re: help me join these tables
Date: Fri, 10 May 2002 23:30:31 GMT
Message-ID: <rKYC8.19388$RR3.10325_at_sccrnsc02>
[Quoted] Ditto Daniel's response.
Additional:
[Quoted] I'm not sure you realize the scope of the question you've outlined.
It is possible to read a lot into "corresponding 'role'". Without normalized tables and enforced RI, I have no way to figure out exactly what constitutes a complete and "corresponding 'role'"?
[Quoted] What I "think" the question means is: For all the rows of each User in tblUser_Access, all the values of column Screen_ID for that User must match all the values of all the Rows of Any of the "Roles" in tblRole_Access.
[Quoted] However, it could also mean: Any User with a Screen_ID value not found in tblScreen. This would to mean seem to indicate an "invalid role", after all, how can a role be valid if it contains a value that is not entered in the system? But if I do this, I won't even look at tblRole_Access, I'll only join tblUser_Access and tblScreen.
Can you see what I mean now? I can think of several other ways "corresponding 'role'" might play out. If I cange one word of the first interpretation to "all the Rows of One of the", then it completely changes the query, but this is based on the fact that I don't know if each user must have only one role, or may have many roles. If the tables were normalized with RI, then this would be obvious.
If I run the following Difference query, I find everone in tblUser_Access without a valid Screen_ID. Which, as I mentioned above, seems to be to indicate a User with an Invalid Role. By knowing those with Invalid Roles, we know whether all tblUser_Access rows have good Roles, and which Users are in error (if I've guessed correctly).
SELECT U1.UserName
,U1.User_ID
FROM tblUser_Access AS U1
WHERE NOT EXISTS
(SELECT S1.Screen_ID
FROM tblScreen AS S1
WHERE U1.Screen_ID = S1.Screen_ID)
For the results of
Jack 100
Mary 88
the two Users with bad Screen values.
Toss in DISTINCT to eliminate dups if you absolutely have to. If you're getting dups, then there are users who are assigned multiple screens not defined in the system. This is variously known as, "A bad situation." from a security standpoint.
When trying to determine whether the roles themselves as assigned to the Users are valid by checking the two tables against each other. . . it would be reverse engineering information that existed at one time, but was then lost by assigned Screens to Users instead of just assigning a Role to a User.
As for analysis of applicability of existing Roles, you could trying this:
TRANSFORM COUNT(U1.Screen_ID)
SELECT U1.UserName
FROM tblUser_Access AS U1
INNER JOIN
tblRole_Access AS R1
ON U1.Screen_ID = R1.Screen_ID
GROUP BY U1.UserName
PIVOT R1.Role_ID
To see how many screens each user has under each Role.
UserName 1 2
Jack 1 1 Mitch 4 2
Role 1 has 4 Screens, but Jack shows a count of 1. This tells us Jack is not properly assigned Role 1 (he's missing three Screens). Jack also has only 1 screen for Role 2, but Role 2 has 3 screens, and so Jack is also improperly set up for Role 2. Mitch has only 2 Screens for Role 2, and so is also improperly setup for that Role.
Or you could switch it around
TRANSFORM COUNT(R1.Role_ID)
SELECT U1.UserName
FROM tblUser_Access AS U1
INNER JOIN
tblRole_Access AS R1
ON U1.Screen_ID = R1.Screen_ID
GROUP BY U1.UserName
PIVOT U1.Screen_ID
Results:
UserName 1 2 3 4
Jack 2 Mitch 1 2 2 1
This tells us jack has Screen 3 assigned twice, under different Roles. Mitch has screens 1 through 4, but has screens 2 and 3 again, under a different Role.
However, I'm wandering way off course here . . . normalization is your best bet.
[Quoted] "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
news:3CDC0740.2B8CA567_at_exesolutions.com...
> Mitch Abaza wrote:
>
> > I know I should have clarified further. The User_Access table
represents
> > data imported from various Excel spreadsheets that detail the security
> > requirements for 500+ users. It is not a real table and is clearly not
> > normalized. All your suggestions are of course correct if I was
> > establishing a set of "real" security tables. But these tables are just
> > going to be used to help me figure out what users security requirements
> > (User_Access rows where User_ID = x) can be paired with existing Roles.
The
> > omission of Screen_ID = 4 was a mistake
> >
> > How can I write a query that would tell me whether all the User_Access
rows
> > for a given User_ID have a corresponding set of Role_Access records?
> >
> > Thanks
> > --
> > Mitch Abaza
>
> If it is not normalized ... I would write a stored procedure with
normalized
> output into another table. Otherwise you will be fighting this forever.
>
> Daniel Morgan
>
Received on Sat May 11 2002 - 01:30:31 CEST