Skip to content

Security question management

This operation manages the per-user security-question answers stored in EXT_USER_SECURITY_QUESTION and the question catalog loaded from EXT_USER_SEC_QUESTION_CODE. It supports initial account setup, subsequent edits, and password-recovery style retrievals.

Field Value
Operation ID OP-EU-03
Trigger User-account workflows load question choices or save question answers
Inputs User ID, security-question code, answer text, culture code
Outputs Persisted security-question row(s) or localized active question choices
Primary components clsUserSecurityQuestionCollection.GetAllSecurityQuestions, clsUserSecurityQuestionCollection.GetUserSecurityQuestionsByUserID
Entry point Business\SAIF.Business.External.Users\clsUserSecurityQuestion.vb

Steps

  1. Allocate a question row when a new answer is created. Create pulls the next EXT_USER_SEC_QUESTION_SEQ value so the answer row can be inserted later.
  2. Load the current answers for a user. GetUserSecurityQuestionsByUserID selects all stored answers for a user, joins the code table for question text and expiration, and returns a collection keyed by UserID (SQL-028, EXT_USER_SECURITY_QUESTION, EXT_USER_SEC_QUESTION_CODE).
  3. Load the active question catalog for selection UIs. GetAllSecurityQuestions returns non-expired question codes and localized text by filtering the code table to rows whose expiration date is null or in the future (SQL-029, EXT_USER_SEC_QUESTION_CODE).
  4. Validate each answer before persistence. The question object requires a non-empty answer, a non-empty question code, and a question code that still appears in the active catalog.
  5. If the selected question code is expired or absent from the active catalog, validation fails and reports the expired-question message.
  6. Validate the collection-level rules. The collection rejects duplicate question codes and, when editing existing rows, requires the collection count to remain at three questions.
  7. Insert a new answer row. BeforeInsert stamps create/update metadata and writes the question code, answer text, and owning user ID into EXT_USER_SECURITY_QUESTION (SQL-008, EXT_USER_SECURITY_QUESTION).
  8. Update an existing answer row with optimistic concurrency. BeforeUpdate binds the same values plus UPDATE_NUM; a zero-row result becomes a DB-contention error (BR-EU-05, SQL-049, EXT_USER_SECURITY_QUESTION).
  9. Delete an answer row by sequence number. BeforeDelete removes the row using EXT_USER_SEC_QUESTION_SEQ_NUM (SQL-055, EXT_USER_SECURITY_QUESTION).

Process flow

The following diagram visualizes this operation's control flow.

flowchart TD
    Start([Question load or save request]) --> Choice{Load choices or answers?}
    Choice -->|Choices| Catalog[Load active question catalog]
    Choice -->|Answers| Answers[Load stored user answers]
    Answers --> Edit[User adds, edits, or removes answers]
    Catalog --> Edit
    Edit --> Validate[Validate answer text, code, duplicates, and count]
    Validate --> Persist[Insert, update, or delete row]
    Persist --> Done([Return saved questions])

Failure and edge-case behavior

  • An expired question code can still exist on a stored row, but the next edit forces the user to replace it with an active code.
  • Collection validation prevents the same question from being answered twice.
  • Updates fail with DB-contention messaging when UPDATE_NUM no longer matches the stored row (BR-EU-05).

Sources

  • Business\SAIF.Business.External.Users\clsUserSecurityQuestion.vb
  • Business\SAIF.Business.External.Users\clsUser.vb