Question
FireDAC and EMS credential expiration and least-privilege boundary
Mira Vale
0 reputation · 02 Jun 2022, 09:49 UTC
75.2K views
Loading…
0 reputation · 02 Jun 2022, 09:49 UTC
A Delphi application is planned to use FireDAC for least-privilege database access and RAD Server/EMS for token-based API access. The design goal is consistent credential use and handling of expired credentials across the two component boundaries.
FireDAC supports least-privilege access via connection parameters and delegates authentication to the underlying driver and OS. Credential expiration is not managed inside Delphi; connection failures are surfaced as engine exceptions with vendor-specific codes and retry or re-prompt logic is left to application code.
EMS provides token-based authentication with configurable lifetimes and role-based access for least-privilege design, but token issuance, refresh and revocation policies are application-defined and not enforced by the framework. Indy HTTP clients surface 401 responses for expired or invalid credentials without built-in renewal or standardized expired-credential detection.
No cross-component standard API exists in Delphi for detecting expired credentials and prompting renewal across FireDAC, EMS and Indy.
Should credential expiration detection and re-authentication be centralized in the application layer or handled per component with OS credential manager reliance? Is there a documented, driver-agnostic way to distinguish expired credential failures from generic connection failures in FireDAC? How should least-privilege role mapping be aligned between FireDAC database users and EMS roles without duplication?
35275 reputation · 02 Jun 2022, 14:22 UTC
Credential expiration detection and re-authentication should be centralized in the application layer. Because FireDAC (database access) and Indy/EMS (API access) operate on different protocols—one using driver-level connection parameters and the other using HTTP headers—there is no shared framework-level event to synchronize them. A centralized manager should handle the retrieval of credentials from the OS credential manager and distribute them to the respective components.
There is no driver-agnostic way to distinguish expired credentials from generic connection failures across all FireDAC drivers. FireDAC surfaces vendor-specific error codes. For EMS-specific connections, an expired token typically manifests as an EFDException containing the string "Token expired".
To handle this without manual string parsing, use the TFDConnection.OnBeforeConnect event to verify token validity or trigger a refresh via the TFDPhysEMSDriverLink component before the connection attempt is made.
To avoid duplication between FireDAC database users and EMS roles, implement Identity Mapping at the EMS server level. Instead of creating mirrored users in both the database and the API, the EMS server should act as the single authority:
delphiRole_ReadOnly, Role_Editor).TFDPhysEMSDriverLink.Username and Password and enable UseTokenCache = True.TFDConnection.OnBeforeConnect handler, call TFDPhysEMSDriverLink.RefreshToken to ensure a valid token exists before the connection opens.To verify the boundary is functioning as intended, use the following scoped tests:
TFDConnection remains operational without manual intervention after the 5-minute mark.DELETE or UPDATE operation via FireDAC and verify the server returns a 403 Forbidden or equivalent permission error.Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 02 Jun 2022, 15:58 UTC
The TFDConnection.OnRecover event (introduced in 10.4 Sydney) fires after a connection loss and before an automatic retry, providing a driver-agnostic place to refresh an EMS token and update the connection parameters before FireDAC re-attempts the database handshake. This is cleaner than parsing EFDDBEngineException error codes in a generic exception handler.
When the EMS token TTL is shorter than the FireDAC connection pool lifetime, schedule a proactive refresh in a background thread (e.g., at 80 % of TTL) and push the new token to both the TFDPhysEMSDriverLink and any Indy HTTP clients via a shared ICredentialProvider interface. This avoids the 401 round-trip on the API side while the DB connection is still valid.
For least-privilege alignment without duplicating users, maintain a lightweight mapping table in the EMS schema (e.g., ems_role_map with columns ems_role, db_user, db_schema). The EMS endpoint resolver reads this table at token issuance and embeds the target db_user in a custom claim; the FireDAC connection string then uses that claim value via User_Name parameter, keeping the Delphi client free of role logic.