Re: help me join these tables

On Fri, 10 May 2002 23:30:31 GMT, "Chris2" <indanthrene_at_NOSPAM.yahoo.com> wrote:

>Ditto Daniel's response.
>
I have been through several security implementations. Based on my experience I second Chris's comments. You do NOT want to define users access at the screen level. You may also want to consider that some users may be granted view only access to screens on which other users have update access.

>
>Additional:
>
>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'"?
>
I you must make this determination you could do a procedure to compare each users access against the roles defined in the system. In doing this I am assuming you have a normalized definition of role consisting of a relationship between role and screen. Can a user have multiple roles.
>
>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.
>
Validation consists of two queries run for each user both of which must return zero rows. It might be possible to build a complex correlated query to do this, but a procedure is probably cleaner. I think the procedure might loop over each role for each users determining

Screens not in Standard Role:

   	User's Screens for Role
	MINUS
	Standard Screens for Roles

and
Screens missing from Standard Role:

	Standard Screens for Roles
	MINUS
   	User's Screens for Role

If both these queries are empty you have a full and complete assignment of the role

You might want to insert results into a results table for reporting.

>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.
>
This should be the target of this whole exercise.

 If a user's access reflected in the spreadsheet correctly reflects the security requirements, and does not match the Standard role then you have discovered an alternate role. You shouldn't have a lot of these.

If few users of a role match a standard role, then the standard role is probably incorrectly specified.

Although I would discourage it, minor variations from standard could be accomplished by a table relating to the user directly to screens. This can used to indicate screen blocked for the user or added to the standard role.

>
>"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
>> >
Loading this data into normalized working tables would likely make the above comparisons much simpler. You could then do the required queries as a series of SELECT ... MINUS SELECT ... queries. I would pair the queries showing missing data in both directions. For example (Defined screens MINUS Used screens) and (Used screens MINUS Defined Screen).

Bill Thorsteinson

>> > 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 - 17:04:15 CEST