User locked, error message ‘invalid logon credentials’.

While creating a simple test application, I stumbled upon a feature in apex that I wanted to share. This is not a solution mind you, just a description of what happens to help you think of a solution that will suit your needs.

Consider the following:

Let’s say that you are a regular user, trying to logon to an APEX application (4.0.1). And this application has the standard security features applied that are provided by apex. And because this user has been away for a long time (or any other reason you can think of), his account was locked by the application administrator.

Upon returning and logging on to Apex, the user gets following error message:

However the logon credentials provided were correct.

Question: what happened?

— Requesting the workspace id for the user…

— (if you find too many results just add ‘where user_name = <the apex user> ‘

Select user_name, workspace_id from apex_workspace_apex_users;

— Find out that this account is locked…

select user_name,account_locked from wwv_flow_users;

–attempt to unlock the account…

begin
  wwv_flow_api.set_security_group_id(6433908187606561);
  apex_util.unlock_account('TEUN');
end;

— Getting the just error code that you must be an administrator to unlock an account…

.ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 356
ORA-06512: at "SYS.HTP", line 1368
ORA-06512: at "SYS.HTP", line 1443
ORA-06512: at "SYS.HTP", line 1735
ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 298
ORA-20987: User  requires ADMIN privilege to perform this operation.
ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 693
ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 1008
ORA-06512: at "APEX_040100.WWV_FLOW_FND_USER_API", line 76
ORA-06512: at "APEX_040100.WWV_FLOW_FND_USER_API", line 1740
ORA-06512: at "APEX_040100.HTMLDB_UTIL", line 1696
ORA-06512: at line 3

Therefore:

If you have a user with admin privileges then it is quite possible to unlock your own account, or any other, using SQL*PLUS

If you are a regular user and your account is locked, then basically any action you try to perform in the database, will be prevented by an error message “invalid logon credentials”.