18 Managing an Oracle HTML DB Hosted Service

Understanding Oracle Default Schema Restrictions

When Oracle HTML DB installs, the Oracle HTML DB administrator does not have the ability to assign Oracle default schemas to workspaces. Default schemas (such as SYS, SYSTEM, and RMAN) are reserved by Oracle for various product features and for internal use. Access to a default schema can be a very powerful privilege. For example, a workspace with access to the default schema SYSTEM can run applications that parse as the SYSTEM user.

In order for an Oracle HTML DB administrator to have the ability to assign Oracle default schemas, the database administrator (DBA) must explicitly grant the privilege.

The DBA can remove this restriction and grant the privilege using SQL*Plus to run the HTMLDB_SITE_ADMIN_PRIVS.ALLOW procedure within the HTML DB engine schema. For example:

EXEC HTMLDB_SITE_ADMIN_PRIVS.ALLOW;

The DBA can reimpose this restriction and revoke this privilege using the HTMLDB_SITE_ADMIN_PRIVS.RESTRICT procedure within the HTML DB engine schema. For example:

EXEC HTMLDB_SITE_ADMIN_PRIVS.RESTRICT;

The DBA can determine the current status of the privilege using SQL*Plus to run the HTMLDB_SITE_ADMIN_PRIVS.REPORT procedure within the HTML DB engine schema. For example:

SET SERVEROUTPUT ON 
EXEC HTMLDB_SITE_ADMIN_PRIVS.REPORT;

EXEC HTMLDB_SITE_ADMIN_PRIVS.REPORT returns number of rows in the wwv_flow_restrict_admin table. 0 rows means that the Oracle HTML DB administrator is restricted and may not assign Oracle default schemas to workspaces. One or more rows means that the Oracle HTML DB administrator may assign Oracle default schemas to workspaces.

About Workspace Provisioning

When an Oracle HTML DB administrator creates a new workspace with a new tablespace, the datafile for the new tablespace is managed by Oracle-managed files.

Oracle-managed files simplifies the administration of the Oracle database and eliminates the need for the database administrator (DBA) to directly manage the operating system files that comprise the database. Using Oracle-managed files the DBA specifies operations in terms of database objects rather than filenames. The datafile for the any new tablespaces will be named according to the Oracle-managed files conventions and the placement of these files will be determined by the database initialization parameter DB_CREATE_FILE_DEST.

If the Oracle-Managed Files is not enabled, the datafile will be created in the same directory as the first datafile of the tablespace in which Oracle HTML DB was installed.

Specifying a Provisioning Mode

As an Oracle HTML DB administrator, you determine how the process of creating (or provisioning) a workspace works for your Oracle HTML DB development environment.

In manual provision mode, an Oracle HTML DB administrator creates new workspaces and notifies the Workspace administrator of the login information. In request provision mode, users request workspaces directly in a self-service fashion. In this scenario, users use a link on the login page to access a request form. Once the workspace request has been granted, they are e-mailed the appropriate login information.

To specify a provisioning mode:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Toggle Provisioning Status.

3. Select one of the following:

   • Manual

   • Request

4. Click Apply Changes.

Creating a Workspace Without a Request

Administrators can create a workspace manually by running the Provision Workspace Wizard. You can access this wizard from either the Oracle HTML DB Administration Services home page or the Workspace Administration tab.

To create a workspace manually:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage Workspaces, select Create New Workspace.

   The Provision Workspace Wizard appears.

3. Specify a workspace name and description and click Next.Select a schema, or enter the name for a new schema, followed by a password, and initial disk space quota and click Next.

4. Specify a Workspace administrator by providing a username, password, and e-mail address and click Next.

5. Confirm your selections and click Provision.

Viewing Workspace Reports

Oracle HTML DB administrators can view detailed information about a specific workspace by viewing the Workspace Utilization Report.

To view a workspace report:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage Workspaces, select Report Workspace Attributes.

3. Select a workspace from the Workspace list and click Go.

   The Workspace Utilization Report appears. Table 18-1 describes the various sections of the Workspace Utilization Report.

Viewing a Pending Service or Change Request

You can view existing service requests and change requests from the Notifications list on the Administration home page, or from the Service Requests or Change Requests pages.

Topics in this section include:

• Viewing a Pending Request from the Notifications List

• Viewing a Request from the Workspace Utilization Report

• Viewing Requests from the Service Requests Page

• Viewing Requests from the Change Requests Page

Approving a Service or Change Request

To approve a pending service request:

1. Navigate to the appropriate request page as described in "Viewing a Pending Service or Change Request".

