2.13 Managing Schemas

Manage the schemas within an Oracle Application Express instance.

See Also:

Parent topic: Oracle Application Express Administration Services

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

See Also:

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

Parent topic: Managing Schemas

2.13.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_SITE_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'

Parent topic: Managing Schemas

2.13.3 Understanding Oracle Default Schema Restrictions

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

Parent topic: Managing Schemas

2.13.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.

Parent topic: Understanding Oracle Default Schema Restrictions

2.13.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"

Parent topic: Understanding Oracle Default Schema Restrictions

2.13.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_180200.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"

Parent topic: Understanding Oracle Default Schema Restrictions

2.13.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.

Parent topic: Understanding Oracle Default Schema Restrictions

2.13.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_050200.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.

2.13.3.4.2 Removing Schema Exceptions for a Workspace

DBAa can remove all schema exceptions for a workspace.

The DBA 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_180200.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.

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

2.13.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.

Parent topic: Understanding Oracle Default Schema Restrictions