Configure XDB
Configure Oracle’s XDB Server for Web Applications
There are many developers who use Oracle APEX to develop robust web applications because it’s simply a great tool. While APEX gets better with each new release, there are developers who want to know how to build native PL/SQL web applications.
Oracle APEX uses Oracle XML Database (XDB) Server as its engine. The XDB Server is a multiprotocol web server that lets you configure it with the DBMS_XDB and DBMS_EPG PL/SQL Built-in packages. You also have the ability to configure the XDB Server to concurrently run standalone native PL/SQL web applications.
This article shows you how to configure the XDB Server to run PL/SQL native web applications. It shows you two approaches. The first uses a standard schema, which requires a schema name and password at login. The second uses the anonymous schema, which lets you embed your Access Control List and authentication functions inside the database.
This article has two parts that involve setting up Data Access Descriptors (DADs). They are:
Configure Oracle’s XDB Server for Web Applications
A Secured DAD uses basic HTTP authentication, and discloses the schema name and password to all you log into the application. Basic HTTP authentication starts when you authenticate and ends when you close the browser.
You must setup a port for your native PL/SQL web application. Since the APEX application uses port 8080, you should use the same port. If you change the port number, it is also changes for the APEX application. You set the port number to the default with the SETHTTPPORT procedure of the DBMS_XDB package, like this:
SQL> DECLARE 2 lv_port NUMBER; 3 BEGIN 4 SELECT dbms_xdb.gethttpport() 5 INTO lv_port 6 FROM dual; 7 8 /* Check for default port and reset. */ 9 IF NOT lv_port = 8080 THEN 10 dbms_xdb.sethttpport(8080); 11 END IF; 12 END; 13 / |
After you setup the HTTP port number, you need to create and authorize the secured DAD. You use the CREATE_DAD procedure to create the DAD, and the AUTHORIZE_DAD procedure to authorize the DAD.
The following command creates the STUDENT_DAD DAD:
SQL> BEGIN 2 dbms_epg.create_dad( 3 dad_name => 'STUDENT_DAD' 4 , PATH => '/studentdb/*'); 5 END; 6 / |
This command creates the DAD and points the DAD to a /studentdb/* URL component. The /studentdb/ component identifies a path element. The trailing asterisk identifies all executable PL/SQL procedures inside the DAD.
The following command authorizes the STUDENT_DAD DAD:
SQL> BEGIN 2 dbms_epg.authorize_dad( 3 dad_name => 'STUDENT_DAD' 4 , USER => 'STUDENT'); 5 END; 6 / |
The DAD name is the same but the path points to an Oracle STUDENT schema. Effectively, the CREATE_DAD procedure maps the DAD to the URL component; and the AUTHORIZE_DAD procedure maps the DAD to the user’s schema where you deploy the native PL/SQL procedures.
After you create and authorize the DAD, you can create a helloworld procedure to test your configuration. You should create it in the STUDENT user’s schema, which is a STUDENT container schema.
The following uses Oracle’s built-in PL/SQL web toolkit to create a native PL/SQL web page. Only line 11 is dynamic. The USER keyword on line 11 returns the owning schema name at runtime.
SQL> CREATE OR REPLACE PROCEDURE student.helloworld AS 2 BEGIN 3 -- Set an HTML meta tag and render page. 4 owa_util.mime_header('text/html'); -- <META Content-type:text/html> 5 htp.htmlopen; -- <HTML> 6 htp.headopen; -- <HEAD> 7 htp.htitle('Hello World!'); -- <TITLE>HelloWorld!</TITLE> 8 htp.headclose; -- </HEAD> 9 htp.bodyopen; -- <BODY> 10 htp.line; -- <HR> 11 htp.print('Hello ['||USER||']!'); -- Hello [dynamic user_name]! 12 htp.line; -- <HR> 13 htp.bodyclose; -- </BODY> 14 htp.htmlclose; -- </HTML> 15 END HelloWorld; 16 / |
After creating the helloworld function, you call it from a URL in a browser. The server hostname is oracle12c, which means you would call the native PL/SQL procedure with the following syntax:
http://oracle12c/studentdb/helloworld |
It prompts for Basic HTTP security credentials, like this:
image
You need to provide the STUDENT user name or a CDB Oracle 12c C##PLSQL user name, and the password to the dialog box. After entering the correct credentials, you should see the following web page if you’re using the Oracle 12c C##PLSQL user name:
image
The helloworld procedure renders a web page with the title element of “Hello World!” The helloworld procedure renders a content body with the message of “Hello [STUDENT]!”
There’s only one downside when you use Oracle’s PL/SQL web toolkit. The PL/SQL web toolkit renders HTML tags in uppercase text, as you can see in the following source code:
<HTML> <HEAD> <TITLE>Hello World!</TITLE><H1>Hello World!</H1> </HEAD> <BODY> <HR> Hello [STUDENT]! <HR> </BODY> </HTML> |
You need to type them as string literals in the procedure if you want the browser to render them in lowercase HTML tags. Alternatively, you can write your own replacement to the HTF and HTP PL/SQL built-in packages to render lowercase HTML tags.
This section has shown you how to render a native PL/SQL web application with a secured DAD. The next section builds on this discussion and shows you how to build them with an unsecured DAD.
Setting Up a Unsecured DAD
The benefit of setting up a native PL/SQL web application with an unsecured DAD is that you can have much more control. You can setup your own encryption, authentication, and application metadata.
You need to determine the current configuration of the XDB server before you can configure it to meet your needs. Oracle Database 12c, like prior releases, provides the epgstat.sql script in the Oracle home. This script lets you check the XDB server’s current configuration.
The following query displays the configuration file for the XDB server:
SQL> SELECT dbms_xdb.cfg_get() FROM dual; |
You can run the script as the SYSTEM user, like so:
SQL> ?/rdbms/admin/epgstat.SQL |
It should return the following initial configuration, which includes your secured STUDENT_DAD DAD setup:
+--------------------------------------+
| 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
/studentdb/* STUDENT_DAD
2 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-function wwv_flow_epg_include_modules.authorize
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
-------------------------------- --------------------------------
STUDENT_DAD STUDENT
1 row selected.
+----------------------------+
| DAD authentication schemes |
+----------------------------
DAD Name User Name Auth Scheme
-------------------- -------------------------------- ------------------
APEX ANONYMOUS Anonymous
STUDENT_DAD Dynamic
2 rows selected.
+--------------------------------------------------------+
| ANONYMOUS user status: |
| To use static or anonymous authentication in any DAD, |
| the ANONYMOUS account must be unlocked. |
+--------------------------------------------------------+
Database User Status
--------------- --------------------
ANONYMOUS EXPIRED & LOCKED
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?
----------------------------------
false
1 row selected. |
All the key values from the diagnostic script are bold highlighted. There are two things that you need to change to the configuration. You need to unlock the ANONYMOUS schema and open access to the ANONYMOUS schema.
You need to unlock the ANONYMOUS schema, which you can do with the following syntax as the SYSTEM user:
SQL> ALTER USER anonymous ACCOUNT UNLOCK; SQL> ALTER USER anonymous IDENTIFIED BY NULL; |
Unlocking the access to the ANONYMOUS repository is a bit more complex and requires an anonymous PL/SQL block. The following opens the ANONYMOUS repository:
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 lv_configxml XMLTYPE; 3 lv_value VARCHAR2(5) := 'true'; -- (true/false) 4 BEGIN 5 lv_configxml := DBMS_XDB.cfg_get(); 6 7 -- Check for the element. 8 IF lv_configxml.EXISTSNODE('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN 9 -- Add missing element. 10 SELECT INSERTCHILDXML 11 ( lv_configxml 12 ,'/xdbconfig/sysconfig/protocolconfig/httpconfig' 13 ,'allow-repository-anonymous-access' 14 , XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">' 15 || lv_value 16 || '</allow-repository-anonymous-access>') 17 ,'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') 18 INTO lv_configxml 19 FROM dual; 20 21 dbms_output.put_line('Element inserted.'); 22 ELSE 23 -- Update existing element. 24 SELECT UPDATEXML 25 ( DBMS_XDB.cfg_get() 26 ,'/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()' 27 , lv_value 28 ,'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') 29 INTO lv_configxml 30 FROM dual; 31 32 dbms_output.put_line('Element updated.'); 33 END IF; 34 35 -- Configure the element. 36 dbms_xdb.cfg_update(lv_configxml); 37 dbms_xdb.cfg_refresh; 38 END; 39 / |
It should print the following when run successfully:
Element inserted. |
You should see the following differences when you rerun the epgstat.sql script:
+--------------------------------------------------------+ | ANONYMOUS USER status: | | TO USE static OR anonymous authentication IN ANY DAD, | | the ANONYMOUS account must be unlocked. | +--------------------------------------------------------+ 1 ROW selected. DATABASE USER Status --------------- -------------------- ANONYMOUS OPEN +-------------------------------------------------------------------+ | 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. |
After you have open the ANONYMOUS schema and allow access to the ANONYMOUS repository, you need to grant execute privilege from the STUDENT user to the ANONYMOUS user and create a synonym for the ANONYMOUS user. The ANONYMOUS user needs a synonym for the helloworld procedure owned by the STUDENT user.
You grant the privilege and create the synonym as the SYSTEM user with the following syntax:
SQL> GRANT EXECUTE ON student.helloworld2 TO anonymous; SQL> CREATE SYNONYM anonymous.helloworld2 FOR student.helloworld2; |
You need to create and authorize a new GENERIC_DAD DAD for the next example. You do that with the following command creates the GENERIC_DAD DAD:
SQL> BEGIN 2 dbms_epg.create_dad( 3 dad_name => 'GENERIC_DAD' 4 , PATH => '/db/*'); 5 END; 6 / |
You should note the URL subdirectory for this approach is db. The next command authorizes the GENERIC_DAD DAD:
SQL> BEGIN 2 dbms_epg.authorize_dad( 3 dad_name => 'GENERIC_DAD' 4 , USER => 'ANONYMOUS'); 5 END; 6 / |
The anonymous configuration requires a new step. You set the GENERIC_DAD DAD’s database-username as generic with the following call to the SET_DAD_ATTRIBUTE of the DBMS_EPG package:
SQL> BEGIN 2 dbms_epg.set_dad_attribute( 3 dad_name => 'GENERIC_DAD' 4 , attr_name => 'database-username' 5 , attr_value => 'ANONYMOUS'); 6 END; 7 / |
At this point, you should close the browser to end the scope of the Basic HTTP security permission from the secured DAD example. Launch the browser and enter the following URL:
http://oracle12c/db/helloworld |
You should note that the virtual directory in the URL has changed from studentdb to db. The studentdb virtual directory maps to the secured DAD. The db virtual directory maps to the unsecured DAD.
The native PL/SQL web page should render the following native PL/SQL web page:
You should notice that the user is the ANONYMOUS user while the actual stored procedure is the helloworld procedure stored in the STUDENT database user’s schema.
The connection through the ANONYMOUS schema doesn’t require credentials. You should only deploy synonyms to stored procedures that you deploy in other schemas. The stored procedures should verify access against your internal authentication functions and procedures.
This article has shown you how to write, deploy, and test native PL/SQL web applications in secured and unsecured DADs. You can find complete re-runnable scripts for this article on github.com at the following URL.
