DBMS_XDB.SETLISTENERLOCALACCESS

I noticed an new addition to the DBMS_XDB package a while ago named SETLISTENERLOCALACCESS that, despite it is part of a XMLDB package, was first seen by me on a APEX forum (it is not listed in the Oracle 10g manuals (eg. Oracle® Database PL/SQL Packages and Types Reference).

What does it do?

The manual (Oracle® Database Express Edition Installation Guide !) described it as follows:

4.4 Making Oracle Database XE Server Available to Remote Clients
After you install Oracle Database XE Server, its graphical user interface is only available from the local server, not remotely.

Security Note:

With remote HTTP access to Oracle Database XE, all information exchanged between the browser and the database is in clear text—that is, unencrypted—including database user names and passwords. If this is cause for concern, do not enable remote HTTP connection to the database.

Asking for an explanation on the XMLDB forum the following information was given:

TRUE

If l_access is FALSE, setListenerLocalAccess allows access to the XML DB HTTP server on both the localhost and the non-localhost interfaces.

If you want a more secure database instance, invoke setListenerLocalAccess with l_access as TRUE .

Describing the procedure DBMS_XDB.SETLISTENERLOCALACCESS (located in $ORACLE_HOME\rdbms\admin\dbmsxdb.sql):

---------------------------------------------
-- PROCEDURE - setListenerLocalAccess
--    set/reset the all listeners (HTTP and HTTP2) local access
-- PARAMETERS -
--    l_access
--         Either TRUE or FALSE
---------------------------------------------
PROCEDURE setListenerLocalAccess(l_access boolean);

After setting my SQL*Plus session in trace, it looks like setting the value to TRUE, among others, the xdbconfig.xml file is updated regarding the /xdbconfig/sysconfig/protocolconfig/httpconfig/ element, updates are done on XDB.XDB$ROOT_INFO, XDB.XDB$RESOURCE, etc.

SQL>  execute DBMS_XDB.SETLISTENERLOCALACCESS(TRUE)

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> describe  XDB.XDB$ROOT_INFO
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 RESOURCE_ROOT                                      ROWID
 RCLIST                                             RAW(2000)
 FTP_PORT                                           NUMBER(5)
 FTP_PROTOCOL                                       VARCHAR2(4000)
 HTTP_PORT                                          NUMBER(5)
 HTTP_PROTOCOL                                      VARCHAR2(4000)
 HTTP_HOST                                          VARCHAR2(4000)
 HTTP2_PORT                                         NUMBER(5)
 HTTP2_PROTOCOL                                     VARCHAR2(4000)
 HTTP2_HOST                                         VARCHAR2(4000)

SQL> set head off
SQL> select * from  XDB.XDB$ROOT_INFO;

AAAMyJAACAAADevAAA
010728EAEC0B21CEF960E0400A0A1401225128EAEC0B21D1F960
F960E0400A0A1401225128EAEC0B21D7F960E0400A0A14012251
225128EAEC0B21DDF960E0400A0A1401225128EAEC0B21E0F960
      2100
tcp
      8080
tcp
localhost

SQL> execute DBMS_XDB.SETLISTENERLOCALACCESS(FALSE)

PL/SQL procedure successfully completed.

SQL>  select * from  XDB.XDB$ROOT_INFO;

AAAMyJAACAAADevAAA
010728EAEC0B21CEF960E0400A0A1401225128EAEC0B21D1F960
F960E0400A0A1401225128EAEC0B21D7F960E0400A0A14012251
225128EAEC0B21DDF960E0400A0A1401225128EAEC0B21E0F960
      2100
tcp
      8080
tcp

This is definitely something to take into account, when you experience unexplainable (remote) connection problems, regarding, for instance, setting up XMLDB Protocol Server (a.k.a. the Oracle Listener) functionality. I guess this will be an important procedure to harden your security.