reassigning access to DB users to reflect changing company structure

[adgesap]

Hello,

I look after a database of employees for a company that is undergoing massive restructuring and I'm going crazy trying to rework the views/permissions for different users.

Background:
The company divides the country into regions and each of the regions is subdivided into separate offices. Previously, each office was lead by a general manager, whose PA was able to see all the employees within that office. (Basic view was to allow PA to see everybody who had the same cost code as themselves) So far so simple:

Now:
We're reorganizing into a more vertical alignment, with the general managers (and respective PA) becoming a "Head" of different vertical channels within each region. To add to the complication, the vertical channels are being grouped within "Districts", but each channel is divided into different districts. ie:

Assume there are 10 offices per region (A,B,C,D,E,F,G,H,I,J)
For the sales channel, there may be two districts, each with a Head and PA (who needs access to the right people in the database):
Sales Head 1 looks after sales people in A,B,C,D,E
Sales Head 2 looks after sales people in F,G,H,I,J

BUT

Administration Head 1 heads the admin teams for A,D,F,J
Administration Head 2 heads the admin teams for B,C,I
Administration Head 3 heads the admin teams for E,G,H

How do I control the views for the different PAs, so they only see the right people in the right channel and office(s). I'm trying to avoid the solution where I need to create a separate view/permission for each PA.

 

[ceh4702]

Well you could use security Groups and put each person in two different groups Like a District Group and a Channel groups and base access upon the group access they have. I do not think there is an easy way to do this. It will created a Security nightmare every time someone is hired or promoted or applies for a vertical or horizontal job position it will create havoc on the Security classifications. Personally, I dont see why you have to hide from everyone what everyone else is doing.

In the end the assignment of the people in the gourps would have to be managed by someone or by request form the channel and district leaders, or by request from HR or some other method. IT personnel can only assign people to a group. People in IT dont manage the personnel. This is one thing you have to get set before you attempt such a task. You have to let people know that you are not keeping track of personnel and it is their responsiblity to request the changes as needed. You probably dont want to grant security access privliges to manage groups to district leaders or channel leaders.

Another option could be one set of views for channels and one set of views for districts. Then you would need a field for channel and district or some way to look it up like an HR position or joining table. Or you may just need a way to look up the Channels and incorporate that into a view.

What if salesmen are selling for multiple channels?

If you do not like my post feel free to point out your opinion or my errors.

 

[Thargy]

adgesap,

this problem looks tailor-made for roles.

These are an ideal way to grant complex permissions simply.
By creating granular roles, you can "dice and slice" your permissions easily. Does your RDBMS support roles?

I use Oracle, and I know that it does.

Regards

Tharg

Grinding away at things Oracular