[SOLUTION] – Oracle APEX – XDB Authentication Popup dialog

I am facing with “XDB authentication popup dialog” for a long time. This usually happened when I tried to access the apex root url such as http://xxx.xxx.xxx.xx/apex. When I was accessing to the apex login page directly, such as http://xxx.xxx.xxx.xxx/apex/f?p=4550, everything was fine. So, I did not give importance to fix XDB authentication issue for long time.

Since Today morning, this issue popped up for any page that I would tried to access. It became a very annoying problem and I had to fix it immediately or just forget about using APEX until the issue is fixed. I already knew that it has to do with permissions. A little google search took me to apexexplorer.com. I know the admin “Md. Kamam Hossain”. He is an early bird APEX Certified expert. So, I was confident about this solution and wanted to try that instead of searching at other urls.

However, the script which he published has some syntax errors. So, I am publishing the working script here.

Make sure you are connected to SQL Plus with SYSDBA privileges such as using the command “CONNECT SYS AS SYSDBA”


DECLARE
l_configxml XMLTYPE;
l_value VARCHAR2(5) := 'true'; --(true/false)
BEGIN
l_configxml := DBMS_XDB.cfg_get();
IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
-- Add missing element.
SELECT insertChildXML
(
l_configxml,
'/xdbconfig/sysconfig/protocolconfig/httpconfig',
'allow-repository-anonymous-access',
XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' ||
l_value ||
'</allow-repository-anonymous-access>'),
'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
)
INTO l_configxml
FROM dual;
DBMS_OUTPUT.put_line('Element inserted');
ELSE
-- Update existing element.
SELECT updateXML
(
DBMS_XDB.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
l_value,
'xmlns=”http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
)
INTO l_configxml
FROM dual;
DBMS_OUTPUT.put_line('Element updated.');
END IF;
DBMS_XDB.cfg_update(l_configxml);
DBMS_XDB.cfg_refresh;
END;
/


You can download the script here

Apart from executing the script, you need to also unlock XDB & Anonymous account. Use the command below for it.


SQL > ALTER USER ANONYMOUS IDENTIFIED BY anonymous;
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL> ALTER USER XDB IDENTIFIED BY xdb;
SQL> ALTER USER XDB ACCOUNT UNLOCK;