Login Management

The "Login Management" plugin provides an overview of all active users, their last login and the number of logins. Furthermore, an expiration date can be set for users, who are not provisioned by an identity provider (e.g. AD, SCIM) and who are not protected. The table can also be exported as excel, pdf or csv.

The task will deactivate the above specified users in two cases:

• The expiration date has been reached.
• The user has not logged in or has never used the account after a configurable number of days.

In addition, the user will be informed by email a configurable number of days before the deactivation date.

If configured, the task also sets an expiration date for the password after a configurable number of days for unprovisioned and unprotected users.

Database

Database Index

With a large number of users, performance can be significantly increased by creating an index on the stats table. Otherwise, it may take several minutes for the view to open.

create index ix_ims_stats_user_id_event_type
    on ims_stats (ims_user_id, ims_event_type);

Database View

Before installation of the extension, it is necessary to create the view login_management manually by executing the following command on your CELUM database.

If you update to a 6.13 version or above, you have to delete old view and create it new, because of changes in the columns.

MS SQL

CREATE VIEW login_management AS
    SELECT u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created,
    DATEDIFF(d, u.IMS_CREATED, GETDATE()) daysCreated,
    MAX(s.ims_time) last_login,
    COUNT(case when s.ims_event_type = 1 then 1 else null end) logins,
    DATEDIFF(d, MAX(s.ims_time), GETDATE()) daysLastLogin,
    u.ims_authenticator_name,
    u.ims_expiration_date,
    u.ims_password_expiration_date,
    upv.value As 'uiLocale'
    FROM ims_authorizable u
    LEFT JOIN ims_stats s
    ON s.ims_user_id = u.ims_id AND s.ims_event_type = 1
    LEFT JOIN ims_userprofile up
    ON u.ims_id = up.ims_userid
    LEFT JOIN ims_userprofilevalue upv
    ON up.ims_id = upv.ims_id AND upv.name = 'uiLocale'
    WHERE u.ims_discriminator = 'usr' AND u.ims_deactivated = 0 AND u.ims_authenticator_name != 'api'
    GROUP BY  u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created, u.ims_authenticator_name, u.ims_expiration_date, u.ims_password_expiration_date, upv.value

MySQL

CREATE VIEW login_management AS
    SELECT u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created,
    (TO_DAYS(CURDATE()) - TO_DAYS(u.ims_created)) daysCreated,
    MAX(s.ims_time) last_login,
    COUNT(case when s.ims_event_type = 1 then 1 else null end) logins,
    (TO_DAYS(CURDATE()) - TO_DAYS(MAX(s.ims_time))) daysLastLogin,
    u.ims_authenticator_name,
    u.ims_expiration_date,
    u.ims_password_expiration_date,
    upv.value As 'uiLocale'
    FROM ims_authorizable u
    LEFT JOIN ims_stats s
    ON s.ims_user_id = u.ims_id AND s.ims_event_type = 1
    LEFT JOIN ims_userprofile up
    ON u.ims_id = up.ims_userid
    LEFT JOIN ims_userprofilevalue upv
    ON up.ims_id = upv.ims_id AND upv.name = 'uiLocale'
    WHERE u.ims_discriminator = 'usr' AND u.ims_deactivated = 0 AND u.ims_authenticator_name != 'api'
    GROUP BY u.ims_id

Oracle

CREATE VIEW login_management AS
    SELECT u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created,
    (trunc(sysdate) - trunc(u.ims_created)) daysCreated,
    MAX(s.ims_time) last_login,
    COUNT(case when s.ims_event_type = 1 then 1 else null end) logins,
    (trunc(sysdate) - trunc(max(s.ims_time))) daysLastLogin,
    u.ims_authenticator_name,
    u.ims_expiration_date,
    u.ims_password_expiration_date,
    TO_CHAR(upv.value) AS uiLocale
    FROM ims_authorizable u
    LEFT OUTER JOIN ims_stats s
    ON s.ims_user_id = u.ims_id AND s.ims_event_type = 1
    LEFT OUTER  JOIN ims_userprofile up
    ON u.ims_id = up.ims_userid
    LEFT OUTER  JOIN ims_userprofilevalue upv
    ON up.ims_id = upv.ims_id AND upv.name = 'uiLocale'
    WHERE u.ims_discriminator = 'usr' AND u.ims_deactivated = 0 AND u.ims_authenticator_name != 'api'
    GROUP BY  u.ims_id, u.ims_firstname, u.ims_lastname, u.ims_name, u.ims_email, u.ims_kind, u.ims_created, u.ims_authenticator_name, u.ims_expiration_date, u.ims_password_expiration_date, TO_CHAR(upv.value)

Properties

To be configured in {home}/appserver/conf/custom.properties.

loginManagement.license

type: String, required: yes, default: -

License key (delivered by brix IT Solutions)

loginManagement.visibleForGroup

type: List of long (comma-separated), required: no, default: -

Restrict the use of the plugin to this user group IDs (superadmins always see it in any case).

loginManagement.expireDays

type: long, required: no, default: 0

Defines how many days since the last login/creation date the user account will be deactivated. If not defined, nothing happens.

Super Administrators (including the API-User) will never be deactivated

loginManagement.reminderDays

type: long, required: no, default: 0

Defines how many days before the deactivation the user should receive a reminder mail. If not defined, nothing happens.

loginManagement.cronExpression

type: time (seconds minutes hours days months years, separated with "space"), required: no, default: 0 0 1 * * ? (each night at 01:00)

When and how often the task is running.

loginManagement.protectedUsers

type: list of UserIds (comma-separated), required: no, default: -, since: 1.1

Protected users are never deactivated, regardless of the settings above. Useful for technical users. Note that superadmins are never deactivated, so those don't need to be listed here.

loginManagement.pwExpires

type: boolean, required: no, default: false

Defines if a password expiration date should be set for unprovisioned users.

loginManagement.protectedPwUsers=

type: list of UserIds (comma-separated), required: no, default: -, since: 1.3

Password of protected user never expires. Useful for technical users. Note that superadmins' password never expires, so those don't need to be listed here.

password.expirationDays

type: long, required: no, default: 30

Defines after how many days the password should expire.

loginManagement.deleteDays

type: long, required: no, default: 0, since: 1.3.7

Defines after how many days since the deactivation date the user account should be deleted. If not defined, nothing happens.

loginManagement.protectedDeleteUsers

type: list of UserIds (comma-separated), required: no, default: -, since: 1.3.7

Protected users are never deleted, regardless of the settings above.

Screenshots

Compatibility Matrix

Release Notes

1.0.0

Release: 2021-01-22

Initial Version

1.1

Release: 2021-06-29

Added protectedUsers

1.2

Release: 2021-07-26

• Added restriction for provisioned users
• added expiration date
• added passwort expiration date
• added export

1.3

Release: 2022-03-16

• Added protectedPwUsers
• added infomail for expiration date

1.3.1

Released 2022-08-16

Added support for CELUM 6.13

1.3.7

Released 2023-03-14

• Nova Support
• delete deactivated user