Display Features Relevant to the Current User

[#1]

Hosted feature service settings enable the item owner to control who can see and edit features through the settings shown below. It’s very common that users need to see or edit more than “their own” but less than “all features”. For example, in a statewide dataset, a user from County A may need to see and edit all features associated with County A.

One way to support this need is by using the CURRENT_USER keyword that’s part of the REST API query operation. This can be applied to a hosted feature service view in ArcGIS Online or Enterprise 11.2+. Here are the steps on how to do this in ArcGIS Online:

1. Make sure your hosted feature layer has a field with a comma separated list of usernames for users who should have access to each record, like this example:
   
   
   
2. In ArcGIS Online, navigate to your hosted feature service Item Details and click Create View Layer then follow the instructions here.
   
   
   
   • NEW OPTION: when you create the view, you have the option of applying a filter for  "is current user" - this will take the username of the current user viewing the data and only return records containing their username in the selected field . However, this is an EQUALs operator so the field should only contain one username, unlike the approach outlined below which works for a field containing a string of multiple usernames.
     • 
3. Go to the Item Details of the hosted view layer. Scroll down and click View next to the URL
   
   
4. Click Admin at the top left of the window
   
   
   
5. Click on the layer name under Layers:
   
   
   
6. Scroll down to the bottom of the page and click Update Definition
7. Delete all the JSON in the Update Layer Definition dialog box
8. Navigate to https://jsonlint.com to help define the JSON object to be passed
9. Paste the following into the JSON validator, then the Update Layer Definition dialog box: 
   
   {"viewDefinitionQuery" : "POSITION(CURRENT_USER in ViewUser) > 0"}
   
   
   
10. Replace ViewUser with the field in your data which contains a comma separated list of usernames that should have access to each record, like this example:
    
    
    
11. Click Update Service Definition.

Now, with the view definition applied, my user (payten_HAPS) can only see feature 1 and 2, while others would see “their” features.

I’m excited to hear about how you use this!
Stay tuned for subsequent posts/blogs with more details on considerations and use cases 😊

[DataOfficer]

Thank you for posting this.

This could be extremely useful for us. Particularly in managing community user access to specific features that they have not created / do not 'own' themselves, but we need to give them access to. Currently we use various hosted views and Groups as a middle ground for achieving this, but being able to fine-tune the access at a feature level would be a significant improvement. 

Does the ViewUsers approach work regardless of organisation? I.e., if the user is from outside of the layer organisation, does that cause any issues?

From a practical point of view, we would be looking to manage access to 10's of features (from a pool of thousands) for each user, across several hundred different users. Therefore some way of automating the comma separated listed of users for each feature will be essential (e.g. pulling from a spreadsheet, or from Group membership).

[PaytenJarnagin]

Hi @DataOfficer - The CURRENT_USER keyword filter will work for users outside of the layer's organization. I've tested the above approach with my community members and other, external ArcGIS Online members 🙂 

Side note: hosted feature layer views with a current_user filter applied should NOT be shared publicly. The caching of public layers impacts the layer's ability to filter by the specific user. 

Agreed, you'll definitely need a way to efficiently manage the "access data" (field with string of usernames) for each feature.. We are working on another post or blog with some considerations / possible approaches. I will link it here when it's available! 

[DataOfficer]

@PaytenJarnagin thank you that's great.

[rakeshmodia585]

Tried out but not working for me.

The "viewlayerquery" does not exist. Tried to add property using adddefinition function but no luck. 

[rakeshmodia585]

ViewDefinitionQuery property is working for me instead of ViewLayerQuery property.

[LAG155]

I have applied the filter and it works correctly, thank you for sharing such a valuable solution.

Filter limits layer editing?

[Marc_Graham]

Hi,

How complex can the SQL in the viewDefinitionQuery be? Could we map a selection of user names to some other attributes?

Thanks,

Marc

[PaytenJarnagin]

@LAG155 - applying a current user view definition would reduce the # of features a user can access and therefore edit; but users should have no issue editing what they can access! 

You may want to consider a view definition like this to expose features where the username look-up field is null... which would otherwise be hidden in that view. 

{"viewDefinitionQuery" : "POSITION(CURRENT_USER in EditUsers) > 0 OR (EditUser is NULL)"}

[PaytenJarnagin]

Hi @Marc_Graham - No, the usernames have to be in a field, and present for each record the user needs to see. You could log an ENH for the requested functionality. I can see how it would be useful! 

Instead of the current_user view, some users are leveraging a look-up table of user info to "personalize" Surveys and Dashboards - planning to write a post on this soon! Will share here once it's up 🙂 

[Marc_Graham]

Hi @PaytenJarnagin 

Thanks sounds great thanks, look forward to reading it.

[LAG155]

Hi @PaytenJarnagin ,

Thank you.
When modifying the capabilities in the view console, the editing process doesn't activate. I made the modification to the JSON and it works correctly.
A real success for my need to filter entities based on the assignments made to the logged-in user.

], 
  "capabilities" : "Create,Delete,Query,Update,Editing,Sync,ChangeTracking", 
  "viewDefinitionQuery" : "POSITION(CURRENT_USER in userAGOL) > 0", 
  "definitionQuery" : "POSITION(CURRENT_USER in userAGOL) > 0", 
  "layerOverrides" : [
    "allowGeometryUpdates", 
    "capabilities"
  ]

[Marc_Graham]

Hi @PaytenJarnagin 

I am trying to filter some features based on the following:

