A customer maintenance form in Uniface 10, part 9 - users, roles, a change history and a login lockout

Up to part 8 the app had no idea who was using it. Every change was stamped with $user, the Windows login, and every user could do everything. For a single-user desktop tool that is fine. As soon as a second person works with the same customer file, three questions come up:

• Who changed this customer?
• What exactly was changed, and what was the value before?
• Who is allowed to change anything at all?

This part adds three services that answer them: USER_SVC (users, passwords, roles), HISTORY_SVC (a field-level change log) and AUDIT_SVC (a login log with a lockout and a password rule). All three are implemented and covered by tests. They are not yet wired into the forms - there is no login dialog yet. I say that up front because it matters for how far the security claims below go.

The tables

CREATE TABLE APP_USER (
  USER_NAME     VARCHAR(30) NOT NULL PRIMARY KEY,
  PASSWORD_HASH VARCHAR(64) NOT NULL,
  USER_ROLE     VARCHAR(10) NOT NULL,
  IS_ACTIVE     INTEGER);

CREATE TABLE CUSTOMER_HISTORY (
  HISTORY_ID  INTEGER NOT NULL PRIMARY KEY,
  CUSTOMER_ID INTEGER NOT NULL,
  CHANGED_AT  DATETIME NOT NULL,
  CHANGED_BY  VARCHAR(30),
  FIELD_NAME  VARCHAR(30) NOT NULL,
  OLD_VALUE   VARCHAR(200),
  NEW_VALUE   VARCHAR(200));
CREATE INDEX IX_CUSTOMER_HISTORY_CUSTOMER ON CUSTOMER_HISTORY (CUSTOMER_ID);

CREATE TABLE APP_LOGIN_LOG (
  LOG_ID     INTEGER PRIMARY KEY,
  USER_NAME  VARCHAR(30) NOT NULL,
  LOGIN_AT   DATETIME NOT NULL,
  SUCCESS    INTEGER NOT NULL);
CREATE INDEX IX_APP_LOGIN_LOG_USER ON APP_LOGIN_LOG (USER_NAME, LOGIN_AT);

HISTORY_ID comes from the same number-range table CUSTOMER_SEQ as customers and addresses (a new row HISTORY). LOG_ID is a plain SQLite rowid, because nothing else refers to it.

The DDL was run in the IDE's EDIT SQL dialog, one statement at a time, each followed by COMMIT. Without the COMMIT nothing reached the file - a detail that cost some confusion earlier in the project.

Users and roles

Three roles, one comparison

Roles are ordered: READ < EDIT < ADMIN. A right check becomes a number comparison instead of a list of ifs:

entry ROLE_LEVEL
params
    string pRole : IN
    numeric pLevel : OUT
endparams
    selectcase pRole
    case "READ"
        pLevel = 1
    case "EDIT"
        pLevel = 2
    case "ADMIN"
        pLevel = 3
    elsecase
        pLevel = 0
    endselectcase
    return 0
end

public operation HAS_RIGHT
params
    string pRight : IN
    boolean pAllowed : OUT
endparams
variables
    string vUser, vRole
    numeric vHave, vNeed
endvariables
    pAllowed = 0
    activate $instancename.CURRENT_USER(vUser, vRole)
    call ROLE_LEVEL(vRole, vHave)
    call ROLE_LEVEL(pRight, vNeed)
    if (vNeed > 0 & vHave >= vNeed)
        pAllowed = 1
    endif
    return 0
end

An unknown right (vNeed = 0) is never granted. That is the kind of default you want to decide consciously, not by accident.

Who is logged in: $91 and $92

A successful login stores user name and role in the global registers $91 and $92. They live as long as the application runs and are visible in every component, which is exactly the lifetime of a desktop session:

public operation CURRENT_USER
params
    string pUser : OUT
    string pRole : OUT
endparams
    if ($91 = "")
        pUser = $user
        pRole = "EDIT"
    else
        pUser = $91
        pRole = $92
    endif
    return 0
end

Without a login, the app behaves as before: the Windows user with role EDIT. The test for this is literally called "without login the developer mode applies". It keeps every existing form working while the login is not wired in - and it is also the first thing I would remove before calling this "secure", see below.

The first user becomes administrator

There is no setup wizard. If APP_USER is empty, the first login creates that user as ADMIN and LOGIN returns 1 instead of 0, so a caller can show "you are the first user and have been made administrator":

    call COUNT_ALL(vCount)
    if (vCount = 0)
        call CREATE_ROW(vUser, pPassword, "ADMIN", 1, pError)
        if ($status < 0)
            return -1
        endif
        vFirst = 1
    endif

The last administrator cannot lock everyone out

UPDATE_USER refuses to demote or deactivate the last active admin:

    if (vOldRole = "ADMIN" & vOldActive = 1 & (vRole != "ADMIN" | vActive = 0))
        sql "SELECT COUNT(*) FROM APP_USER WHERE USER_ROLE = 'ADMIN' AND COALESCE(IS_ACTIVE, 1) = 1", "CUSTOMERS"
        vAdmins = $result
        if (vAdmins <= 1)
            pError = "The last active administrator cannot be changed to another role or deactivated."
            return -1
        endif
    endif