2. Click Adjust.

   The Adjust Request page appears.

3. From Project Status, select one of the following:

   • To approve the request, click Approved.

   • To decline the request, click Declined.

4. Follow the on-screen instructions.

To approve a pending change request:

1. Navigate to the Service Change Request page as described in "Viewing a Request from the Workspace Utilization Report".

2. Click View Request.

   The Process Change Request page appears.

3. Review the displayed reports.

4. Select one of the following:

   • To approve a request for a schema, click Create Schema.

   • To approve a request for additional disk space, click Provision Space.

   • To approve a request to terminate the service, click Terminate Service

   • To deny a request, click Deny Request.

5. Follow the on-screen instructions.

Deleting an Existing Request

To delete an existing service or change request:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Navigate to the appropriate request page. (See "Viewing a Pending Service or Change Request".)

3. From Status, select the type of request you wish to delete.

4. Click the Edit icon associated with the request you wish to delete.

5. When the request appears, click Terminate or Delete if the request is still active, or click Delete if the request has already been terminated.

Identifying Inactive Workspaces

The first step in determining if a workspace is inactive is to establish some basic rules. A common approach is to base the rules on the Oracle HTML DB activity records found in the current HTML DB engine schema.

The following DDL (data definition language) creates a table of all workspaces requested before June 28, 2004 but that have been inactive since June 10, 2004. In this example, inactivity is determined by checking a key within the HTML DB engine schema for the most recent updates by each workspace.

CREATE TABLE ws_to_purge AS
 SELECT c.security_group_id, c.company_name, c.admin_email, c.request_date,
 SYSDATE last_updated_on, 'Y' ok_to_delete
   FROM wwv_flow_provision_company c
  WHERE
c.request_date <= to_date('20040628','YYYYMMDD') AND
     (  not exists
 (SELECT NULL /* Activity Log */
        FROM wwv_flow_activity_log l
       WHERE l.security_group_id = c.security_group_id
         AND l.time_stamp > to_date('20040610','YYYYMMDD'))
 )
    AND NOT EXISTS
     (SELECT NULL /* workspace applications */
        FROM wwv_flows f
       WHERE f.security_group_id = c.security_group_id
         AND f.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Pages */
        FROM wwv_flow_steps s
       WHERE s.security_group_id = c.security_group_id
         AND s.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Regions */
        FROM wwv_flow_page_plugs p
       WHERE p.security_group_id = c.security_group_id
         AND p.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Items */
        FROM wwv_flow_step_items i
       WHERE i.security_group_id = c.security_group_id
         AND i.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Templates */
        FROM wwv_flow_templates t
       WHERE t.security_group_id = c.security_group_id
         AND t.last_updated_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* Files uploaded */
        FROM wwv_flow_file_objects$ o
       WHERE o.security_group_id = c.security_group_id
         AND o.created_on > to_date('20040610','YYYYMMDD'))
    AND NOT EXISTS
     (SELECT NULL /* SQL Workshop history */
        FROM wwv_flow_sw_sql_cmds s
       WHERE s.security_group_id = c.security_group_id
         AND s.created_on > to_date('20040610','YYYYMMDD'));

Once you identify inactive workspaces, you can purge them. Purging inactive workspaces is a two step process:

• First, remove the resources (that is, the database schemas, tablespaces, and data files) associated with each inactive workspace

• Second, drop the inactive workspaces from Oracle HTML DB

Removing the Resources Associated with Inactive Workspaces

Once you have identified inactive workspaces in a single table, the next step is to remove them.

To remove the resources associated with inactive workspaces:

1. Identify the schemas used by the workspaces to be deleted by joining the table containing the identified inactive workspaces to wwv_flow_company_schemas.

2. Drop the schemas, tablespaces, and data files used exclusively by the inactive workspaces from the database by running a query similar to the following.

   SELECT s.schema
     FROM ws_to_purge ws,
          wwv_flow_company_schemas s
   WHERE s.security_group_id = ws.security_group_id
      AND ws.ok_to_delete = 'Y';
   
   

Deleting Inactive Workspaces

Once you remove the resources associated with an inactive workspace, you can delete it. You can delete inactive workspaces manually using the Oracle HTML DB Administration Services application. Or, you can delete them programmatically as shown in the following example.

BEGIN 
     FOR c1 IN (SELECT security_group_id  
                 FROM ws_to_purge
                 WHERE ok_to_delete = 'Y')
     LOOP
         WWV_FLOW_PROVISIONING.TERMINATE_SERVICE_BY_SGID(c1.security_group_id);
     END LOOP;
 END;

