Defining Table Permissions

Hello, I’m looking to define my table permissions by user role and tried IFS(). I’m a role “S_USR_ROLE_ADMIN” in the user table but it’s only providing read only access. Is the expression incorrect? Is there a better way of doing this?

I believe the way you wrote your expression it is checking to see if Role is also equal to email.
Thats why you are getting ready only access.

Did you try to use a AND expression in your formula and have 2 separate checks?

1 Like

ALL CHANGES and READ ONLY should be ALL_CHANGES and READ_ONLY. Note the underscores (_).

IFS(
LOOKUP(USEREMAIL(),"users","Email","Role")="S_USR_ROLE_ADMIN","ALL_CHANGES",
LOOKUP(USEREMAIL(),"users","Email","Role")="S_USR_ROLE_DISPAT","READ_ONLY"
)

Because of a quirk in the behavior of the is-equal-to operator (=), it’s possible a logged-in user that isn’t present in the users table or that has no Role assigned may be granted ALL_CHANGES access. When using the is-equal-to operator, it’s best to put a value that can never be blank on the left side, like this:

IFS(
"S_USR_ROLE_ADMIN"=LOOKUP(USEREMAIL(),"users","Email","Role"),"ALL_CHANGES",
"S_USR_ROLE_DISPAT"=LOOKUP(USEREMAIL(),"users","Email","Role"),"READ_ONLY"
)

In general, and especially when dealing with security, it’s good practice to define a default value in case none of your conditions match:

IFS(
"S_USR_ROLE_ADMIN"=LOOKUP(USEREMAIL(),"users","Email","Role"),"ALL_CHANGES",
"S_USR_ROLE_DISPAT"=LOOKUP(USEREMAIL(),"users","Email","Role"),"READ_ONLY",
TRUE,"READ_ONLY"
)

Your expression includes two identical LOOKUP() sub-expressions. LOOKUP() is expensive, so reducing its use would benefit performance. You could rewrite your expression using SWITCH():

SWITCH(
LOOKUP(USEREMAIL(),"users","Email","Role"),
"S_USR_ROLE_ADMIN","ALL_CHANGES",
"S_USR_ROLE_DISPAT","READ_ONLY",
"READ_ONLY"
)
3 Likes

An insightful response @Steve in all respects- especially for recommended use of SWITCH () for efficiency, use of default value in expressions and that catch in expression READ ONLY instead of required READ_ONLY

2 Likes

Outstanding stuff! Reviewing it closely now - thanks again Steve!

1 Like

Worked perfectly Steve! A million thanks!

1 Like