Migration from 1.0.0 to 1.1.0

Updated Minimum Java Runtime Version

The minimum required Java runtime version has been updated to Java 11. This version is suggested for deployment because version 11 is a long term support (LTS) version of Java. The software should work`` on Java versions 11, 12, 13, 14, and 15.

Embedded Bouncy Castle Library (Version 1.68)

Bouncy Castle library has been updated to version 1.68 and it is now included directly in the application bundle (*.war) for both Web Flow and Next Step.

You can now safely remove any previous configurations of Bouncy Castle library you made earlier, such as installing BC globally inside JRE, putting it in the container’s lib folder, or creating JBoss or Wildfly modules.

Database Changes

Following database changes were introduced in version 1.1.0:

DDL update script for Oracle:

-- New sequences
CREATE SEQUENCE ns_application_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_credential_policy_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_otp_policy_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_user_contact_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_user_identity_history_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_role_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_user_role_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_user_alias_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_hashing_config_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_credential_definition_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_otp_definition_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE SEQUENCE ns_credential_history_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

-- Table ns_operation_method_config stores configuration of authentication methods per operation name.
CREATE TABLE ns_operation_method_config (
  operation_name     VARCHAR2(32 CHAR) NOT NULL,             -- Name of the operation, for example "login" or "authorize_payment".
  auth_method        VARCHAR2(32 CHAR) NOT NULL,             -- Name of the authentication method: APPROVAL_SCA, CONSENT, INIT, LOGIN_SCA, POWERAUTH_TOKEN, SHOW_OPERATION_DETAIL, SMS_KEY, USER_ID_ASSIGN, USERNAME_PASSWORD_AUTH, OTP_CODE.
  max_auth_fails     INTEGER NOT NULL,                       -- Maximum allowed number of authentication fails.
  PRIMARY KEY (operation_name, auth_method),
  CONSTRAINT ns_operation_method_fk1 FOREIGN KEY (operation_name) REFERENCES ns_operation_config (operation_name),
  CONSTRAINT ns_operation_method_fk2 FOREIGN KEY (auth_method) REFERENCES ns_auth_method (auth_method)
);

-- Table ns_application stores Next Step applications.
CREATE TABLE ns_application (
  application_id         NUMBER(19,0) NOT NULL PRIMARY KEY,       -- Next Step application ID (autogenerated).
  name                   VARCHAR2(256 CHAR) NOT NULL,             -- Application name used for identification.
  description            VARCHAR2(256 CHAR),                      -- Description of the application.
  status                 VARCHAR2(32 CHAR) NOT NULL,              -- Application status: ACTIVE, REMOVED.
  timestamp_created      TIMESTAMP,                               -- Timestamp when application was created.
  timestamp_last_updated TIMESTAMP                                -- Timestamp when application was last updated.
);

-- Table ns_credential_policy stores credential policies.
CREATE TABLE ns_credential_policy (
  credential_policy_id       NUMBER(19,0) NOT NULL PRIMARY KEY,             -- Credential policy ID (autogenerated).
  name                       VARCHAR2(256 CHAR) NOT NULL,                   -- Credential policy name used for identification.
  description                VARCHAR2(256 CHAR),                            -- Description of the credential policy.
  status                     VARCHAR2(32 CHAR) NOT NULL,                    -- Credential policy status: ACTIVE, REMOVED.
  username_length_min        NUMBER(10,0),                                  -- Minimum length of username.
  username_length_max        NUMBER(10,0),                                  -- Maximum length of username.
  username_allowed_pattern   VARCHAR2(256 CHAR),                            -- Allowed pattern for username (regular expression).
  credential_length_min      NUMBER(10,0),                                  -- Minimum length of credential value.
  credential_length_max      NUMBER(10,0),                                  -- Maximum length of credential value.
  limit_soft                 NUMBER(10,0),                                  -- Soft limit of failed attempts.
  limit_hard                 NUMBER(10,0),                                  -- Hard limit of failed attempts.
  check_history_count        NUMBER(10,0) DEFAULT 0 NOT NULL,               -- Number of historical credential values to check.
  rotation_enabled           NUMBER(1) DEFAULT 0 NOT NULL,                  -- Whether credential rotation is enabled.
  rotation_days              NUMBER(10,0),                                  -- Number of days for credential rotation.
  credential_temp_expiration INTEGER,                                       -- Expiration time of TEMPORARY credentials in seconds.
  username_gen_algorithm     VARCHAR2(256 CHAR) DEFAULT 'DEFAULT' NOT NULL, -- Algorithm used for generating the username.
  username_gen_param         VARCHAR2(4000 CHAR) NOT NULL,                  -- Parameters used when generating the username.
  credential_gen_algorithm   VARCHAR2(256 CHAR) DEFAULT 'DEFAULT' NOT NULL, -- Algorithm used for generating the credential.
  credential_gen_param       VARCHAR2(4000 CHAR) NOT NULL,                  -- Parameters used when generating the credential.
  credential_val_param       VARCHAR2(4000 CHAR) NOT NULL,                  -- Parameters used when validating the credential.
  timestamp_created          TIMESTAMP,                                     -- Timestamp when policy was created.
  timestamp_last_updated     TIMESTAMP                                      -- Timestamp when policy was last updated.
);

-- Table ns_credential_policy stores one time password policies.
CREATE TABLE ns_otp_policy (
  otp_policy_id          NUMBER(19,0) NOT NULL PRIMARY KEY,                 -- One time password policy ID (autogenerated).
  name                   VARCHAR2(256 CHAR) NOT NULL,                       -- One time password policy name used for identification.
  description            VARCHAR2(256 CHAR),                                -- Description of the one time password policy.
  status                 VARCHAR2(32 CHAR) NOT NULL,                        -- One time password policy status: ACTIVE, REMOVED.
  length                 NUMBER(10,0) NOT NULL,                             -- One time password length.
  attempt_limit          NUMBER(10,0),                                      -- Maximum number of authentication attempts.
  expiration_time        NUMBER(10,0),                                      -- One time password expiration time.
  gen_algorithm          VARCHAR2(256 CHAR) DEFAULT 'DEFAULT' NOT NULL,     -- Algorithm used for generating the one time password.
  gen_param              VARCHAR2(4000 CHAR) NOT NULL,                      -- Parameters used when generating the OTP.
  timestamp_created      TIMESTAMP,                                         -- Timestamp when policy was created.
  timestamp_last_updated TIMESTAMP                                          -- Timestamp when policy was last updated.
);

-- Table ns_user_identity stores user identities.
CREATE TABLE ns_user_identity (
  user_id                VARCHAR2(256 CHAR) NOT NULL PRIMARY KEY,           -- User identity identifier (not autogenerated).
  status                 VARCHAR2(32 CHAR) NOT NULL,                        -- User identity status: ACTIVE, BLOCKED, REMOVED.
  extras                 CLOB,                                              -- Extra attributes with data related to user identity.
  timestamp_created      TIMESTAMP,                                         -- Timestamp when user identity was created.
  timestamp_last_updated TIMESTAMP                                          -- Timestamp when user identity was last updated.
);

