Re: Defining Table Permissions

Daisy_Ramirez · 01-09-2020 03:20 PM

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?

Jonathan_S

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?

Steve

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"
)

Suvrutt_Gurjar

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

Daisy_Ramirez

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

Daisy_Ramirez

Worked perfectly Steve! A million thanks!

James_Fussell1

Is it possible to have an overriding condition to allow the Switch to work? Example: The Switch only works if you belong to the Blue Group?

Marc_Dillon

You mean like nesting a Switch inside of an IF expression?

IF( something = “Blue Group” , SWITCH(…) , “READ_ONLY”)

James_Fussell1

Correct. Can you help with that?

Marc_Dillon

Sure