Deleting SQL Workshop Logs

The SQL Workshop logs maintain a history of recent commands and scripts run in the SQL Command Processor.

To delete log files entries:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Logs.

   The Manage Logs page appears.

3. Select Review SQL Workshop logs.

4. Select one of the following:

   • Script File executions log entries

   • Control File execution log entries

   • SQL Command Processor history log entries

   • SQL Archives entries

5. To delete entries by age:

   • Specify the age of the entries to deleted.

   • Click Delete Entries.

6. To delete all entries, click Truncate Log.

Deleting Page View Activity Log Entries

Page view activity logs track user activity for an application. Developers enable logging within their application on the Edit Application Attributes page.

The HTML DB engine actually uses two logs to track user activity. At any given time, one log is designated as current. For each rendered page view, the HTML DB engine inserts one row into the log file. A log switch occurs at the interval listed on the Manage Activity Logs page. At that point, the HTML DB engine removes all entries in the noncurrent log and designates it as current.

To truncate the activity logs manually:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Logs.

   The Manage Logs page appears.

3. Select Review page view activity log, with option to truncate.

4. Click Truncate Logs.

5. Click either Truncate Log 1 or Truncate Log 2.

Deleting Developer Activity Log Entries

The Developer Activity Log tracks changes to applications within an individual workspace. Log entries older than one month are automatically deleted.

To delete Developer Activity Log entries:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Logs.

   The Manage Logs page appears.

3. Select Developer activity logs, review with option to delete entries.

4. On the Developer Activity Logs page, click Manage.

5. Specify the age of the entries to be deleted and click Delete Entries.

Deleting Click Counting Log Entries

The External Clicks Log counts clicks from an Oracle HTML DB application to an external site. You can implement this functionality using COUNT_CLICK procedure.

To delete click counting log entries:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Logs.

   The Manage Logs page appears.

3. Select External click counting log, review with option to truncate.

4. On the Click Counting Log page, click Manage.

5. Specify the age of the entries to be deleted and click Delete Entries.

Deleting the HTML DB Mail Log Entries

The HTML DB Mail Log records message header information and send date of successfully sent mail message.

To truncate the mail log:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Logs.

   The Manage Logs page appears.

3. Select Manage mail log.

4. On the Manage Mail Log page, click Truncate Log.

Purging Sessions by Age

Using the Purge Session page, administrators can purge sessions by age.

To view specific session details:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Select the Manage Session State.

3. Select Purge old sessions by age.

4. On the Purge Session page, specify:

   • The maximum number of sessions to be purged

   • The age of session to be purged

5. To view a report of session statistics, click Count Sessions.

6. To purge the selected sessions, click Purge Sessions.

Viewing Session Details Before Purging

Before purging sessions, administrators can use the Recent Sessions page to first view a listing of recent sessions and then drill down on session details.

To purge sessions by age:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Select the Manage Session State.

3. Select Report recent sessions with drill down to session details.

4. On the Recent Sessions page, you can:

   • Click a session ID to view additional details.

   • Click Purge Session to delete the displayed sessions.

Viewing Session Statistics Before Purging

On the Session State Statistics page, administrators can view statistics about current sessions prior to purging.

To view session state statistics:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Select the Manage Session State.

3. Select Report session counts.

4. Click Purge Sessions to delete the current sessions.

Accessing the HTML DB Environment Preferences Page

To access Oracle HTML DB environment preferences:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Environment Preferences.

   The HTML DB Environment Preferences page appears.

Configuring Oracle HTML DB to Send Mail

If you enable request provision mode or enable users to reset their passwords using a link on the login page, you must configure Oracle HTML DB to send mail. In order to enable Oracle HTML DB to send mail, you must configure a number of settings on the Environment Preferences page.

To configure Oracle HTML DB to send mail:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Environment Preferences.

   The HTML DB Environment Preferences page appears. Configure the following Run settings:

   • DEVELOPMENT_SERVICE_URL - If you are running in request provisioning mode, the value of this setting is used in the e-mail when the request is approved. This setting defines the URL for the service. If this setting is not present, the URL will be derived from your environment.

   • SMTP_HOST_ADDRESS - Defines the server address of the SMTP server. On installation, this will be set to localhost. If you are using another server for SMTP relaying, change localhost to that server's address.

   • SMTP_HOST_PORT - Defines the port the SMTP server listens to for mail requests. By default, this setting will be set to 25 at the time of installation.

   • SMTP_FROM - Defines the "from" address when an administrative tasks such as approving a provision request, or resetting a password generates an e-mail.

Restricting User Access by IP Address

