MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle 11g XDB Shake & Bake

with 29 comments

It’s a bit awkward when a post generates a new question, but here’s a quick explanation and example of using XDB (XML Database Server) outside of the realm of APEX. More or less, XDB is an Apache Server equivalent configured inside the database. It’s really a protocol server tied into the Shared Server Oracle*Net Architecture (a correction provided by Marco Gralike). As a note, testing was done by using a NAT static IP addressing for the virtual Windows XP, Vista, and 7 environments.

This blog post will show you how to experiment with the PL/SQL Web Toolkit and build both password protected and unprotected database content. It assumes you have access to the SYS privileged account.

Setting Up a Secure DAD

There’s secure and then there’s secure. This falls in the less than secure category but it does provide a password and uses basic HTTP authentication. The USER is the schema name, and the PASSWORD is the same as that for the SQL*Plus access to the schema.

  1. Connect as the privileged SYS user and run the following script. It creates a generic STUDENT user and grants minimalist privileges, then it creates a DAD (Data Access Descriptor), and authorizes the DAD. Don’t run the command if you’re actively using Oracle APEX on the default configuration of port 8080. It’s there for those folks you are running Tomcat on 8080.
-- This resets the default port so that it doesn't conflict with other environment.
EXECUTE dbms_xdb.SETHTTPPORT(8181);
 
