Managing Schemas

This section describes how to manage the schemas within an Oracle HTML DB instance.

Topics:

Determining the HTML DB Engine Schema
Understanding Oracle Default Schema Restrictions
Managing Workspace to Schema Assignments

Determining the HTML DB Engine Schema

Oracle HTML DB administrators may need to perform certain actions within the HTML DB engine schema. For example, 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 by running the HTMLDB_SITE_ADMIN.UNRESTRICT_SCHEMA procedure within the HTML DB engine schema.

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 to workspaces, the database administrator (DBA) must explicitly grant the privilege using SQL*Plus to run a procedure within the HTMLDB_SITE_ADMIN_PRIVS package.

Note:

All schema and workspace names used as arguments to procedures in the HTMLDB_SITE_ADMIN_PRIVS package are used exactly as they are provided by the caller.

For example, if you pass an argument value such as p_schema =>'system', the lower-case schema name 'system' will be recorded and referenced. This example could return unexpected results if you really meant to reference the common schema name SYSTEM, which would be referenced using upper case.

Topics:

Granting the Privilege to Assign Oracle Default Schemas
Revoking the Privilege to Assign Oracle Default Schemas
Working with Restricted Schemas

Granting the Privilege to Assign Oracle Default Schemas

The DBA can grant an Oracle HTML DB administrator the ability to assign Oracle default schemas to workspaces by using SQL*Plus to run the HTMLDB_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA procedure from within the HTML DB engine schema. For example:

EXEC HTMLDB_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA(p_schema => 'SYSTEM');
COMMIT:

This example would enable the Oracle HTML DB administrator to assign the SYSTEM schema to any workspace.

Revoking the Privilege to Assign Oracle Default Schemas

The DBA can revoke this privilege using SQL*Plus to run the HTMLDB_SITE_ADMIN_PRIVS.RESTRICT_SCHEMA procedure from within the HTML DB engine schema. For example:

EXEC HTMLDB_SITE_ADMIN_PRIVS.RESTRICT_SCHEMA(p_schema => 'SYSTEM');
COMMIT;

This example would prevent the Oracle HTML DB administrator from assigning the SYSTEM schema to any workspace. It does not, however, prevent workspaces that have already had the SYSTEM schema assigned to them from using the SYSTEM schema.

Working with Restricted Schemas

If a schema has been designated as restricted using the RESTRICT_SCHEMA procedure, the DBA can designate specific workspaces as exceptions by running the HTMLDB_SITE_ADMIN_PRIVS.CREATE_EXCEPTION procedure. For example:

EXEC HTMLDB_SITE_ADMIN_PRIVS.CREATE_EXCEPTION(p_schema => 'SYSTEM', p_schema => 'DBA_WORKSPACE');
EXEC HTMLDB_SITE_ADMIN_PRIVS.CREATE_EXCEPTION(p_schema => 'SYSTEM', p_schema => 'AUDITOR_WORKSPACE');
COMMIT:

This example would prevent the Oracle HTML DB administrator from assigning the SYSTEM schema to the workspace named AUDITOR_WORKSPACE. However this restriction only applies to workspace provisioning requests processed after the REMOVE_EXCEPTION procedure has been run. If the AUDITOR_WORKSPACE already had the SYSTEM schema assigned to it, this method would not prevent that workspace from continuing to use the schema.

Removing Workspace Exceptions for a Schema

The DBA can remove all workspace exceptions for a schema by using SQL*Plus to run the HTMLDB_SITE_ADMIN_PRIVS.REMOVE_WORKSPACE_EXCEPTIONS procedure from within the HTML DB engine schema. For example:

EXEC HTMLDB_SITE_ADMIN_PRIVS.REMOVE_WORKSPACE_EXCEPTIONS(p_schema => 'SYSTEM');
COMMIT:

This example would prevent the Oracle HTML DB administrator from assigning the SYSTEM schema to any workspaces if the SYSTEM schema were already restricted, but had one or more exceptions previously created for it.

Removing Schema Exceptions for a Workspace

The DBA can remove all schema exceptions for a workspace by using SQL*Plus to run the REMOVE_SCHEMA_EXCEPTIONS procedure from within the HTML DB engine schema. For example:

EXEC REMOVE_WORKSPACE_EXCEPTIONS(p_workspace => 'AUDITOR_WORKSPACE');
COMMIT:

This example would prevent the Oracle HTML DB administrator from assigning any restricted schemas to the workspace named AUDITOR_WORKSPACE if that workspace had exceptions previously created for it with respect to any restricted schemas.

Determining the Privilege Status

The DBA can determine the current status of the privilege by using SQL*Plus to run the HTMLDB_SITE_ADMIN_PRIVS.REPORT procedure. For example:

SET SERVEROUTPUT ON
EXEC HTMLDB_SITE_ADMIN_PRIVS.REPORT;

This example would display the text of a query that dumps the tables that defines the schema and workspace restrictions.

SELECT a.schema "SCHEMA",b.workspace_name "WORKSPACE" FROM WWV_FLOW_RESTRICTED_SCHEMAS a, WWV_FLOW_RSCHEMA_EXCEPTIONS b WHERE b.schema_id (+)= a.id;

When reviewing the output of this query, remember the following:

A schema name in the SCHEMA column indicates that the schema is restricted.
Schemas that are not listed are not restricted and may be assigned to any workspace.
A workspace name next to a schema name means that an exception exists for the schema for the named workspace.

You can run this query in SQL*Plus as shown above, or you can change it and format the output.

Managing Workspace to Schema Assignments

When a user logs into the Oracle HTML DB, they log in to a shared work area called a workspace. Each workspace can have multiple associated schemas. By associating a workspace with a schema, developers in that workspace can:

Build applications that interact with the database objects in that schema.
Create new database objects in that schema.

Viewing Workspace to Schema Assignments

To create a workspace manually:

Log in to Oracle HTML DB Administration Services. See "Logging in to Oracle HTML DB Administration Services".
Click Manage Workspaces.
Select Manage Workspace to Schema Assignments.

The Manage Workspace to Schema Assignments page appears.
To create a new workspace to schema assignment, click Create and follow the on-screen instructions.
To edit and existing workspace to schema assignment:
1. Select the workspace name.
  
  The Edit Schema to Workspace Assignment page appears.
2. Select a new workspace or schema.
3. Click Apply Changes.