What Should :APP_USER Contain in Your APEX Application?
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.
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
-- get the person.person_id and put into :APP_USER
where username = upper(regexp_replace(:APP_USER, '@.*', null));
p_user => l_person_id );
when no_data_found then
'person not found for username = '
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.