One of my clients has a system that has grown organically over the past 12 years. Started with 4 tables and now has 152 (24 of them are remote ODBC). Some of the tables are rather trivial (for various reasons) but there is a lot of data in others. They only have 6 users at one site. Now we are adding 7 remote users.
The "rub" is that they are in a different country and the client wants restrictions on their account, and indeed all the accounts. The users in Canada will see, in specified tables, only records that were created in their privilege set. Same for the users in the US. This works but there seem to be some unexpected consequences.
eg. a standard FIND by a US user will fail in a search on the field "ReportingCountry" - which is the field that is used for access control. Their privilege set restricts record access to those where $$UserGroup (set at login First Window Open) matches the "ReportingCountry". This seems to break the standard FIND. I have coded around that with some scripts that run with Full Access but I wonder if this is best. Indeed, even as I type this I wonder if I can adjust the limited rights to view records so that $$UserGroup = "USA" and get(window mode) = 0. I fear that there are a lot of break points ahead.
What I am wondering if there is any white paper about the implementation of this type of security model. It would be great if this model has been "Blackwelled or Wimed" already.
I have been googling without much success - likely due to faulty search terms.