App-level security control - turn off for everyone

mateoc15 · 02-24-2023 04:57 PM

I'm trying to find a good way to limit access to my app by time - an hour range. I created a user setting for start and end, and my calculation is too expensive at a row level I guess, so I'm looking for alternatives. The APP_start_time and _end_time are TIME user settings that are NOT seen by the user, basically just constants set by the developer to be used at the global app level.

The logic basically is if you're an admin or currently it's between the hours of X and Y, you're good.

OR(
  IN(USEREMAIL(),Admins[Email]),
  AND(
    (TIMENOW() > [_THISUSER].[APP_start_time]), 
    (TIMENOW() < [_THISUSER].[APP_end_time])
  )
)

Error: Data table 'Employees' is not accessible due to: Ran out of resources evaluating: OR(IN(USEREMAIL(),Admins[Email]), AND((TIMENOW() > [_THISUSER].[APP_start_time]), (TIMENOW() < [_THISUSER].[APP_end_time])))....................................................................................

I'm doing this at the table settings security, not Show If for the views. Maybe the calcualtion can be optimized. But better would be a global "Show if" to decide to take the app offline for some condition or time period. Any alternatives appreciated. Thank you!

dbaum

@mateoc15 wrote:

user setting for start and end

I don't follow how this is intended to work. User setting values are specific to each user's device, and typically can be revised by users. Maybe you're essentially setting constant values for these columns in the app definition and disabling editing for the columns?

Anyway, one idea to try would be:

Create a single-row Schedule table with columns for start and end time and a security filter that loads the row only if the current time is between the two values. You may want to use TIME(UTCNOW()) instead of TIMENOW() so that users can't workaround your schedule by changing their device's timezone.
In the Employees and other tables, incorporate ISNOTBLANK(Schedule[_RowNumber]) into the security filter expression. For example:

OR(
  IN(USEREMAIL(),Admins[Email]),
  ISNOTBLANK(Schedule[_RowNumber])
)

Also, be sure that your security filter expressions for the Employees and Admins tables are not circular. For example, since your Employees table's security filter references the Admins table, the security filter for the Admins table must not reference the Employees table.

mateoc15

The first paragraph is true. I'm basically using invisible user setting values to house some constants I want to use throughout the app.

And your circular reference... reference (?) is probably spot on.

More tables seems like overkill. Anyone ever created a config table to do something like this? Table [Settings] has columns [SettingName] and [SettingValue]. Load the table, create a slice for each [SettingName], one neat table. It's not ideal, but should work. I'll try that rather than user settings. Stay tuned.

Steve

@dbaum wrote:

You may want to use TIME(UTCNOW()) instead of TIMENOW() so that users can't workaround your schedule by changing their device's timezone.

UTCNOW() doesn't work that way--it is also subject to the user's device settings. UTCNOW() is computed from NOW(); it is not an independent value.

dbaum

Wow! I never thought about it one way or the other, but, of course, that's the only way the UTCNOW function could work on a user's device in offline mode. I had indeed explicitly thought that when invoked from the server (e.g., for a security filter) the function would return a reliable UTC time not dependent on the user's device. @lizlynch, this ought to be spelled out in Date and time expressions - AppSheet Help and UTCNOW() - AppSheet Help (and maybe even the function list entry).

mateoc15

And better yet, the UTC timestamp we all seem to have expected should be
achievable somehow also. Maybe a calculator based on lat/lon LOL

lizlynch

@dbaum @Steve - Thanks for raising this! I'm updating all functions that return Date, Time, and DateTime with the following note:

To understand how your mobile device's locale or browser's language setting can impact the [Date | Time | DateTime] format, see Considerations for apps using Date, Time, and DateTime formats.

https://support.google.com/appsheet/answer/10107371

HTH!

Liz

dbaum

That's surely a good idea.

However, I think it doesn't address the nuance raised here. That article is about display format--not about how the actual value is determined.

lizlynch

@dbaum - Oh, apologies! I have updated the Notes section to include the note that is included for the other functions! Thanks for pointing this out.

dbaum

I think the example is still off.

The values returned by NOW(), TODAY(), TIMENOW(), and UTCNOW() reflect the timezone offset of the user's device. For example, if the timezone of the user's device is Pacific Standard Time (PST), the value returned is UTC-08:00; if the timezone of the user's device is Hong Kong, the value returned is UTC+8:00.

First of all, UTCNOW would not return the same result as the other functions. It would return what gets calculated as UTC, not what gets calculated as PST or as Hong Kong time.

More importantly, the explanation is the opposite of actual system behavior. Per Steve's clarification:

@Steve wrote:

UTCNOW() is computed from NOW(); it is not an independent value.

In other words, the functionality is potentially more like "if the timezone of the user's device is PST, the value returned is host time + 08:00 - 08:00". And, more importantly, UTCNOW is not simply UTC, but rather something like host time - device offset.

lizlynch

Thanks, @dbaum - Sincere apologies for the misinterpretation! Will get this updated in the docs soon. (For now, I reverted the incorrect change in the note above.)

lizlynch

@dbaum - Sorry for the delay! I worked with the AppSheet dev team to confirm the behavior and added a note to the UTCNOW() article, as follows:

For client-side interactions, the accuracy of the UTC time returned depends on the accuracy of the system time and timezone offset of the user's device (which can be modified by the user). To capture UTC timestamps that can't be modified by user-controlled settings, use a server-side interaction, such as an automation.

Thank you again for raising this important doc outage!

Liz

dbaum

Thanks for teasing apart the nuances of this functionality and getting it documented. I'm grateful to have confirmation that:

@dbaum wrote:

when invoked from the server (e.g., for a security filter) the function would return a reliable UTC time not dependent on the user's device

FYI, your UTCNOW link doesn't go to the function's publicly available help article.

lizlynch

Of course! Sorry it took time to confirm the behavior. Thank you for reporting the broken link! It's fixed! 🙂

Liz

Steve

@mateoc15 wrote:

Anyone ever created a config table to do something like this?

I use a table named Apps with a key column named App that contains the CONTEXT("AppName") value of the app for which the row applies. A slice named My App with a row filter expression of (CONTEXT("AppName") = [App]) gives me easy direct access to the app settings I put there. For instance, in your case: (TIMENOW() > ANY(My App[APP_start_time])).

mateoc15

So this feels a little more like one column per setting, rather than one setting per row. Sounds more efficient, I'll give it a shot. Thanks!