Jump to content

Possible to limit record access based on username?


This topic is 1926 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello,

I have read through the documentation that I've been able to find and not found an answer to this question.

I am running FMS9Advanced on OSX Server, publishing a database using IWP. I am authenticating users via Active Directory. All works great.

I have a table containing profiles for each user, which includes a username. The profiles are then related to entries in another table via primary key.

What I am trying to do is dynamically set permissions for users based on their username. So for instance, if a user logs in as Jsmith, the database would restrict access to only records related to that users login. (the logins are unique). I know this could be done by creating a layout for each user but that seems pretty kludgy.

Any ideas?

Thanks in advance.

Link to comment
Share on other sites

The users table is distracting you.

Each record needs a field called RecordOwner that auto enters the account name of the creator. The Account Name, not the user name. In the Privilege Set definitions then you simply restrict viewing records to those whose owner matches the Get(AccountName) of the current active account.

This carries over into IWP.

HTH

Steven

Link to comment
Share on other sites

You will likely also want to script Show All Records, as it will otherwise show the no access records (as no access). Users will still be only able to see their own data, but it looks awful. Do a scripted find on a "constant" field (like =1), or the account name field itself, using Get (AccountName), instead. All other Finds are handled automatically.

Link to comment
Share on other sites

Users will still be only able to see their own data, but it looks awful.

You can now use Conditional Formatting to set those records to white or whatever the background color is. That doesn't remove them, but it does take away the <> display badges.

Steven

Link to comment
Share on other sites

  • 2 years later...
  • Newbies

You will likely also want to script Show All Records, as it will otherwise show the no access records (as no access). Users will still be only able to see their own data, but it looks awful. Do a scripted find on a "constant" field (like =1), or the account name field itself, using Get (AccountName), instead. All other Finds are handled automatically.

Indeed, this is an elegant work around as one cannot search on a variable (FMP searches on the variable name such as Get(UserName), not value). One additional piece of information - one needs to set the "storage options" (from the calculation dialog box) to "Do not store calculation result -- recalculate when needed". In this manner, it changes with each user automatically.

Link to comment
Share on other sites

Indeed, this is an elegant work around as one cannot search on a variable (FMP searches on the variable name such as Get(UserName), not value).

Correct. But instead create a calculation field -- let's call it View:

OwnerID = Get( AccountName )

Use this field to perform the finds, and instead of the "constant" field that Fenton uses for the Show All Records.

Link to comment
Share on other sites

Indeed, this is an elegant work around as one cannot search on a variable

Actually, one can:


Enter Find Mode []

Set Field [ YourTable::CreatedBy ; Get (AccountName) ]

Perform Find []


 

However, this is not necessary. As Fenton pointed out, ANY find will automatically omit <no access> records. So you can do a bogus find like:

 


Perform Find [ Find Records; Criteria: YourTable::SerialID: "*"]

as a replacement for Show All Records.

Link to comment
Share on other sites

  • 1 year later...
  • Newbies

My criteria is slightly different.  I am building a database that users need to access records according to an "or" criteria.  Records are assigned two criteria: a creator and a co-packer. Some vendors are both, so they need to view records that they are assigned in one or both of those fields. I have found some methods that would use script in privilege set, but I'm not able to wrap my head around changing it to evaluate multiple fields in the record.  

 

Any ideas out there?

Link to comment
Share on other sites

Don't use two fields; just use one maybe called Role.  Then in Record Level Access, try something such as this:

 

Exact ( Role ; "Creator" ) or Exact (Role; "CoPacker")

 

Then the record can be viewed, or edited, or whatever the restriction is if the value in the field Role is either Creator or CoPacker.

 

Be careful how you set the value in Role, and be sure to protect it from unauthorized changes.

 

Steven

Link to comment
Share on other sites

  • 5 years later...

Anything I need to know that is newer info from this old post? Have an fm17 solution that we will want to have sales reps only able to see records (contacts/leads/jobs) that are assigned to them. Thanks. 

Link to comment
Share on other sites

Not really. Google FileMaker RLA or Record Level Access and you'll find many posts about it. It's fairly straightforward when you have one person assigned to each record.

Link to comment
Share on other sites

  • 2 weeks later...

We are very familiar with limiting access/edit-view privileges to records, layouts, etc.. @Fitch in our case, records will possibly have more than one sales rep assigned though. (Another issue needs addressed as currently it's the FK ID of the lead/job record for the main rep, but sometimes the jobs gets split between two reps. That's another post lol) 

I'm not sure if we are going to need it, but we added a z_recordAccess field to the table.  Maybe, instead, we were thinking that the show all could be based on the fact that either z_createdBy = Get (AccountName) but also need to allow access to the record if the office entered the lead and assigned it to the rep, so could be:

z_createdBy = Get (AccountName) OR Sales Rep = Get (Account Name). 

{We combined users and personnel in to one table for this very reason. And our user names are in fact Name Full of the Personnel table, so the Sales Rep and the Get Account Name would  be the same in our case}

Any feedback on this would be greatly appreciated. I'm going to be a standstill on the project soon without this resolved. Thanks. @Steven H. Blackwell Do you have any input on this? I remember you being quite the Security Pro! 🙂 (I have your old Security book, much was over my head.  Any new ones on the horizon?)

Edited by Amy S
Link to comment
Share on other sites

Your calculation is fine, no need to second-guess it. It's a little more cumbersome with multiple sales reps per record. E.g., if they are stored as a list you'll need to use FilterValues, or PatternCount, or Position in your calculation. No big deal, but it will affect your Find speed if you have a ton of records.

  • Like 1
Link to comment
Share on other sites

On 12/17/2018 at 11:48 AM, Amy S said:

in our case, records will possibly have more than one sales rep assigned though. 

Any feedback on this would be greatly appreciated. 

Hi Amy,

So you have a single job record with a field holding multiple reps assigned?  It might help to see your structure because it sounds like a join table would be worthwhile.

If you ever wanted to generate a report by sales rep, showing their jobs (for example), you would be unable to do so since a single job record could not appear in two sub-summaries (reps).

If you use a join table, reports would be generated from there and the parent job would display under all reps.  If I'm not understanding your structure and your reps are not multiline in single field, you can ignore my response. :wink3:

Edited by LaRetta
changed "join field" to "join table". I figure everyone understood but I wanted to clarify.
  • Like 1
Link to comment
Share on other sites

What Fitch and LaRetta said.

A side note:

9 hours ago, Amy S said:

our user names are in fact Name Full of the Personnel table

That might be good enough for the initial user name suggestion - but sooner or later you will get two people with the same name.

Link to comment
Share on other sites

1 minute ago, comment said:

but sooner or later you will get two people with the same name.

Or a woman will get married and change her name and all her records will break. 😃

Link to comment
Share on other sites

As far as names, I figured the FK ID would be what I tied to not the name field. I always tie to the ID's (Get Number UUID)

But I have more research to do. Such as the join tables. I thought my invoices were using Join Tables but I guess they aren't true JT.  Thanks for the input folks. 

Link to comment
Share on other sites

Amy,

I typically have a User's table, and on Startup, find by User::AccountName and grab the CurrentUserId. Then, my RLA would use a calc, ie,  ValueCount ( Filtervalues ( Table::ListofAllowedUserIds ;  CurrentUserId) ) > 0. 

How you populate the field that holds the ListOfAllowedUserIds is up to you.

Link to comment
Share on other sites

This topic is 1926 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.