3.12 Managing Schemas

Manage the schemas within an Oracle Application Express instance.

• What Is the Oracle Application Express Engine Schema?
  A schema is a logical container for the database objects.
• Determining the Oracle Application Express Engine Schema
  Determine the current Application Express engine schema for an instance.
• Understanding Oracle Default Schema Restrictions
  A database administrator (DBA) grants and revokes privileges within an Oracle Application Express instance.

See Also:

• "Viewing the Parsing Schemas Report"

• "Managing Workspace to Schema Assignments"

3.12.1 What Is the Oracle Application Express Engine Schema?

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_INSTANCE_ADMIN.UNRESTRICT_SCHEMA procedure within the Application Express engine.

See Also:

"Understanding Oracle Default Schema Restrictions" for information about the APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA procedure

3.12.2 Determining the Oracle Application Express Engine Schema

Determine the current Application Express engine schema for an instance.

See Also:

"Understanding Oracle Default Schema Restrictions" for information about the APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA procedure

To determine the current Application Express engine schema for your Oracle Application Express instance:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

2. 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'
   

3.12.3 Understanding Oracle Default Schema Restrictions

A database administrator (DBA) grants and revokes privileges within an Oracle Application Express instance.

• About Assigning Oracle Default Schemas to Workspaces
  In order for an Instance administrator to assign most Oracle default schemas to workspaces, a DBA must explicitly grant the privilege.
• Granting the Privilege to Assign Oracle Default Schemas
  DBAs can grant an Instance administrator the ability to assign Oracle schemas to workspaces.
• Revoking the Privilege to Assign Oracle Default Schemas
  DBAs can revoke the privilege to assign default schemas.
• Working with Restricted Schemas
  DBAs can work with restricted schemas by designating specific workspaces as exceptions, remove all workspaces exceptions for a schema, or remove all schema exceptions for a workspace.
• Determining the Privilege Status
  DBAs can determine the current status of the privileges.

3.12.3.1 About Assigning Oracle Default Schemas to Workspaces

In order for an Instance administrator to assign most Oracle default schemas to workspaces, a DBA must explicitly grant the privilege.

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 DBA must explicitly grant the privilege using SQL*Plus to run a procedure within the APEX_INSTANCE_ADMIN package.

Tip:

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 the APEX_INSTANCE_ADMIN 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.

3.12.3.2 Granting the Privilege to Assign Oracle Default Schemas

DBAs can grant an Instance administrator the ability to assign Oracle schemas to workspaces.

A DBA grants an Instance administrator the ability to assign Oracle schemas to workspaces by using SQL*Plus to run the APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA procedure from within the Application Express engine schema. For example:

EXEC APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA(p_schema => 'RMAN');
COMMIT;

This example would enable the Instance administrator to assign the RMAN schema to any workspace.

See Also:

"Determining the Oracle Application Express Engine Schema"

3.12.3.3 Revoking the Privilege to Assign Oracle Default Schemas

DBAs can revoke the privilege to assign default schemas.

A DBA revokes the privilege to assign default schemas using SQL*Plus to run the APEX_INSTANCE_ADMIN.RESTRICT_SCHEMA procedure from within the Application Express engine schema. For example:

EXEC APEX_INSTANCE_ADMIN.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.

See Also:

"Determining the Oracle Application Express Engine Schema"

3.12.3.4 Working with Restricted Schemas

DBAs can work with restricted schemas by designating specific workspaces as exceptions, remove all workspaces exceptions for a schema, or remove all schema exceptions for a workspace.

• Removing Workspace Exceptions for a Schema
  DBAs can remove all workspace exceptions for a schema.
• Removing Schema Exceptions for a Workspace
  DBAs can remove all schema exceptions for a workspace.
• Designating Specific Workspaces as Exceptions
  DBAs can designate specific workspaces as exceptions.

3.12.3.4.1 Removing Workspace Exceptions for a Schema

DBAs can remove all workspace exceptions for a schema.

A DBA can remove all workspace exceptions for a schema by using SQL*Plus to run the APEX_INSTANCE_ADMIN.REMOVE_SCHEMA_EXCEPTIONS procedure from within the Application Express engine schema. For example:

EXEC APEX_INSTANCE_ADMIN.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.

3.12.3.4.2 Removing Schema Exceptions for a Workspace

DBAs can remove all schema exceptions for a workspace.

DBAs can remove all schema exceptions for a workspace by using SQL*Plus to run the APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE_EXCEPTIONS procedure from within the Application Express engine schema. For example:

EXEC APEX_INSTANCE_ADMIN.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 for any restricted schemas.

3.12.3.4.3 Designating Specific Workspaces as Exceptions

DBAs can designate specific workspaces as exceptions.

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

EXEC APEX_INSTANCE_ADMIN.CREATE_SCHEMA_EXCEPTION(p_schema => 'RMAN', p_workspace=> 'DBA_WORKSPACE');
EXEC APEX_INSTANCE_ADMIN.CREATE_SCHEMA_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_SCHEMA_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.

3.12.3.5 Determining the Privilege Status

DBAs can determine the current status of the privileges.

A DBA can determine the current status of the privileges by using SQL*Plus to run the script report_instance_configuration.sql in the utilities directory. For example:

To run the script report_instance_configuration.sql in the utilities directory:

1. Change to the utilities directory.

   $ cd utilities
   

2. Connect in SQL*Plus and connect to the database where Oracle Application Express is installed as SYS, for example:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

3. Run the following statement:

   SQL> @report_instance_configuration.sql
   

The resulting report displays general information about the Application Express instance, including restricted schemas and schema exceptions.