Password storage

entry HASH
params
    string pUser : IN
    string pPassword : IN
    string pHash : OUT
endparams
variables
    string vKey
endvariables
    vKey = $concat("CUSTOMER_MANAGEMENT:", $uppercase(pUser))
    pHash = $encode("HEX", $encode("HMAC_SHA256", pPassword, vKey))
    return 0
end

The password is never stored, only an HMAC-SHA256 over it, keyed with a fixed prefix plus the upper-cased user name. The user name in the key means two users with the same password get different hashes. Upper-casing it matches the login, which compares names with LOWER(...) = LOWER(...).

Login answers "Unknown user name or wrong password." for both cases, so the dialog does not tell an attacker which user names exist.

I will come back to this function under "weak spots", because it is the one a security reviewer would pick apart first.

A field-level change history

CHANGED_AT/CHANGED_BY on the customer row (part 4) only tell you that something changed. The history table records what: one row per changed field with old and new value.

The service compares the values the form wants to save with what is in the database right now:

    vSql = "SELECT COALESCE(LAST_NAME, ''), COALESCE(FIRST_NAME, ''), COALESCE(EMAIL, ''), COALESCE(PHONE, ''), COALESCE(IS_ACTIVE, 1) FROM CUSTOMER WHERE CUSTOMER_ID = %%(pCustomerId)"
    sql/data vSql, "CUSTOMERS"
    ...
    getitem vRow, vData, 1
    getitem vOld, vRow, 1
    vNew = $item("LAST_NAME", pNewValues)
    call COMPARE(pCustomerId, "LAST_NAME", vOld, vNew, pUser, pCount, pError)

The new values come in as a Uniface associative list (LAST_NAME=...;FIRST_NAME=...) and are read with $item. That keeps the operation signature stable when more fields are tracked later.

COMPARE writes a row only if something is different:

entry COMPARE
params
    numeric pCustomerId : IN
    string pField : IN
    string pOld : IN
    string pNew : IN
    string pUser : IN
    numeric pCount : INOUT
    string pError : OUT
endparams
    pError = ""
    if (pOld = pNew)
        return 0
    endif
    call INSERT_ROW(pCustomerId, pField, pOld, pNew, pUser, pError)
    if ($status < 0)
        return -1
    endif
    pCount = pCount + 1
    return 0
end

Three details:

• The status is translated into words (active/inactive) before it is compared and stored. A history that says STATUS: 1 -> 0 is useless for the person reading it.
• Values are cut to 200 characters before the insert, matching the column. Silent truncation is acceptable here because this is a log, not the data itself.
• Everything goes through SQL_LIT, which doubles single quotes and turns an empty string into NULL. An e-mail address like o'[e-mail] is a normal value, not an SQL problem.

Besides LOG_CHANGES there is LOG_EVENT for things that are not field changes: CREATED, IMPORTED, MERGED, ANONYMIZED. Later parts use it a lot.

An HTML report (AUDIT_SVC.CHANGE_REPORT) lists all changes in a date range, joined to the customer name, with (deleted) for customers that no longer exist:

SELECT h.CHANGED_AT, COALESCE(h.CHANGED_BY, ''), h.CUSTOMER_ID,
       COALESCE(c.LAST_NAME || ', ' || c.FIRST_NAME, '(deleted)'),
       h.FIELD_NAME, COALESCE(h.OLD_VALUE, ''), COALESCE(h.NEW_VALUE, '')
FROM CUSTOMER_HISTORY h
LEFT JOIN CUSTOMER c ON c.CUSTOMER_ID = h.CUSTOMER_ID
WHERE h.CHANGED_AT >= '2026-09-01' AND h.CHANGED_AT < date('2026-09-30', '+1 day')
ORDER BY h.CHANGED_AT, h.HISTORY_ID

The < date(to, '+1 day') makes the "to" date inclusive for the whole day, which <= '2026-09-30' would not be for a timestamp like 2026-09-30 14:12:00. All values are HTML-escaped before they go into the page.

Login log and lockout

AUDIT_SVC.LOG_LOGIN writes one row per attempt. IS_LOCKED decides whether a user is locked:

    vSql = "SELECT COUNT(*) FROM APP_LOGIN_LOG WHERE USER_NAME = '%%(vUser)' AND SUCCESS = 0 AND LOGIN_AT >= datetime('now', 'localtime', '-15 minutes') AND LOG_ID > COALESCE((SELECT MAX(l.LOG_ID) FROM APP_LOGIN_LOG l WHERE l.USER_NAME = '%%(vUser)' AND l.SUCCESS = 1), 0)"
    sql vSql, "CUSTOMERS"
    ...
    pFailures = $result
    if (pFailures >= 5)
        pLocked = 1
    endif

