User Logins to Drill Down to Access Lower Levels (1 Viewer)

[themurph2000]

I'm sure this has been covered before, but I'm having a tough time with it.

I've created a database (with a lot of help from people online), that has a hidden form storing the UserID and Department of a person on a "hidden" form so this data can be used to filter out records specifically entitled to them. (In other words, they can only see their own employees). However, I erred in not realizing that some of their employees are bosses for other employees. Thus, a user would not only have to see the records of people that work for them but also of anybody that works for their people.

i.e. If Joan is the boss for Mike and Mary, and Mike has Moe, Larry, and Curly working for him, Joan would have to see Moe, Larry, and Curly's information, as well as Mike and Mary's, but Mary shouldn't see Moe, Larry, and Curly's information.

Right now, all I have is the list of people and their direct managers. (can be converted to ID's, obviously, if necessary.) As far as I can tell from the raw data, I would need at least 4 levels, the 4th being employees with no access (just regular workers, no bosses)

Any suggestions?

 

[gemma-the-husky]

i presume you have some self links in your employees table.

this will only work if your teams are in a true hierarchy tree, so that each employee stores a single manager to whom he reports (and the guy at the top has no master) or as you say, you have guys at the bottom with no manager - the key is that each employee can only have at most one manager.

now to do what you want, for any employee, you need to trace managers until you either get to the top, or find yourself!

so

A
   b
       1
       2
       3
   c
       4
       5 
   d
       7
       8
       9

so if you are manager b, for each item, find the owner and trace up

say owner 7, then trace 7-d-A - you havent met b, so you cant see this one.
but owner 1, trace 1-b - you find b, so you CAN see this one

--------
now a) this lends itself to a recursive algrithm
b) this will probably perform OK for single item reads, but will be slow for general use as a function in a query

but you need a function

can_i_access_this (baseowner) as boolean
that does this, starting with baseowner, and navigating up until you find the target manager, or reach the top without finding the target manager.

--------
i think the problem is, is that access can easily prevent access to a form at all, for members of a given user group, but its much harder to provide general access to the data, but selectively filter it.

 

[themurph2000]

i presume you have some self links in your employees table.

this will only work if your teams are in a true hierarchy tree, so that each employee stores a single manager to whom he reports (and the guy at the top has no master) or as you say, you have guys at the bottom with no manager - the key is that each employee can only have at most one manager.

now to do what you want, for any employee, you need to trace managers until you either get to the top, or find yourself!

so

A
   b
       1
       2
       3
   c
       4
       5 
   d
       7
       8
       9

so if you are manager b, for each item, find the owner and trace up

say owner 7, then trace 7-d-A - you havent met b, so you cant see this one.
but owner 1, trace 1-b - you find b, so you CAN see this one

--------
now a) this lends itself to a recursive algrithm
b) this will probably perform OK for single item reads, but will be slow for general use as a function in a query

but you need a function

can_i_access_this (baseowner) as boolean
that does this, starting with baseowner, and navigating up until you find the target manager, or reach the top without finding the target manager.

--------
i think the problem is, is that access can easily prevent access to a form at all, for members of a given user group, but its much harder to provide general access to the data, but selectively filter it.

I definitely need to study this, but I think I may have overthought it. Since I only have 400 employees and it does stop at 3 levels, I came up with a "so stupid it actually works" idea to operate from the level of the employee and link each employee with the ID's of the bosses above them manually. (well, a ****load of cutting and pasting). Then I can re-do the filter to accept an entry from any of the potentially 3 boss ID columns.

Thanks for the help.