By 
In this tutorial, we will learn about the Security breach in SharePoint online conditionally show or hide columns in a SharePoint list. It is good that these days we are able to customize the SharePoint Online list form using the JSON formatting, and we can bring the list looks, like custom developed form, that too also can be done very quickly, thanks to Microsoft for this.
However, while we are dealing with the show and hide columns conditionally using the JSON formatting in SharePoint Online, we have explored a couple of permissions flaws in this and some of the things which must need for the business but not sure Microsoft ignored those scenarios.
Conditionally show or hide columns in a SharePoint list – a certain group of columns should be shown only to certain group members – Need Improvements
While we are dealing with the show and hide columns conditionally, this is a very basic need based on the logged-in user roles or whether the logged-in user belongs to a certain SharePoint security group, then only certain columns should be shown to them otherwise not.
Here, as of now, there are no such facilities to check logged-in user role or check whether the user presents to a certain SharePoint group, then only display certain columns.
With the current solution, it supports only people and group column types only with a single user which is very unrealistic. We think Microsoft must break this limitation and come up with more open solutions for handling the security group. You can refer to the unsupported columns and limitation – column types not supported in conditional formulas in SharePoint list/library: Unsupported column types in conditional formulas
Are columns really hidden? Security breach in SharePoint Online conditionally show hide columns
Let us explain this with an example, when we create a site in SharePoint Online, by default three groups get created, Site Owners, Site Members, and Site Visitors with the permission levels as below:
- Site Owners – Full Control
- Site Members – Edit
- Site Visitors – Read
Generally, we add the members to the site Members without getting into the permission level that is assigned to the site members group. Here the site owner or administrator should be very careful before they add a user to the site members group because the site member group has the “Edit” permission level, in the site permission level hierarchy comes in the number 3 position.
Let’s see what a user can do with the “Edit” permission level.
Permission levels in SharePoint online
Below are the permission levels in SharePoint Online:
- Full Control – Has full control
- Design – Design – Can view, add, update, delete, approve, and customize.
- Edit – Can add, edit and delete lists; can view, add, update and delete list items and documents.
- Contribute – Can view, add, update, and delete list items and documents.
- Read – Can view pages and list items and download documents.
- Restricted View – Can view pages, list items, and documents. Documents can be viewed in the browser but not downloaded.
- Limited Access – Can view specific lists, document libraries, list items, folders, or documents when given permissions.
So, clearly, we can see with the “Edit” permission user can add, edit, and delete the “List”, and “View”, basically it is list-level permission. If the requirement is, that the user only should be able to add, edit, and delete list items, then we must not provide the Edit permission otherwise we might expect some uncertainty (anyone can delete the list itself), so, we should give “Contribute” permission. With the “Contribute” permission, the user can view, add, update, and delete list items and documents.
Let’s get back to the main agenda of the article, you will understand why we have diverted and explained the permission level in SharePoint Online.
Show hide column conditionally using JSON formatting in SharePoint list
Here the user “GD” is a member of the site owners group.
Using this “GD” user we are applying for the show and hide JSON formula to the “Approve/Reject” column. This is is just an example, and let’s not focus on the formula part and what it is doing as this is not relevant here.
Now, let’s log in to the same Leave Request list using the other user “GSD1” which is part of the site members group which has the “Edit” permission.
Using the “G1” user (which has the Edit permission level), if we log in to the same list and can see the formula applied to the “Approve/Reject” – this user can edit, and delete the formula.
This is fine because the user has “Edit” level permission, and it is needed as the user is working or customizing the list.
Now, let’s demote this “G1” user to the “Contribute” permission level.
Now, the site members group demoted to the “Contribute” permission level, and as we have seen “GSD1” user is part of this group.
Now, go to the same Leave Request form using the “GSD1” user.
Still, we could see the “Edit columns” option – this shouldn’t be.
We could see the formula that was set to the column.
Now, if we try to click on the “Save” button, we will get a message “Sorry, you don’t have access.”
Although, in the end, the user is restricted to editing/adding the formula, still the user could see the business rules that were set to the column. We think this is a security breach in SharePoint Online conditional formula. You can write your opinion in the comment section.
SharePoint Online conditional show hide column formula doesn’t support the”edit in grid view/datasheet view
This is one of the vital drawbacks – in SharePoint Online conditionally show and hide column formula – this doesn’t support in the “Edit in grid view /datasheet view.
The formula is applied to the “Approve/Reject” column, that’s why it doesn’t show up in the list form, but the same column is visible in the “Edit in grid view” mode.
To avoid this issue, one may say that we can disable the “Edit in grid view” from the list settings page, but that is not recommended, because many businesses use SharePoint list as an alternative to excel where they should be able to quickly manipulate their data like excel in the list and that is only possible using the “Edit in grid view” mode.
By the way, let’s learn how to disable edit in grid view SharePoint.
How to disable and enable edit in grid view SharePoint list?
Go to the List Settings page -> Click on the Advanced Settings link.
Then go to the “Quick property editing” section.
Quick property editing:
Specify whether Quick Edit and Details Pane can be used on this list to bulk edit data.
- Allow items in this list to be edited using Quick Edit and the Details Pane? Select No by default this will be in yes mode.
Changes in site column setting delete the conditional show hide column formula
If you don’t know about this, disaster will happen on your SharePoint site. For example, you have a choice type site column, let’s say the name is – “Project Type” which has the below choice:
- SharePoint
- Dot Net
And if you have added this column to your list, and applied the show hide conditional formula which is working fine, means it is showing the form. Let’s see below:
The formula applied to the Project Type column to show it is hidden – not shown in the form which is expected.
Now, let’s add one more choice option as “Java” in the Project Type site column.
Once we add one more choice to the site column and save it, the conditional show hide formula which was applied to this column will be deleted automatically.
Now, go to the same list, and open the new form, you can see that the “Project Type” column is showing up in the form which was not showing just before, because the conditional formula which was applied to this column for the show hide, it got deleted automatically. To verify this, go to the edit columns from the top right corner, and we can see that “Project Type” is not grayed out anymore, which means the formula is not set in this column, which got deleted automatically.
Just imagine, if this site-level choice column would have been used in many lists on your site, what would have happened? The main disaster would have been in this, is a security breach in the data exposed to an unauthorized person, and the second thing would be re-writing the formula in all lists wherever it is supposed to be.
Note:
- The same behavior is not happening in the list level column, what we meant is – if you have a choice column in the list level, and applied the show hide conditional column formula, now if we add/remove some values in this choice column – the conditional formula will not be deleted, it will be retained as it is.
Summary: Security breach in SharePoint online (conditionally show or hide columns in a SharePoint list)
Thus, in this article, we have learned the below with respect to handling the permission in conditionally show or hide columns in a SharePoint list:
- Permission flaws in SharePoint online list while working on conditionally show or hide columns in a SharePoint list.
- Show hide column conditionally using JSON formatting in SharePoint list.
- Permission levels in SharePoint online
- How to disable and enable edit in grid view SharePoint list
See Also: SharePoint Online Tutorials
You may also like the below SharePoint Online Tutorials:
- PowerApps hide show fields
- Conditionally show or hide columns in a SharePoint list
- SharePoint Online conditional row formatting based on a LOOKUP column value using JSON
- Power Automate[Detailed]: set item level permissions in SharePoint online step by step with HTTP request
- Show or hide columns in a list or library form
About Post Author
