Authentication filtering across many-to-many relationship

I have a many-to-many relationship (with an intermediate table) between two tables, and when a user queries one of the two tables, I want to restrict them to only seeing the records that relate to themselves.

  [Entity, Automapping]
  TUserAccount = class( TORMEntity )
  private
    [Id( 'FID', TIdGenerator.IdentityOrSequence )]
    FID: int64;
    [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FUseraccount')]
    FUserKlasses: TList<TKlasseUser>;
  public
    // fields
    property ID: int64 read FID write FID;
    property UserKlasses: TList<TKlasseUser> read FUserKlasses;
  end;

  [Entity, Automapping]
  TKlasse = class( TORMEntity )
  private
    [Id( 'FID', TIdGenerator.IdentityOrSequence )]
    FID: int64;
    FKlassename: string;
    [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FKlasse')]
    FKlasseUsers: TList<TKlasseUser>;
  public
    // fields
    property ID: int64 read FID write FID;
    property KlasseName: string read FKlasseName write FKlasseName;
    property KlasseUsers: TList<TKlasseUser> read FKlasseUsers write FKlasseUsers;
  end;

  [Entity, Automapping]
  TKlasseUser = class( TORMEntity )
  private
    [Id( 'FID', TIdGenerator.IdentityOrSequence )]
    FID: int64;
    [Association([], CascadeTypeAllButRemove)]
    [JoinColumn('USER_ID', [TColumnProp.Required])]
    FUserAccount: TUserAccount;
    [Association([], CascadeTypeAllButRemove)]
    [JoinColumn('KLASSE_ID', [TColumnProp.Required])]
    FKlasse: TKlasse;
  public
    property ID: int64 read FID write FID;
    // Actual linkage
    property Klasse: TKlasse read FKlasse write FKlasse;
    property User: TUserAccount read FUserAccount write FUserAccount;
  end;

Then in the authentication code (called by subscribing to events in TXDataModuleEvents) I've tried this (amongst many other attempts...)

var
  iiID: int64;
begin
  result := true;
  if ( not AuthorizeAdminManager ) then begin
    iiID := AUser.Claims[ JWT_CLAIM_USER_ID ].AsInt64;
    // not a manager, only allowed to see own klasse, i.e. records which have a link through the klasseuser table
    AArgs.Criteria
      .CreateAlias( 'KlasseUsers', 'ku' )
        .Where( TLinq.Eq( 'ku.UserID', iiID ) )
  end;
end;

but it says it can't find the UserID field of KlasseUser.

I'd love to know how to do this properly. So, the objective is to ensure that if a user tries to list the Klasse table, they only get to see the items that have their User's ID in the link table...

Thanks!

Class TKlasseUser does not have a property UserId, that's why you have that error. A query that would theoretically work (*) is something like this:

    AArgs.Criteria
      .CreateAlias( 'KlasseUsers', 'ku' )
      .CreateAlias( 'ku.User', 'u' )
        .Where( TLinq.Eq( 'u.ID', iiID ) )

However, that query will not do what you expect. It will simply list all TKlasse which happens to be associated with that user, but the TKlasse.UserKlasses property will always come with all items in the list.

Thanks Wagner, yes I see what you mean. Next step is to work out how to do that!

Thanks

And actually, it still doesn't work...

{
    "error": {
        "code": "PropertyNotFound",
        "message": "Property \"User\" not found on class \"TKlasseUser\"."
    }
}

(in Postman)

Is there an issue with the fact that my TKlasseUser class has the property named User but the field named UserAccount? I've only just spotted that...

Ah yes, correct. I overlooked it. What matters is the name of the mapped class member. You should use it this way:

  AArgs.Criteria
      .CreateAlias( 'KlasseUsers', 'ku' )
      .CreateAlias( 'ku.UserAccount', 'u' )
        .Where( TLinq.Eq( 'u.ID', iiID ) )

I've tried that now, and it works (and actually it gives me the exact result I wanted).

Perfect, thanks!

1 Like

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.