ores.iam.account

Table of Contents

An account that can authenticate against the system: one row per user, service, algorithm or LLM identity, carrying the password material, the TOTP secret, the email address and the optional profile and reporting links. The table is bi-temporal and audited (see projects/ores.sql/create/iam/iam_accounts_create.sql): it carries version, the four audit columns and the valid_from=/=valid_to pair with the GIST exclusion, so the model takes the ordinary audited shape and needs no shape flag.

The table is a composite parent: ores_iam_accounts_touch_version_fn lets a child entity (account contact information, party association) bump this account's own version when the child is written. The model declares :generate_touch_function: true, which renders that function under its existing name rather than leaving it hand-written.

The model describes the table and nothing else. Two columns need care:

The generated read surface is live, and it does not collide with the hand-written one. The hand-written account_operations_protocol.hpp owns the writes, iam.v1.accounts.{save,delete,update,lock,unlock,change-password,reset-password,select-party,set-default-party,switch-party,update-email,publish-from-dq}, and the generated account_protocol.hpp owns the reads, iam.v1.accounts.list and iam.v1.accounts.get, with the version reads alongside them. Both registrars are composed in ores.iam/core/src/messaging/registrar.cpp. This entity sets :read_only: true, so the generated half carries no write verb and the split falls out of the flag rather than out of a suppression.

Two behavioural facets are switched off, each with a reason:

1. Flags

2. Columns

2.1. id

Unique identifier for the account.

2.2. account_type

Account type classification. Determines account capabilities: user accounts can login with password, while service, algorithm and llm accounts authenticate via sessions. 

std::string("user")

2.3. username

Unique username for login purposes. It is the natural key, so the generated table adds the partial unique index on (tenant_id, username) the hand-written table already had. 

std::string(faker::internet::username(std::string(faker::person::firstName()), std::string(faker::person::lastName())))

2.4. full_name

The account holder's full (real) name. Not every account represents a person (service, algorithm and llm accounts leave this empty), and this is the only place a human account's real name is recorded. 

std::string(faker::person::firstName()) + " " + std::string(faker::person::lastName())

2.5. password_hash

Hashed password for secure authentication. 

ctx.alphanumeric(64)

2.6. password_salt

Salt used in password hashing for additional security. 

ctx.alphanumeric(32)

2.7. service_password_hash

SHA-256 hash of a service account's password, stored as hex. It is reached only by check_service_credentials and never travels on the wire, so it is a SQL-only column: the generated domain struct omits it and the entity struct and mapper keep it.

2.8. totp_secret

Time-based One-Time Password secret for two-factor authentication. 

ctx.alphanumeric(32)

2.9. email

Email address associated with the account. It is unique within a tenant, so the generated table adds the partial unique index on (tenant_id, email) the hand-written table already had. 

std::string(faker::internet::email(std::string(faker::person::firstName()), std::string(faker::person::lastName())))

2.10. default_party_id

Party to log into automatically when quick-login is enabled. Soft reference to a party owned by ores.refdata; unset means the account must always go through the party picker.

2.11. image_id

Profile picture for this account. Soft reference to an image owned by ores.assets; unset means the account has no profile picture.

2.12. job_title

Job title / functional role of the person holding this account (for example "Head of Desk", "Senior Trader"). Distinct from the RBAC role assignments, which grant coarse permission sets rather than describe what the person actually does. 

faker::person::jobTitle()

2.13. reports_to_account_id

The account this person reports to, capturing the functional reporting line. Soft self-reference to another row in this same table; unset means no reporting line is recorded.

3. Artefact columns

The import carries business_unit_code, role, reports_to_username and photo_key, all four written by the Acme populate and all four absent from the store in that form: business_unit_code is informational only today because ores_iam_accounts_tbl has no business-unit FK, and reports_to_username is resolved to reports_to_account_id at publish. The password salt, the service password hash and the TOTP secret are deliberately absent: the publish supplies empty values and staging is not where those belong.

3.1. id

3.2. version

3.3. username

3.4. full_name

3.5. email

3.6. password_hash

3.7. account_type

3.8. business_unit_code

3.9. role

3.10. job_title

3.11. reports_to_username

3.12. photo_key

4. SQL

4.1. Flags

5. Foreign keys

5.1. image_id

Profile picture for this account, validated against the images table when set.

5.2. reports_to_account_id

Reporting line, validated against this same table when set.

6. Insert trigger

6.1. Validations

column validation_function
account_type ores_iam_validate_account_type_fn

7. C++

7.1. Flags

7.2. Repository

7.3. Domain includes

#include <boost/uuid/nil_generator.hpp>
#include <boost/uuid/uuid.hpp>
#include <chrono>
#include <optional>
#include <string>

7.4. Entity includes

#include <string>
#include "sqlgen/PrimaryKey.hpp"
#include "sqlgen/Timestamp.hpp"

7.5. Conventions

7.6. Table display

column header
id ID (UUID)
username Username
full_name Full Name
email Email
job_title Job Title
change_reason_code Change Reason
modified_by Modified By
recorded_at Recorded At
version Version

7.7. Presentation

The screen's declaration. An account is addressed by its username, the natural key, which is what every caller already holds: an operator knows the name an account signs in with, not its surrogate id. The id stays for foreign-key stability, which is what it is for, and no caller states it.

username is the key the module's own shell commands already take, so the declaration states what its callers already assume rather than asking them to translate.

7.7.1. Detail fields

