How to use a Custom user table for Authentication

If you are using the DB Authenticator as explained here you can customize the SQL query used by the authenticator to fetch user data. This is useful in two kinds of (potentially overlapping) scenarios.

1. You have an existing (legacy) database with its user/password table which you want to keep and use in your Kitto^x application.
2. You want to select additional user data that you then use in views, etc.

Just add the ReadUserCommandText property according to the instructions in the link above (which includes a simple example).
Here is a more complex example extracted from a real-world project:

Auth: DB
  IsClearPassword: True
  ReadUserCommandText: |
    select
      WEB_USER_NAME as USER_NAME, WEB_PASSWORD as PASSWORD_HASH, WEB_ENABLED as IS_ACTIVE,
      LAST_NAME + ' ' + NAME as FULL_NAME, ID as PERSON_ID,
      coalesce((select top 1 1 from ADMIN_OPERATOR 
                where PERSON.ID = ADMIN_OPERATOR.PERSON_ID), 0) as IS_ADMIN_OPERATOR
    from PERSON
    where (WEB_USER_NAME = :P1) and (WEB_ENABLED = %DB.TRUE%)

The additional columns other than the required USER_NAME, PASSWORD_HASH and IS_ACTIVE are custom columns used throughout the application by means of macros such as %Auth:IS_ADMIN_OPERATOR%.

Columns the framework itself looks for

Beyond USER_NAME, PASSWORD_HASH and IS_ACTIVE, four column names are read by the framework when present, so keep the aliases if you want the corresponding feature:

Column	Read by	If missing
MUST_CHANGE_PASSWORD	the imposed password change	the user is never asked to change the password
EMAIL_ADDRESS	password reset by mail	reset cannot find the user
PASSWORD_B_HASH	Auth: DBCrypt — the bcrypt hash	the user is treated as legacy and validated against PASSWORD_HASH; no bcrypt password can be validated
SECRET_CODE	LoginType: PIN — the per-user TOTP secret	PIN login and QR enrolment are refused (see PIN login)

Everything else becomes a session value, reachable as %Auth:<ColumnName>%.

The password column must not be empty

A row whose password column is empty or NULL cannot be logged into — see No login with an empty password. If your legacy table has such rows (an account created from a form without a password field, an invitation not yet completed), their owners get in through the password reset, which needs EMAIL_ADDRESS to be filled in.

Cross-database literals

Use the %DB.TRUE% / %DB.FALSE% macros instead of hardcoded 1/0 for boolean comparisons whenever your WEB_ENABLED (or equivalent) column might be a native boolean on PostgreSQL or Firebird 3+. The macro expands to the dialect-specific literal at query execution time.