Managing Schemas

Manage the schemas within an Oracle APEX instance.

What Is the Oracle APEX Engine Schema?

A schema is a logical container for the database objects.

Instance administrators may need to perform certain actions within the Oracle APEX 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 APEX engine.

See Also: Understanding Oracle Default Schema Restrictions for information about the APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA procedure

Determining the Oracle APEX Engine Schema

Determine the current APEX 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 APEX engine schema for your APEX instance:

Start SQLcl and connect to the database where APEX is installed as SYS, for example:

On Windows:

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

On UNIX and Linux:

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

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'

Understanding Oracle Default Schema Restrictions

A database administrator (DBA) grants and revokes privileges within an APEX 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.

When APEX 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 SQLcl 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.

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 SQLcl to run the APEX_INSTANCE_ADMIN.UNRESTRICT_SCHEMA procedure from within the APEX 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.

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 SQLcl to run the APEX_INSTANCE_ADMIN.RESTRICT_SCHEMA procedure from within the APEX 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.

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.

A DBA can remove all workspace exceptions for a schema by using SQLcl to run the APEX_INSTANCE_ADMIN.REMOVE_SCHEMA_EXCEPTIONS procedure from within the APEX 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.

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 SQLcl to run the APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE_EXCEPTIONS procedure from within the APEX 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.

Designating Specific Workspaces as Exceptions

DBAs can designate specific workspaces as exceptions.

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_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 allow the Instance administrator to assign the RMAN schema to the workspaces named DBA_WORKSPACE and AUDITOR_WORKSPACE.

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 SQLcl 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:

Change to the utilities directory.
```
$ cd utilities
```

Connect in SQLcl and connect to the database where APEX is installed as SYS, for example: