XDB username and password required

XDB and ACL configuration issue in Oracle APEX 4.x

Apex 4.2 (PL/SQL gateway) and Oracle 11g

Just a quick post about a problem that we encountered the other day for who knows what time and that is why I decided to blog about it. Maybe it will save us some time next time we/you encounter the same problem.

Story goes like this.....

On UAT server where APEX installation was done months ago users all of the sudden started getting XDB authentication required message.

  

Since these do not come very often it caught us by surprise. 

These are few crucial Cookbook-steps you need to do to check if your ACL permissions are configured properly. 

Key thing here, of course is to find out why this started to happen? 

We managed to track this down to few external files that were added to newly created application referencing some CSS and JavaScript files which other application on the same server did not do.  Okay but why would this now be a problem?

Well simple theory behind this is that APEX images/files are stored within XMLDB, in order to access the XMLDB resources the appropriate ACL (Access Control List) assigned to the images directory need to have anonymous read-contents access otherwise (you guessed it) you are prompted for a username and password.

Please note that after seeing so many posts about the same problem this may help but again depending on your situation it may not. 

Advice would be:

• check that your ANONYMOUS account is not locked - how to do this

select account_status from dba_users where username='ANONYMOUS';

ANONYMOUS is an Oracle user account specifically designed for HTTP access. It has only one system privilege, that is “create session” and the account is locked by default. If it is unlocked it can access objects in the XDB Repository that are protected by an ACL (Access Control Lists) mentioning this rule.  

When APEX is installed then there should be a /sys/acls/ro_anonymous_acl.xml file that grants read access to the /images/ or /i/ directory (depending on the APEX version). Example of such file

<acl description="File /sys/acl/rr_acl.xml"
     xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
     xmlns:dav="DAV:"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                         http://xmlns.oracle.com/xdb/acl.xsd">
  <ace>
    <principal>ANONYMOUS</principal>
    <grant>true</grant>
    <privilege>
      <read-properties/>
      <read-contents/>
      <resolve />
    </privilege>
  </ace>
</acl>  

If you lock ANONYMOUS or remove the ACL defined privileges then APEX can not show/access these files in XDB Repository folder (/images or /i).

•   if it is you have to unlock it with

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

•  check that your XDB user is not locked

select account_status from dba_users where username='XDB';

• again similar to step 2 if XDB user is locked you have to unlock it with

ALTER USER XDB ACCOUNT UNLOCK;

• next check ACL configuration file first that it contains <read- > line for both properties and contest similar as shown in yellow above

  select xdburitype('/sys/acls/ro_anonymous_acl.xml').getclob() from dual

• if it doesn't add these lines to the ACL config file and give it a go. Note here we had a file containing <read-content> but without a <read-properties> and everything else was fine but things were still not working so it is worth checking before taking next step

• then as last thing check XDB configuration access by running 

DECLARE
    l_configxml XMLTYPE;
    l_value VARCHAR2(5) := 'true';
BEGIN
    l_configxml := DBMS_XDB.cfg_get();
    IF l_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
       DBMS_OUTPUT.put_line('Config Element missing');
    ELSE
      DBMS_OUTPUT.put_line('Config Element exists but may need updating');
    END IF;
END; 

• this will give you an idea where other problem might be. In both cases you can run the following script

SET SERVEROUTPUT ON;

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

What script does is it checks for configuration if it does not exist it creates one else it updates existing one. Hopefully by now your popup window should be long gone. 
On top of this the only thing that might be needed is resetting passwords for these two accounts.

ALTER USER ANONYMOUS IDENTIFIED BY anonymous;

ALTER USER XDB IDENTIFIED BY xdb;

And for the reference if none of the above helped some of these queries might be helpful.

SELECT XMLSerialize(DOCUMENT DBMS_XDB.getACLDocument('/images') AS CLOB) FROM DUAL;
SELECT DBMS_XDB.cfg_get() FROM DUAL;
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

Beautiful article with all very useful details about ACL and XDB was posted by Marco.

Last note is that if you are using APEX listener for your APEX application there is no need for any of these. Lucky you :)

Well thanks for reading hope it helped.

Cheers,
SLino