Oracle 11g XDB Shake & Bake
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 genericSTUDENTuser 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.
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.
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 theSYSuser:
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; / |
- 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 theANONYMOUSaccount. 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 grantEXECUTEpermissions on theHELLOWORLDprocedure to theANONYMOUSuser account. TheGRANTallows you to give unrestricted access to theANONYMOUSaccount, 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 theSTUDENT.HELLOWORLDprocedure. TheSYNONYMprovides 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 grantCREATE ANY SYNONYMas theSYSTEMuser to theANONYMOUSuser.)
SQL> CREATE SYNONYM helloworld FOR student.helloworld; |
ANONYMOUS account. The following syntax lets you do that as the privileged SYSTEM user.
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.
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.
Subscribe via RSS