User Roles and User Permissions for Access Table Data

[Sameera_De_Silv]

Hi,

I am new to App Sheets and creating a business lead entering and viewing app. Can anyone please guide me how to full-fill below requirement.

User role and restrictions requirement
Assign different user roles (admin, manager, supervisor, user) and assign different permissions.
Admin: Access all features, Views and menu items, Create all types of users
Manager: Create supervisor and user accounts, Add own leads, View and edit all leads
Supervisor: Create only user accounts, Add own leads, View and edit own and supervisor assigned user leads
User: View, Add and Edit only own leads

Note: I have created two tables in google sheets for user details and lead details

Thank you,
Sam

[WillowMobileSys]

Out of the box, AppSheep currently only supports Admin and User as roles for any whitelisted users. I have heard some buzz that they may have capability for more in the future.

Since you need more than the two roles, you will need to build and manage your own but it’s not hard IF you are using AppSheet’s user login feature.

In your Users table, add a column for Roles and a column for Email. When adding Users make sure that the Email column is populated with the email the user authenticates with to access the app.

Then it’s just a matter of grabbing the user’s Role and using that value for security checks throughout the app. To get this value the expression might be:

ANY(SELECT(Users[Role], USEREMAIL() = [Email]))

I have also seen some developers use a slice to make this easier in the app. For example create a Slice, maybe named Current User, with the criteria USEREMAIL() = [Email]. Since a Slice can be treated as a table, you can then get the role by this expression Current User[Role].

[Sameera_De_Silv]

Hi Willow, I tried to make this up for hours and hours and i didn’t slept properly for last 24 hours. I can’t fix it after trying numerus times. but i don’t want to give-up,

So i have decide to make it more simple for now in order to finish this project quickly. If you don’t mind can you please help me up.

New method as below

Only two user roles which “Admin” and “User” provided by app sheet in user section. I will allow one domain (under Gsuite) to so all the user under this domain will validate through google auth and login/ or maybe add users one by one by defining user role.

Permission : (same slice/UX will filter each lead and show based on Admni or User role)

1. Users: can add,edit,view only own leads
2. Admin: can add,edit,view all leads

From Appsheet user log/ monitoring I will audit the user activities later

Thank you,
Sam

[LeventK · Accepted solution]

@Sameera_De_Silva
You need to create your own USERS table with ROLES and ACCESS PRIVILEGES. As per this table, you can use LOOKUP() expression to locate any user’s role and access privilege to any table and set your table’s access mode and (if needed) security filter expressions to filter away data.

help.appsheet.com

User Settings: Appropriate Use and Limitations

When and why to use user settings.

help.appsheet.com

Limiting Users to Their Own Data

You can use Security Filters to control which records a user can see and update.

help.appsheet.com

Scaling Using Security Filters

Security filters can be used to limit the data fetched by AppSheet on a per-user basis.

help.appsheet.com

Limiting Users to Particular Tables, Views, and Actions

How to make sure that your users can see and do only what they need to

[Sameera_De_Silv]

Thank you LeventK, I will try this. Thx.

[LeventK]

You’re welcome. Please read the documentation and put your best effort and make your hands dirty a bit more with further trials. Provided you cannot manage it or you don’t receive a satisfactory result, you can always come-up with Qs. We are here to help and provide assistance to the best of our knowledge and experience. Learning Curve is a bit steep, needs some patience and trial&errors. Otherwise it will be just a copycat of one and you will possibly learn nothing at the end.

[Sameera_De_Silv]

Thx for the quick reply Willow. I will try this and update you soon. Thx again.

[Sameera_De_Silv]

Yes sure,. Totaly agreed. I will try again and update,. Thx.

[LeventK]

Perfect. You’re welcome and it’s my pleasure to be helped of.

[Sameera_De_Silv]

Hi LeventK,

I don’t have enough words to thank you. I was able to limit leads to user level: own leads only, manager level: all leads (view, add & edit).

This was the link you sent where I have found exactly what I wanted : https://help.appsheet.com/en/articles/1128148-limiting-users-to-their-own-data

Only thing to do now is create a different menu item for create manager accounts in app (MANAGER_TABLE). The problem I’m facing is when I create the menu item all users can see the “Create new admin” menu tab. I needs to make it accessible only for emails in MANAGER_TABLE > Email ID column users.

Can you please guide me how to hide a menu item based on the manager email column. All emails in “Email ID” column should able to view, add, edit specific “create manager” UX/ slice. It should not visible to other users.

Thank you,
Sam

[LeventK]

@Sameera_De_Silva
Each UX view has a Show_if property under Display. You can use a similar expression there to show/hide that UX view to certain user set. Very similar to showing/hiding data.

[Sameera_De_Silv]

I have used the same expression and got it done,. Thank you verymuch LeventK. Now its ready to deploy

[LeventK]

Perfect, congrats.