field label widget type is_key is_required placeholder
username Username usernameEdit line_edit true true Enter the account name
full_name Full Name fullNameEdit line_edit     Enter the full name
email Email emailEdit line_edit     Enter the email address
job_title Job Title jobTitleEdit line_edit     Enter the job title
account_type Type accountTypeEdit line_edit     Enter the account type

7.7.2. Columns

enum_name field header type width
Username username Username string 200
FullName full_name Full Name string 240
Email email Email string 260
JobTitle job_title Job Title string 200
AccountType account_type Type string 140

7.8. Paste blocks

7.8.1. read_all

Reads every version of every account. The generated CRUD set reads all versions of one account by primary key, which is what the history path needs; the CLI export and the repository test read the whole table, so that unfiltered read stays declared here, unscoped by tenant exactly as the hand-written repository was. 

std::vector<domain::account> read_all(context ctx);

std::vector<domain::account> account_repository::read_all(context ctx) {
    const auto query = sqlgen::read<std::vector<account_entity>> | order_by("valid_from"_c.desc());

    return execute_read_query<account_entity, domain::account>(
        ctx,
        query,
        [](const auto& entities) { return account_mapper::map(entities); },
        lg(),
        "Reading all accounts.");
}

7.8.2. read_latest_by_username

Reads the latest active account with the given username. The generated CRUD set reads by primary key only, and the login and password-reset paths address the row by username, so the read stays declared here. Deliberately unscoped by tenant, exactly as the hand-written repository was: the login flow resolves the account before the tenant is known. 

std::vector<domain::account> read_latest_by_username(context ctx, const std::string& username);

std::vector<domain::account>
account_repository::read_latest_by_username(context ctx, const std::string& username) {
    BOOST_LOG_SEV(lg(), debug) << "Reading latest account by username: " << username;

    static const auto max(make_timestamp(MAX_TIMESTAMP, lg()));
    const auto query = sqlgen::read<std::vector<account_entity>> |
                       where("username"_c == username && "valid_to"_c == max.value()) |
                       order_by("valid_from"_c.desc());

    return execute_read_query<account_entity, domain::account>(
        ctx,
        query,
        [](const auto& entities) { return account_mapper::map(entities); },
        lg(),
        "Reading latest account by username");
}

7.8.3. read_latest_by_email

Reads the latest active account with the given email. The password-reset and self-service paths address the row by email, so the read stays declared here rather than forcing those callers onto a full table scan. 

std::vector<domain::account> read_latest_by_email(context ctx, const std::string& email);

std::vector<domain::account>
account_repository::read_latest_by_email(context ctx, const std::string& email) {
    BOOST_LOG_SEV(lg(), debug) << "Reading latest account by email: " << email;

    static const auto max(make_timestamp(MAX_TIMESTAMP, lg()));
    const auto query = sqlgen::read<std::vector<account_entity>> |
                       where("email"_c == email && "valid_to"_c == max.value()) |
                       order_by("valid_from"_c.desc());

    return execute_read_query<account_entity, domain::account>(
        ctx,
        query,
        [](const auto& entities) { return account_mapper::map(entities); },
        lg(),
        "Reading latest account by email");
}

7.8.4. check_service_credentials

Verifies a non-human account's credentials without loading the account through the domain type: the stored hash is a SQL-only column, so the comparison runs against the entity. Returns the account UUID on success, or nullopt when the account is absent, is a user account, or the SHA-256 of the supplied password does not match. 

#include <array>
#include <format>
#include <openssl/evp.h>
#include <boost/lexical_cast.hpp>
#include <boost/uuid/uuid_io.hpp>

std::optional<boost::uuids::uuid> check_service_credentials(context ctx,
                                                            const std::string& username,
                                                            const std::string& password);

std::optional<boost::uuids::uuid>
account_repository::check_service_credentials(context ctx,
                                              const std::string& username,
                                              const std::string& password) {
    BOOST_LOG_SEV(lg(), debug) << "Checking service credentials for: " << username;

    static const auto max(make_timestamp(MAX_TIMESTAMP, lg()));
    const auto query = sqlgen::read<std::vector<account_entity>> |
                       where("username"_c == username && "valid_to"_c == max.value()) | limit(1);

    const auto r = sqlgen::session(ctx.connection_pool()).and_then(query);
    ensure_success(r, lg());

    if (!r || r->empty())
        return std::nullopt;

    const auto& entity = r->front();
    if (entity.account_type == "user") {
        BOOST_LOG_SEV(lg(), debug) << "Rejecting user account for service login: " << username;
        return std::nullopt;
    }

    std::array<unsigned char, EVP_MAX_MD_SIZE> digest{};
    unsigned int digest_len = 0;
    EVP_Digest(password.data(), password.size(), digest.data(), &digest_len, EVP_sha256(), nullptr);

    std::string computed_hash;
    computed_hash.reserve(digest_len * 2);
    for (unsigned int i = 0; i < digest_len; ++i)
        computed_hash += std::format("{:02x}", digest[i]);

    if (!entity.service_password_hash.has_value() ||
        computed_hash != entity.service_password_hash.value()) {
        BOOST_LOG_SEV(lg(), debug) << "Password mismatch for service account: " << username;
        return std::nullopt;
    }

    return boost::lexical_cast<boost::uuids::uuid>(entity.id.value());
}

8. See also

  • ores.iam — component group overview.

Emacs 29.3 (Org mode 9.6.15)