Record Level Access via portal

[smartin73]

Hi all,

I'm am familiar with Record Level Access and have it implemented in a few solutions previously (really cool stuff). I'm running into a roadblock though in my latest project. I have a database where multiple users will need access to multiple records (and not see others). For example, Mary has access to accounts 3,6,7. Danielle has access to accounts 2 & 5. Carol has access to records 1,4,8,9...etc, etc.

This solution has a user table in it plus a portal (to a table called access) that connects the accounts to the user table. Displaying the hypothetical access via a portal was easy. I just for the life of me can't figure out how to the restrict access so staff can't see what they don't have access too.

I've search the forum and found a bunch of stuff on record level access but nothing like this. Any help would be greatly appreciated.

[bcooney]

The table that you are trying to limit access to is called Accounts? If so, what field in this table will help you to determine which users have access to it?

For example, if in the Accounts table, you have a field, GroupID_Created, that is equal to the GroupID of the Staff member who created it, then you can limit view/edit access by this.

Need more info about how you know that this record should only be seen by Mary, for example.

[smartin73]

Sorry I wasn't clearer. Yes, I'm trying to restrict access to Accounts.

Ideally, the staff member responsible for assigning the permissions to Accounts will choose from a drop-down created from a value list based off the user table. It could be 1 staff member assigned to an account or it could be 4, there is no standard (and given this is grant based work, there will not be).

The end result is to have an easy way to manage and restrict record access based on consistently changing access privileges. Does that clarify it a bit more?

[Steven H. Blackwell]

First, welcome.

Second, you're going about this in a very convoluted and insecure fashion.

Just treat the records with the same RLA process that you normally would do. The fact that they are seen through a portal will not affect the RLA restrictions.

Steven

[smartin73]

Thanks for the welcome.

I'm curious how & why you think is this insecure? In the end, I want FileMaker to be able to handle the RLA restrictions, but only based off of user input.

I would tend to think that none of us here would want to have to change RLA every time the access changes, which could be every day. Agreed? :qwery:

[bcooney]

I want to state something just to clarify. When you say "Accounts" you mean a table so named, and it really has nothing to do with the file's Accounts and Privs, right?

"Ideally, the staff member responsible for assigning the permissions to Accounts will choose from a drop-down created from a value list based off the user table."

And, what's in this value list? StaffIDs?

"It could be 1 staff member assigned to an account or it could be 4, there is no standard (and given this is grant based work, there will not be)."

Hmm..how do you assign more than one staff member to the Account record with just a drop down list?

[Vaughan]

" I have a database where multiple users will need access to multiple records (and not see others). For example, Mary has access to accounts 3,6,7. Danielle has access to accounts 2 & 5. Carol has access to records 1,4,8,9...etc, etc. "

There is multiple uses of the term "accounts" being used which confuses matters. I'll assume that in the quote above, "account" refers to a customer.

I'd suggest setting up each customer account record with a field that allows the entry of the users that can access that record. Entry multiple users paragraph delimited. A checkbox set would be adequate.

In the database, set up the record level access (RLA) so that access is granted to each user only if their id is in the users field.

This does not require the RLA calculations to be changed on a daily basis, but it does allow the customer account records to be assigned to different people.

The assigning of customer accounts to users may have to be done by a user with admin privileges because normal users will not be able to see any record that they are not already assigned to.

[smartin73]

Sorry again for being short with details and tone.

I was able to figure this out, but I wanted to clarify all points and document for how I figured it out. I also wanted to see what everyone thought of my solution.

"Accounts" refers to the table name and not to Accounts and Privileges. The value list does contain staff id's.

There is a "Staff List" table that contains first, last, id, and AccountName.

I have a table called "Access". It relates to "Accounts" via a field called accountID. It's basically used exactly like a lines items table via "Accounts". Due to this it contains a few needed fields plus a calc field of AccountName (it holds the AccountName stored in "Staff List" locally inside "Access"). "Staff List" is related to "Access" via staffID.

At this point, all of this really means nothing towards RLA yet - b/c like I said, it looks just like standard line item database. This allows an administrator to add and/or remove staff associated with the record.

In Acct&Prv, I have a specific Privilege Set for our administrative staff with a bunch of externally authenticated accounts attached to it.

I tried at first with no real success to simply limited the "Accounts" table with RLA, but it never restricted the correct records.

So here is how I got it all to work.

I restricted "Access" with the following restriction:

Get ( AccountName ) = acct_name

and I setup "Accounts" with the following restriction:

Access::account_id = account_ID

I tested late this afternoon and it appears to have worked. I was seeing the records I was supposed to have access to and then added and removed my access to other random records (as the admin). I then logged in as the user was was able to what I supposed to see each time.

What do you guys think? Believe it or not just talking it through made a huge difference. Thx.

[Steven H. Blackwell]

I'm curious how & why you think is this insecure?

Becasue you appear to be using a table to control privileges. Such tables can usually be hacked very easily.

Steven