Multi-Level Access , Hierarchy Access

[#1]

Hi,

I have a requirement of access rights.

Please consider the following scenario.

There is a top level person - Manager (say)

There are two senior employees under Manager.

There are two and three junior employees under two senior employees respectively.

Hierarchy as follows.

Manager

--- Senior Employee (1)

------ Employee (1)

------ Employee (2)

--- Senior Employee (2)

------ Employee (3)

------ Employee (4)

------ Employee (5)

So.. what I want is...

1 - Manager can see all work details of all senior employees and their junior employees.

2 - Senior Employee can see all details of their junior employees but not any details of other senior employee and their junior employees.

3 - Junior Employees can see their own details, not their sibling child.

Please give me idea, how to achieve this task. Can anyone assist me with some sort of demo data ?

Thanks.

Regards,

Sachin A.

[pat_agen]

hi Sachin,

questions:

how is your source data laid out? what is the table you are trying to apply security to? you say it is "details". Is this details for all employees (that is managers, senior employees and junior employees)? If so is there a key to this table ? (EmployeeID for example)?

how will you receive your hierarchy information? or is that for you to decide? is the hierarchy "ragged" or are there always the same 3 levels (manger, senior, junior)?

[#3]

Pat, let meanswer your questions –

My data source islaid out as follows –

I have one mastertable ‘Employees’ that contains demographic info (or state info) of allemployees. This includes the fields – employee_id, name, date_of_birth,address, phone_number.

As each managerand senior employee is also an employee of the organization, their demographicinfo is also stored in this same table.

I have anothertable ‘Employee Manager’ which maps each employee id with his manager’s id. So,this table has the fields –  employee_id and manager_id, with employee_idas the foreign key to the ‘Employees’ table.

I have a thirdtable called ‘Work Hours’ which contains the number of hours each employee hasworked, per day. This table has three fields – date, employee_id andwork_hours.

The abovehierarchy is somewhat static, with 3 levels.

In the light ofthe above data model, how do I achieve the requirements 1 through 3 above? Justto add, I am required to provide the access controls while dashboard viewing,as well as pdf reporting.

I have fullflexibility in editing the data model, and can add/remove fields  andtables if required.

[#4]

Hi,

Find the Attached Application.

Login with ADMIN userId.

[#5]

Sorry ,Forgot to Attach the Script for finding Hierarchy in the above Application.

Find the below Application which gives the Heirarchy.