• an attribute has a specific value
• the username is in a field
• OR the username is a specific user

I can get the first two to work but not the second.

This works:

{"viewDefinitionQuery" : "POSITION(CURRENT_USER in AGOL_UID) > 0 AND LiveStatus = 'N'"}

This doesn't:

{"viewDefinitionQuery" : "(POSITION(CURRENT_USER in AGOL_UID) > 0 OR CURRENT_USER = 'specific_username') AND LiveStatus = 'N'"}

It returns an error: "viewDefinitionQuery has invalid html content"

Any ideas?

Thanks,

Marc

[SilasFrantz]

@PaytenJarnagin totally epic feature!

A few questions, trying to understand precisely what `CURRENT_USER` is and how it might be possible to extend in a large org with complex existing using groups, etc., here goes...

1. What is `CURRENT_USER` and by what mechanism does it get evaluated? Is it simply replaced with the string value of the username, or is it an object with other properties? Assuming just a string, any plans to upgrade to something with properties (e.g., `.EMAIL_ADDRESS`)? Is there any developer-friendly way to preview the value, akin to the `test` feature for validating Arcade expressions?

2. Are there other ways to set this, besides the REST API or AGOL assistant? Is it possible to set on the desktop side (e.g., within an ArcGIS Pro definition query) before publishing?

3. Do you know if folks have successfully set up relationship classes, and tested `CURRENT_USER` against related tables?

I see mention of a planned blog post on some of this, any progress on that? Would love any further info on this feature!

Linking docs for reference: https://doc.arcgis.com/en/arcgis-online/reference/sql-agol.htm

[SilasFrantz]

Just documenting my adventure & findings, RE questions 1-3 above^

1. `CURRENT_USER` is a standard SQL function across many types of SQL databases. How it gets evaluated depends on the context (how it's set up / how data is accessed), more on this below. Since it's based in, and occasionally interoperable with database SQL (which doesn't have a concept of email addresses, like AGOL / Portal), it seems unlikely to be given added properties (like `.EMAIL_ADDRESS`) in AGOL SQL. I haven't found a developer-friendly way to preview what the value returns.

2. Yes, but TLDR is that it's likely generally most practical to set at the web level (using ArcGIS REST API or AGOL Assistant). I did have success with setting it on the desktop side, both for FileGDB and SQL Server data, with FileGDB behaving most like what gets published to Portal (we're on Enterprise 11.2, for context). Specifically, if you set `POSITION(CURRENT_USER in ViewUser) > 0` on a FileGDB feature class and publish as a web layer, the CURRENT_USER variable remains dynamic in the web layer -- i.e., responds to who is viewing the data on the web. Alternate syntaxes like `ViewUser LIKE '%' || CURRENT_USER || '%'`, which work at the FileGDB level, do not seem to work after publishing to AGOL / Portal.

It unfortunately also doesn't look like the definition query makes it into the ArcGIS Online Assistant JSON "definitionExpression", so you can't see or change it after publishing. It's just kinda invisibly doing its thing, which might work OK for some use-cases, if you want to manage def queries on the desktop side for FileGDB data.

For data stored in SQL Server, on the other hand, the syntax "POSITION(CURRENT_USER in ViewUser) > 0" doesn't work -- ArcGIS Pro will reject it. You can work around this in Pro by doing something like `ViewUser LIKE '%' + CURRENT_USER + '%'`, but this will run your SQL at the database level, where CURRENT_USER has a different value (in my case, "DBO"). And after publishing to the web, this results in some gloriously buggy behavior, where popups and attribute table data is filtered (according to the SQL-level database user, not the user accessing on the web) but symbology isn't filtered at all!

All this isn't a problem when setting using AGOL Assistant JSON or ArcGIS REST API, so that's still my preferred method -- e.g., in AGOL Assistant, adding `"layerDefinition": {"definitionExpression": "POSITION(CURRENT_USER in ViewUser) > 0"}`.

3. It appears that web layer filtering generally doesn't see related data, even when a relationship class is set up inside the geodatabase before publishing. Nor is web filtering able to see fields generated with Arcade, of which there's some discussion here - either of these would enable really nice solutions where your user list comes from a related table (or system of tables). Deferring the evaluation of CURRENT_USER to the web-side when publishing referenced data from SQL Server, similarly would allow for (IMO, the most) elegant solutions like `project_code IN (SELECT project_code FROM user_access WHERE user = CURRENT_USER)`.

For posterity, the workaround we'll use (because we need to manage our data in SQL server, maintain complex project-level user-access, and want to use a single app / web map), will be to store a `ViewUsers` text field inside the data and have it updated, by lots of different triggers, by a SQL stored procedure. And we'll set the user filter on the web side as bolded above.

[PaytenJarnagin]

Hi @Marc_Graham - 

While I can't explain the invalid HTML content error.. I can suggest a possible solution:

It sounds like IF the current_user is User A you want to return ALL features, ELSE you only want to return features where current_user is present in AGOL_UID. The query doesn't support this level of conditional logic to NOT filter for User A but filter for everyone else 😞 

To accomplish this, your view definition would filter features based on: 

• if the attribute has a specific value (LiveStatus = N)
• AND the current user's username is in a field

Then, to return all features for your User A, you would need to establish a workflow to get their username associated with each feature OR create a separate view for them. 

[PaytenJarnagin]

Thanks for conducting those tests and sharing your results @SilasFrantz!

The follow-up blog is almost ready - hoping to get it posted before UC 🤞