This section describes how to manage the schemas within an Oracle Application Express instance.
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.
See Also:
"Understanding Oracle Default Schema Restrictions" for information about theAPEX_SITE_ADMIN.UNRESTRICT_SCHEMA procedureTo 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.
Note:
All schema and workspace names used as arguments to procedures in theAPEX_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.
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_040100.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_040100.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_040100.APEX_SITE_ADMIN_PRIVS.CREATE_EXCEPTION(p_schema => 'RMAN', p_workspace=> 'DBA_WORKSPACE'); EXEC APEX_040100.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 already had 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_040100.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_040100.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_040100.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.