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.
- Connect as the privileged
SYSuser and run the following script. It creates a generic
STUDENTuser 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');
- Connect as the
STUDENTuser 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; /
- Open a browser of your choice, and enter the following URL.
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.
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.
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.
- 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
SYSprivileged user to open that up if its missing. You can check whether or not it’s missing by running this as the
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; /
- These tasks also require the privileged
SYSTEMuser account, and you should sign on to that account to run these commands. The first thing you may need to do is unlock the
ANONYMOUSaccount. 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;
- These tasks require the privileged
SYSuser 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');
- Connect as the
STUDENTuser and grant
EXECUTEpermissions on the
HELLOWORLDprocedure to the
ANONYMOUSuser account. The
GRANTallows you to give unrestricted access to the
ANONYMOUSaccount, which in turn provides it to your web audience.
SQL> GRANT EXECUTE ON helloworld TO anonymous;
- Connect as the
ANONYMOUSuser and create a local synonym that point to the
SYNONYMprovides 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 SYNONYMas the
SYSTEMuser to the
SQL> CREATE SYNONYM helloworld FOR student.helloworld;
ANONYMOUSaccount. The following syntax lets you do that as the privileged
SQL> ALTER USER anonymous IDENTIFIED BY NULL;
- Open a browser of your choice, and enter the following URL, which won’t require a User Name or Password.
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.