Cannot use group based permissions in Umbraco Forms 10

[karlmacklin]

Cannot use group based permissions in Umbraco Forms 10! Can't load the UI properly when this is true.

Reproduction

Specifics

Umbraco and Umbraco Forms version 10.0.0!

Steps to reproduce

1. Use Umbraco 10.0.0, and Umbraco Forms 10.0.0.

2. In appsettings.json define:

"Forms": {
      "Security": {
        "ManageSecurityWithUserGroups": true
      }
    }

3. Start Umbraco, log in as admin and go to the "Users" section.

4. Expand the Forms Security section and click Group permissions:

Expected result

Show something related to group permissions for forms.

Actual result

There is an ajax request being performed in the admin looking like: http://localhost:8080/umbraco/backoffice/UmbracoForms/FormSecurity/GetByUserId?userId=undefined&explicitOnly=true (where localhost:8080 is my local dev environment)

userId being undefined sticks out like a sore thumb. Naturally this requests returns error 500 and body of:

{"ExceptionMessage":"User not found - ID: 0","ExceptionType":"System.InvalidOperationException, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","StackTrace":"   at Umbraco.Forms.Web.Editors.FormSecurityControllerBase.GetFormSecurityByUserId(Int32 userId, Boolean explicitOnly)\r\n   at lambda_method996(Closure , Object , Object[] )\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()\r\n--- End of stack trace from previous location ---\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()\r\n--- End of stack trace from previous location ---\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)"}

Also there is a request to http://localhost:8080/umbraco/backoffice/umbracoapi/users/GetById?id= that returns 404. This screenshot above related to this.

Also, the same problem happens when going to the Forms Security > User permissions section.

[AndyButland]

Can I check if you expand "Group Permissions" or "User Permissions", does that work OK? This may be "just" a UI bug, as if you click specifically on "Group Permissions" or "User Permissions" there's actually nothing to show.

Clearly it would be better to disable the click or at least not try to make a network call/show an error, but that may be all the issue is - and if you expand these nodes and click on the items underneath, it'll work OK.

At least that's what I find with a clean V10 install.

[karlmacklin]

Sure thing @AndyButland , it does not seem to work. If I expand and try to click on any group it fails.

For example, I expand groups, and click Administrators:

This results in a request to http://localhost:8080/umbraco/backoffice/UmbracoForms/FormSecurity/GetByUserGroupId?groupId=1 which results in:

{"ExceptionMessage":"Cannot insert the value NULL into column 'UserGroupId', table 'master.dbo.UFUserGroupSecurity'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated.","ExceptionType":"Microsoft.Data.SqlClient.SqlException, Microsoft.Data.SqlClient, Version=3.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5","StackTrace":"   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\r\n   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)\r\n   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at Microsoft.Data.SqlClient.SqlCommand.ExecuteScalar()\r\n   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() in C:\\projects\\dotnet\\src\\MiniProfiler.Shared\\Data\\ProfiledDbCommand.cs:line 343\r\n   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.<ExecuteScalar>b__32_0()\r\n   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)\r\n   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteScalar()\r\n   at NPoco.Database.<>c__DisplayClass297_0.<ExecuteScalarHelper>b__0()\r\n   at NPoco.Database.ExecuteScalarHelper(DbCommand cmd)\r\n   at NPoco.DatabaseTypes.SqlServerDatabaseType.ExecuteInsert[T](Database db, DbCommand cmd, String primaryKeyName, Boolean useOutputClause, T poco, Object[] args)\r\n   at NPoco.Database.InsertAsyncImp[T](PocoData pocoData, String tableName, String primaryKeyName, Boolean autoIncrement, T poco, Boolean sync)\r\n   at NPoco.AsyncHelper.RunSync[T](Task`1 task)\r\n   at NPoco.Database.Insert[T](String tableName, String primaryKeyName, Boolean autoIncrement, T poco)\r\n   at NPoco.Database.Insert[T](T poco)\r\n   at Umbraco.Forms.Data.Storage.UserGroupSecurityStorage.InsertUserGroupSecurity(UserGroupSecurity userGroupSecurity)\r\n   at Umbraco.Forms.Web.Editors.FormSecurityController.ApplyUserGroupSecurity(FormSecurityForGroup formSecurity, Int32 groupId)\r\n   at Umbraco.Forms.Web.Editors.FormSecurityController.GetByUserGroupId(Int32 groupId)\r\n   at lambda_method883(Closure , Object , Object[] )\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()\r\n--- End of stack trace from previous location ---\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()\r\n--- End of stack trace from previous location ---\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)"}

