Use a separate User table for Record Level security

[Ella]

I have just been trying a different approach to separate my user table (user_id, u_username, u_password, u_userlevel, u_activated etc) from my client table (client_id, cli_name, cli_phone, cli_turnover etc) and started testing detail additions on a master/detail page. As you probably all know, I now realise that it is the field on the 'many' side of the master/detail join that has the 'one' side (Primary Key) inserted as the foreign key in the new detail record (just as in the MS Access parent/child join fields) BUT if record level security is also configured, and the detail table contains a target user_id field, then of course this also gets populated (derived from the user_id of the logged-in user). In other words, the user_id in the new detail record has nothing to do with its master table.

I was a bit confused because the help file demonstrates the simplest case of Employees and Orders - separating out the user table is not obvious. In a real-world application, many of the 'people' tables will be designed to store very different types of object (teachers, students, cleaners, contractors etc) and having a single multipurpose table would be very cumbersome. The basic constraint in PHPMaker is due to the fact that you can only refer to ONE User ID from ONE specific table for Record Level security. Having a separate USER table for user ids and other login details should enable a more flexible design.

NOTE: When you create a new User (eg a teacher) you simply have to ensure that her new user_id (PK) is also inserted as a Foreign Key into her personal record (teacher_id (A/I PK), tea_user_id (FK), tea_name, tea_qualifications etc) in the 'teacher' table - this would stop her seeing the other teacher records.

Of course there may be another approach to this issue but I will report back if I hit a brick wall. I look forward to any comments and hope it helps someone out of the fog.

[Ella]

Further thoughts suggest that there are still limitations on how a manager would see protected records that are owned by another's user_id. If I understand the system, it may be that the reportsto/parent id is one way around this problem ie every subordinate user can only see their own records but each user reports to a user id at the manager Userlevel. I'll play some more tomorrow...

[Ella]

Pleased to report that I have now got all this working in the fitness app I'm building (still v10). Here are a few more details:

User ID:
Just to recap, the whole point of this exercise is to separate my user table from any other specific 'people' tables for staff, clients, cleaners etc but still have User ID record level permissions. Otherwise you have to stick with one table eg employees that has to be used for all record level purposes.

User Level:
The 'User Level' and 'reportsto' features mean that a User with the User Level of Manager can see all the Trainers and their Clients' data; and when a Trainer logs in they only see their own Clients and data; and when a Client logs in they can only see their own private data.

Creating new users and clients:
There is a one-one relation between user and client: user.user_id (PK) is inserted into client.cli_user_id (FK).
When a trainer or manager activates a newly created User and then creates a new Client record, I use a lookup to insert the new user_id into the foreign key in the client table, in my case cli_user_id. To avoid accidentally reusing a user_id, I feed the client username lookup with a MySQL view (freeuseridsvw) based on the user_ids that are NOT IN the client table (thus the lookup only lists the unallocated or free user_ids, see filter below).

Different lookup displayed for List and Add/Edit:
The trick here is to use a standard User lookup for the client LIST and only use the lookup filter when adding or editing a client. Because the standard (ie non-filtered) lookup shows the username (from the user table) in the list of clients, I don't actually need to store a username (eg Ella99) in the client table.

I enable the view in the Lookup_Selecting event for the client table like this:

function Lookup_Selecting($fld, &$filter) {
(CurrentPageID() == "add" || CurrentPageID() == "edit") ? ew_AddFilter($filter, "user_id IN(SELECT user_id FROM freeuseridsvw)") : "";
}

The edit option will probably be removed since this is bound to create messed-up data.

Adding related data (eg body measurements) for a client:
When a client logs in and picks up their user id, they should only be able to see their own measurements (with User ID record level security). So when the trainer adds data to a client's new measurement record, the mea_client_id (FK) and mea_user_id fields must also be filled in. This is done on a simple add measurement page by showing a dropdown list of client's names (looked up from the client table) for the trainer to choose a client. This will insert the client_id as the main foreign key in the measurement table. The trick here is to also use Autofill on the same lookup to insert the cli_user_id (previously saved in the client table when the client was created) to populate the mea_user_id field for record level security to work.

Since username is stored in user and not in client, a simple view could be used to populate the lookup with client_id/username rather than the less unique client_id/first and last names.

It's also worth pointing out that I have not set this up for entering measurements in master/detail mode since the trainer always knows the client's username and can simply enter a new set of data on one simple add screen.

Anyhow, I have created specific tables for trainers, managers etc with whatever fields I need and I can still apply security to any of their related data.

I am looking forward to getting this working in Version 11 and, believe it or not, there are only two lines of code in the whole project! Good job EW.