This section describes how to manage the schemas within an Oracle Application Express instance.
Topics:
A schema is a logical container for the database objects. Instance administrators may need to perform certain actions within the Application Express engine schema. For example, in order for an Instance administrator to have the ability to assign Oracle default schemas, the database administrator (DBA) must explicitly grant the privilege by running the APEX_SITE_ADMIN.UNRESTRICT_SCHEMA
procedure within the Application Express engine.
"Understanding Oracle Default Schema Restrictions" for information about the APEX_SITE_ADMIN.UNRESTRICT_SCHEMA
procedure
To determine the current Application Express engine schema for your Oracle Application Express instance:
Use SQL*Plus to connect to the database.
Run the following query in a schema with DBA privileges (for example, SYSTEM
).
SELECT DISTINCT TABLE_OWNER FROM all_synonyms WHERE SYNONYM_NAME = 'WWV_FLOW' and OWNER = 'PUBLIC'
When Oracle Application Express installs, the Instance 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 Instance administrator to have the ability to assign most 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, however, that beginning with Oracle Application Express release 3.1, the SYS
and SYSTEM
schemas may no longer be used by workspaces as parsing schemas.
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'
is 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:
The DBA can grant an Instance administrator the ability to assign Oracle 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 APEX_040200.APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA(p_schema => 'RMAN'); COMMIT;
This example would enable the Instance administrator to assign the RMAN
schema to any workspace.
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 APEX_040200.APEX_SITE_ADMIN_PRIVS.RESTRICT_SCHEMA(p_schema => 'RMAN'); COMMIT;
This example would prevent the Instance administrator from assigning the RMAN
schema to any workspace. It does not, however, prevent workspaces that have already had the RMAN
schema assigned to them from using the RMAN
schema.
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 APEX_040200.APEX_SITE_ADMIN_PRIVS.CREATE_EXCEPTION(p_schema => 'RMAN', p_workspace=> 'DBA_WORKSPACE'); EXEC APEX_040200.APEX_SITE_ADMIN_PRIVS.CREATE_EXCEPTION(p_schema => 'RMAN', p_workspace => 'AUDITOR_WORKSPACE'); COMMIT;
This example would prevent the Instance administrator from assigning the RMAN
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 has the RMAN
schema assigned to it, this method would not prevent that workspace from continuing to use the schema.
The DBA can remove all workspace exceptions for a schema by using SQL*Plus to run the APEX_SITE_ADMIN_PRIVS.REMOVE_SCHEMA_EXCEPTIONS
procedure from within the Application Express engine schema. For example:
EXEC APEX_040200.APEX_SITE_ADMIN_PRIVS.REMOVE_SCHEMA_EXCEPTIONS(p_schema => 'RMAN'); COMMIT;
This example would prevent the Instance administrator from assigning the RMAN
schema to any workspaces if the RMAN
schema were already restricted, but had one or more exceptions previously created for it.
The DBA can remove all schema exceptions for a workspace 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 APEX_040200.APEX_SITE_ADMIN_PRIVS.REMOVE_WORKSPACE_EXCEPTIONS(p_workspace => 'AUDITOR_WORKSPACE'); COMMIT;
This example would prevent the Instance 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.
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 APEX_040200.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.