The Challenge

Changing usernames in Apex applications leads to various complications. The app_user identifier is referenced in multiple locations, such as APEX preferences, report subscriptions, audit history, and Quick SQL, to name a few. Changing the username can result in the loss of associations and require additional efforts to convert all the affected components.

The Solution

To address this challenge, we recommend immediately changing the value of app_user to an immutable value after a user logs in. This immutable value can be the primary key of the person table or any other unique identifier that won't change over time. By doing so, all the preferences, subscriptions, and other metadata will be associated with the new immutable ID instead of the username.

To implement this solution, edit your APEX Authentication scheme and add the following to the PL/SQL Code text item (modified for your actual person/user table, of course):

procedure post_authN as
l_person_id     person.person_id%type;
begin
    -- get the person.person_id and put into :APP_USER
    select person_id
      into l_person_id
      from person
      where username = upper(regexp_replace(:APP_USER, '@.*', null));

    apex_custom_auth.set_user (
        p_user => l_person_id );

exception
   when no_data_found then
      raise_application_error(
                             -20000,
                             'person not found for username = '
                             || :app_user
      );
end;

In the "Post-Authentication Procedure Name," enter post_authN.

That's it. Now your :APP_USER value will be the immutable primary key of your user. Users will never loose their preferences, report subscriptions, etc.

Benefits and Considerations

Changing to an immutable ID offers several benefits:

• User preferences and metadata remain intact even if the username changes.

• It provides a consistent and reliable way to identify users throughout the application.

• It simplifies future maintenance and data management.

However, there are a few considerations to keep in mind:

• Viewing user-related data, such as page views or activity logs, may require additional joins with the person table.

• If you have existing user data, this will be a challenging change. You'll need to update everyone's metadata as part of this change.

• You will likely want to create a few application-level items to hold the person's first and last name and possibly username.

• You will need to change the navigation bar entry from :APP_USER to :G_PERSON_NAME (or the appropriate value).

If you prefer a video format, check out APEX Instant Tips numero 97.