Archive for the ‘XDB Server’ Category
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
SYS
user and run the following script. It creates a genericSTUDENT
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'); |
- 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; / |
- 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
SYS
privileged user to open that up if its missing. You can check whether or not it’s missing by running this as theSYS
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; / |
- 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 theANONYMOUS
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; |
- 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'); |
- Connect as the
STUDENT
user and grantEXECUTE
permissions on theHELLOWORLD
procedure to theANONYMOUS
user account. TheGRANT
allows you to give unrestricted access to theANONYMOUS
account, which in turn provides it to your web audience.
SQL> GRANT EXECUTE ON helloworld TO anonymous; |
- Connect as the
ANONYMOUS
user and create a local synonym that point to theSTUDENT.HELLOWORLD
procedure. TheSYNONYM
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 grantCREATE ANY SYNONYM
as theSYSTEM
user to theANONYMOUS
user.)
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.
Oracle 11g XDB DADs
Somebody asked me why the DBMS_EPG.GET_DAD_LIST
is a procedure because you can’t just simply list the DAD
values. I answered that Oracle chose to implement it that way. Then, they asked how they could query it. I suggested they just run the epgstat.sql
diagnostic script provided in the $ORACLE_HOME/rdbms
directory, which provides those values and much more.
You can run the diagnostic script as the SYS
privileged user, or as any user that has been granted the XDBADMIN
role, like this:
SQL> @?/rdbms/admin/epgstat.sql |
Notwithstanding the diagnostic script, they asked how you could wrap the OUT
mode PL/SQL data type in the procedure call, and return the list of values in a SQL query. Because the formal parameter is a PL/SQL data type, this requires two key things. One is a local variable that maps to the DBMS_EPG
package collection data type, and a pipelined table function. Here’s one way to solve the problem:
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 | CREATE OR REPLACE TYPE dad_list AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION get_dbms_epg_dads RETURN dad_list PIPELINED IS -- Deine a local variable as the OUT mode target of GET_DAD_LIST procedure. SOURCE DBMS_EPG.VARCHAR2_TABLE; -- Declare a local variable of the SQL collection data type. list DAD_LIST := dad_list(); BEGIN -- Call the procedure to populate the source. dbms_epg.get_dad_list(SOURCE); -- Extend space for all defined DAD values. list.EXTEND(SOURCE.COUNT); -- Assign values from PL/SQL collection to SQL collection. FOR i IN 1..source.COUNT LOOP list(i) := SOURCE(i); PIPE ROW(list(i)); END LOOP; RETURN; END get_dbms_epg_dads; / -- Set SQL*Plus width. SET LINESIZE 79 -- Query collection. SELECT column_value AS "DAD LIST" FROM TABLE(get_dbms_epg_dads); |
Marco Gralike provided a simpler approach them the Pipelined Table Function here. I’ve copied the code example below:
1 2 3 4 5 6 | SELECT u.dad AS "PL/SQL DAD List" FROM XMLTable(XMLNAMESPACES ( DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd' ) , '/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-language="PL/SQL"]' PASSING DBMS_XDB.CFG_GET() COLUMNS DAD VARCHAR2(15) PATH '/servlet/servlet-name/text()') u; |
Hope this proves handy to somebody else, too.