Flexible user role management

I’ve been working on my first AppSheet app and of course that meant reading through the vast amount of information in the documentation, samples and this excellent community. I thought that I might be able to contribute a little bit back and so here is the mechanism I’m using for managing user roles and permissions in my app.

It all starts with the Current_User slice approach described in the Current_User (Slice) - How to conform your app around WHO is using the app article created by @MultiTech_Visions so I won’t bother discussing that in detail here. Suffice it to say I have a User Table which has a User Email column and a Role ID column which is of type Enum referencing a slice called refUserRole.

My first step was to create a table (my data is in a Google Sheet) containing the user roles and their associated permissions. This can be maintained through a Table View.

User Roles Table View

This is a simple table list all the roles that a user can have (Role ID column) and then a simple Yes/No indicator to determine which items the roles has access to. It should be fairly obvious that it is easy to add additional rows to this table to create new roles. It is also relatively easy to add new columns if new data or UX items are added to the app in future. The table itself is very easy to maintain through Quick Edit.

For further reasons of future flexibility that I won’t cover here, I actually have the list of User Role vales in another table called Context Table, but it would certainly be possible to simply enforce a unique role name in this table.

The next step was to create a slice of that table to be referenced through the Enum field in the User Table.
refUserRole Slice
The row filter for the slice is as follows:

AND([Context ID].[Value] = "User Role",[Active],OR([Value]<>"Super Admin",INDEX(Current_User[User Role], 1)="Super Admin"))

Part of the reason for the slice is because I’m using that Context Table, but this also allows me to check that a role is active and that the Super Admin role can only be granted if the current user has that role. This is very important to ensure that lower level admins, who may well be granted the right to assign user roles, can not assign the top level unless they already have it.

Show If Constraints
With roles defined and assigned to users through the User Table, the Current_User slice can now be referenced for any view to determine whether the current user should be able to see it. For example the following constraint would only show a view if the role assigned to the current user includes a Yes in the permission column called Automation List:

Index(SELECT(User Roles[Automation List], ([Role ID] = Index(Current_User[Role ID], 1))), 1)

Another example, here is the restriction for the table view to maintain User Roles:

Index(SELECT(User Roles[User Roles], ([Role ID] = Index(Current_User[Role ID], 1))), 1)

You can see the code snippet is extremely easy to copy and modify for any UX element and from an admin perspective it means that all user access and permissions can be maintained through the app itself without having to have access to the development environment.

I hope this is useful to someone, I’m actually using a similar technique to determine which fields are required on a form based upon the status of the current record, but that is probably worth a separate post.

24 8 1,353
8 REPLIES 8

May i ask how did you do your UX?

 

 

The two screenshots of the original post show table views. The second is a table view in QuickEdit mode, which can be enabled per table:

Steve_0-1665003267339.png

 

tintin007
Participant II

Hi, 

New to appsheet but trying to get this to work and i am a bit stuck

AND([Context ID].[Value] = "User Role",[Active],OR([Value]<>"Super Admin",INDEX(Current_User[User Role], 1)="Super Admin"))

This formula has got me puzzled. 

Does the [context ID].[Value] refer to a table with a column [value] in it ? 

Does the [Active] refer to a column in a table ?

Does the Enum role id column in the user table the only ref column, Would this be enum with a base type Ref. 

 

 

 


@tintin007 wrote:

Does the [context ID].[Value] refer to a table with a column [value] in it ? 


Yes.


@tintin007 wrote:

Does the [Active] refer to a column in a table ?


Yes.


@tintin007 wrote:

Would this be enum with a base type Ref.


Yes.

More on this topic at  SUMMARY TIP: User permissions, roles, and settings - Google Cloud Community

Hi Dbaum

Thanks for confirming those. I am assuming the [Active] column was checkbox. and the [value] not sure if that was a checkbox in which of the tables.

 

 


@tintin007 wrote:

I am assuming the [Active] column was checkbox


Yes, per the following syntax values in the [Active] column must be the Yes/No data type.

AND(...,[Active],...)

@tintin007 wrote:

[value] not sure if that was a checkbox


Per the following syntax, values in the [Value] column must be the Text type.

[Context ID].[Value] = "User Role"

 

Thanks, I have a better understanding of it now, Just cant quite visualize what data is in the [value] column, would that be referring to the [Role ID] column

Yes, the original tip references an additional layer of abstraction that isn't explicated and isn't critical to the implementation.


@graham_howe wrote:

For further reasons of future flexibility that I won’t cover here, I actually have the list of User Role vales in another table called Context Table, but it would certainly be possible to simply enforce a unique role name in this table.


 

Top Labels in this Space