table and security layout suggestions.

[AGairAdmin]

This is the basis for a multi company, multi teams "crew" unique to the company, the company's equipment and multi role user 

tables? = company, user, crew , equipment

i would need a unique user, (or user qualifier)  that can view, edit, delete from any table or column, like an global admin (our in app person or controller)

the rest of the users will only need to view or reference the user table and company table 

for the "crew" and equipment table= a user associated with a company who has a certain role can add, edit and delete, assign users to crews and equipment.

so.. 

a company can have employees who have different roles and then divide up the employees into crews and assign to equipment by an employee with a certain role that can add edit or delete in the table"crews" and "equipment" . the company and its employees do not need to edit add or delete data in the "user" and "company" tables 

any suggestions would be greatly appreciated 

[Kirk_Masden]

I haven't done this so, if I'm wrong, I hope someone will correct me.

I think you could set up your app so that views involved in global administration could only be seen by qualified users and that all other users would view or reference only the user table and company table.  

I would think that a display condition for the managing views could be a certain user ID.

[AGairAdmin]

would that still make the user's app contain other company info? 

i spent all day trying different security filter configurations under the idea of if the security filter for tables "user" and "company" to filter by company name since it is a common column on each table, then the users app would never download info from another company. 

it could be i need to completely rethink how to do the users and company tables,  i have 25% of the main app done on another instance and realized i needed to get the users figured out. 

[Kirk_Masden]

As I say, I have not made the kind of app you are working on so I'm not sure but I have a couple of thoughts.

1. If users cannot access the Google spreadsheet or other data source on the web, then I don't think they would be able to access the data you refer to, as long as the AppSheet app did not give them access to that table via a view.  However, perhaps skilled hackers would be able to find a way in.  I don't know about that.  I hope someone else can give us more reliable information.

2. Another alternative is to make a separate app for managers that draws upon the same data.  I don't think this should be necessary, though.
I hope someone else can respond.

[AGairAdmin]

You are helping a lot, ive been thinking myself into circles about this and a different perspective is very helpful for the thought process. your idea for a managers app, or "office" app separately. 

personally im really enjoying creating such a complicated app for an industry i understand inside and out, im just frustrated at my lack of knowledge for actual devo process but every day i am learning more about the process. 


curious

for the 2nd app idea , do you think just the "global admin" would need the 2nd app developed for or do you think maybe break it down further  to 3 apps , company users, company managers, and global admin?  


again this is a great sounding board and there are no bad ideas here! Thanks in advanced !

[Kirk_Masden]

I think 3 apps might be fine if you are comfortable with that.  I'm still hoping, though, that someone with more experience in this area will chime in.  🙂

[Steve · Accepted solution]

columns

companies
  company (key, text, required, unique)
  name (label, name, required, unique)
  admins (enumlist of ref to users from within company)

users
  user (key, text, required, unique)
  email (email, email, required, unique)
  company (ref to companies, required)
  name (label, name, required, unique within company)

crews
  crew (key, text, required, unique)
  company (ref to companies, required)
  name (label, text, required, unique within company)
  members (enumlist of ref to users from users within company)

equipment
  equipment (key, text, required, unique)
  company (ref to companies, required)
  description (label, text, required, unique within company)
  crew (ref to crews from crews within company)
  assignee (ref to users from users within company)

slice row filter expressions

my user
  (useremail() = [email])

my company
  in([company], my user[company])

my crews
  isnotblank(intersect(my user[user], [members]))

my equipment
  in([assignee], my user[user])

security filters

companies
  or(
    ("admin" = userrole()),
    in([company], my user[company])
  )

users
  or(
    ("admin" = userrole()),
    (useremail() = [email])
  )

crews
  or(
    ("admin" = userrole()),
    isnotblank(intersect(my user[user], [members])),
    isnotblank(intersect(my user[user], split(("" & my company[admins]), " , ")))
  )

equipment
  or(
    ("admin" = userrole()),
    in([assignee], my user[user]),
    isnotblank(intersect(my user[user], split(("" & my company[admins]), " , ")))
  )

tables are updates allowed? expressions

companies
  if(
    ("admin" = userrole()),
    "ALL_CHANGES",
    "READ_ONLY"
  )

users
  if(
    ("admin" = userrole()),
    "ALL_CHANGES",
    "READ_ONLY"
  )

crews
  if(
    or(
      ("admin" = userrole()),
      isnotblank(intersect(my user[user], split(("" & my company[admins]), " , ")))
    ),
    "ALL_CHANGES",
    "READ_ONLY"
  )

equipment
  if(
    or(
      ("admin" = userrole()),
      isnotblank(intersect(my user[user], split(("" & my company[admins]), " , ")))
    ),
    "ALL_CHANGES",
    "READ_ONLY"
  )

[AGairAdmin]

This was super helpful @Steve , after reading this i have a much better understanding of how to layout my tables and apply the security filters. thank you so very much.