Limit record access by Account (many-to-many)

[sirius25]

I'm building a solution to allow users to access certain client records. Ultimately I will be need to do this in another FileMaker application with existing client records, but for the time being I'm working with a streamlined proof-of-concept application.

Multiple users need access to the same client record, and users also need to access multiple client records. For example, User1 needs access to ClientA and ClientB; User2 needs access to ClientB and ClientC.

I have attempted to do this via two additional tables: "user" and "userClient", a "UserClientIDs" value list, and a "RestrictedAccess" privilege set with custom record access.

I am having trouble getting the client records to filter properly using FileMaker security.

Tables

"user" table: The "user" table contains a record for each restricted user, storing the name of the FileMaker Account.

• pkUserID: unique ID
• UserAccount: stores the name of the corresponding FileMaker Account

"userClient" table: The "userClient" table is a join table for creating a many-to-many relationship between "user" and "client" tables, for filtering user-client access.

• pkUserClientID: unique ID
• fkUserID: corresponds to "pkUserID" in the "user" table
• fkClientID: corresponds to "pkClientID in the "client" table

"client" table: The "client" table contains client information.

• pkClientID: unique ID
• ClientName: the client name (representing real information about the client that users need to access)

Value List

"UserClientIDs" value list: from "userClient::fkClientID"

Privilege Set

"RestrictedAccess" privilege set with the following record privileges:

• user: limited view access: UserAccount=Get(AccountName)
• userClient: limited view access: user::UserAccount=Get(AccountName)
• client: limited view access: Position (ValueListItems (Get (FileName); "UserClientIDs"); pkClientID; 1; 1)
  • (Note: the UserClientIDs value list gets filtered via the userClient limited view privilege)

For testing the problem I'm encountering (see below), I also created a second privilege set with the same settings for "user" and "userClient", but with a different setting for client, that doesn't utilize the value list:

• client: limited view access: Position (ExecuteSQL ("SELECT DISTINCT uc.fkClientID FROM \"userClient\" uc";"";""); pkClientID; 1; 1)

The behavior when using this privilege set is identical to the first privilege set.

The Problem

The limited access for the "user" table works; restricted access users can only see their own "user" records.

The limited access for the "userClient" table works; restricted access users can only see the "userClient" records associated with their "user" record (and so only the corresponding fkClientIDs for the clients they're authorized to view).

The limited access for the "client" table is not working; restricted access users are able to view all "client" records; both those associated with their "user" record (via "userClient"), but also records that should be restricted to them.

A Strange Thing...

Showing all records results in "<No Access>" being displayed for restricted "user" or "userClient" records, but all of the "client" records are displayed. However, if I run a script with an empty "Perform Find []" (from this topic), the non-authorized "client" records are omitted (as are the <No Access> "user" and "userClient" records).

Also, an expression for "Position (ValueListItems (Get (FileName); "UserClientIDs"); pkClientID; 1; 1)" in the Data Viewer shows "1" when selecting a client record that should be accessible to the currently logged-in user, and it shows a "0" if selecting a different client record.

So, according to the Data Viewer, the restricted client records should not be viewable. And even the "Perform Find []" script that omits the <No Access> records properly omits the restricted client records. And yet they are visible to all users.

Can somebody help me with this? Is this even possible to do using FileMaker security? I can upload the test app if that will help.

[Ocean West]

I may recommend:

https://github.com/DelfsEngineering/FM-HAM

 

 

[sirius25]

I uploaded this to a GitHub repository.

22 minutes ago, Ocean West said:

I may recommend:

https://github.com/DelfsEngineering/FM-HAM

I'll check that out. Meanwhile, I'm still curious as to what's happening in my FM application. It seems like this should be simple.

[comment]

I believe the correct way to limit the users' access to the client table would be by:

not IsEmpty ( FilterValues ( Get ( AccountName ) ; List ( user::UserAccount ) ) )

I don't know if your approach using a value list is feasible - but even if it is, it would be an unnecessary complication. BTW, even the join table might be an overkill; unless you need it to produce a report of clients by users or similar, you might settle for a checkbox field in one of the "main" tables.

 

Edited April 16, 2022 by comment

[sirius25]

After exploring HAM, I don't believe that it will help me. HAM eases the management of custom account privileges, but the logic for those privileges still needs to be created, and that's where I'm running into problems.

[sirius25]

On 4/16/2022 at 12:43 PM, comment said:

I believe the correct way to limit the users' access to the client table would be by:

not IsEmpty ( FilterValues ( Get ( AccountName ) ; List ( user::UserAccount ) ) )

I don't know if your approach using a value list is feasible - but even if it is, it would be an unnecessary complication. BTW, even the join table might be an overkill; unless you need it to produce a report of clients by users or similar, you might settle for a checkbox field in one of the "main" tables.

 

This was the simple answer I was looking for, thank you! I do need the join table, or else some other way to keep track of which users are allowed to view which clients.