-- Table ns_user_contact stores contact information for user identities.
CREATE TABLE ns_user_contact (
  user_contact_id        NUMBER(19,0) NOT NULL PRIMARY KEY,                 -- User contact identifier (autogenerated).
  user_id                VARCHAR2(256 CHAR) NOT NULL,                       -- User identity identifier.
  name                   VARCHAR2(256 CHAR) NOT NULL,                       -- User contact name used for identification.
  type                   VARCHAR2(32 CHAR) NOT NULL,                        -- User contact type: PHONE, EMAIL, OTHER.
  value                  VARCHAR2(256 CHAR) NOT NULL,                       -- User contact value.
  is_primary             NUMBER(1) DEFAULT 0 NOT NULL,                      -- Whether contact is primary.
  timestamp_created      TIMESTAMP,                                         -- Timestamp when contact was created.
  timestamp_last_updated TIMESTAMP,                                         -- Timestamp when contact was last updated.
  CONSTRAINT ns_user_contact_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_user_identity stores history for user identities.
CREATE TABLE ns_user_identity_history (
  user_identity_history_id NUMBER(19,0) NOT NULL PRIMARY KEY,               -- User identity history identifier (autogenerated).
  user_id                  VARCHAR2(256 CHAR) NOT NULL,                     -- User identity identifier.
  status                   VARCHAR2(32 CHAR) NOT NULL,                      -- User identity status: ACTIVE, BLOCKED, REMOVED.
  roles                    VARCHAR2(256 CHAR),                              -- Assigned user roles.
  extras                   CLOB,                                            -- Extra attributes with data related to user identity.
  timestamp_created        TIMESTAMP,                                       -- Timestamp when user identity snapshot was created.
  CONSTRAINT ns_user_identity_history_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_role stores user role definitions.
CREATE TABLE ns_role (
  role_id                NUMBER(19,0) NOT NULL PRIMARY KEY,                 -- Role identifier (autogenerated).
  name                   VARCHAR2(256 CHAR) NOT NULL,                       -- Role name used for identification.
  description            VARCHAR2(256 CHAR),                                -- Description of role.
  timestamp_created      TIMESTAMP,                                         -- Timestamp when role was created.
  timestamp_last_updated TIMESTAMP                                          -- Timestamp when role was last updated.
);

-- Table ns_user_role stores assignment of roles to user identities.
CREATE TABLE ns_user_role (
  user_role_id             NUMBER(19,0) NOT NULL PRIMARY KEY,               -- User role identifier (autogenerated).
  user_id                  VARCHAR2(256 CHAR) NOT NULL,                     -- User identity identifier.
  role_id                  NUMBER(19,0) NOT NULL,                           -- Role identifier.
  status                   VARCHAR2(32 CHAR) NOT NULL,                      -- User role status: ACTIVE, REMOVED.
  timestamp_created        TIMESTAMP,                                       -- Timestamp when user role was created.
  timestamp_last_updated   TIMESTAMP,                                       -- Timestamp when user role was last updated.
  CONSTRAINT ns_role_identity_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id),
  CONSTRAINT ns_user_role_fk FOREIGN KEY (role_id) REFERENCES ns_role (role_id)
);

-- Table ns_user_alias stores user aliases.
CREATE TABLE ns_user_alias (
  user_alias_id            NUMBER(19,0) NOT NULL PRIMARY KEY,               -- User alias identifier (autogenerated).
  user_id                  VARCHAR2(256 CHAR) NOT NULL,                     -- User identity identifier.
  name                     VARCHAR2(256 CHAR) NOT NULL,                     -- User alias name used for identification.
  value                    VARCHAR2(256 CHAR) NOT NULL,                     -- User alias value.
  status                   VARCHAR2(32 CHAR) NOT NULL,                      -- User alias status: ACTIVE, REMOVED.
  extras                   CLOB,                                            -- Extra attributes with data related to user alias.
  timestamp_created        TIMESTAMP,                                       -- Timestamp when user alias was created.
  timestamp_last_updated   TIMESTAMP,                                       -- Timestamp when user alias was last updated.
  CONSTRAINT ns_user_alias_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_hashing_config stores configuration of hashing algorithms.
CREATE TABLE ns_hashing_config (
  hashing_config_id        NUMBER(19,0) NOT NULL PRIMARY KEY,               -- Hashing configuration identifier (autogenerated).
  name                     VARCHAR2(256 CHAR) NOT NULL,                     -- Hashing configuration name used for identification.
  algorithm                VARCHAR2(256 CHAR) NOT NULL,                     -- Hashing algorithm name.
  status                   VARCHAR2(32 CHAR) NOT NULL,                      -- Hashing configuration status: ACTIVE, REMOVED.
  parameters               VARCHAR2(256 CHAR),                              -- Hashing algorithm parameters.
  timestamp_created        TIMESTAMP,                                       -- Timestamp when hashing configuration was created.
  timestamp_last_updated   TIMESTAMP                                        -- Timestamp when hashing configuration was last updated.
);

-- Table ns_credential_definition stores definitions of credentials with reference to credential policies and applications.
CREATE TABLE ns_credential_definition (
  credential_definition_id   NUMBER(19,0) NOT NULL PRIMARY KEY,               -- Credential definition identifier (autogenerated).
  name                       VARCHAR2(256 CHAR) NOT NULL,                     -- Credential definition name used for identification.
  description                VARCHAR2(256 CHAR),                              -- Description of the credential definition.
  application_id             NUMBER(19,0) NOT NULL,                           -- Application identifier.
  organization_id            VARCHAR2(256 CHAR),                              -- Organization this credential belongs to.
  credential_policy_id       NUMBER(19,0) NOT NULL,                           -- Credential policy identifier.
  category                   VARCHAR2(32 CHAR) NOT NULL,                      -- Credential category: PASSWORD, PIN, OTHER.
  encryption_enabled         NUMBER(1) DEFAULT 0 NOT NULL,                    -- Whether encryption of stored credentials is enabled.
  encryption_algorithm       VARCHAR2(256 CHAR),                              -- Algorithm used for stored credential encryption.
  hashing_enabled            NUMBER(1) DEFAULT 0 NOT NULL,                    -- Whether credential hashing is enabled.
  hashing_config_id          NUMBER(19,0),                                    -- Algorithm used for credential hashing.
  e2e_encryption_enabled     NUMBER(1) DEFAULT 0 NOT NULL,                    -- Whether end to end encryption of credential values is enabled.
  e2e_encryption_algorithm   VARCHAR2(256 CHAR),                              -- Algorithm used for end to end encryption of credential.
  e2e_encryption_transform   VARCHAR2(256 CHAR),                              -- Cipher transformation used for end to end encryption of credential.
  e2e_encryption_temporary   NUMBER(1) DEFAULT 0 NOT NULL,                    -- Whether end to end encryption of temporary credential values is enabled.
  data_adapter_proxy_enabled NUMBER(1) DEFAULT 0 NOT NULL,                    -- Whether credential API calls should be proxied through Data Adapter.
  status                     VARCHAR2(32 CHAR) NOT NULL,                      -- Credential definition status: ACTIVE, REMOVED.
  timestamp_created          TIMESTAMP,                                       -- Timestamp when credential definition was created.
  timestamp_last_updated     TIMESTAMP,                                       -- Timestamp when credential definition was last updated.
  CONSTRAINT ns_credential_application_fk FOREIGN KEY (application_id) REFERENCES ns_application (application_id),
  CONSTRAINT ns_credential_policy_fk FOREIGN KEY (credential_policy_id) REFERENCES ns_credential_policy (credential_policy_id),
  CONSTRAINT ns_credential_hash_fk FOREIGN KEY (hashing_config_id) REFERENCES ns_hashing_config (hashing_config_id),
  CONSTRAINT ns_application_organization_fk FOREIGN KEY (organization_id) REFERENCES ns_organization (organization_id)
);

-- Table ns_otp_definition stores definitions of one time passwords with reference to credential policies and applications.
CREATE TABLE ns_otp_definition (
  otp_definition_id          NUMBER(19,0) NOT NULL PRIMARY KEY,               -- One time password definition identifier (autogenerated).
  name                       VARCHAR2(256 CHAR) NOT NULL,                     -- One time password definition name used for identification.
  description                VARCHAR2(256 CHAR),                              -- Description of the one time password definition.
  application_id             NUMBER(19,0) NOT NULL,                           -- Application identifier.
  otp_policy_id              NUMBER(19,0) NOT NULL,                           -- One time password policy identifier.
  encryption_enabled         NUMBER(1) DEFAULT 0 NOT NULL,                    -- Whether encryption of stored one time passwords is enabled.
  encryption_algorithm       VARCHAR2(256 CHAR),                              -- Algorithm used for stored one time password encryption.
  data_adapter_proxy_enabled NUMBER(1) DEFAULT 0 NOT NULL,                    -- Whether one time password API calls should be proxied through Data Adapter.
  status                     VARCHAR2(32 CHAR) NOT NULL,                      -- One time password definition status: ACTIVE, REMOVED.
  timestamp_created          TIMESTAMP,                                       -- Timestamp when one time password definition was created.
  timestamp_last_updated     TIMESTAMP,                                       -- Timestamp when one time password definition was last updated.
  CONSTRAINT ns_otp_application_fk FOREIGN KEY (application_id) REFERENCES ns_application (application_id),
  CONSTRAINT ns_otp_policy_fk FOREIGN KEY (otp_policy_id) REFERENCES ns_otp_policy (otp_policy_id)
);

-- Table ns_credential_storage stores credential values, counters and other data related to credentials.
CREATE TABLE ns_credential_storage (
  credential_id                    VARCHAR2(256 CHAR) NOT NULL PRIMARY KEY, -- Credential identifier (generated by application as UUID).
  credential_definition_id         NUMBER(19,0) NOT NULL,                   -- Credential definition identifier.
  user_id                          VARCHAR2(256 CHAR) NOT NULL,             -- User identity identifier.
  type                             VARCHAR2(32 CHAR) NOT NULL,              -- Credential type: PERMANENT, TEMPORARY.
  user_name                        VARCHAR2(256 CHAR),                      -- Username.
  value                            VARCHAR2(256 CHAR) NOT NULL,             -- Credential value.
  status                           VARCHAR2(32 CHAR) NOT NULL,              -- Credential status: ACTIVE, BLOCKED_TEMPORARY, BLOCKED_PERMANENT, REMOVED.
  attempt_counter                  NUMBER(19,0) DEFAULT 0 NOT NULL,         -- Attempt counter for both successful and failed attempts.
  failed_attempt_counter_soft      NUMBER(19,0) DEFAULT 0 NOT NULL,         -- Soft failed attempt counter.
  failed_attempt_counter_hard      NUMBER(19,0) DEFAULT 0 NOT NULL,         -- Hard failed attempt counter.
  encryption_algorithm             VARCHAR2(256 CHAR),                      -- Encryption algorithm used for encrypting credential value.
  hashing_config_id                NUMBER(19,0),                            -- Hashing configuration used when credential value was hashed.
  timestamp_created                TIMESTAMP,                               -- Timestamp when credential was created.
  timestamp_expires                TIMESTAMP,                               -- Timestamp when credential expires.
  timestamp_blocked                TIMESTAMP,                               -- Timestamp when credential was blocked.
  timestamp_last_updated           TIMESTAMP,                               -- Timestamp when credential was last updated.
  timestamp_last_credential_change TIMESTAMP,                               -- Timestamp when credential value was last changed.
  timestamp_last_username_change   TIMESTAMP,                               -- Timestamp when username value was last changed.
  CONSTRAINT ns_credential_definition_fk FOREIGN KEY (credential_definition_id) REFERENCES ns_credential_definition (credential_definition_id),
  CONSTRAINT ns_credential_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_credential_history stores historical values of credentials.
CREATE TABLE ns_credential_history (
  credential_history_id       NUMBER(19,0) NOT NULL PRIMARY KEY,            -- Credential history identifier (autogenerated).
  credential_definition_id    NUMBER(19,0) NOT NULL,                        -- Credential identifier.
  user_id                     VARCHAR2(256 CHAR) NOT NULL,                  -- User identity identifier.
  user_name                   VARCHAR2(256 CHAR),                           -- Username.
  value                       VARCHAR2(256 CHAR) NOT NULL,                  -- Credential value.
  encryption_algorithm        VARCHAR2(256 CHAR),                           -- Encryption algorithm used for encrypting credential value.
  hashing_config_id           NUMBER(19,0),                                 -- Hashing configuration used when credential value was hashed.
  timestamp_created           TIMESTAMP,                                    -- Timestamp when credential was created.
  CONSTRAINT ns_credential_history_definition_fk FOREIGN KEY (credential_definition_id) REFERENCES ns_credential_definition (credential_definition_id),
  CONSTRAINT ns_credential_history_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_otp_storage stores one time password values, counters and other data related to one time passwords.
CREATE TABLE ns_otp_storage (
  otp_id                      VARCHAR2(256 CHAR) NOT NULL PRIMARY KEY,      -- One time password identifier (generated by application as UUID).
  otp_definition_id           NUMBER(19,0) NOT NULL,                        -- One time password definition identifier.
  user_id                     VARCHAR2(256 CHAR),                           -- User identifier.
  credential_definition_id    NUMBER(19,0),                                 -- Credential definition identifier used when updating failed counter.
  operation_id                VARCHAR2(256 CHAR),                           -- Operation identifier.
  value                       VARCHAR2(256 CHAR),                           -- One time password value.
  salt                        BLOB,                                         -- Cryptographic salt used when generating one time password.
  status                      VARCHAR2(32 CHAR) NOT NULL,                   -- One time password status: ACTIVE, USED, BLOCKED, REMOVED.
  otp_data                    CLOB,                                         -- Data used for generating one time password.
  attempt_counter             NUMBER(19,0) DEFAULT 0 NOT NULL,              -- One time password attempt counter.
  failed_attempt_counter      NUMBER(19,0) DEFAULT 0 NOT NULL,              -- One time password failed attempt counter.
  encryption_algorithm        VARCHAR2(256 CHAR),                           -- Encryption algorithm used for encrypting OTP value.
  timestamp_created           TIMESTAMP,                                    -- Timestamp when one time password was created.
  timestamp_verified          TIMESTAMP,                                    -- Timestamp when one time password was verified.
  timestamp_blocked           TIMESTAMP,                                    -- Timestamp when one time password was blocked.
  timestamp_expires           TIMESTAMP,                                    -- Timestamp when one time password expires.
  CONSTRAINT ns_otp_definition_fk FOREIGN KEY (otp_definition_id) REFERENCES ns_otp_definition (otp_definition_id),
  CONSTRAINT ns_otp_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_authentication stores authentication attempts.
CREATE TABLE ns_authentication (
  authentication_id           VARCHAR2(256 CHAR) NOT NULL PRIMARY KEY,      -- Authentication identifier (autogenerated).
  user_id                     VARCHAR2(256 CHAR),                           -- User identity identifier.
  type                        VARCHAR2(32 CHAR) NOT NULL,                   -- Authentication type: CREDENTIAL, OTP, CREDENTIAL_OTP.
  credential_id               VARCHAR2(256 CHAR),                           -- Credential identifier.
  otp_id                      VARCHAR2(256 CHAR),                           -- One time password identifier.
  operation_id                VARCHAR2(256 CHAR),                           -- Operation identifier.
  result                      VARCHAR2(32 CHAR) NOT NULL,                   -- Overall authentication result.
  result_credential           VARCHAR2(32 CHAR),                            -- Authentication result for credential authentication.
  result_otp                  VARCHAR2(32 CHAR),                            -- Authentication result for one time password authentication.
  timestamp_created           TIMESTAMP,                                    -- Timestamp when authentication record was created.
  CONSTRAINT ns_auth_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id),
  CONSTRAINT ns_auth_credential_fk FOREIGN KEY (credential_id) REFERENCES ns_credential_storage (credential_id),
  CONSTRAINT ns_auth_otp_fk FOREIGN KEY (otp_id) REFERENCES ns_otp_storage (otp_id),
  CONSTRAINT ns_auth_operation_fk FOREIGN KEY (operation_id) REFERENCES ns_operation (operation_id)
);

-- Table audit_log stores auditing information
CREATE TABLE audit_log (
    audit_log_id       VARCHAR2(36 CHAR) PRIMARY KEY,
    application_name   VARCHAR2(256 CHAR) NOT NULL,
    audit_level        VARCHAR2(32 CHAR) NOT NULL,
    audit_type         VARCHAR2(256 CHAR),
    timestamp_created  TIMESTAMP,
    message            CLOB NOT NULL,
    exception_message  CLOB,
    stack_trace        CLOB,
    param              CLOB,
    calling_class      VARCHAR2(256 CHAR) NOT NULL,
    thread_name        VARCHAR2(256 CHAR) NOT NULL,
    version            VARCHAR2(256 CHAR),
    build_time         TIMESTAMP
);

-- Table audit_param stores auditing parameters
CREATE TABLE audit_param (
    audit_log_id       VARCHAR2(36 CHAR),
    timestamp_created  TIMESTAMP,
    param_key          VARCHAR2(256 CHAR),
    param_value        VARCHAR2(4000 CHAR)
);

ALTER TABLE ns_operation_history ADD authentication_id VARCHAR2(256 CHAR);
ALTER TABLE ns_operation_history ADD pa_operation_id VARCHAR2(256 CHAR);
ALTER TABLE ns_operation_history ADD CONSTRAINT history_authentication_fk FOREIGN KEY (authentication_id) REFERENCES ns_authentication (authentication_id);
ALTER TABLE ns_operation_history ADD CONSTRAINT ns_history_chosen_method_fk FOREIGN KEY (chosen_auth_method) REFERENCES ns_auth_method (auth_method);
ALTER TABLE ns_operation ADD external_operation_name VARCHAR2(32 CHAR);
ALTER TABLE ns_operation ADD CONSTRAINT operation_config_fk FOREIGN KEY (operation_name) REFERENCES ns_operation_config (operation_name);
ALTER TABLE ns_operation_config ADD expiration_time INTEGER;
ALTER TABLE ns_operation_config ADD CONSTRAINT ns_operation_afs_fk FOREIGN KEY (afs_config_id) REFERENCES wf_afs_config (config_id);
ALTER TABLE ns_organization ADD default_credential_name VARCHAR2(256 CHAR);
ALTER TABLE ns_organization ADD default_otp_name VARCHAR2(256 CHAR);

CREATE UNIQUE INDEX ns_application_name ON ns_application (name);
CREATE UNIQUE INDEX ns_credential_policy_name ON ns_credential_policy (name);
CREATE UNIQUE INDEX ns_otp_policy_name ON ns_otp_policy (name);
CREATE INDEX ns_user_contact_user_id ON ns_user_contact (user_id);
CREATE UNIQUE INDEX ns_user_contact_unique ON ns_user_contact (user_id, name, type);
CREATE INDEX ns_user_identity_status ON ns_user_identity (status);
CREATE INDEX ns_user_identity_created ON ns_user_identity (timestamp_created);
CREATE INDEX ns_user_identity_history_user ON ns_user_identity_history (user_id);
CREATE INDEX ns_user_identity_history_created ON ns_user_identity_history (timestamp_created);
CREATE UNIQUE INDEX ns_role_name ON ns_role (name);
CREATE INDEX ns_user_role_user_id ON ns_user_role (user_id);
CREATE INDEX ns_user_role_role ON ns_user_role (role_id);
CREATE INDEX ns_user_alias_user_id ON ns_user_alias (user_id);
CREATE UNIQUE INDEX ns_credential_definition_name ON ns_credential_definition (name);
CREATE UNIQUE INDEX ns_otp_definition_name ON ns_otp_definition (name);
CREATE INDEX ns_credential_storage_user_id ON ns_credential_storage (user_id);
CREATE INDEX ns_credential_storage_status ON ns_credential_storage (status);
CREATE UNIQUE INDEX ns_credential_storage_query1 ON ns_credential_storage (CASE WHEN user_name IS NOT NULL THEN credential_definition_id || '&' || user_name END);
CREATE INDEX ns_credential_storage_query1_perf ON ns_credential_storage (credential_definition_id, user_name);
CREATE UNIQUE INDEX ns_credential_storage_query2 ON ns_credential_storage (user_id, credential_definition_id);
CREATE INDEX ns_credential_storage_query3 ON ns_credential_storage (credential_definition_id, status);
CREATE INDEX ns_credential_history_user_id ON ns_credential_history (user_id);
CREATE INDEX ns_otp_storage_user_id ON ns_otp_storage (user_id);
CREATE INDEX ns_otp_storage_user_id_status ON ns_otp_storage (user_id, status);
CREATE INDEX ns_otp_storage_operation_id ON ns_otp_storage (operation_id);
CREATE INDEX ns_authentication_user_id ON ns_authentication (user_id);
CREATE INDEX ns_authentication_operation_id ON ns_authentication (operation_id);
CREATE INDEX ns_authentication_timestamp_created ON ns_authentication (timestamp_created);
CREATE UNIQUE INDEX ns_hashing_config_name ON ns_hashing_config (name);
CREATE UNIQUE INDEX ns_user_alias_unique ON ns_user_alias (user_id, name);
CREATE UNIQUE INDEX ns_user_role_unique ON ns_user_role (user_id, role_id);
CREATE INDEX audit_log_timestamp ON audit_log (timestamp_created);
CREATE INDEX audit_log_application ON audit_log (application_name);
CREATE INDEX audit_log_level ON audit_log (audit_level);
CREATE INDEX audit_log_type ON audit_log (audit_type);
CREATE INDEX audit_param_log ON audit_param (audit_log_id);
CREATE INDEX audit_param_timestamp ON audit_param (timestamp_created);
CREATE INDEX audit_param_key ON audit_param (param_key);
CREATE INDEX audit_param_value ON audit_param (param_value);

Following foreign keys should be activated only in case all user identities are stored in Next Step identity. In case any user identity is stored in an external system, ignore this DDL update.

ALTER TABLE ns_operation ADD CONSTRAINT ns_operation_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_user_prefs ADD CONSTRAINT ns_user_prefs_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_otp_storage ADD CONSTRAINT ns_otp_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_authentication ADD CONSTRAINT ns_auth_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);

DDL update script for MySQL:

-- Table ns_operation_method_config stores configuration of authentication methods per operation name.
CREATE TABLE ns_operation_method_config (
  operation_name     VARCHAR(32) NOT NULL,             -- Name of the operation, for example "login" or "authorize_payment".
  auth_method        VARCHAR(32) NOT NULL,             -- Name of the authentication method: APPROVAL_SCA, CONSENT, INIT, LOGIN_SCA, POWERAUTH_TOKEN, SHOW_OPERATION_DETAIL, SMS_KEY, USER_ID_ASSIGN, USERNAME_PASSWORD_AUTH, OTP_CODE.
  max_auth_fails     INTEGER NOT NULL,                 -- Maximum allowed number of authentication fails.
  PRIMARY KEY (operation_name, auth_method),
  FOREIGN KEY ns_operation_method_fk1 (operation_name) REFERENCES ns_operation_config (operation_name),
  FOREIGN KEY ns_operation_method_fk2 (auth_method) REFERENCES ns_auth_method (auth_method)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_application stores Next Step applications.
CREATE TABLE ns_application (
  application_id         INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,  -- Next Step application ID (autogenerated).
  name                   VARCHAR(256) NOT NULL,                        -- Application name used for identification.
  description            VARCHAR(256),                                 -- Description of the application.
  status                 VARCHAR(32) NOT NULL,                         -- Application status: ACTIVE, REMOVED.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,          -- Timestamp when application was created.
  timestamp_last_updated TIMESTAMP                                    -- Timestamp when application was last updated.
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_credential_policy stores credential policies.
CREATE TABLE ns_credential_policy (
  credential_policy_id       INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  -- Credential policy ID (autogenerated).
  name                       VARCHAR(256) NOT NULL,                        -- Credential policy name used for identification.
  description                VARCHAR(256),                                 -- Description of the credential policy.
  status                     VARCHAR(32) NOT NULL,                         -- Credential policy status: ACTIVE, REMOVED.
  username_length_min        INTEGER,                                      -- Minimum length of username.
  username_length_max        INTEGER,                                      -- Maximum length of username.
  username_allowed_pattern   VARCHAR(256),                                 -- Allowed pattern for username (regular expression).
  credential_length_min      INTEGER,                                      -- Minimum length of credential value.
  credential_length_max      INTEGER,                                      -- Maximum length of credential value.
  limit_soft                 INTEGER,                                      -- Soft limit of failed attempts.
  limit_hard                 INTEGER,                                      -- Hard limit of failed attempts.
  check_history_count        INTEGER DEFAULT 0 NOT NULL,                   -- Number of historical credential values to check.
  rotation_enabled           INTEGER DEFAULT 0 NOT NULL,                   -- Whether credential rotation is enabled.
  rotation_days              INTEGER,                                      -- Number of days for credential rotation.
  credential_temp_expiration INTEGER,                                      -- Expiration time of TEMPORARY credentials in seconds.
  username_gen_algorithm     VARCHAR(256) DEFAULT 'DEFAULT' NOT NULL,      -- Algorithm used for generating the username.
  username_gen_param         VARCHAR(4000) NOT NULL,                       -- Parameters used when generating the username.
  credential_gen_algorithm   VARCHAR(256) DEFAULT 'DEFAULT' NOT NULL,      -- Algorithm used for generating the credential.
  credential_gen_param       VARCHAR(4000) NOT NULL,                       -- Parameters used when generating the credential.
  credential_val_param       VARCHAR(4000) NOT NULL,                       -- Parameters used when validating the credential.
  timestamp_created          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,          -- Timestamp when policy was created.
  timestamp_last_updated     TIMESTAMP                                     -- Timestamp when policy was last updated.
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_credential_policy stores one time password policies.
CREATE TABLE ns_otp_policy (
  otp_policy_id          INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- One time password policy ID (autogenerated).
  name                   VARCHAR(256) NOT NULL,                       -- One time password policy name used for identification.
  description            VARCHAR(256),                                -- Description of the one time password policy.
  status                 VARCHAR(32) NOT NULL,                        -- One time password policy status: ACTIVE, REMOVED.
  length                 INTEGER NOT NULL,                            -- One time password length.
  attempt_limit          INTEGER,                                     -- Maximum number of authentication attempts.
  expiration_time        INTEGER,                                     -- One time password expiration time.
  gen_algorithm          VARCHAR(256) DEFAULT 'DEFAULT' NOT NULL,     -- Algorithm used for generating the one time password.
  gen_param              VARCHAR(4000) NOT NULL,                      -- Parameters used when generating the OTP.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when policy was created.
  timestamp_last_updated TIMESTAMP                                    -- Timestamp when policy was last updated.
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_user_identity stores user identities.
CREATE TABLE ns_user_identity (
  user_id                VARCHAR(256) NOT NULL PRIMARY KEY,           -- User identity identifier (not autogenerated).
  status                 VARCHAR(32) NOT NULL,                        -- User identity status: ACTIVE, BLOCKED, REMOVED.
  extras                 TEXT,                                        -- Extra attributes with data related to user identity.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user identity was created.
  timestamp_last_updated TIMESTAMP                                    -- Timestamp when user identity was last updated.
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_user_contact stores contact information for user identities.
CREATE TABLE ns_user_contact (
  user_contact_id        INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- User contact identifier (autogenerated).
  user_id                VARCHAR(256) NOT NULL,                       -- User identity identifier.
  name                   VARCHAR(256) NOT NULL,                       -- User contact name used for identification.
  type                   VARCHAR(32) NOT NULL,                        -- User contact type: PHONE, EMAIL, OTHER.
  value                  VARCHAR(256) NOT NULL,                       -- User contact value.
  is_primary             INTEGER DEFAULT 0 NOT NULL,                  -- Whether contact is primary.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when contact was created.
  timestamp_last_updated TIMESTAMP,                                   -- Timestamp when contact was last updated.
  CONSTRAINT ns_user_contact_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_user_identity stores history for user identities.
CREATE TABLE ns_user_identity_history (
  user_identity_history_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- User identity history identifier (autogenerated).
  user_id                  VARCHAR(256) NOT NULL,                       -- User identity identifier.
  status                   VARCHAR(32) NOT NULL,                        -- User identity status: ACTIVE, BLOCKED, REMOVED.
  roles                    VARCHAR(256),                                -- Assigned user roles.
  extras                   TEXT,                                        -- Extra attributes with data related to user identity.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user identity snapshot was created.
  CONSTRAINT ns_user_identity_history_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_role stores user role definitions.
CREATE TABLE ns_role (
  role_id                INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Role identifier (autogenerated).
  name                   VARCHAR(256) NOT NULL,                       -- Role name used for identification.
  description            VARCHAR(256),                                -- Description of role.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when role was created.
  timestamp_last_updated TIMESTAMP                                    -- Timestamp when role was last updated.
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_user_role stores assignment of roles to user identities.
CREATE TABLE ns_user_role (
  user_role_id             INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- User role identifier (autogenerated).
  user_id                  VARCHAR(256) NOT NULL,                       -- User identity identifier.
  role_id                  INTEGER NOT NULL,                            -- Role identifier.
  status                   VARCHAR(32) NOT NULL,                        -- User role status: ACTIVE, REMOVED.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user role was created.
  timestamp_last_updated   TIMESTAMP,                                   -- Timestamp when user role was last updated.
  CONSTRAINT ns_role_identity_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id),
  CONSTRAINT ns_user_role_fk FOREIGN KEY (role_id) REFERENCES ns_role (role_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_user_alias stores user aliases.
CREATE TABLE ns_user_alias (
  user_alias_id            INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- User alias identifier (autogenerated).
  user_id                  VARCHAR(256) NOT NULL,                       -- User identity identifier.
  name                     VARCHAR(256) NOT NULL,                       -- User alias name used for identification.
  value                    VARCHAR(256) NOT NULL,                       -- User alias value.
  status                   VARCHAR(32) NOT NULL,                        -- User alias status: ACTIVE, REMOVED.
  extras                   TEXT,                                        -- Extra attributes with data related to user alias.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user alias was created.
  timestamp_last_updated   TIMESTAMP,                                   -- Timestamp when user alias was last updated.
  CONSTRAINT ns_user_alias_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_hashing_config stores configuration of hashing algorithms.
CREATE TABLE ns_hashing_config (
  hashing_config_id        INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Hashing configuration identifier (autogenerated).
  name                     VARCHAR(256) NOT NULL,                       -- Hashing configuration name used for identification.
  algorithm                VARCHAR(256) NOT NULL,                       -- Hashing algorithm name.
  status                   VARCHAR(32) NOT NULL,                        -- Hashing configuration status: ACTIVE, REMOVED.
  parameters               VARCHAR(256),                                -- Hashing algorithm parameters.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when hashing configuration was created.
  timestamp_last_updated   TIMESTAMP                                    -- Timestamp when hashing configuration was last updated.
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_credential_definition stores definitions of credentials with reference to credential policies and applications.
CREATE TABLE ns_credential_definition (
  credential_definition_id   INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Credential definition identifier (autogenerated).
  name                       VARCHAR(256) NOT NULL,                       -- Credential definition name used for identification.
  description                VARCHAR(256),                                -- Description of the credential definition.
  application_id             INTEGER NOT NULL,                            -- Application identifier.
  organization_id            VARCHAR(256),                                -- Organization this credential belongs to.
  credential_policy_id       INTEGER NOT NULL,                            -- Credential policy identifier.
  category                   VARCHAR(32) NOT NULL,                        -- Credential category: PASSWORD, PIN, OTHER.
  encryption_enabled         INTEGER DEFAULT 0 NOT NULL,                  -- Whether encryption of stored credentials is enabled.
  encryption_algorithm       VARCHAR(256),                                -- Algorithm used for stored credential encryption.
  hashing_enabled            INTEGER DEFAULT 0 NOT NULL,                  -- Whether credential hashing is enabled.
  hashing_config_id          INTEGER,                                     -- Algorithm used for credential hashing.
  e2e_encryption_enabled     INTEGER DEFAULT 0 NOT NULL,                  -- Whether end to end encryption of credential values is enabled.
  e2e_encryption_algorithm   VARCHAR(256),                                -- Algorithm used for end to end encryption of credential.
  e2e_encryption_transform   VARCHAR(256),                                -- Cipher transformation used for end to end encryption of credential.
  e2e_encryption_temporary   INTEGER DEFAULT 0 NOT NULL,                  -- Whether end to end encryption of temporary credential values is enabled.
  data_adapter_proxy_enabled INTEGER DEFAULT 0 NOT NULL,                  -- Whether credential API calls should be proxied through Data Adapter.
  status                     VARCHAR(32) NOT NULL,                        -- Credential definition status: ACTIVE, REMOVED.
  timestamp_created          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when credential definition was created.
  timestamp_last_updated     TIMESTAMP,                                   -- Timestamp when credential definition was last updated.
  CONSTRAINT ns_credential_application_fk FOREIGN KEY (application_id) REFERENCES ns_application (application_id),
  CONSTRAINT ns_credential_policy_fk FOREIGN KEY (credential_policy_id) REFERENCES ns_credential_policy (credential_policy_id),
  CONSTRAINT ns_credential_hash_fk FOREIGN KEY (hashing_config_id) REFERENCES ns_hashing_config (hashing_config_id),
  CONSTRAINT ns_application_organization_fk FOREIGN KEY (organization_id) REFERENCES ns_organization (organization_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_otp_definition stores definitions of one time passwords with reference to credential policies and applications.
CREATE TABLE ns_otp_definition (
  otp_definition_id          INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- One time password definition identifier (autogenerated).
  name                       VARCHAR(256) NOT NULL,                       -- One time password definition name used for identification.
  description                VARCHAR(256),                                -- Description of the one time password definition.
  application_id             INTEGER NOT NULL,                            -- Application identifier.
  otp_policy_id              INTEGER NOT NULL,                            -- One time password policy identifier.
  encryption_enabled         INTEGER DEFAULT 0 NOT NULL,                  -- Whether encryption of stored one time passwords is enabled.
  encryption_algorithm       VARCHAR(256),                                -- Algorithm used for stored one time password encryption.
  data_adapter_proxy_enabled INTEGER DEFAULT 0 NOT NULL,                  -- Whether one time password API calls should be proxied through Data Adapter.
  status                     VARCHAR(32) NOT NULL,                        -- One time password definition status: ACTIVE, REMOVED.
  timestamp_created          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when one time password definition was created.
  timestamp_last_updated     TIMESTAMP,                                   -- Timestamp when one time password definition was last updated.
  CONSTRAINT ns_otp_application_fk FOREIGN KEY (application_id) REFERENCES ns_application (application_id),
  CONSTRAINT ns_otp_policy_fk FOREIGN KEY (otp_policy_id) REFERENCES ns_otp_policy (otp_policy_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_credential_storage stores credential values, counters and other data related to credentials.
CREATE TABLE ns_credential_storage (
  credential_id                    VARCHAR(256) NOT NULL PRIMARY KEY,   -- Credential identifier (generated by application as UUID).
  credential_definition_id         INTEGER NOT NULL,                    -- Credential definition identifier.
  user_id                          VARCHAR(256) NOT NULL,               -- User identity identifier.
  type                             VARCHAR(32) NOT NULL,                -- Credential type: PERMANENT, TEMPORARY.
  user_name                        VARCHAR(256),                        -- Username.
  value                            VARCHAR(256) NOT NULL,               -- Credential value.
  status                           VARCHAR(32) NOT NULL,                -- Credential status: ACTIVE, BLOCKED_TEMPORARY, BLOCKED_PERMANENT, REMOVED.
  attempt_counter                  INTEGER DEFAULT 0 NOT NULL,          -- Attempt counter for both successful and failed attempts.
  failed_attempt_counter_soft      INTEGER DEFAULT 0 NOT NULL,          -- Soft failed attempt counter.
  failed_attempt_counter_hard      INTEGER DEFAULT 0 NOT NULL,          -- Hard failed attempt counter.
  encryption_algorithm             VARCHAR(256),                        -- Encryption algorithm used for encrypting credential value.
  hashing_config_id                INTEGER,                             -- Hashing configuration used when credential value was hashed.
  timestamp_created                TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when credential was created.
  timestamp_expires                TIMESTAMP,                           -- Timestamp when credential expires.
  timestamp_blocked                TIMESTAMP,                           -- Timestamp when credential was blocked.
  timestamp_last_updated           TIMESTAMP,                           -- Timestamp when credential was last updated.
  timestamp_last_credential_change TIMESTAMP,                           -- Timestamp when credential value was last changed.
  timestamp_last_username_change   TIMESTAMP,                           -- Timestamp when username value was last changed.
  CONSTRAINT ns_credential_definition_fk FOREIGN KEY (credential_definition_id) REFERENCES ns_credential_definition (credential_definition_id),
  CONSTRAINT ns_credential_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_credential_history stores historical values of credentials.
CREATE TABLE ns_credential_history (
  credential_history_id       INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Credential history identifier (autogenerated).
  credential_definition_id    INTEGER NOT NULL,                            -- Credential identifier.
  user_id                     VARCHAR(256) NOT NULL,                       -- User identity identifier.
  user_name                   VARCHAR(256),                                -- Username.
  value                       VARCHAR(256) NOT NULL,                       -- Credential value.
  encryption_algorithm        VARCHAR(256),                                -- Encryption algorithm used for encrypting credential value.
  hashing_config_id           INTEGER,                                     -- Hashing configuration used when credential value was hashed.
  timestamp_created           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when credential was created.
  CONSTRAINT ns_credential_history_definition_fk FOREIGN KEY (credential_definition_id) REFERENCES ns_credential_definition (credential_definition_id),
  CONSTRAINT ns_credential_history_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_otp_storage stores one time password values, counters and other data related to one time passwords.
CREATE TABLE ns_otp_storage (
  otp_id                      VARCHAR(256) NOT NULL PRIMARY KEY,   -- One time password identifier (generated by application as UUID).
  otp_definition_id           INTEGER NOT NULL,                    -- One time password definition identifier.
  user_id                     VARCHAR(256),                        -- User identifier.
  credential_definition_id    INTEGER,                             -- Credential definition identifier used when updating failed counter.
  operation_id                VARCHAR(256),                        -- Operation identifier.
  value                       VARCHAR(256),                        -- One time password value.
  salt                        VARBINARY(16),                       -- Cryptographic salt used when generating one time password.
  status                      VARCHAR(32) NOT NULL,                -- One time password status: ACTIVE, USED, BLOCKED, REMOVED.
  otp_data                    TEXT,                                -- Data used for generating one time password.
  attempt_counter             INTEGER DEFAULT 0 NOT NULL,          -- One time password attempt counter.
  failed_attempt_counter      INTEGER DEFAULT 0 NOT NULL,          -- One time password failed attempt counter.
  encryption_algorithm        VARCHAR(256),                        -- Encryption algorithm used for encrypting OTP value.
  timestamp_created           TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when one time password was created.
  timestamp_verified          TIMESTAMP,                           -- Timestamp when one time password was verified.
  timestamp_blocked           TIMESTAMP,                           -- Timestamp when one time password was blocked.
  timestamp_expires           TIMESTAMP,                           -- Timestamp when one time password expires.
  CONSTRAINT ns_otp_definition_fk FOREIGN KEY (otp_definition_id) REFERENCES ns_otp_definition (otp_definition_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table ns_authentication stores authentication attempts.
CREATE TABLE ns_authentication (
  authentication_id           VARCHAR(256) NOT NULL PRIMARY KEY,           -- Authentication identifier (autogenerated).
  user_id                     VARCHAR(256),                                -- User identity identifier.
  type                        VARCHAR(32) NOT NULL,                        -- Authentication type: CREDENTIAL, OTP, CREDENTIAL_OTP.
  credential_id               VARCHAR(256),                                -- Credential identifier.
  otp_id                      VARCHAR(256),                                -- One time password identifier.
  operation_id                VARCHAR(256),                                -- Operation identifier.
  result                      VARCHAR(32) NOT NULL,                        -- Overall authentication result.
  result_credential           VARCHAR(32),                                 -- Authentication result for credential authentication.
  result_otp                  VARCHAR(32),                                 -- Authentication result for one time password authentication.
  timestamp_created           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when authentication record was created.
  CONSTRAINT ns_auth_credential_fk FOREIGN KEY (credential_id) REFERENCES ns_credential_storage (credential_id),
  CONSTRAINT ns_auth_otp_fk FOREIGN KEY (otp_id) REFERENCES ns_otp_storage (otp_id),
  CONSTRAINT ns_auth_operation_fk FOREIGN KEY (operation_id) REFERENCES ns_operation (operation_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table audit_log stores auditing information
CREATE TABLE audit_log (
    audit_log_id       VARCHAR(36) PRIMARY KEY,
    application_name   VARCHAR(256) NOT NULL,
    audit_level        VARCHAR(32) NOT NULL,
    audit_type         VARCHAR(256),
    timestamp_created  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    message            TEXT NOT NULL,
    exception_message  TEXT,
    stack_trace        TEXT,
    param              TEXT,
    calling_class      VARCHAR(256) NOT NULL,
    thread_name        VARCHAR(256) NOT NULL,
    version            VARCHAR(256),
    build_time         TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table audit_param stores auditing parameters
CREATE TABLE audit_param (
    audit_log_id       VARCHAR(36),
    timestamp_created  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    param_key          VARCHAR(256),
    param_value        VARCHAR(3072)
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE ns_operation_history ADD authentication_id VARCHAR(256);
ALTER TABLE ns_operation_history ADD pa_operation_id VARCHAR(256);
ALTER TABLE ns_operation_history ADD CONSTRAINT history_authentication_fk FOREIGN KEY (authentication_id) REFERENCES ns_authentication (authentication_id);
ALTER TABLE ns_operation_history ADD CONSTRAINT ns_history_chosen_method_fk FOREIGN KEY (chosen_auth_method) REFERENCES ns_auth_method (auth_method);
ALTER TABLE ns_operation ADD external_operation_name VARCHAR(32);
ALTER TABLE ns_operation ADD CONSTRAINT operation_config_fk FOREIGN KEY (operation_name) REFERENCES ns_operation_config (operation_name);
ALTER TABLE ns_operation_config ADD expiration_time INTEGER;
ALTER TABLE ns_operation_config ADD CONSTRAINT ns_operation_afs_fk FOREIGN KEY (afs_config_id) REFERENCES wf_afs_config (config_id);
ALTER TABLE ns_organization ADD default_credential_name VARCHAR(256);
ALTER TABLE ns_organization ADD default_otp_name VARCHAR(256);

CREATE UNIQUE INDEX ns_application_name ON ns_application (name);
CREATE UNIQUE INDEX ns_credential_policy_name ON ns_credential_policy (name);
CREATE UNIQUE INDEX ns_otp_policy_name ON ns_otp_policy (name);
CREATE INDEX ns_user_contact_user_id ON ns_user_contact (user_id);
CREATE UNIQUE INDEX ns_user_contact_unique ON ns_user_contact (user_id, name, type);
CREATE INDEX ns_user_identity_status ON ns_user_identity (status);
CREATE INDEX ns_user_identity_created ON ns_user_identity (timestamp_created);
CREATE INDEX ns_user_identity_history_user ON ns_user_identity_history (user_id);
CREATE INDEX ns_user_identity_history_created ON ns_user_identity_history (timestamp_created);
CREATE UNIQUE INDEX ns_role_name ON ns_role (name);
CREATE INDEX ns_user_role_user_id ON ns_user_role (user_id);
CREATE INDEX ns_user_role_role_id ON ns_user_role (role_id);
CREATE INDEX ns_user_alias_user_id ON ns_user_alias (user_id);
CREATE UNIQUE INDEX ns_credential_definition_name ON ns_credential_definition (name);
CREATE UNIQUE INDEX ns_otp_definition_name ON ns_otp_definition (name);
CREATE INDEX ns_credential_storage_user_id ON ns_credential_storage (user_id);
CREATE INDEX ns_credential_storage_status ON ns_credential_storage (status);
CREATE UNIQUE INDEX ns_credential_storage_query1 ON ns_credential_storage (credential_definition_id, user_name);
CREATE UNIQUE INDEX ns_credential_storage_query2 ON ns_credential_storage (user_id, credential_definition_id);
CREATE INDEX ns_credential_storage_query3 ON ns_credential_storage (credential_definition_id, status);
CREATE INDEX ns_credential_history_user_id ON ns_credential_history (user_id);
CREATE INDEX ns_otp_storage_user_id ON ns_otp_storage (user_id);
CREATE INDEX ns_otp_storage_user_id_status ON ns_otp_storage (user_id, status);
CREATE INDEX ns_otp_storage_operation_id ON ns_otp_storage (operation_id);
CREATE INDEX ns_authentication_user_id ON ns_authentication (user_id);
CREATE INDEX ns_authentication_operation_id ON ns_authentication (operation_id);
CREATE INDEX ns_authentication_timestamp_created ON ns_authentication (timestamp_created);
CREATE UNIQUE INDEX ns_hashing_config_name ON ns_hashing_config (name);
CREATE UNIQUE INDEX ns_user_alias_unique ON ns_user_alias (user_id, name);
CREATE UNIQUE INDEX ns_user_role_unique ON ns_user_role (user_id, role_id);
CREATE INDEX audit_log_timestamp ON audit_log (timestamp_created);
CREATE INDEX audit_log_application ON audit_log (application_name);
CREATE INDEX audit_log_level ON audit_log (audit_level);
CREATE INDEX audit_log_type ON audit_log (audit_type);
CREATE INDEX audit_param_log ON audit_param (audit_log_id);
CREATE INDEX audit_param_timestamp ON audit_param (timestamp_created);
CREATE INDEX audit_param_key ON audit_param (param_key);
CREATE FULLTEXT INDEX audit_param_value ON audit_param (param_value);

Following foreign keys should be activated only in case all user identities are stored in Next Step identity. In case any user identity is stored in an external system, ignore this DDL update.

ALTER TABLE ns_operation ADD CONSTRAINT ns_operation_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_user_prefs ADD CONSTRAINT ns_user_prefs_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_otp_storage ADD CONSTRAINT ns_otp_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_authentication ADD CONSTRAINT ns_auth_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);

DDL update script for PostgreSQL:

-- New sequences
CREATE SEQUENCE ns_application_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_credential_policy_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_otp_policy_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_user_contact_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_user_identity_history_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_role_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_user_role_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_user_alias_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_hashing_config_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_credential_definition_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_otp_definition_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;
CREATE SEQUENCE ns_credential_history_seq MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20;

-- Table ns_operation_method_config stores configuration of authentication methods per operation name.
CREATE TABLE ns_operation_method_config (
  operation_name     VARCHAR(32) NOT NULL,             -- Name of the operation, for example "login" or "authorize_payment".
  auth_method        VARCHAR(32) NOT NULL,             -- Name of the authentication method: APPROVAL_SCA, CONSENT, INIT, LOGIN_SCA, POWERAUTH_TOKEN, SHOW_OPERATION_DETAIL, SMS_KEY, USER_ID_ASSIGN, USERNAME_PASSWORD_AUTH, OTP_CODE.
  max_auth_fails     INTEGER NOT NULL,                 -- Maximum allowed number of authentication fails.
  CONSTRAINT ns_operation_method_pk PRIMARY KEY (operation_name, auth_method),
  CONSTRAINT ns_operation_method_fk1 FOREIGN KEY (operation_name) REFERENCES ns_operation_config (operation_name),
  CONSTRAINT ns_operation_method_fk2 FOREIGN KEY (auth_method) REFERENCES ns_auth_method (auth_method)
);

-- Table ns_application stores Next Step applications.
CREATE TABLE ns_application (
  application_id         INTEGER PRIMARY KEY NOT NULL,                 -- Next Step application ID (autogenerated).
  name                   VARCHAR(256) NOT NULL,                        -- Application name used for identification.
  description            VARCHAR(256),                                 -- Description of the application.
  status                 VARCHAR(32) NOT NULL,                         -- Application status: ACTIVE, REMOVED.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,          -- Timestamp when application was created.
  timestamp_last_updated TIMESTAMP                                     -- Timestamp when application was last updated.
);

-- Table ns_credential_policy stores credential policies.
CREATE TABLE ns_credential_policy (
  credential_policy_id       INTEGER NOT NULL PRIMARY KEY,                 -- Credential policy ID (autogenerated).
  name                       VARCHAR(256) NOT NULL,                        -- Credential policy name used for identification.
  description                VARCHAR(256),                                 -- Description of the credential policy.
  status                     VARCHAR(32) NOT NULL,                         -- Credential policy status: ACTIVE, REMOVED.
  username_length_min        INTEGER,                                      -- Minimum length of username.
  username_length_max        INTEGER,                                      -- Maximum length of username.
  username_allowed_pattern   VARCHAR(256),                                 -- Allowed pattern for username (regular expression).
  credential_length_min      INTEGER,                                      -- Minimum length of credential value.
  credential_length_max      INTEGER,                                      -- Maximum length of credential value.
  limit_soft                 INTEGER,                                      -- Soft limit of failed attempts.
  limit_hard                 INTEGER,                                      -- Hard limit of failed attempts.
  check_history_count        INTEGER DEFAULT 0 NOT NULL,                   -- Number of historical credential values to check.
  rotation_enabled           BOOLEAN DEFAULT FALSE NOT NULL,               -- Whether credential rotation is enabled.
  rotation_days              INTEGER,                                      -- Number of days for credential rotation.
  credential_temp_expiration INTEGER,                                      -- Expiration time of TEMPORARY credentials in seconds.
  username_gen_algorithm     VARCHAR(256) DEFAULT 'DEFAULT' NOT NULL,      -- Algorithm used for generating the username.
  username_gen_param         VARCHAR(4000) NOT NULL,                       -- Parameters used when generating the username.
  credential_gen_algorithm   VARCHAR(256) DEFAULT 'DEFAULT' NOT NULL,      -- Algorithm used for generating the credential.
  credential_gen_param       VARCHAR(4000) NOT NULL,                       -- Parameters used when generating the credential.
  credential_val_param       VARCHAR(4000) NOT NULL,                       -- Parameters used when validating the credential.
  timestamp_created          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,          -- Timestamp when policy was created.
  timestamp_last_updated     TIMESTAMP                                     -- Timestamp when policy was last updated.
);

-- Table ns_credential_policy stores one time password policies.
CREATE TABLE ns_otp_policy (
  otp_policy_id          INTEGER NOT NULL PRIMARY KEY,                -- One time password policy ID (autogenerated).
  name                   VARCHAR(256) NOT NULL,                       -- One time password policy name used for identification.
  description            VARCHAR(256),                                -- Description of the one time password policy.
  status                 VARCHAR(32) NOT NULL,                        -- One time password policy status: ACTIVE, REMOVED.
  length                 INTEGER NOT NULL,                            -- One time password length.
  attempt_limit          INTEGER,                                     -- Maximum number of authentication attempts.
  expiration_time        INTEGER,                                     -- One time password expiration time.
  gen_algorithm          VARCHAR(256) DEFAULT 'DEFAULT' NOT NULL,     -- Algorithm used for generating the one time password.
  gen_param              VARCHAR(4000) NOT NULL,                      -- Parameters used when generating the OTP.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when policy was created.
  timestamp_last_updated TIMESTAMP                                    -- Timestamp when policy was last updated.
);

-- Table ns_user_identity stores user identities.
CREATE TABLE ns_user_identity (
  user_id                VARCHAR(256) NOT NULL PRIMARY KEY,           -- User identity identifier (not autogenerated).
  status                 VARCHAR(32) NOT NULL,                        -- User identity status: ACTIVE, BLOCKED, REMOVED.
  extras                 TEXT,                                        -- Extra attributes with data related to user identity.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user identity was created.
  timestamp_last_updated TIMESTAMP                                    -- Timestamp when user identity was last updated.
);

-- Table ns_user_contact stores contact information for user identities.
CREATE TABLE ns_user_contact (
  user_contact_id        INTEGER NOT NULL PRIMARY KEY,                -- User contact identifier (autogenerated).
  user_id                VARCHAR(256) NOT NULL,                       -- User identity identifier.
  name                   VARCHAR(256) NOT NULL,                       -- User contact name used for identification.
  type                   VARCHAR(32) NOT NULL,                        -- User contact type: PHONE, EMAIL, OTHER.
  value                  VARCHAR(256) NOT NULL,                       -- User contact value.
  is_primary             BOOLEAN DEFAULT FALSE NOT NULL,              -- Whether contact is primary.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when contact was created.
  timestamp_last_updated TIMESTAMP,                                   -- Timestamp when contact was last updated.
  CONSTRAINT ns_user_contact_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_user_identity stores history for user identities.
CREATE TABLE ns_user_identity_history (
  user_identity_history_id INTEGER NOT NULL PRIMARY KEY,                -- User identity history identifier (autogenerated).
  user_id                  VARCHAR(256) NOT NULL,                       -- User identity identifier.
  status                   VARCHAR(32) NOT NULL,                        -- User identity status: ACTIVE, BLOCKED, REMOVED.
  roles                    VARCHAR(256),                                -- Assigned user roles.
  extras                   TEXT,                                        -- Extra attributes with data related to user identity.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user identity snapshot was created.
  CONSTRAINT ns_user_identity_history_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_role stores user role definitions.
CREATE TABLE ns_role (
  role_id                INTEGER NOT NULL PRIMARY KEY,                -- Role identifier (autogenerated).
  name                   VARCHAR(256) NOT NULL,                       -- Role name used for identification.
  description            VARCHAR(256),                                -- Description of role.
  timestamp_created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when role was created.
  timestamp_last_updated TIMESTAMP                                    -- Timestamp when role was last updated.
);

-- Table ns_user_role stores assignment of roles to user identities.
CREATE TABLE ns_user_role (
  user_role_id             INTEGER NOT NULL PRIMARY KEY,                -- User role identifier (autogenerated).
  user_id                  VARCHAR(256) NOT NULL,                       -- User identity identifier.
  role_id                  INTEGER NOT NULL,                            -- Role identifier.
  status                   VARCHAR(32) NOT NULL,                        -- User role status: ACTIVE, REMOVED.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user role was created.
  timestamp_last_updated   TIMESTAMP,                                   -- Timestamp when user role was last updated.
  CONSTRAINT ns_role_identity_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id),
  CONSTRAINT ns_user_role_fk FOREIGN KEY (role_id) REFERENCES ns_role (role_id)
);

-- Table ns_user_alias stores user aliases.
CREATE TABLE ns_user_alias (
  user_alias_id            INTEGER NOT NULL PRIMARY KEY,                -- User alias identifier (autogenerated).
  user_id                  VARCHAR(256) NOT NULL,                       -- User identity identifier.
  name                     VARCHAR(256) NOT NULL,                       -- User alias name used for identification.
  value                    VARCHAR(256) NOT NULL,                       -- User alias value.
  status                   VARCHAR(32) NOT NULL,                        -- User alias status: ACTIVE, REMOVED.
  extras                   TEXT,                                        -- Extra attributes with data related to user alias.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when user alias was created.
  timestamp_last_updated   TIMESTAMP,                                   -- Timestamp when user alias was last updated.
  CONSTRAINT ns_user_alias_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_hashing_config stores configuration of hashing algorithms.
CREATE TABLE ns_hashing_config (
  hashing_config_id        INTEGER NOT NULL PRIMARY KEY,                -- Hashing configuration identifier (autogenerated).
  name                     VARCHAR(256) NOT NULL,                       -- Hashing configuration name used for identification.
  algorithm                VARCHAR(256) NOT NULL,                       -- Hashing algorithm name.
  status                   VARCHAR(32) NOT NULL,                        -- Hashing configuration status: ACTIVE, REMOVED.
  parameters               VARCHAR(256),                                -- Hashing algorithm parameters.
  timestamp_created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when hashing configuration was created.
  timestamp_last_updated   TIMESTAMP                                    -- Timestamp when hashing configuration was last updated.
);

-- Table ns_credential_definition stores definitions of credentials with reference to credential policies and applications.
CREATE TABLE ns_credential_definition (
  credential_definition_id   INTEGER NOT NULL PRIMARY KEY,                -- Credential definition identifier (autogenerated).
  name                       VARCHAR(256) NOT NULL,                       -- Credential definition name used for identification.
  description                VARCHAR(256),                                -- Description of the credential definition.
  application_id             INTEGER NOT NULL,                            -- Application identifier.
  organization_id            VARCHAR(256),                                -- Organization this credential belongs to.
  credential_policy_id       INTEGER NOT NULL,                            -- Credential policy identifier.
  category                   VARCHAR(32) NOT NULL,                        -- Credential category: PASSWORD, PIN, OTHER.
  encryption_enabled         BOOLEAN DEFAULT FALSE NOT NULL,              -- Whether encryption of stored credentials is enabled.
  encryption_algorithm       VARCHAR(256),                                -- Algorithm used for stored credential encryption.
  hashing_enabled            BOOLEAN DEFAULT FALSE NOT NULL,              -- Whether credential hashing is enabled.
  hashing_config_id          INTEGER,                                     -- Algorithm used for credential hashing.
  e2e_encryption_enabled     BOOLEAN DEFAULT FALSE NOT NULL,              -- Whether end to end encryption of credential values is enabled.
  e2e_encryption_algorithm   VARCHAR(256),                                -- Algorithm used for end to end encryption of credential.
  e2e_encryption_transform   VARCHAR(256),                                -- Cipher transformation used for end to end encryption of credential.
  e2e_encryption_temporary   BOOLEAN DEFAULT FALSE NOT NULL,              -- Whether end to end encryption of temporary credential values is enabled.
  data_adapter_proxy_enabled BOOLEAN DEFAULT FALSE NOT NULL,             -- Whether credential API calls should be proxied through Data Adapter.
  status                     VARCHAR(32) NOT NULL,                        -- Credential definition status: ACTIVE, REMOVED.
  timestamp_created          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when credential definition was created.
  timestamp_last_updated     TIMESTAMP,                                   -- Timestamp when credential definition was last updated.
  CONSTRAINT ns_credential_application_fk FOREIGN KEY (application_id) REFERENCES ns_application (application_id),
  CONSTRAINT ns_credential_policy_fk FOREIGN KEY (credential_policy_id) REFERENCES ns_credential_policy (credential_policy_id),
  CONSTRAINT ns_credential_hash_fk FOREIGN KEY (hashing_config_id) REFERENCES ns_hashing_config (hashing_config_id),
  CONSTRAINT ns_application_organization_fk FOREIGN KEY (organization_id) REFERENCES ns_organization (organization_id)
);

-- Table ns_otp_definition stores definitions of one time passwords with reference to credential policies and applications.
CREATE TABLE ns_otp_definition (
  otp_definition_id          INTEGER NOT NULL PRIMARY KEY,                -- One time password definition identifier (autogenerated).
  name                       VARCHAR(256) NOT NULL,                       -- One time password definition name used for identification.
  description                VARCHAR(256),                                -- Description of the one time password definition.
  application_id             INTEGER NOT NULL,                            -- Application identifier.
  otp_policy_id              INTEGER NOT NULL,                            -- One time password policy identifier.
  encryption_enabled         BOOLEAN DEFAULT FALSE NOT NULL,              -- Whether encryption of stored one time passwords is enabled.
  encryption_algorithm       VARCHAR(256),                                -- Algorithm used for stored one time password encryption.
  data_adapter_proxy_enabled BOOLEAN DEFAULT FALSE NOT NULL,              -- Whether one time password API calls should be proxied through Data Adapter.
  status                     VARCHAR(32) NOT NULL,                        -- One time password definition status: ACTIVE, REMOVED.
  timestamp_created          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when one time password definition was created.
  timestamp_last_updated     TIMESTAMP,                                   -- Timestamp when one time password definition was last updated.
  CONSTRAINT ns_otp_application_fk FOREIGN KEY (application_id) REFERENCES ns_application (application_id),
  CONSTRAINT ns_otp_policy_fk FOREIGN KEY (otp_policy_id) REFERENCES ns_otp_policy (otp_policy_id)
);

-- Table ns_credential_storage stores credential values, counters and other data related to credentials.
CREATE TABLE ns_credential_storage (
  credential_id                    VARCHAR(256) NOT NULL PRIMARY KEY,   -- Credential identifier (generated by application as UUID).
  credential_definition_id         INTEGER NOT NULL,                    -- Credential definition identifier.
  user_id                          VARCHAR(256) NOT NULL,               -- User identity identifier.
  type                             VARCHAR(32) NOT NULL,                -- Credential type: PERMANENT, TEMPORARY.
  user_name                        VARCHAR(256),                        -- Username.
  value                            VARCHAR(256) NOT NULL,               -- Credential value.
  status                           VARCHAR(32) NOT NULL,                -- Credential status: ACTIVE, BLOCKED_TEMPORARY, BLOCKED_PERMANENT, REMOVED.
  attempt_counter                  INTEGER DEFAULT 0 NOT NULL,          -- Attempt counter for both successful and failed attempts.
  failed_attempt_counter_soft      INTEGER DEFAULT 0 NOT NULL,          -- Soft failed attempt counter.
  failed_attempt_counter_hard      INTEGER DEFAULT 0 NOT NULL,          -- Hard failed attempt counter.
  encryption_algorithm             VARCHAR(256),                        -- Encryption algorithm used for encrypting credential value.
  hashing_config_id                INTEGER,                             -- Hashing configuration used when credential value was hashed.
  timestamp_created                TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when credential was created.
  timestamp_expires                TIMESTAMP,                           -- Timestamp when credential expires.
  timestamp_blocked                TIMESTAMP,                           -- Timestamp when credential was blocked.
  timestamp_last_updated           TIMESTAMP,                           -- Timestamp when credential was last updated.
  timestamp_last_credential_change TIMESTAMP,                           -- Timestamp when credential value was last changed.
  timestamp_last_username_change   TIMESTAMP,                           -- Timestamp when username value was last changed.
  CONSTRAINT ns_credential_definition_fk FOREIGN KEY (credential_definition_id) REFERENCES ns_credential_definition (credential_definition_id),
  CONSTRAINT ns_credential_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_credential_history stores historical values of credentials.
CREATE TABLE ns_credential_history (
  credential_history_id       INTEGER NOT NULL PRIMARY KEY,                -- Credential history identifier (autogenerated).
  credential_definition_id    INTEGER NOT NULL,                            -- Credential identifier.
  user_id                     VARCHAR(256) NOT NULL,                       -- User identity identifier.
  user_name                   VARCHAR(256),                                -- Username.
  value                       VARCHAR(256) NOT NULL,                       -- Credential value.
  encryption_algorithm        VARCHAR(256),                                -- Encryption algorithm used for encrypting credential value.
  hashing_config_id           INTEGER,                                     -- Hashing configuration used when credential value was hashed.
  timestamp_created           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when credential was created.
  CONSTRAINT ns_credential_history_definition_fk FOREIGN KEY (credential_definition_id) REFERENCES ns_credential_definition (credential_definition_id),
  CONSTRAINT ns_credential_history_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id)
);

-- Table ns_otp_storage stores one time password values, counters and other data related to one time passwords.
CREATE TABLE ns_otp_storage (
  otp_id                      VARCHAR(256) NOT NULL PRIMARY KEY,   -- One time password identifier (generated by application as UUID).
  otp_definition_id           INTEGER NOT NULL,                    -- One time password definition identifier.
  user_id                     VARCHAR(256),                        -- User identifier.
  credential_definition_id    INTEGER,                             -- Credential definition identifier used when updating failed counter.
  operation_id                VARCHAR(256),                        -- Operation identifier.
  value                       VARCHAR(256),                        -- One time password value.
  salt                        BYTEA,                       -- Cryptographic salt used when generating one time password.
  status                      VARCHAR(32) NOT NULL,                -- One time password status: ACTIVE, USED, BLOCKED, REMOVED.
  otp_data                    TEXT,                                -- Data used for generating one time password.
  attempt_counter             INTEGER DEFAULT 0 NOT NULL,          -- One time password attempt counter.
  failed_attempt_counter      INTEGER DEFAULT 0 NOT NULL,          -- One time password failed attempt counter.
  encryption_algorithm        VARCHAR(256),                        -- Encryption algorithm used for encrypting OTP value.
  timestamp_created           TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when one time password was created.
  timestamp_verified          TIMESTAMP,                           -- Timestamp when one time password was verified.
  timestamp_blocked           TIMESTAMP,                           -- Timestamp when one time password was verified.
  timestamp_expires           TIMESTAMP,                           -- Timestamp when one time password expires.
  CONSTRAINT ns_otp_definition_fk FOREIGN KEY (otp_definition_id) REFERENCES ns_otp_definition (otp_definition_id)
);

-- Table ns_authentication stores authentication attempts.
CREATE TABLE ns_authentication (
  authentication_id           VARCHAR(256) NOT NULL PRIMARY KEY,           -- Authentication identifier (autogenerated).
  user_id                     VARCHAR(256),                                -- User identity identifier.
  type                        VARCHAR(32) NOT NULL,                        -- Authentication type: CREDENTIAL, OTP, CREDENTIAL_OTP.
  credential_id               VARCHAR(256),                                -- Credential identifier.
  otp_id                      VARCHAR(256),                                -- One time password identifier.
  operation_id                VARCHAR(256),                                -- Operation identifier.
  result                      VARCHAR(32) NOT NULL,                        -- Overall authentication result.
  result_credential           VARCHAR(32),                                 -- Authentication result for credential authentication.
  result_otp                  VARCHAR(32),                                 -- Authentication result for one time password authentication.
  timestamp_created           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- Timestamp when authentication record was created.
  CONSTRAINT ns_auth_credential_fk FOREIGN KEY (credential_id) REFERENCES ns_credential_storage (credential_id),
  CONSTRAINT ns_auth_otp_fk FOREIGN KEY (otp_id) REFERENCES ns_otp_storage (otp_id),
  CONSTRAINT ns_auth_operation_fk FOREIGN KEY (operation_id) REFERENCES ns_operation (operation_id)
);

-- Table audit_log stores auditing information
CREATE TABLE audit_log (
    audit_log_id       VARCHAR(36) PRIMARY KEY,
    application_name   VARCHAR(256) NOT NULL,
    audit_level        VARCHAR(32) NOT NULL,
    audit_type         VARCHAR(256),
    timestamp_created  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    message            TEXT NOT NULL,
    exception_message  TEXT,
    stack_trace        TEXT,
    param              TEXT,
    calling_class      VARCHAR(256) NOT NULL,
    thread_name        VARCHAR(256) NOT NULL,
    version            VARCHAR(256),
    build_time         TIMESTAMP
);

-- Table audit_param stores auditing parameters
CREATE TABLE audit_param (
    audit_log_id       VARCHAR(36),
    timestamp_created  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    param_key          VARCHAR(256),
    param_value        VARCHAR(4000)
);

ALTER TABLE ns_operation_history ADD authentication_id VARCHAR(256);
ALTER TABLE ns_operation_history ADD pa_operation_id VARCHAR(256);
ALTER TABLE ns_operation_history ADD CONSTRAINT history_authentication_fk FOREIGN KEY (authentication_id) REFERENCES ns_authentication (authentication_id);
ALTER TABLE ns_operation_history ADD CONSTRAINT ns_history_chosen_method_fk FOREIGN KEY (chosen_auth_method) REFERENCES ns_auth_method (auth_method);
ALTER TABLE ns_operation ADD external_operation_name VARCHAR(32);
ALTER TABLE ns_operation ADD CONSTRAINT operation_config_fk FOREIGN KEY (operation_name) REFERENCES ns_operation_config (operation_name);
ALTER TABLE ns_operation_config ADD expiration_time INTEGER;
ALTER TABLE ns_operation_config ADD CONSTRAINT ns_operation_afs_fk FOREIGN KEY (afs_config_id) REFERENCES wf_afs_config (config_id);
ALTER TABLE ns_organization ADD default_credential_name VARCHAR(256);
ALTER TABLE ns_organization ADD default_otp_name VARCHAR(256);

CREATE UNIQUE INDEX ns_application_name ON ns_application (name);
CREATE UNIQUE INDEX ns_credential_policy_name ON ns_credential_policy (name);
CREATE UNIQUE INDEX ns_otp_policy_name ON ns_otp_policy (name);
CREATE INDEX ns_user_contact_user_id ON ns_user_contact (user_id);
CREATE UNIQUE INDEX ns_user_contact_unique ON ns_user_contact (user_id, name, type);
CREATE INDEX ns_user_identity_status ON ns_user_identity (status);
CREATE INDEX ns_user_identity_created ON ns_user_identity (timestamp_created);
CREATE INDEX ns_user_identity_history_user ON ns_user_identity_history (user_id);
CREATE INDEX ns_user_identity_history_created ON ns_user_identity_history (timestamp_created);
CREATE UNIQUE INDEX ns_role_name ON ns_role (name);
CREATE INDEX ns_user_role_user_id ON ns_user_role (user_id);
CREATE INDEX ns_user_role_role_id ON ns_user_role (role_id);
CREATE INDEX ns_user_alias_user_id ON ns_user_alias (user_id);
CREATE UNIQUE INDEX ns_credential_definition_name ON ns_credential_definition (name);
CREATE UNIQUE INDEX ns_otp_definition_name ON ns_otp_definition (name);
CREATE INDEX ns_credential_storage_user_id ON ns_credential_storage (user_id);
CREATE INDEX ns_credential_storage_status ON ns_credential_storage (status);
CREATE UNIQUE INDEX ns_credential_storage_query1 ON ns_credential_storage (credential_definition_id, user_name);
CREATE UNIQUE INDEX ns_credential_storage_query2 ON ns_credential_storage (user_id, credential_definition_id);
CREATE INDEX ns_credential_storage_query3 ON ns_credential_storage (credential_definition_id, status);
CREATE INDEX ns_credential_history_user_id ON ns_credential_history (user_id);
CREATE INDEX ns_otp_storage_user_id ON ns_otp_storage (user_id);
CREATE INDEX ns_otp_storage_user_id_status ON ns_otp_storage (user_id, status);
CREATE INDEX ns_otp_storage_operation_id ON ns_otp_storage (operation_id);
CREATE INDEX ns_authentication_user_id ON ns_authentication (user_id);
CREATE INDEX ns_authentication_operation_id ON ns_authentication (operation_id);
CREATE INDEX ns_authentication_timestamp_created ON ns_authentication (timestamp_created);
CREATE UNIQUE INDEX ns_hashing_config_name ON ns_hashing_config (name);
CREATE UNIQUE INDEX ns_user_alias_unique ON ns_user_alias (user_id, name);
CREATE UNIQUE INDEX ns_user_role_unique ON ns_user_role (user_id, role_id);
CREATE INDEX audit_log_timestamp ON audit_log (timestamp_created);
CREATE INDEX audit_log_application ON audit_log (application_name);
CREATE INDEX audit_log_level ON audit_log (audit_level);
CREATE INDEX audit_log_type ON audit_log (audit_type);
CREATE INDEX audit_param_log ON audit_param (audit_log_id);
CREATE INDEX audit_param_timestamp ON audit_param (timestamp_created);
CREATE INDEX audit_param_key ON audit_param (param_key);
CREATE INDEX audit_param_value ON audit_param (param_value);

Following foreign keys should be activated only in case all user identities are stored in Next Step identity. In case any user identity is stored in an external system, ignore this DDL update.

ALTER TABLE ns_operation ADD CONSTRAINT ns_operation_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_user_prefs ADD CONSTRAINT ns_user_prefs_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_otp_storage ADD CONSTRAINT ns_otp_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);
ALTER TABLE ns_authentication ADD CONSTRAINT ns_auth_user_fk FOREIGN KEY (user_id) REFERENCES ns_user_identity (user_id);

Configuration of Next Step Identity

The Next Step identity needs to be configured at least with basic configuration. This migration guide describes configuration of Next Step identity in Data Adapter proxy mode which is used by default for compatibility. Please update the following SQL queries with actual names (application name, policy names, etc.).

The configuration script below configures one application, two organizations and policies / definitions for these organizations. The Data Adapter proxy mode is enabled, so user identities are not expected to be stored in Next Step identity yet. However, once the Data Adapter proxy gets disabled, the policies and definitions can become immediately effective.

Configuration for Oracle:

-- Add OTP_CODE authentication method for standalone OTP code use cases.
INSERT INTO ns_auth_method (auth_method, order_number, check_user_prefs, user_prefs_column, user_prefs_default, check_auth_fails, max_auth_fails, has_user_interface, has_mobile_token, display_name_key)
VALUES ('OTP_CODE', 10, 0, NULL, NULL, 1, 3, 1, 0, 'method.otpCode');

-- Configure default credential and OTP definitions for RETAIL organization, update organization name if required.
UPDATE ns_organization SET default_credential_name = 'RETAIL_CREDENTIAL', default_otp_name = 'RETAIL_OTP' WHERE organization_id = 'RETAIL';
-- Configure default credential and OTP definitions for RETAIL organization, update organization name if required.
UPDATE ns_organization SET default_credential_name = 'SME_CREDENTIAL', default_otp_name = 'SME_OTP' WHERE organization_id = 'SME';

-- Sample Next Step application configuration.
INSERT INTO ns_application (application_id, name, description, status, timestamp_created) values (1, 'APP', 'Sample application', 'ACTIVE', CURRENT_TIMESTAMP);

-- Credential and OTP policy configuration.
INSERT INTO ns_credential_policy (credential_policy_id, name, description, status, username_length_min, username_length_max, username_allowed_pattern, credential_length_min, credential_length_max, limit_soft, limit_hard, check_history_count, rotation_enabled, username_gen_algorithm, username_gen_param, credential_gen_algorithm, credential_gen_param, credential_val_param, timestamp_created) values (1, 'CREDENTIAL_POLICY', 'Sample credential policy', 'ACTIVE', 8, 20, '[0-9]+', 8, 40, 3, 5, 3, 0, 'RANDOM_DIGITS', '{"length": 8}', 'RANDOM_PASSWORD', '{"length": 12, "includeSmallLetters": true, "smallLettersCount": 5, "includeCapitalLetters": true, "capitalLettersCount": 5, "includeDigits": true, "digitsCount": 1, "includeSpecialChars": true, "specialCharsCount": 1}', '{"includeWhitespaceRule": true, "includeUsernameRule": true, "includeAllowedCharacterRule": false, "allowedChars": "", "includeAllowedRegexRule": false, "allowedRegex": ".*", "includeIllegalCharacterRule": false, "illegalChars": "", "includeIllegalRegexRule": false, "illegalRegex": "", "includeCharacterRule": true, "includeSmallLetters": "true", "smallLettersMin": 1, "includeCapitalLetters": true, "capitalLettersMin": 1, "includeAlphabeticalLetters": true, "alphabeticalLettersMin": 2, "includeDigits": true, "digitsMin": 1, "includeSpecialChars": true, "specialCharsMin": 1}', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_policy (otp_policy_id, name, description, status, length, attempt_limit, expiration_time, gen_algorithm, gen_param) values (1, 'OTP_POLICY', 'Sample OTP policy', 'ACTIVE', 8, 3, 300, 'OTP_DATA_DIGEST', '{}');

-- Credential and OTP definition configuration.
INSERT INTO ns_credential_definition (credential_definition_id, name, description, application_id, organization_id, credential_policy_id, category, data_adapter_proxy_enabled, status, timestamp_created) values (1, 'RETAIL_CREDENTIAL', 'Sample credential definition for retail', 1, 'RETAIL', 1, 'PASSWORD', 1, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_credential_definition (credential_definition_id, name, description, application_id, organization_id, credential_policy_id, category, data_adapter_proxy_enabled, status, timestamp_created) values (2, 'SME_CREDENTIAL', 'Sample credential definition for SME', 1, 'SME', 1, 'PASSWORD', 1, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_definition (otp_definition_id, name, description, application_id, otp_policy_id, data_adapter_proxy_enabled, status, timestamp_created) values (1, 'RETAIL_OTP', 'Sample OTP definition for retail', 1, 1, 1, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_definition (otp_definition_id, name, description, application_id, otp_policy_id, data_adapter_proxy_enabled, status, timestamp_created) values (2, 'SME_OTP', 'Sample OTP definition for SME', 1, 1, 1, 'ACTIVE', CURRENT_TIMESTAMP);

-- Update sequences.
ALTER SEQUENCE ns_application_seq INCREMENT BY 1;
ALTER SEQUENCE ns_credential_policy_seq INCREMENT BY 1;
ALTER SEQUENCE ns_credential_definition_seq INCREMENT BY 2;
ALTER SEQUENCE ns_otp_policy_seq INCREMENT BY 1;
ALTER SEQUENCE ns_otp_definition_seq INCREMENT BY 2;

Configuration for MySQL:

-- Add OTP_CODE authentication method for standalone OTP code use cases.
INSERT INTO ns_auth_method (auth_method, order_number, check_user_prefs, user_prefs_column, user_prefs_default, check_auth_fails, max_auth_fails, has_user_interface, has_mobile_token, display_name_key)
VALUES ('OTP_CODE', 10, FALSE, NULL, NULL, TRUE, 3, TRUE, FALSE, 'method.otpCode');

-- Configure default credential and OTP definitions for RETAIL organization, update organization name if required.
UPDATE ns_organization SET default_credential_name = 'RETAIL_CREDENTIAL', default_otp_name = 'RETAIL_OTP' WHERE organization_id = 'RETAIL';
-- Configure default credential and OTP definitions for RETAIL organization, update organization name if required.
UPDATE ns_organization SET default_credential_name = 'SME_CREDENTIAL', default_otp_name = 'SME_OTP' WHERE organization_id = 'SME';

-- Sample Next Step application configuration.
INSERT INTO ns_application (name, description, status, timestamp_created) values ('APP', 'Sample application', 'ACTIVE', CURRENT_TIMESTAMP);

-- Credential and OTP policy configuration.
INSERT INTO ns_credential_policy (name, description, status, username_length_min, username_length_max, username_allowed_pattern, credential_length_min, credential_length_max, limit_soft, limit_hard, check_history_count, rotation_enabled, username_gen_algorithm, username_gen_param, credential_gen_algorithm, credential_gen_param, credential_val_param, timestamp_created) values ('CREDENTIAL_POLICY', 'Sample credential policy', 'ACTIVE', 8, 20, '[0-9]+', 8, 40, 3, 5, 3, FALSE, 'RANDOM_DIGITS', '{"length": 8}', 'RANDOM_PASSWORD', '{"length": 12, "includeSmallLetters": true, "smallLettersCount": 5, "includeCapitalLetters": true, "capitalLettersCount": 5, "includeDigits": true, "digitsCount": 1, "includeSpecialChars": true, "specialCharsCount": 1}', '{"includeWhitespaceRule": true, "includeUsernameRule": true, "includeAllowedCharacterRule": false, "allowedChars": "", "includeAllowedRegexRule": false, "allowedRegex": ".*", "includeIllegalCharacterRule": false, "illegalChars": "", "includeIllegalRegexRule": false, "illegalRegex": "", "includeCharacterRule": true, "includeSmallLetters": "true", "smallLettersMin": 1, "includeCapitalLetters": true, "capitalLettersMin": 1, "includeAlphabeticalLetters": true, "alphabeticalLettersMin": 2, "includeDigits": true, "digitsMin": 1, "includeSpecialChars": true, "specialCharsMin": 1}', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_policy (name, description, status, length, attempt_limit, expiration_time, gen_algorithm, gen_param) values ('OTP_POLICY', 'Sample OTP policy', 'ACTIVE', 8, 3, 300, 'OTP_DATA_DIGEST', '{}');

-- Credential and OTP definition configuration.
INSERT INTO ns_credential_definition (name, description, application_id, organization_id, credential_policy_id, category, data_adapter_proxy_enabled, status, timestamp_created) values ('RETAIL_CREDENTIAL', 'Sample credential definition for retail', 1, 'RETAIL', 1, 'PASSWORD', TRUE, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_credential_definition (name, description, application_id, organization_id, credential_policy_id, category, data_adapter_proxy_enabled, status, timestamp_created) values ('SME_CREDENTIAL', 'Sample credential definition for SME', 1, 'SME', 1, 'PASSWORD', TRUE, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_definition (name, description, application_id, otp_policy_id, data_adapter_proxy_enabled, status, timestamp_created) values ('RETAIL_OTP', 'Sample OTP definition for retail', 1, 1, TRUE, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_definition (name, description, application_id, otp_policy_id, data_adapter_proxy_enabled, status, timestamp_created) values ('SME_OTP', 'Sample OTP definition for SME', 1, 1, TRUE, 'ACTIVE', CURRENT_TIMESTAMP);

Configuration for PostgreSQL:

-- Add OTP_CODE authentication method for standalone OTP code use cases.
INSERT INTO ns_auth_method (auth_method, order_number, check_user_prefs, user_prefs_column, user_prefs_default, check_auth_fails, max_auth_fails, has_user_interface, has_mobile_token, display_name_key)
VALUES ('OTP_CODE', 10, FALSE, NULL, NULL, TRUE, 3, TRUE, FALSE, 'method.otpCode');

-- Configure default credential and OTP definitions for RETAIL organization, update organization name if required.
UPDATE ns_organization SET default_credential_name = 'RETAIL_CREDENTIAL', default_otp_name = 'RETAIL_OTP' WHERE organization_id = 'RETAIL';
-- Configure default credential and OTP definitions for RETAIL organization, update organization name if required.
UPDATE ns_organization SET default_credential_name = 'SME_CREDENTIAL', default_otp_name = 'SME_OTP' WHERE organization_id = 'SME';

-- Sample Next Step application configuration.
INSERT INTO ns_application (application_id, name, description, status, timestamp_created) values (1, 'APP', 'Sample application', 'ACTIVE', CURRENT_TIMESTAMP);

-- Credential and OTP policy configuration.
INSERT INTO ns_credential_policy (credential_policy_id, name, description, status, username_length_min, username_length_max, username_allowed_pattern, credential_length_min, credential_length_max, limit_soft, limit_hard, check_history_count, rotation_enabled, username_gen_algorithm, username_gen_param, credential_gen_algorithm, credential_gen_param, credential_val_param, timestamp_created) values (1, 'CREDENTIAL_POLICY', 'Sample credential policy', 'ACTIVE', 8, 20, '[0-9]+', 8, 40, 3, 5, 3, FALSE, 'RANDOM_DIGITS', '{"length": 8}', 'RANDOM_PASSWORD', '{"length": 12, "includeSmallLetters": true, "smallLettersCount": 5, "includeCapitalLetters": true, "capitalLettersCount": 5, "includeDigits": true, "digitsCount": 1, "includeSpecialChars": true, "specialCharsCount": 1}', '{"includeWhitespaceRule": true, "includeUsernameRule": true, "includeAllowedCharacterRule": false, "allowedChars": "", "includeAllowedRegexRule": false, "allowedRegex": ".*", "includeIllegalCharacterRule": false, "illegalChars": "", "includeIllegalRegexRule": false, "illegalRegex": "", "includeCharacterRule": true, "includeSmallLetters": "true", "smallLettersMin": 1, "includeCapitalLetters": true, "capitalLettersMin": 1, "includeAlphabeticalLetters": true, "alphabeticalLettersMin": 2, "includeDigits": true, "digitsMin": 1, "includeSpecialChars": true, "specialCharsMin": 1}', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_policy (otp_policy_id, name, description, status, length, attempt_limit, expiration_time, gen_algorithm, gen_param) values (1, 'OTP_POLICY', 'Sample OTP policy', 'ACTIVE', 8, 3, 300, 'OTP_DATA_DIGEST', '{}');

-- Credential and OTP definition configuration.
INSERT INTO ns_credential_definition (credential_definition_id, name, description, application_id, organization_id, credential_policy_id, category, data_adapter_proxy_enabled, status, timestamp_created) values (1, 'RETAIL_CREDENTIAL', 'Sample credential definition for retail', 1, 'RETAIL', 1, 'PASSWORD', TRUE, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_credential_definition (credential_definition_id, name, description, application_id, organization_id, credential_policy_id, category, data_adapter_proxy_enabled, status, timestamp_created) values (2, 'SME_CREDENTIAL', 'Sample credential definition for SME', 1, 'SME', 1, 'PASSWORD', TRUE, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_definition (otp_definition_id, name, description, application_id, otp_policy_id, data_adapter_proxy_enabled, status, timestamp_created) values (1, 'RETAIL_OTP', 'Sample OTP definition for retail', 1, 1, TRUE, 'ACTIVE', CURRENT_TIMESTAMP);
INSERT INTO ns_otp_definition (otp_definition_id, name, description, application_id, otp_policy_id, data_adapter_proxy_enabled, status, timestamp_created) values (2, 'SME_OTP', 'Sample OTP definition for SME', 1, 1, TRUE, 'ACTIVE', CURRENT_TIMESTAMP);

-- Update sequences.
ALTER SEQUENCE ns_application_seq INCREMENT BY 1;
ALTER SEQUENCE ns_credential_policy_seq INCREMENT BY 1;
ALTER SEQUENCE ns_credential_definition_seq INCREMENT BY 2;
ALTER SEQUENCE ns_otp_policy_seq INCREMENT BY 1;
ALTER SEQUENCE ns_otp_definition_seq INCREMENT BY 2;

Next Step Application Configuration

The Next Step application contains new application properties which can be configured.

# Data Adapter Server Service URL
powerauth.dataAdapter.service.url=http://localhost:8080/powerauth-data-adapter

# PowerAuth Server URL
powerauth.service.url=http://localhost:8080/powerauth-java-server/rest
powerauth.service.security.clientToken=
powerauth.service.security.clientSecret=
# Whether invalid SSL certificates should be accepted
powerauth.service.ssl.acceptInvalidSslCertificate=false

# Use original username for a removed credential when the credential is recreated
powerauth.nextstep.identity.credential.useOriginalUsername=false

# Maximum number of attempts when generating username
powerauth.nextstep.identity.credential.generateUsernameMaxAttempts=100

# Enable or disable operations support in PowerAuth server
powerauth.nextstep.pa.operations.enabled=false

# Key used for end-to-end encryption of credentials
powerauth.nextstep.e2eEncryption.key=

# Key used for database record encryption
powerauth.nextstep.db.master.encryption.key=

The following configuration is mandatory:

  • PowerAuth Data Adapter REST API configuration (property powerauth.dataAdapter.service.url)
  • PowerAuth Server REST API configuration (properties powerauth.service.*)

The encryption keys need to be configured in the following scenarios:

  • End-to-end encryption (property powerauth.nextstep.e2eEncryption.key), use the same key as in Web Flow
  • Database record encryption (property powerauth.nextstep.db.master.encryption.key), generate a new random key

If you want to enable operations support for PowerAuth server, configuration operation templates in PowerAuth server first and then set property powerauth.nextstep.pa.operations.enabled to value true.

Web Flow Application Configuration

The Web Flow application contains new application properties which can be configured.

# Enable or disable operations support in PowerAuth server
powerauth.webflow.pa.operations.enabled=false

# Configuration of authentication using temporary credentials
powerauth.webflow.authentication.allowTemporaryCredentials=false

If you want to enable operations support for PowerAuth server, configuration operation templates in PowerAuth server first, enable operations in Next Step server, and then set property powerauth.webflow.pa.operations.enabled to value true.

Data Adapter Changes

The Data Adapter interface contains two new methods which should be implemented:

  • Method sendAuthorizationSms – this method sends and SMS containing an OTP code generated by Next Step. In case you only use Next Step in Data Adapter proxy mode, the implementation is not required because the original createAndSendAuthorizationSms method is used and OTP code is generated by Data Adapter.
  • Method getPAOperationMapping – this method is used for mapping a Next Step operation into individual small operations. For instance, a PSD2 payment Next Step operation is mapped into two small operations for the login and for the payment. This mapping is used by PowerAuth operations, specify the operation template information matching configuration in PowerAuth server in case PowerAuth operation support is enabled in Next Step and Web Flow.
Last updated on Jun 21, 2021 (12:53) Edit on Github Send Feedback
Search

1.8.x

PowerAuth Web Flow