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”.