search

simpleidserver / SimpleIdServer

OpenID, OAuth 2.0, SCIM2.0, UMA2.0, FAPI, CIBA & OPENBANKING Framework for ASP.NET Core
https://simpleidserver.com/
Apache License 2.0
682 stars 90 forks source link

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 #721

Open LazaroOnline opened 3 months ago

LazaroOnline commented 3 months ago

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\u0027s@test.com"
    },
    {
      "primary": true,
      "type": "work",
      "value": "3941e4da-b284-4381-a8e1-ba703803820e.work\u0027s@test.com"
    }
  ]
}

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 commented 3 months ago

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.