Record level security with a hierarchy

[KornGeek]

I am developing a database that will store (among other things) employee performance evaluations. I have implemented user-level security and all is well (so far).

The customer has told me that they want to restrict access to the evaluations in the following manner.

1) Employees cannot view their own evaluations on the system, or any other evaluations except as described below.

2) Managers can view evaluations for all Employees who report to them. In most cases, these will be evaluations that the Manager created, however if a new Manager comes in, they need to be able to see the evaluations done by the previous Manager.

3) Managers can view evaluations that can be viewed by those Managers who report to them. This is recursive, allowing the CEO to view all evaluations.

As an example:
The Sales Manager for Office A can create and view evaluations for all Sales Employees working out of Office A. The Accounting Manager for Office A can create and view evaluations for all Accounting Employees in Office A. The Accounting Manager cannot see the evaluations for the Sales Employees.

The Regional Sales Manager for Region 1 can create and view evaluations for all Sales Managers in Region 1, as well as view the evaluations for all Sales Employees in each Office in Region 1.

The VP of Sales can view create and view evaluations for the Regional Sales Managers, as well as see the evaluations for all Sales Managers and Sales Employees. The CEO can create and view evaluations for all VPs, as well as see the evaluations for all Employees.

At this point, I am not even sure where to begin with how to set this up, and I am open to any ideas that you may have.

 

[jsteph]

I think the direction in which to start is by completely locking down all access to the tables in question.

Then create queries with 'owner access option'. These queries are the forms' base recordsource.

The form's can then have a filter set in code that filters by the logged in group/user's set of records. The relationship between a user and what he can see is something you'd build and put in a table, then in code you parse the table and collect the value(s) that make up the criteria of which records can be viewed by that logon user.

This way, you completely control the access to records via forms. There are ways in which you can prevent the user from opening the forms' base queries on their own as well, not limited to simply marking them as hidden.
--Jim

 

[psemianonymous]

I would put security so each manager can only see the evaluations he/she did. Make an entirely separate interface for the executives so they can (for example) run a report on all positive comments so they can give inspirational messages or whatever.

But don't bother with the "Celko tree" (google for it) that you're trying to build; it's going to be difficult to build and difficult for them to set up properly. They probably don't need it.

Anyway, everything's negotiable. Offer them this reduced solution and the "full solution" they requested. I don't think they know how difficult the task they've asked of you is.

Just my recommendation; don't take me too seriously. --Peter

 

[MichaelRed]

The employee:supervisor relationship process is well documented, and only requires a single table with EmpId and SupId to completly document an oprganization hierarchy. This has (recently) been discussed in several threaads here (tek-tips). Permitting access to revords based on the hierarchy shuold not be so difficult, once the hierarchy is set up, as the permissible reecords are easuly identified by the hirearchy.

Maintenance of the hierarchy, however becomes a truly important task and you would need to design and implement a easy to manipulate user interface with all of the necessary business rules of the organization (perterinent to the management of the hierarchy) built into the interface (form). Then the only ones with access to the form would (obviously) be in the position to avail themselves of what-ever information, so there is the additional business "rule" of who would have access to the interface itself? Would this be available to the HR department data entry clerk? Only the CEO? Somewhere between these? Should supervisors be able to use this interface to change the status of employees below them?




MichaelRed

 

[dilettante]

There is no way you are going to have the access security you need using this model of application development.

Whether your database is Access, SQL Server, Oracle, or whatever you have problems in a simple model such as this because you need to grant users read access to things you don't want them wandering through and even write access to things you wouldn't want them to be able to randomly scribble into.

Security and access requirements more granular than table-level and field-level rights will be even more problematic.

These are some of the reasons n-tier application architectures were developed. The bad thing for you may be that your client application development tool may be Access.

A VB DCOM approach or even a web approach would offer you better options. Of course the other downside to this (besides needing new tools) is that you'll need an application server to run the mid-tier logic on. A simple file server won't suffice.


Right now no matter what level of sophistication you try to develop using Access security you'll have some serious exposures. What keeps the innocent (or the sneaky) user from just opening the database (back end or no) with MS Access and viewing/polluting the data? Sure there are a few hacks that can limit this, but I don't think any of them are secure.

You need a middle tier here.

Not only will this buy you finer and more robust security, you'll gain in database integrity and scalability too. As far as I know row-level security wasn't even added to SQL Server 2005, let alone Access 2000/2002.

 

[KornGeek]

After reviewing the suggestions here and sketching out a few ideas of my own, I came to the conclusion that it would be impractical to implement this as originally described. Between the time to develop this and the effort to maintain an org chart in this database, the cost to the customer outweighed the benefits. I pitched an alternative to them, which they accepted.

Each evaluation record will have a field for the Owner of the record. This will default to the manager who creates the record. Owners will be able to view the evaluations they created. If the manager leaves and a new one comes in, the Owner field can be edited to allow the new manager to have access to the data. Also, a Group is being set up under Access security. Membership in this Group allows a user to view all evaluations (essentially, this is for the CEO). This gives them most of what they wanted with considerably less development time and effort to maintain the system data.

 

[psemianonymous]

Does this mean my suggestion won? --Peter

 

[KornGeek]

Yep, the solution you suggested is pretty much what is being done. However, instead of a separate interface for the managers, they will use the same interface, but have access to more records.

 

[dilettante]

What keeps users from opening the MDB and just walking through every record of any tables they have access to?

 

[jsteph]

What keeps users from opening the MDB and just walking through every record of any tables they have access to

This is why I suggested the Owner Access queries. In this approach all tables are locked completely, and the queries are the recordsource of forms, so all control can be handled in form's code. The queries themselves can be kept from users fairly easily.
--Jim

 

[KornGeek]

I have implemented user-level security which ensures that users open the database using the correct mdw. Further, I have blocked most avenues for accessing the database window. There is still a way to get in and do further development. There are ways for unauthorized users to get in, but it will keep out all but the very determined.

Access security isn't perfect, but management doesn't expect people to attempt to bypass it. It's mostly to keep out those who might wander into the wrong areas.