Trying to expand User permissions results in a very unhappy scenario:

This is a ajax request to http://localhost:8080/umbraco/backoffice/umbracoforms/formsecuritytree/GetNodes?id=users&application=users&tree=&use=main&culture= With result:

{"ExceptionMessage":"Input string was not in a correct format.","ExceptionType":"System.FormatException, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e","StackTrace":"   at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type)\r\n   at System.Int32.Parse(String s, NumberStyles style, IFormatProvider provider)\r\n   at Umbraco.Forms.Web.Trees.FormSecurityTreeController.<>c.<GetTreeNodes>b__6_1(UserSecurity x)\r\n   at System.Linq.Enumerable.SelectListIterator`2.ToList()\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at Umbraco.Forms.Web.Trees.FormSecurityTreeController.GetTreeNodes(String id, FormCollection queryStrings)\r\n   at Umbraco.Cms.Web.BackOffice.Trees.TreeControllerBase.GetTreeNodesAsync(String id, FormCollection queryStrings)\r\n   at Umbraco.Cms.Web.BackOffice.Trees.TreeControllerBase.GetNodes(String id, FormCollection queryStrings)\r\n   at lambda_method810(Closure , Object )\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()\r\n--- End of stack trace from previous location ---\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)"}

Is there anything else I can test that can help finding the core issue?

[AndyButland]

Hmm... I'm not sure I'm afraid at the moment. As I say, I'm not seeing this with a clean install.

Perhaps can you confirm please if this is an upgrade, or a new install? If it's an upgrade, can I check that you have removed the App_Plugins/UmbracoForms folder (discussed here). Possibly if they are still hanging around, that could cause some conflicts and issues.

And also if an upgrade, what version did you upgrade from please (to allow me to try to replicate)?

[karlmacklin]

It's an upgrade from whatever was the latest official version before the version 10 release basically.

App_Plugins/UmbracoForms folder is non-existant as well.

I'll confirm what happens by clearing the database completely, as long as I find a way to save current DB (it's a docker volume so should be fairly easy to just point to a new one).

We have an important stage environment the client is using to populate upcoming website data, so we will do what we can to solve this without clearing the database, but I'll check so we at least know.

[karlmacklin]

@AndyButland

Clean database does the trick, so this definitely seems to be a version migration issue?

For this problematic database, I actually remember we tried the umbraco forms release candidate for version 10 before you release the real one.

[AndyButland]

OK. If it's feasible for you to provide me a project zip and a database backup (with a back-office login I can use) I'm happy to take that and try to replicate here, and then may more easily be able to work out what's going on. If that's possible and useful for you, I'm at abl AT umbraco DOT dk.

[karlmacklin]

@AndyButland Definitely, I'll need a few minutes figure out how to make a database dump correctly, I'll get you download links ASAP for the db and project.

[AndyButland]

As discussed over email. part of the issue seems to be installation specific, but there is also a bug here that we need to fix.

This relates to editing of group permissions. When you navigate to a group, e.g. "Administrators", if no group record is found one is added by default. With V10 we've inadvertently added an autoincrement setting on the code representation of the primary key of the UFUserGroupSecurity table, which it shouldn't have. And that causes this insert to fail (as it omits the UserGroupId column, leading to the error message you see)..

We'll fix that for the next patch update.

Meantime, you can workaround this by adding an empty record for each user group, with the following SQL:

INSERT INTO UFUserGroupSecurity (UserGroupId,ManageDataSources,ManagePreValueSources,ManageWorkflows,ManageForms,ViewEntries,EditEntries)
SELECT id,0,0,0,0,0,0
FROM umbracoUserGroup

Which will resolve the issue at least until a new user group is created.