-- This creates the STUDENT Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DAD','/sampledb/*');
 
-- This authorizes the STUDENT_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DAD','STUDENT');
  1. Connect as the STUDENT user and run the following script to create a PL/SQL Web Toolkit procedure.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE PROCEDURE HelloWorld AS
BEGIN
  -- Set an HTML meta tag and render page.
  owa_util.mime_header('text/html');  -- <META Content-type:text/html>
  htp.htmlopen;                       -- <HTML>
  htp.headopen;                       -- <HEAD>
  htp.htitle('Hello World!');         -- <TITLE>HelloWorld!</TITLE>
  htp.headclose;                      -- </HEAD>
  htp.bodyopen;                       -- <BODY>
  htp.line;                           -- <HR>
  htp.print('Hello ['||USER||']!');   -- Hello [dynamic user_name]!
  htp.line;                           -- <HR>
  htp.bodyclose;                      -- </BODY>
  htp.htmlclose;                      -- </HTML>
END HelloWorld;
/
  1. Open a browser of your choice, and enter the following URL.
http://localhost:8181/sampledb/helloworld

You then see (or should see) the following Basic HTTP Authentication dialog box. Enter the STUDENT user as the User Name and the Password for the database account. Then, click the OK button.

XDB_BasicHTTPAuthentication

Provided you enter the User Name and Password correctly, you should see the following inside the browser’s display panel. The USER name is a system session scope variable, which will always return the owner of the package because its created as a Definers Rights procedure.

XDB_ProcedureDisplay

You have now successfully configured your Basic HTTP Authentication XDB, which may offer you some possibilities outside of using Oracle APEX.

Setting Up an Unsecured DAD

The trick here is building on what you did by eliminating the authentication. You do this by using the ANONYMOUS account, like Oracle’s APEX does. Well, not quite like it does because APEX provides a very good user authentication model. It allows you to connect to the ANONYMOUS user where you present and validate your credentials.

Since you have to do all the prior steps, these steps are numbered after those above. You start with step #4.

  1. Generally, the XML configuration is missing one key node that allows repository anonymous access. The missing node disallows anonymous login. You can run the code below as the SYS privileged user to open that up if its missing. You can check whether or not it’s missing by running this as the SYS user:
SQL> @?/rdbms/admin/epgstat.SQL

If it returns the following as the last element of the output, you’ll need to run the PL/SQL block below.

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+
 
Allow repository anonymous access?
----------------------------------
false
 
1 row selected.

When you run this script, make sure you’re the privileged SYS user. Then, rerun the epgstat.sql script to verify that you’ve enabled anonymous access to the repository. You may also need to refresh your browser cache before retesting it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SET SERVEROUTPUT ON
DECLARE
  lv_configxml XMLTYPE;
  lv_value     VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
  lv_configxml := DBMS_XDB.cfg_get();
 
  -- Check for the element.
  IF lv_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           ( lv_configxml
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig'
           , 'allow-repository-anonymous-access'
           , XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">'
       	   || lv_value
       	   || '</allow-repository-anonymous-access>')
       	   , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_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()'
           , lv_value
           , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO   lv_configxml
    FROM   dual;
 
    DBMS_OUTPUT.put_line('Element updated.');
  END IF;
 
  -- Configure the element.
  DBMS_XDB.cfg_update(lv_configxml);
  DBMS_XDB.cfg_refresh;
END;
/
  1. These tasks also require the privileged SYSTEM user account, and you should sign on to that account to run these commands. The first thing you may need to do is unlock the ANONYMOUS account. It is locked by default. After you unlock it, you’ll need to verify no default password was assigned by unassigning a password. The following two commands accomplish those tasks.
-- Unlock the user account.
ALTER USER anonymous ACCOUNT UNLOCK;
-- Ensure a password is assigned to the account so you can create a synonym later.
ALTER USER anonymous IDENTIFIED BY ANONYMOUS;
  1. These tasks require the privileged SYS user account because you’re going to create and authorize another DAD.
-- This creates the STUDENT_DB_DAD Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DB_DAD','/db/*');
 
-- This authorizes the STUDENT_DB_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DB_DAD','ANONYMOUS');
 
-- Open the anonymous account by setting the database-username parameter and value.
EXECUTE dbms_epg.set_dad_attribute('STUDENT_DB_DAD','database-username','ANONYMOUS');
  1. Connect as the STUDENT user and grant EXECUTE permissions on the HELLOWORLD procedure to the ANONYMOUS user account. The GRANT allows you to give unrestricted access to the ANONYMOUS account, which in turn provides it to your web audience.
SQL> GRANT EXECUTE ON helloworld TO anonymous;
  1. Connect as the ANONYMOUS user and create a local synonym that point to the STUDENT.HELLOWORLD procedure. The SYNONYM provides a program name for the URL statement. It’s hides the ownership of the actual procedure by supressing the schema name. (You may need to grant CREATE ANY SYNONYM as the SYSTEM user to the ANONYMOUS user.)
SQL> CREATE SYNONYM helloworld FOR student.helloworld;
After you’ve created the synonym, you want to remove the password from the ANONYMOUS account. The following syntax lets you do that as the privileged SYSTEM user.

SQL> ALTER USER anonymous IDENTIFIED BY NULL;
  1. Open a browser of your choice, and enter the following URL, which won’t require a User Name or Password.
http://localhost:8181/db/helloworld

You should see the same browser panel information as that shown by step #3 above, except one thing. The difference is the user name, which should now be ANONYMOUS. The execution occurs with the permissions of the invoker. This means you’ll see the data you’re allowed to see by the owning schema.

Written by maclochlainn

December 2nd, 2009 at 3:54 am

29 Responses to 'Oracle 11g XDB Shake & Bake'

Subscribe to comments with RSS or TrackBack to 'Oracle 11g XDB Shake & Bake'.

  1. Nice demos. Thanks for posting them.

    Hans Forbrich

    7 Feb 10 at 11:42 am

  2. On step 8 I had to grant privileges to ANONYMOUS to create a synonym.

    GRANT CREATE any SYNONYM TO anonymous;

    William Sawyer

    25 Mar 10 at 12:37 am

  3. Great catch, I’ve added it to step #8.

    maclochlainn

    25 Mar 10 at 7:44 am

  4. I spent a good amount of time experimenting with your codes and they proved to be fantastic. Please keep up with the good work which is highly treasured. God bless.

    Anderson

    30 Mar 10 at 9:15 am

  5. This worked flawlessly for me. It was a huge help to me and I’m appreciative of your work.

    Minor comments. I, of course, entered this install completely confused. So anything confusing tends to be really confusing.

    It wasn’t clear to me whether the synonyms were necessary. They clearly are not, but I spent time creating them when in my situation, they are not something I’m interested in doing.

    My only problem left is getting all of this to run on port 80. I’m off to do more digging.

    Again, a truly quality post. Thanks.

    Kurt Look

    13 Jun 10 at 12:20 pm

  6. I have a procedure that I was using in oracle9i with apache plsql toolkit, now I am migrating to Oracle11g
    I have created a DAD and compile the procedure but the images are not displaying, I had the images in apache alis , How can I display the image in Oracle11g ?

    Marcel

    28 Oct 10 at 5:23 am

  7. It is a great subject from you but can you tell us how to avoid authentication??? I mean that if I am calling the url

    http://localhost:8181/db/helloworld

    The procedure executes directly without asking for any user name or password. Maybe you could suggest a way to pass username and password to XDB server. Then, the user will not be asked for any information.

    saleh

    26 Dec 10 at 7:16 pm

  8. So you like the idea of connecting to the schema but you want to then authenticate against an Access Control List (a list of users in a table). Is that right?

    If so, that’s a bit more complex. You need to create your fine-grained user access control in a different schema and route verification to that schema within your procedures. There are a number of approaches to how you may want to accomplish that. Let me suggest you check out the PHP code for that as a started to see if we’re talking about the same thing. You can fine it here.

    maclochlainn

    26 Dec 10 at 8:30 pm

  9. Kurt, You probably found the answer but it’s in step #1 of the post. Just change the value from 8181 to 80 provided nothing else is running on it.

    -- This resets the default port so that it doesn't conflict with other environment.
    EXECUTE dbms_xdb.SETHTTPPORT(80);

    maclochlainn

    26 Dec 10 at 8:37 pm

  10. Hello,

    I have installed oracle 11g Enterprise on window 7 ULTIMATE 32 bit OS.

    Oracle EM is working perfectly.

    I’ve run some queries using Toad. Looks like all is fine from that perspective.

    I’ve restored 4 schemas to the database to test with. All code looks great.

    I can access Procedures and Packages via URL.

    http://test-pc:8181/portal/Hello_World

    I get a Windows Security popup for XDB prompting PORTAL for login credentials. I type in the credentials and I get the Hello_World web form.

    When I try to access the database tables:

    http://test-pc:8181/portal/user_pkg.login?as_client_id=test

    I expect another Windows Security popup to access the database itself since all logins are coded via Packages. I don’t get one, therefore the Package defaults to an unauthized form from the package. Without the additional prompt the program does not have credentials to move forward via web access.

    I believe I have my DADS set up partially correct that allows me to access procedures without any database table access. Like Hello_World works just fine.

    Could you offer some advice? I’m a beginner when it comes to Oracle.

    Regards,
    Larry

    Larry

    6 Jun 11 at 10:58 pm

  11. Thanks for your blogs, they really helped me a great deal in setting up Oracle for the first time!

    Regards,
    Larry

    Larry

    6 Jun 11 at 11:00 pm

  12. My guess is that you need to install the XML DB.

    maclochlainn

    7 Jun 11 at 8:14 pm

  13. Me again,

    The software intsalled XDB, maybe since I have a custome database I need to reinstall.

    I ran script logged in as SYS as SYSDBA without changes: catqm.sql change_on_install XDB TEMP

    Should I have used something other than “change_on_install”? Should this be my database/sid name?

    I ran script logged in as SYS as SYSDBA without changes: catxdbj.sql

    For some reason when I try to update init.ora with dispatchers=”(PROTOCOL=TCP) (SERVICE=XDB)” I get permission error. Not matter what I change for ownershiip, I can’t update this file. Is Oracle blocking file permissions for some reason?

    Looks like the sid in the init.ora file is ORCL which is from the initial install I assume. Why wouldn’t the install use the sid name I assigned at install time?

    Thanks again for your help.
    Larry

    Larry

    8 Jun 11 at 1:16 pm

  14. I changed permissions on the init.ora file and modified the dispatchers. Nothing has changed, I only get 1 login window for oracle-pc and I have to use the PORTAl login credentials to access. I need the login credentials to look up my login credentials in from a database.

    Larry

    8 Jun 11 at 6:11 pm

  15. Here is my XDB setup, maybe I’m missing something here?

    SQL> @?/rdbms/admin/epgstat
    +————————————–+
    | XDB protocol ports: |
    | XDB is listening for the protocol |
    | when the protocol port is non-zero. |
    +————————————–+

    HTTP Port FTP Port
    ——— ——–
    8080 0

    1 row selected.

    +—————————+
    | DAD virtual-path mappings |
    +—————————+

    Virtual Path DAD Name
    ——————————– ——————————–
    /apex/* APEX
    /email/* EMAIL
    /test/* TEST
    /portal/* PORTAL

    4 rows selected.

    +—————-+
    | DAD attributes |
    +—————-+

    DAD Name DAD Param DAD Value
    ———— ———————— —————————————-
    APEX database-username ANONYMOUS
    default-page apex
    document-table-name wwv_flow_file_objects$
    request-validation-funct wwv_flow_epg_include_modules.authorize
    ion

    document-procedure wwv_flow_file_mgr.process_download
    nls-language american_america.al32utf8
    document-path docs

    7 rows selected.

    +—————————————————+
    | DAD authorization: |
    | To use static authentication of a user in a DAD, |
    | the DAD must be authorized for the user. |
    +—————————————————+

    DAD Name User Name
    ——————————– ——————————–
    EMAIL EMAIL
    TEST TEST
    PORTAL PORTAL

    3 rows selected.

    +—————————-+
    | DAD authentication schemes |
    +—————————-+

    DAD Name User Name Auth Scheme
    ——————– ——————————– ——————
    APEX ANONYMOUS Anonymous
    EMAIL Dynamic
    TEST Dynamic
    PORTAL Dynamic

    4 rows selected.

    +——————————————————–+
    | ANONYMOUS user status: |
    | To use static or anonymous authentication in any DAD, |
    | the ANONYMOUS account must be unlocked. |
    +——————————————————–+

    Database User Status
    ————— ——————–
    ANONYMOUS OPEN

    1 row selected.

    +——————————————————————-+
    | ANONYMOUS access to XDB repository: |
    | To allow public access to XDB repository without authentication, |
    | ANONYMOUS access to the repository must be allowed. |
    +——————————————————————-+

    Allow repository anonymous access?
    ———————————-
    true

    1 row selected.

    SQL>

    Larry

    8 Jun 11 at 6:53 pm

  16. Larry, nothing sticks out as the problem. I think you know the standard Oracle instructions are in Chapter 34 of the Administering Oracle XML DB Developer’s Guide, and I assume you’ve verified those steps.

    Questions:

    Is this an upgrade?
    What’s the OS?

    Thanks,
    Michael

    maclochlainn

    11 Jun 11 at 12:40 pm

  17. No upgrade. New install on Window 7 Ultimate for testing purposes. Once I installed software I restored an older backup database version to test with.

    The problem I see is this: The XDB at test-pc is asking for login credentials of schema PORTAL when in fact I need the login credentials forwarded to portal.user_pkg.login which is another set of login privledges to access the database through html code.

    http://test-pc:8080/portal/user_pkg.login?as_client_id=test

    The login is getting me to this procedure but not carrying through the login variables that my procedure needs to access the database via html.

    I tried changing the table content to to the forced login credentials, but that didn’t work either.

    The link you have posted for me did not connect. I’m searching for the documentation and will read through it.

    Thanks for your help!
    Larry

    Larry

    13 Jun 11 at 4:40 pm

  18. I tried the link again and is now working. I’ve read this before but will reread since I may have missed something important.

    Thanks!
    Larry

    Larry

    13 Jun 11 at 4:41 pm

  19. Does the Oracle 11g (Release 2) XDB HTTP Server support OWA_COOKIE package, because I am finding it is not storing anything in the cookies.

    Thanks.

    Rajeev.

    Rajeev

    15 Sep 11 at 9:34 am

  20. It appears that it’s supported, what problems did you run into? What’s your test case that fails? If you post it, I’ll try to look at it next week.

    maclochlainn

    18 Sep 11 at 12:14 pm

  21. I re-did the test. It works now.
    Thanks.

    Rajeev

    29 Sep 11 at 2:29 pm

  22. Hi,

    You cannot remove the password from the ANONYMOUS user by doing alter user anonymous identified by NULL. All that does is set the password to the string null:

    SQL> ALTER USER anonymous IDENTIFIED BY NULL;
     
    USER altered.
     
    SQL> CONNECT anonymous/NULL@//192.168.254.2:1521/orcl
    Connected.

    The best you can do is set an impossible password, there are no null passwords in Oracle

    Kind regards

    Pete

    Pete Finnigan

    6 Jun 13 at 11:58 am

  23. Pete,

    Good catch! I’ve no idea which iteration it was but I tested in both the 10gR2 and 11gR2 versions without success. I also ran in Oracle 12c but that’s beta and I’m not allowed to discussed it. ;-) I’ll have to update the blog post. Thanks.

    Oracle 10g:

    SQL> ALTER USER anonymous IDENTIFIED BY NULL;
    ALTER USER anonymous IDENTIFIED BY NULL
                                       *
    ERROR at line 1:
    ORA-00988: missing OR invalid password(s)

    Oracle 11gR2:

    SQL> ALTER USER anonymous IDENTIFIED BY NULL;
    ALTER USER anonymous IDENTIFIED BY NULL
                                       *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06531: Reference TO uninitialized collection
    ORA-06512: at line 11

    maclochlainn

    6 Jun 13 at 1:26 pm

  24. Via my website I saw a reference to this post. Some small comments/advice…

    From Windows XP Servicepack 2 and/or everything older than Windows 7 and upwards, Windows changed its default behavior to digest authentication. Oracle XMLDB / the XDB protocol listener doesn’t support digest authentication up to 11.2 (in Oracle 12c it is supported). So by default basic authentication is the only option for most XDB connections.

    Windows itself had a lot of issues with their own authentication change, among others regarding sharepoint.

    This might be the solution for some issues out there. See: http://support.microsoft.com/kb/841215

    Also port allocation on port number 1024 and below (port 80) need root/administrator/super privileges… In the Oracle XMLDB Developers Guide is a small section in the latest manuals how to set this up for the listener…

    Marco Gralike

    6 Nov 13 at 1:47 pm

  25. Marco, Thanks, I’ll check it out. Michael

    maclochlainn

    11 Nov 13 at 2:03 am

  26. Hi,

    very nice Michael. I was wondering if Workflow 2.6.4 can work along with XDB under a 11gR2 installation (workflow monitor)…

    Alex

    alexv

    25 Jul 14 at 8:06 am

  27. Alexv, Haven’t tried it but let me know what you find out.

    maclochlainn

    25 Jul 14 at 11:41 am

  28. Hi Michael,

    I want to try it, but in the same installation we have APEX configured too with XDB (epg)at port 8082.
    You wrote at the beginning of post not to setup port if using APEX on default port. Can I proceed and if yes use a different port ? Can both be active and accessed afterall ?

    thank you

    alexv

    28 Jul 14 at 4:05 am

  29. Alexv, Yes.

    maclochlainn

    30 Jul 14 at 2:18 pm

Leave a Reply