On HTTP 401 Unauthorized (with Oracle EPG)

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It could happen that suddenly your Apex application that has been working for years starts asking for a username and password in order to access the XDB repository.
You hit the Cancel button and all you get is:

401 Unauthorized

No html, no images, nothing is returned, your app is blank.
What the hell happened with it?

How could this happen if I didn't change anything in a long time?
Where do I start looking first?

First of all, we need to assess what's gone awry.
If you are a Firefox user, Firebug comes in handy with its panels: check out the "Net" panel and see which component is giving troubles.

In a previous posting, I had problems with static files and images in the repository, but not with the main Apex component, the f function that is processing and returning the main HTML page.

If the page is just "garbled" and some components are missing, the problems are likely caused by permissions set at the XDB resource level (see the previous posting about this).

If HTTP 401 is returned by the main page itself instead, either there is problem with the XDB configuration file or what else?

In the former case, you should compare the current xdbconfig.xml with a working backup copy (some time ago I wrote a posting also about recovering a corrupted xdbconfig.xml, check it out).
It's absolutely necessary to keep backup copies of working configurations that will save your time in case of troubles like these, allowing you to make comparisons or quickly restore them if anything went wrong and it's much better than guessing its content without knowing if a certain option was set or unset when the system was running just fine.

Now, supposing the xdbconfig.xml is ok, still the same as before, where else should I turn my attention to?
Luckily enough, my sixth sense told me to look next at the database user status.
OK, great, but which one?

Depending on your configuration you may have a couple of Apex database users that have been created over time:
ANONYMOUS and APEX_PUBLIC_USER.
The good one is returned by the following query if the database is XE with EPG (Embedded PL/SQL Gateway) and the DAD is named APEX:

-- DBA user required
select dbms_epg.get_dad_attribute('APEX','database-username') d from dual;

D

--------- 

ANONYMOUS

The next step is to check what's the status of user ANONYMOUS.

select account_status, lock_date, expiry_date

  from dba_users
where username = 'ANONYMOUS';

ACCOUNT_STATUS                   LOCK_DATE           EXPIRY_DATE       
-------------------------------- ------------------- -------------------
LOCKED                           01-04-2014 18:37:26                    

Here is the answer. A locked account will make your Apex site look miserable in no time.
Just unlock the account ALTER USER ANONYMOUS ACCOUNT UNLOCK and everything will resume working immediately.
Thereafter you just need to find what or who made that account become locked suddenly, but that's another story.