Understanding Oracle Default Schema Restrictions

When Oracle Application Express installs, the Oracle Application Express 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 Application Express 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 APEX_SITE_ADMIN_PRIVS package.

Note:

All schema and workspace names used as arguments to procedures in the APEX_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 Application Express administrator the ability to assign Oracle default schemas to workspaces by using SQL*Plus to run the APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA procedure from within the Application Express engine schema. For example:

EXEC FLOWS_030100.APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA(p_schema => 'SYSTEM');
COMMIT;

This example would enable the Oracle Application Express 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 APEX_SITE_ADMIN_PRIVS.RESTRICT_SCHEMA procedure from within the Application Express engine schema. For example:

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

This example would prevent the Oracle Application Express 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 APEX_SITE_ADMIN_PRIVS.CREATE_EXCEPTION procedure. For example:

EXEC FLOWS_030100.APEX_SITE_ADMIN_PRIVS.CREATE_EXCEPTION(p_schema => 'SYSTEM', p_workspace=> 'DBA_WORKSPACE');
EXEC FLOWS_030100.APEX_SITE_ADMIN_PRIVS.CREATE_EXCEPTION(p_schema => 'SYSTEM', p_workspace  => 'AUDITOR_WORKSPACE');
COMMIT;

This example would prevent the Oracle Application Express 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 APEX_SITE_ADMIN_PRIVS.REMOVE_WORKSPACE_EXCEPTIONS procedure from within the Application Express engine schema. For example:

EXEC FLOWS_030100.APEX_SITE_ADMIN_PRIVS.REMOVE_WORKSPACE_EXCEPTIONS(p_schema => 'SYSTEM');
COMMIT;

This example would prevent the Oracle Application Express 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 Application Express engine schema. For example:

EXEC FLOWS_030100.APEX_SITE_ADMIN_PRIVS.REMOVE_WORKSPACE_EXCEPTIONS(p_workspace => 'AUDITOR_WORKSPACE');
COMMIT;

This example would prevent the Oracle Application Express 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 APEX_SITE_ADMIN_PRIVS.REPORT procedure. For example:

SET SERVEROUTPUT ON
EXEC FLOWS_030100.APEX_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.