SCIM Error 500 on PATCH/PUT /Users: database user permission error: INSERT, CREATE command denied to user 'scim'@'172.17.0.1' for table SCIMRepresentationAttributeLstTemp360a37e6

[LazaroOnline]

SCIM database user permission error: INSERT, CREATE command denied to user 'scim'@'172.17.0.1' for table SCIMRepresentationAttributeLstTemp360a37e6

PATCH /Users/{id}

{
  "schemas": [
    "urn:ietf:params:scim:api:messages:2.0:PatchOp"
  ],
  "Operations": [
    {
      "op": "replace",
      "path": "userName",
      "value": "ryan3"
    }
  ]
}

OR PUT /Users/{guid}

{
  "schemas": [
    "urn:ietf:params:scim:schemas:core:2.0:User",
    "urn:ietf:params:scim:schemas:extension:custom:1.0:User"
  ],
  "id": "eb12792d-2ca1-4b13-a166-1e4ffc589a1a",
  "userName": "3941e4da-b284-4381-a8e1-ba703803820e",
  "active": true,
  "displayName": "3941e4da-b284-4381-a8e1-ba703803820eDisplayName",
  "externalId": "5acfa6db-c5a6-4799-b7cf-e651eb9645b8",
  "name": {
    "formatted": "3941e4da-b284-4381-a8e1-ba703803820eGivenName FamilyName",
    "familyName": "3941e4da-b284-4381-a8e1-ba703803820eFamilyName",
    "givenName": "3941e4da-b284-4381-a8e1-ba703803820eGivenName"
  },
  "emails": [
    {
      "primary": false,
      "type": "home",
      "value": "3941e4da-b284-4381-a8e1-ba703803820e.home\[email protected]"
    },
    {
      "primary": true,
      "type": "work",
      "value": "3941e4da-b284-4381-a8e1-ba703803820e.work\[email protected]"
    }
  ]
}

SimpleIdServer\src\Scim\SimpleIdServer.Scim\Commands\Handlers\PatchRepresentationCommandHandler.cs:line 71

await using (var transaction = await _scimRepresentationCommandRepository.StartTransaction().ConfigureAwait(false))
{
    await _scimRepresentationCommandRepository.BulkDelete(patchResultLst.Where(p => p.Operation == SCIMPatchOperations.REMOVE && p.Attr != null).Select(p => p.Attr), existingRepresentation.Id).ConfigureAwait(false);
    await _scimRepresentationCommandRepository.BulkInsert(patchResultLst.Where(p => p.Operation == SCIMPatchOperations.ADD && p.Attr != null).Select(p => p.Attr), existingRepresentation.Id).ConfigureAwait(false);
    await _scimRepresentationCommandRepository.BulkUpdate(patchResultLst.Where(p => p.Operation == SCIMPatchOperations.REPLACE && p.Attr != null).Select(p => p.Attr)).ConfigureAwait(false);

EXCEPTION: MySqlConnector.MySqlException

INSERT, CREATE command denied to user 'scim'@'172.17.0.1' for table 'SCIMRepresentationAttributeLstTemp360a37e6'

   at MySqlConnector.Core.ServerSession.<ReceiveReplyAsyncAwaited>d__93.MoveNext()
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__2.MoveNext()
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken)
   at MySqlConnector.MySqlDataReader.<CreateAsync>d__111.MoveNext()
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext()
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteNonQueryAsync>d__15.MoveNext()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteNonQueryAsync>d__15.MoveNext()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.<ExecuteNonQueryAsync>d__15.MoveNext()
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.<ExecuteSqlRawAsync>d__17.MoveNext()
   at EFCore.BulkExtensions.SqlAdapters.MySql.MySqlAdapter.<MergeAsync>d__7`1.MoveNext()
   at EFCore.BulkExtensions.SqlAdapters.MySql.MySqlAdapter.<MergeAsync>d__7`1.MoveNext()
   at EFCore.BulkExtensions.SqlAdapters.MySql.MySqlAdapter.<MergeAsync>d__6`1.MoveNext()
   at EFCore.BulkExtensions.SqlBulkOperation.<MergeAsync>d__5`1.MoveNext()
   at EFCore.BulkExtensions.DbContextBulkTransaction.<ExecuteAsync>d__1`1.MoveNext()
   at SimpleIdServer.Scim.Persistence.EF.EFSCIMRepresentationCommandRepository.<BulkUpdate>d__24.MoveNext()
   at SimpleIdServer.Scim.Commands.Handlers.PatchRepresentationCommandHandler.<UpdateRepresentation>d__8.MoveNext() in C:\SimpleIdServer\src\Scim\SimpleIdServer.Scim\Commands\Handlers\PatchRepresentationCommandHandler.cs:line 71
   at SimpleIdServer.Scim.Commands.Handlers.PatchRepresentationCommandHandler.<UpdateRepresentation>d__8.MoveNext() in C:\SimpleIdServer\src\Scim\SimpleIdServer.Scim\Commands\Handlers\PatchRepresentationCommandHandler.cs:line 82
   at SimpleIdServer.Scim.Commands.Handlers.PatchRepresentationCommandHandler.<Handle>d__7.MoveNext() in C:\SimpleIdServer\src\Scim\SimpleIdServer.Scim\Commands\Handlers\PatchRepresentationCommandHandler.cs:line 54
   at SimpleIdServer.Scim.Api.BaseApiController.<InternalPatch>d__37.MoveNext() in C:\SimpleIdServer\src\Scim\SimpleIdServer.Scim\Api\BaseApiController.cs:line 612

Tested in the current latest of "SimpleIdServer.Scim" v4.0.7

[simpleidserver]

Since version 4, the SCIM library has been utilizing the EFCore.BulkExtensions library to perform bulk insertion and updating of records in the SCIMRepresentationAttribute table. This modification was implemented to significantly enhance performance, allowing for the insertion of large volumes of data without encountering performance issues.

To achieve this improvement, EFCore.BulkExtensions inserts the data into a temporary table, as illustrated here: link.

As a result, it is imperative that the technical user executing the SCIM API possess the CREATE permission on the database schema.