Cleanup Service should purge unused user profiles and history of failed logins

Current Situation

• User profiles are created on demand when a user account performs a first time login.
• User profiles are visible from each Identity Service in its Profile sub-view.

Desired Behavior

• The JS7 Cleanup Service should purge profiles that have not been used for a longer time from the database.
• The JOC Cockpit GUI offers the Settings page with "cleanup" settings. A setting is added to specify the number of days for which a user account did not login to JOC Cockpit. With the number of days being exceeded the profile will be removed by the JS7 Cleanup Service.

Maintainer Note

• Settings -> cleanup
  • 2 new settings introduced:
    • profile_age
      • Default: 365d
      • Any profiles for the accounts with the last successful login older than this value will be purged.
    • failed_login_history_age
      • Default: 90d
      • Any failed login history entries older than this value will be purged.

Test Instructions

• Create a new Account, e.g. cleanup_test
  • Manage Identity Services -> JOC -> Accounts -> Add Account
    • Role: all
• JOC Login as cleanup_test:
  1. try to login with a wrong password.
  2. login with the correct password.
• Check in the database:
  • select * from JOC_CONFIGURATIONS where ACCOUNT='cleanup_test';
    • the query should return 2 entries with the CONFIGURATION_TYPE PROFILE and SETTING.
  • select * from IAM_HISTORY_DETAILS where IAM_HISTORY_ID in (select ID from IAM_HISTORY where ACCOUNT_NAME='cleanup_test');
    • the query should return details about the failed login.
  • select * from IAM_HISTORY where ACCOUNT_NAME='cleanup_test';
    • the query should return rows with the LOGIN_SUCCESS=0 and LOGIN_SUCCESS=1.
    • set LOGIN_DATE for this entries older than the Settings profile_age , e.g. 2020-01-01 00:00:00.
• Configure the cleanup service to start at short notice:
  • Settings -> cleanup -> period , period_begin
• Check in the database after the cleanup service was executed (<joc>/logs/service-cleanup.log):
  • select * from JOC_CONFIGURATIONS where ACCOUNT='cleanup_test';
  • select * from INV_FAVORITES where ACCOUNT='cleanup_test';
  • select * from DEP_KEYS where ACCOUNT='cleanup_test';
  • select * from INV_CERTS where ACCOUNT='cleanup_test';
  • select * from IAM_HISTORY_DETAILS where IAM_HISTORY_ID in (select ID from IAM_HISTORY where ACCOUNT_NAME='cleanup_test');
    • should return no result.
  • select * from IAM_HISTORY where ACCOUNT_NAME='cleanup_test';
    • should return only 1 row with the LOGIN_SUCCESS=1.