Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #14: Dynamic Web Pages

Learn how to create dynamic web pages by linking the output to the contents of a relational database.

Lab Instructions

This lab has four unit testing steps placed into one test script.

[10 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Lab Instructions →

You begin these steps after running the following script:

  1. [2 points] Verify the XDB Server port is 8080.

You use the dbms_xdb.gethttpport procedure to check the XDB Server port and you use the dbms_xdb.sethttpport procedure to set the XDB Server port.

Instruction Details →

You check and set the XDB Server port.

You check whether the XDB Server port is set to 8080 and if it is not set to that port the code resets it.

DECLARE
  lv_port  NUMBER;
BEGIN
  SELECT dbms_xdb.gethttpport()
  INTO   lv_port
  FROM dual;
 
  /* Check for default port and reset. */
  IF NOT lv_port = 8080 THEN
    dbms_xdb.sethttpport(8080);
  END IF;
END;
/

You can verify the port setting with the following query:

SELECT dbms_xdb.gethttpport() FROM dual;

It should return the following output:

DBMS_XDB.GETHTTPPORT()
----------------------
                  8080

  1. [2 points] Configure the XDB Server to run a helloworld procedure in the student database.

You use need to create the Data Access Descriptor (DAD), authorize the DAD, and test the helloworld procedure from the DAD.

Instruction Details →

You create, authorize, and test the secured studentdb DAD.

  1. As the system user, you call the dbms_epg.create_dad procedure to set the Data Access Descriptor (DAD) name and map it to a URL path. 

    1
2
3
4
5
6
7

    BEGIN
  /* Create the student_dad. */
  dbms_epg.create_dad(
      dad_name => 'STUDENT_DAD'
    , path => '/studentdb/*');
END;
/
  2. As the system user, you call the dbms_epg.authorize_dad procedure to set the DAD name and map it to a URL path. 

    1
2
3
4
5
6
7

    BEGIN
  /* Authorize the student_dad for the student user. */
  dbms_epg.authorize_dad(
      dad_name => 'STUDENT_DAD'
    , USER => 'STUDENT');
END;
/
  3. As the system user, you create the helloworld procedure in the student database to test the creation and authorization of your new DAD. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    CREATE OR REPLACE PROCEDURE student.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;
/
  4. You use the following URL to access the helloworld procedure. 

    http://localhost:8080/studentdb/helloworld

    It will should prompt you for the user credentials for the student database in a dialog box like the following. You should use student for user name and student for password:

    Line 11 calls USER, which is a reserved word for the database name. Since you connect to the student database as the student user, it will return student.

    After you enter the user credentials for the student database and click the OK button, you will see the following web page:

    If the web page displays as shown above, you have successfully created and authorized the studentdb DAD.

  1. [2 points] Enable an anonymous user account.

You can view the default XDB configuration setup by clicking on the Default Configuration Setting link below.

Default Configuration Settings →

As the system user, you can query the XDB Server’s configuration by running the following query:

SET LONG 500000
SELECT dbms_xdb.cfg_get() FROM dual;

DBMS_XDB.CFG_GET()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>15</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-use>
    <persistent-sessions>false</persistent-sessions>
    <default-lock-timeout>3600</default-lock-timeout>
    <xdbcore-logfile-path>/sys/log/xdblog.xml</xdbcore-logfile-path>
    <xdbcore-log-level>0</xdbcore-log-level>
    <resource-view-cache-size>1048576</resource-view-cache-size>
    <protocolconfig>
      <common>
        <extension-mappings>
          <mime-mappings>
            <mime-mapping>
              <extension>au</extension>
              <mime-type>audio/basic</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>avi</extension>
              <mime-type>video/x-msvideo</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>bin</extension>
              <mime-type>application/octet-stream</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>bmp</extension>
              <mime-type>image/bmp</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>css</extension>
              <mime-type>text/css</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>doc</extension>
              <mime-type>application/msword</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>eml</extension>
              <mime-type>message/rfc822</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>gif</extension>
              <mime-type>image/gif</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>htm</extension>
              <mime-type>text/html</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>html</extension>
              <mime-type>text/html</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jpe</extension>
              <mime-type>image/jpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jpeg</extension>
              <mime-type>image/jpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jpg</extension>
              <mime-type>image/jpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>js</extension>
              <mime-type>application/x-javascript</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jsp</extension>
              <mime-type>text/html</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mid</extension>
              <mime-type>audio/mid</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mov</extension>
              <mime-type>video/quicktime</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>movie</extension>
              <mime-type>video/x-sgi-movie</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mp3</extension>
              <mime-type>audio/mpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mpe</extension>
              <mime-type>video/mpg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mpeg</extension>
              <mime-type>video/mpg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mpg</extension>
              <mime-type>video/mpg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>msa</extension>
              <mime-type>application/x-msaccess</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>msw</extension>
              <mime-type>application/x-msworks-wp</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>pcx</extension>
              <mime-type>application/x-pc-paintbrush</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>pdf</extension>
              <mime-type>application/pdf</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>png</extension>
              <mime-type>image/png</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>ppt</extension>
              <mime-type>application/vnd.ms-powerpoint</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>ps</extension>
              <mime-type>application/postscript</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>qt</extension>
              <mime-type>video/quicktime</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>ra</extension>
              <mime-type>audio/x-realaudio</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>ram</extension>
              <mime-type>audio/x-realaudio</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>rm</extension>
              <mime-type>audio/x-realaudio</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>rtf</extension>
              <mime-type>application/rtf</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>rv</extension>
              <mime-type>video/x-realvideo</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>sgml</extension>
              <mime-type>text/sgml</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>svg</extension>
              <mime-type>image/svg+xml</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>tif</extension>
              <mime-type>image/tiff</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>tiff</extension>
              <mime-type>image/tiff</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>txt</extension>
              <mime-type>text/plain</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>url</extension>
              <mime-type>text/plain</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>vrml</extension>
              <mime-type>x-world/x-vrml</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>wav</extension>
              <mime-type>audio/wav</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>wpd</extension>
              <mime-type>application/wordperfect5.1</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>xls</extension>
              <mime-type>application/vnd.ms-excel</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>xml</extension>
              <mime-type>text/xml</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>xsd</extension>
              <mime-type>text/xml</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>xsl</extension>
              <mime-type>text/xml</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>zip</extension>
              <mime-type>application/x-zip-compressed</mime-type>
            </mime-mapping>
            <mime-mapping xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
              <extension>htc</extension>
              <mime-type>text/x-component</mime-type>
            </mime-mapping>
            <mime-mapping xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
              <extension>xbl</extension>
              <mime-type>text/xml</mime-type>
            </mime-mapping>
          </mime-mappings>
          <lang-mappings>
            <lang-mapping>
              <extension>en</extension>
              <lang>english</lang>
            </lang-mapping>
          </lang-mappings>
          <charset-mappings/>
          <encoding-mappings>
            <encoding-mapping>
              <extension>gzip</extension>
              <encoding>zip file</encoding>
            </encoding-mapping>
            <encoding-mapping>
              <extension>tar</extension>
              <encoding>tar file</encoding>
            </encoding-mapping>
          </encoding-mappings>
        </extension-mappings>
        <session-pool-size>50</session-pool-size>
        <session-timeout>6000</session-timeout>
      </common>
      <ftpconfig>
        <ftp-port>0</ftp-port>
        <ftp-listener>local_listener</ftp-listener>
        <ftp-protocol>tcp</ftp-protocol>
        <logfile-path>/sys/log/ftplog.xml</logfile-path>
        <log-level>0</log-level>
        <session-timeout>6000</session-timeout>
        <buffer-size>8192</buffer-size>
      </ftpconfig>
      <httpconfig>
        <http-port>8080</http-port>
        <http-listener>local_listener</http-listener>
        <http-protocol>tcp</http-protocol>
        <max-http-headers>64</max-http-headers>
        <max-header-size>16384</max-header-size>
        <max-request-body>2000000000</max-request-body>
        <session-timeout>6000</session-timeout>
        <server-name>XDB HTTP Server</server-name>
        <logfile-path>/sys/log/httplog.xml</logfile-path>
        <log-level>0</log-level>
        <servlet-realm>XDB</servlet-realm>
        <webappconfig>
          <welcome-file-list>
            <welcome-file>index.html</welcome-file>
            <welcome-file>index.htm</welcome-file>
          </welcome-file-list>
          <error-pages/>
          <servletconfig>
            <servlet-mappings>
              <servlet-mapping>
                <servlet-pattern>/Test</servlet-pattern>
                <servlet-name>TestServlet</servlet-name>
              </servlet-mapping>
              <servlet-mapping>
                <servlet-pattern>/oradb/*</servlet-pattern>
                <servlet-name>DBURIServlet</servlet-name>
              </servlet-mapping>
              <servlet-mapping>
                <servlet-pattern>/orarep/*</servlet-pattern>
                <servlet-name>ReportFmwkServlet</servlet-name>
              </servlet-mapping>
              <servlet-mapping xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"
>
                <servlet-pattern>/i/*</servlet-pattern>
                <servlet-name>PublishedContentServlet</servlet-name>
              </servlet-mapping>
              <servlet-mapping xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"
>
                <servlet-pattern>/apex/*</servlet-pattern>
                <servlet-name>APEX</servlet-name>
              </servlet-mapping>
              <servlet-mapping xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"
>
                <servlet-pattern>/studentdb/*</servlet-pattern>
                <servlet-name>STUDENT_DAD</servlet-name>
              </servlet-mapping>
            </servlet-mappings>
            <servlet-list>
              <servlet>
                <servlet-name>TestServlet</servlet-name>
                <servlet-language>Java</servlet-language>
                <display-name>XDB Test Servlet</display-name>
                <description>A servlet to test the internals of the XDB Servlet
API</description>
                <servlet-class>xdbtserv</servlet-class>
                <servlet-schema>xdb</servlet-schema>
              </servlet>
              <servlet>
                <servlet-name>DBURIServlet</servlet-name>
                <servlet-language>C</servlet-language>
                <display-name>DBURI</display-name>
                <description>Servlet for accessing DBURIs</description>
                <security-role-ref>
                  <role-name>authenticatedUser</role-name>
                  <role-link>authenticatedUser</role-link>
                </security-role-ref>
              </servlet>
              <servlet>
                <servlet-name>ReportFmwkServlet</servlet-name>
                <servlet-language>C</servlet-language>
                <display-name>REPT</display-name>
                <description>Servlet for accessing reports</description>
                <security-role-ref>
                  <role-name>authenticatedUser</role-name>
                  <role-link>authenticatedUser</role-link>
                </security-role-ref>
              </servlet>
              <servlet xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
                <servlet-name>PublishedContentServlet</servlet-name>
                <servlet-language>C</servlet-language>
                <display-name>Unauthenticated File Access Servlet</display-name>
 
                <description>Servlet for files for unauthenticated users</descri
ption>
                <init-param>
                  <param-name>RootFolder</param-name>
                  <param-value>/images</param-value>
                  <description>RootFolder</description>
                </init-param>
                <security-role-ref>
                  <role-name>anonymousServletRole</role-name>
                  <role-link>anonymousServletRole</role-link>
                </security-role-ref>
              </servlet>
              <servlet xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
                <servlet-name>APEX</servlet-name>
                <servlet-language>PL/SQL</servlet-language>
                <display-name>APEX</display-name>
                <plsql xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
                  <database-username xmlns="http://xmlns.oracle.com/xdb/xdbconfi
g.xsd">ANONYMOUS</database-username>
                  <default-page xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd
">apex</default-page>
                  <document-table-name xmlns="http://xmlns.oracle.com/xdb/xdbcon
fig.xsd">wwv_flow_file_objects$</document-table-name>
                  <path-alias xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
r</path-alias>
                  <path-alias-procedure xmlns="http://xmlns.oracle.com/xdb/xdbco
nfig.xsd">wwv_flow.resolve_friendly_url</path-alias-procedure>
                  <nls-language xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd
">american_america.al32utf8</nls-language>
                  <request-validation-function xmlns="http://xmlns.oracle.com/xd
b/xdbconfig.xsd">wwv_flow_epg_include_modules.authorize</request-validation-func
tion>
                </plsql>
                <security-role-ref xmlns="http://xmlns.oracle.com/xdb/xdbconfig.
xsd">
                  <role-name>anonymousServletRole</role-name>
                  <role-link>anonymousServletRole</role-link>
                </security-role-ref>
              </servlet>
              <servlet xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">
                <servlet-name>STUDENT_DAD</servlet-name>
                <servlet-language>PL/SQL</servlet-language>
                <display-name>STUDENT_DAD</display-name>
                <security-role-ref xmlns="http://xmlns.oracle.com/xdb/xdbconfig.
xsd">
                  <role-name>authenticatedUser</role-name>
                  <role-link>authenticatedUser</role-link>
                </security-role-ref>
              </servlet>
            </servlet-list>
          </servletconfig>
        </webappconfig>
        <authentication>
          <allow-mechanism>basic</allow-mechanism>
          <digest-auth>
            <nonce-timeout>300</nonce-timeout>
          </digest-auth>
        </authentication>
        <http-host xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">localhost</
http-host>
      </httpconfig>
    </protocolconfig>
    <xdbcore-xobmem-bound>1024</xdbcore-xobmem-bound>
    <xdbcore-loadableunit-size>16</xdbcore-loadableunit-size>
    <acl-evaluation-method>ace-order</acl-evaluation-method>
  </sysconfig>
</xdbconfig>

Instruction Details →

You set up an unsecured DAD by following the instructional steps.

  1. As the system user, you can verify your starting point by calling the follow script file (the “?” lets you refer to the Oracle home directory. 

    SQL> @?/rdbms/admin/epgstat.sql

    You should see the following output:

    +--------------------------------------+
| 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-funct wwv_flow_epg_include_modules.authorize
             ion
 
             path-alias-procedure     wwv_flow.resolve_friendly_url
             nls-language             american_america.al32utf8
             path-alias               r
 
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       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?
----------------------------------
false
 
1 row selected.

  2. As the system user, you need to unlock Oracle’s anonymous user account. By default, the anonymous user account is always closed. The system user can unlock the account and suspend any password for the anonymous user with the two statements: 

    ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER anonymous IDENTIFIED BY NULL;

  3. As the system user, you can now configure the anonymous repository. You should note that it is more complex than a secured repository. The XML instructions configure the XDB server. A debugging comment is provided on line 21 that advises when you insert a new configuration instruction. 

    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;
/

    It should print the following when successful:

    Anonymous element inserted.

  4. As the system user, you can verify your changes by calling the same epgstat.sql script file again. 

    @?/rdbms/admin/epgstat.sql

    While you get more output, the changes you want to verify are the last two elements. They should look like the following when the anonymous user account is open and anonymous user access allowed.

    +--------------------------------------------------------+
| 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.

You have successfully configured the anonymous user access. Next, you need to create, authorize, and configure an unsecured DAD.

  1. [4 points] Configure the XDB Server to run a helloworld2 procedure in the anonymous database..

As the system user, you will create, authorize, and configure an unsecured DAD before testing the helloworld2 procedure. You should note that:

  • You create the helloworld2 procedure in the student database
  • You grant execute on the student user’s helloworld2 procedure to the anonymous user account
  • You create a helloworld synonym to hide the actual helloworld2 procedure name and enable calls from the anonymous user to access the helloworld2 procedure in the student schema

Instruction Details →

There are several more steps required when setting up an unsecured DAD. You do not want to create any code in the anonymous user’s account because it could be viewed without any security.

As a rule, you create the tables and stored programs in another directory and only grant execute privileges on the stored programs to the anonymous user. You would also include your own level of database security by prompting for individual user credentials of your end-users. The end-user’s credentials only should have access to call procedures via synonyms in the anonymous user’s account.

  1. As the system user, you call the dbms_epg.create_dad procedure to set the Data Access Descriptor (DAD) name and map it to a URL path. 

    1
2
3
4
5
6
7

    BEGIN
  /* Create the student_dad. */
  dbms_epg.create_dad(
      dad_name => 'GENERIC_DAD'
    , path => '/db/*');
END;
/
  2. As the system user, you call the dbms_epg.authorize_dad procedure to set the DAD name and map it to a URL path. 

    1
2
3
4
5
6
7

    BEGIN
  /* Authorize the student_dad for the student user. */
  dbms_epg.authorize_dad(
      dad_name => 'GENERIC_DAD'
    , USER => 'ANONYMOUS');
END;
/
  3. As the system user, you call the dbms_epg.set_dad_attribute procedure to hide providing the anonymous user name as an attribute of the DAD’s URL. 

    1
2
3
4
5
6
7

    BEGIN
  dbms_epg.set_dad_attribute(
      dad_name => 'GENERIC_DAD'
    , attr_name => 'database-username'
    , attr_value => 'ANONYMOUS');
END;
/
  4. As the system user, you need to verify that the anonymousServletRole is not present in your XDB configuration because it prevents anonymous connectivity. You can query the XDB configuration to determine whether it is present with the following query: 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

    SELECT   extractValue(VALUE(dad)
                     ,'/servlet/servlet-name'
                     ,'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"') dad_name
,        VALUE(param).getRootElement() param_name
,        extractValue(VALUE(param), '/*') param_value
FROM     xdb.xdb$config cfg
,        TABLE(XMLSequence(EXTRACT(cfg.object_value
                                  , '/xdbconfig/sysconfig/protocolconfig/httpconfig'
                                  ||'/webappconfig/servletconfig/servlet-list'
                                  ||'/servlet[servlet-language="PL/SQL"]'
                                  , 'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'))) dad
,        TABLE(XMLSequence(EXTRACT(VALUE(dad)
              ,'/servlet/security-role-ref/*'
              ,'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'))) param
ORDER BY dad_name;
  5. As the system user, you can remove the limitation with the following: 

    1
2
3
4
5
6
7
8
9
10

    DECLARE
  cfg XMLType := dbms_xdb.cfg_get();
BEGIN
  cfg := cfg.deleteXML('/xdbconfig/sysconfig/protocolconfig/httpconfig'
      ||               '/webappconfig/servletconfig/servlet-list'
      ||               '/servlet[servlet-name="DMSWU"]'
      ||               '/security-role-ref');
  dbms_xdb.cfg_update(cfg);
END;
/
  6. As the system user, you create a near duplicate of the helloworld procedure as helloworld2 in the student database. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    CREATE OR REPLACE PROCEDURE student.helloworld2 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 Anonymous 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 HelloWorld2;
/
  7. As the system user, you grant execute privileges on the helloworld2 procedure to the anonymous user’s account. Then, you create a helloworld synonym that points to the helloworld2 procedure. 

    1
2

    GRANT EXECUTE ON student.helloworld2 TO anonymous;
CREATE SYNONYM anonymous.helloworld FOR student.helloworld2;
  8. You use the following URL to access the helloworld procedure. 

    http://localhost:8080/db/helloworld

    It should render the page generated by the helloworld2 procedure without prompting you for credentials, like:

    Line 7 changes the form title from “Hello World!” to “Hello Anonymous World!” and Line 11’s call to USER returns anonymous rather than the student user, which is now hidden.

Written by michaelmclaughlin

August 13th, 2018 at 12:17 pm

Posted in