Authenticated/Authorized CRUDs

Question

I'm learning to code with Delphi 12 and I'm trying to create a simple app for an agenda.

I have a mySQL database set up with 2 tables:

users
id
username
password

appointments
id
user_id (FK)
Company
Date
Time

I already coded a full-stack version of the app but now I'm trying to split the client and server by using TMS XData as framework for my server.

I have declared and mapped the User and Appointment entities and the automatically generated CRUDs work but I need to authorize the operation of reading and writing only the appointment entities bound to the logged user. I created a JWT Login Service that works and correctly returns the token but I'm not sure on how to modify the CRUDs to make use of that token to authorize the operations.

How should I proceed?

PS. I'm using FireDAC components to connect to the database.

By reading the DOCs I understood that I should be using both TMS Spark's Middleware to verify if the token is correct and possibly [EntityAuthorize(EntitySetPermissions*OPERATION*)] when declaring the object but I'm not sure if this is right and how to check for the right user_id.

This is what I tried to do but when testing using Postman, it always returns 404. My server is running on "http://localhost:2001/tms/xdata " so what in Postman the URL I'm using is "http://localhost:2001/tmx/xdata/CRUDService/GetAppointments" with method GET and with the Authorization key in the Headers with value "Bearer my-jwt".

unit CRUDServiceImplementation_u;

interface

uses
  XData.Service.Common,
  XData.Server.Module,
  XData.Sys.Exceptions,
  FireDAC.Comp.Client,
  Data.DB,
  AppointmentObj_u, LoginUser_u,
  GlobalElements_u,
  System.Generics.Collections,
  CRUDService_u,
  JOSE.Core.JWT,
  JOSE.Core.JWS,
  JOSE.Core.Builder,  Sparkle.Security;

type
  [ServiceImplementation]
  TCRUDService = class(TInterfacedObject, ICRUDService)
  private
  function GetUserID: integer;
  public
 [HttpGet] function GetAppointments: TList<TAppointment>;
 [HttpPost] procedure AddAppointment(const Appointment: TAppointment);
 [HttpPatch] procedure UpdateAppointment(const Appointment: TAppointment);
 [HttpDelete] procedure DeleteAppointment(const ID: Integer);

  end;

implementation

uses
  System.SysUtils;

function TCRUDService.GetUserID: Integer;
var
  CurrentUserID: Integer;
  User: IUserIdentity;
  Query: TFDQuery;
begin
  User := TXDataOperationContext.Current.Request.User;
  if User = nil then
    raise EXDataHttpUnauthorized.Create('User not authenticated');

  Query := TFDQuery.Create(nil);
  try
    Query.Connection := FDConnection1;
    Query.SQL.Text := 'SELECT Id FROM users WHERE username = :username AND password = :password';
    Query.ParamByName('username').AsString := User.Claims['username'].AsString;
    Query.ParamByName('password').AsString := User.Claims['password'].AsString;
    Query.Open;

    if not Query.IsEmpty then
      CurrentUserID := Query.FieldByName('Id').AsInteger
    else
      raise EXDataHttpUnauthorized.Create('User not found or invalid credentials');
  finally
    Query.Free;
  end;

  Result := CurrentUserID;
end;



function TCRUDService.GetAppointments: TList<TAppointment>;
var
  Query: TFDQuery;
  Appointments: TList<TAppointment>;
  CurrentUserID: Integer;
begin
  Appointments := TList<TAppointment>.Create;
  CurrentUserID := GetUserID; // Call the GetUserID function to get the current user's ID

  Query := TFDQuery.Create(nil);
  try
    Query.Connection := FDConnection1;
    Query.SQL.Text := 'SELECT * FROM appointments WHERE UserID = :UserID';
    Query.ParamByName('UserID').AsInteger := CurrentUserID;
    Query.Open;

    while not Query.Eof do
    begin
      var Appointment := TAppointment.Create;
      Appointment.ID := Query.FieldByName('ID').AsInteger;
      Appointment.UserID := Query.FieldByName('UserID').AsInteger;
      Appointment.Company := Query.FieldByName('Company').AsString;
      Appointment.Date := Query.FieldByName('Date').AsDateTime;
      Appointment.Time := Query.FieldByName('Time').AsDateTime;
      Appointments.Add(Appointment);
      Query.Next;
    end;
  finally
    Query.Free;
  end;

  Result := Appointments;
end;

procedure TCRUDService.AddAppointment(const Appointment: TAppointment);
var
  Query: TFDQuery;
  CurrentUserID: Integer;
begin
  CurrentUserID := GetUserID; // Retrieve the current user's ID

  Query := TFDQuery.Create(nil);
  try
    Query.Connection := FDConnection1;
    Query.SQL.Text := 'INSERT INTO appointments (UserID, Company, Date, Time) VALUES (:UserID, :Company, :Date, :Time)';
    Query.ParamByName('UserID').AsInteger := CurrentUserID;
    Query.ParamByName('Company').AsString := Appointment.Company;
    Query.ParamByName('Date').AsDateTime := Appointment.Date;
    Query.ParamByName('Time').AsDateTime := Appointment.Time;
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;


procedure TCRUDService.UpdateAppointment(const Appointment: TAppointment);
var
  Query: TFDQuery;
  CurrentUserID: Integer;
begin
  CurrentUserID := GetUserID; // Retrieve the current user's ID

  // Ensure the user owns the appointment
  Query := TFDQuery.Create(nil);
  try
    Query.Connection := FDConnection1;
    Query.SQL.Text := 'UPDATE appointments SET Company = :Company, Date = :Date, Time = :Time WHERE ID = :ID AND UserID = :UserID';
    Query.ParamByName('ID').AsInteger := Appointment.ID;
    Query.ParamByName('UserID').AsInteger := CurrentUserID;
    Query.ParamByName('Company').AsString := Appointment.Company;
    Query.ParamByName('Date').AsDateTime := Appointment.Date;
    Query.ParamByName('Time').AsDateTime := Appointment.Time;
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;

procedure TCRUDService.DeleteAppointment(const ID: Integer);
var
  Query: TFDQuery;
  CurrentUserID: Integer;
begin
  CurrentUserID := GetUserID; // Retrieve the current user's ID

  // Ensure the user owns the appointment
  Query := TFDQuery.Create(nil);
  try
    Query.Connection := FDConnection1;
    Query.SQL.Text := 'DELETE FROM appointments WHERE ID = :ID AND UserID = :UserID';
    Query.ParamByName('ID').AsInteger := ID;
    Query.ParamByName('UserID').AsInteger := CurrentUserID;
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;




initialization
  RegisterServiceType(TCRUDService);

end.