Oracle HTML DB administrators can restrict user access to an Oracle HTML DB instance by creating a Runtime setting named RESTRICT_IP_RANGE.

To restrict user access by IP address:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Environment Preferences.

   The HTML DB Environment Preferences page appears.

3. Click Create.

4. On the Runtime Settings page:

   1. In Name, enter RESTRICT_IP_RANGE.

   2. In Value, enter an IP address. Use an asterisk (*) to specify a wildcard.

      • Enter an IP address from one to four levels. For example:

        141, 141.* ...
        192.128.23.1 ...
        
        

      • Enter a comma delimited list of IP addresses.

   
   

   Note: When using wildcards, do not include additional numeric values after wildcard characters. For example, 138.*.41.2.

   
   

Disabling Access to Oracle HTML DB Administration Services

Oracle HTML DB administrators can restrict user access to Oracle HTML DB Administration Services by creating a Runtime setting named DISABLE_ADMIN_LOGIN. Creating this setting in production environments prevents unauthorized users from logging in to Oracle HTML DB Administration Services and possibly compromising user login credentials.

To disable user access to Oracle HTML DB Administration Services:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Environment Preferences.

   The HTML DB Environment Preferences page appears.

3. Click Create.

4. On the Runtime Settings page:

   1. In Name, enter DISABLE_ADMIN_LOGIN

   2. In Value, enter YES in upper case letters

Setting this value and logging out, prevents anyone for logging in to Oracle HTML DB Administration Services.

To reverse this setting, connect in SQL*Plus as the HTML DB engine schema and execute the following:

UPDATE WWV_FLOW_PLATFORM_PREFS set value = 'NO' WHERE name = 'DISABLE_ADMIN_LOGIN'
   /
commit
   /

Disabling Access to Oracle HTML DB Internal Applications

Oracle HTML DB administrators can restrict user access to Oracle HTML DB Internal applications by creating a Runtime setting named DISABLE_WORKSPACE_LOGIN. Creating this setting in production environments prevents unauthorized users from running applications in the Internal workspace (that is, Application Builder, SQL Workshop, and Data Workshop) and possibly compromising login credentials. Administrators who use this feature should also consider disabling user access to Oracle HTML DB Administration Services.

To disable user access to the Internal workspace:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Environment Preferences.

   The HTML DB Environment Preferences page appears.

3. Click Create.

4. On the Runtime Settings page:

   1. In Name, enter DISABLE_WORKSPACE_LOGIN

   2. In Value, enter YES in upper case letters

Setting this value and logging out, prevents anyone for logging in to the Internal workspace.

To reverse this restriction, log in to Oracle HTML DB Administration Services and remove the DISABLE_WORKSPACE_LOGIN Runtime, or change the Value to NO.

Viewing the Mail Queue

Oracle HTML DB administrators can use the Manage Mail Queue page to monitor e-mail messages in the mail queue.

To monitor messages in the mail queue:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Mail Queue.

   The Mail Queue page appears.

3. To send e-mail messages, select the messages to be send and click Send All Mail.

4. To delete e-mail messages, select the messages to be deleted and click Delete.

Viewing the HTML DB Mail Log

The HTML DB Mail Log records message header information and send date of successfully sent mail message.

To view the mail log:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Logs.

   The Manage Logs page appears.

3. Select Manage mail log.

Adding a New Task

To add new task to a Site-Specific Tasks lists:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Site-Specific Task Lists.

   The Site-Specific Tasks page appears.

3. To create a new link, click Create.

4. On the Site-Specific Tasks page you can specify the following:

   1. In Display Sequence, indicate the relative order of this task within the list.

   2. In Display Location, indicate the page on which the task should display (that is, the Workspace Login page or Workspace Home page).

   3. In Task Name, enter a name for this task.

   4. In Tasks Link, enter the link target for this task using f?p syntax.

   
   

   See Also: "Using f?p Syntax to Link Pages"

   
   

Editing an Existing Task

To edit an existing task:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Site-Specific Task Lists.

   The Site-Specific Tasks page appears.

3. Click the Edit icon adjacent to the appropriate link.

4. On the Site-Specific Tasks page, edit the appropriate attributes.

5. Click Apply Changes.

Deleting a Task

To delete an existing task:

1. Log in to Oracle HTML DB Administration Services. (See "Logging in to Oracle HTML DB Administration Services".)

2. Under Manage HTML DB Service, select Manage Site-Specific Task Lists.

   The Site-Specific Tasks page appears.

3. Click the Edit icon adjacent to the appropriate link.

4. Click Delete.