In words: count the failed attempts of the last 15 minutes that happened after the last successful login. Five or more means locked. The lock expires by itself, and a successful login resets the counter - without a separate "failed attempts" column that has to be kept in sync.

The password rule lives in CHECK_PASSWORD: at least 8 characters, at least one letter and one digit, no spaces. "Is this a letter?" is answered without a character table:

        if ($scan("0123456789", vCh) > 0)
            vDigits = vDigits + 1
        elseif ($lowercase(vCh) != $uppercase(vCh))
            vLetters = vLetters + 1
        endif

A character whose lower and upper case differ is a letter. That also covers umlauts like ä; ß is a corner case I have not tested.

Tests

The tests are part of EXT_TST_SVC and FEAT2_TST_SVC. As always, they insert their own data and end with rollback. The relevant lines from the log:

PASS: USER table starts empty
PASS: USER first login creates an administrator
PASS: USER wrong password is rejected
PASS: USER name is not case-sensitive
PASS: USER short password is rejected
PASS: USER name with space is rejected
PASS: USER is created
PASS: USER duplicate name is rejected
PASS: USER unknown role is rejected
PASS: USER reader logs in with role READ
PASS: USER reader has no EDIT right
PASS: USER reader has READ right
PASS: USER current user is the reader
PASS: USER deactivated user cannot log in
PASS: USER last administrator cannot lose the role
PASS: USER changed password works
PASS: USER list contains both users
PASS: USER without login the developer mode applies
PASS: HISTORY new customer writes one entry
PASS: HISTORY two changed fields write two entries
PASS: HISTORY status change is readable
PASS: HISTORY e-mail change keeps old and new value
PASS: HISTORY unchanged values write nothing
PASS: HISTORY delete for customer removes all
PASS: PASSWORD shorter than 8 characters is rejected
PASS: PASSWORD without digit is rejected
PASS: PASSWORD without letter is rejected
PASS: PASSWORD with a space is rejected
PASS: PASSWORD with letters and digits is accepted
PASS: LOGIN four failures do not lock
PASS: LOGIN five failures lock the user
PASS: LOGIN success resets the lock
PASS: LOGIN log lists the newest entry first
PASS: CHANGES report lists today's changes
PASS: CHANGES report with from after to is rejected

"USER table starts empty" is not a formality: the test deletes all users inside its transaction, so "first login creates an administrator" is tested on a guaranteed empty table and the real users come back with the final rollback.

The lockout test writes four failures, checks "not locked", writes the fifth, checks "locked", writes a success and checks "not locked" again - with the user name in different case in between, because the log stores names in lower case.

Weak spots - what I would attack myself

I asked for this list deliberately. If you read this as a security design, these are the points you should raise:

1. The password hash is fast. HMAC-SHA256 with a key derived from the user name is a salted fast hash. Anyone who gets customers.db can try billions of passwords per second on a GPU. The right tool is a slow key derivation function (PBKDF2, bcrypt, scrypt, Argon2) with a random per-user salt. I have not checked whether Uniface 10's $encode offers one; if not, it has to come from a 3GL or .NET call.
2. Two password rules. USER_SVC requires 6 characters, AUDIT_SVC.CHECK_PASSWORD requires 8 plus letter and digit. The stricter rule was added later and is not yet used by CREATE_USER. That is a real inconsistency, and exactly the kind that survives into production.
3. The lockout fails open. If the COUNT query fails, IS_LOCKED returns "not locked". For a local desktop app I chose availability; for anything reachable over a network it should be the other way round.
4. Lockout by user name enables denial of service. Anyone can lock out the administrator by typing the name five times with a wrong password. Common mitigations are a delay instead of a hard lock, or locking per workstation.
5. The "developer mode" fallback. Without a login, CURRENT_USER returns EDIT. Once the login dialog exists, that fallback must go.
6. The real boundary is the file. This is a two-tier desktop app with a SQLite file. Whoever can open customers.db with any SQLite tool bypasses every role check and can rewrite the history. Roles here protect against mistakes, not against a determined colleague. If that matters, the data belongs on a database server with its own permissions, or behind a service layer on another machine.
7. Rights are not enforced in the services. HAS_RIGHT is a question the caller has to ask. CUSTOMER_SVC.SAVE does not ask it. Given point 6 that is consistent, but it should be a conscious decision, and it should be written down.

Takeaways

Order your roles and a right check becomes one comparison. Unknown rights are denied by construction.

Store the history as field/old/new rows and translate codes into words before you store them. It costs one table and makes "who changed the e-mail address?" a query instead of an investigation.

Derive state from the log instead of keeping counters in sync: "failures since the last success within 15 minutes" is one SQL statement.

Write down what your security does not do. Everything in the list above was visible in the code, and everything in it would have come up in the comments anyway.

Next part: importing customers from a CSV file with a dry run, and backing up a SQLite file that Uniface keeps open.