How to Structure Your Oracle APEX Workspaces
While at Kscope16, I had the opportunity to talk to several organizations that were new to Oracle Application Express (APEX) who wanted to know how to properly set up their environment. The most common question I was asked was, “how do we organize our workspaces?” As with most answers, it depends. This article will cover some considerations when creating workspaces.
What is a Workspace?
Before discussing different options, it’s important to understand what a workspace is. The best similarity is to compare workspaces to folders on an operating system (OS). Workspaces are like folders and Oracle APEX applications are like files. To over simplify things, they are a logical location to store a set of applications in. Like files, applications can only be associated with one workspace.
Note: For technical correctness, you can get around the issue of a file associated with a single folder by using symbolic links or shortcuts. You can not do that in APEX.
Like folders, workspaces have sets of permissions granted to them. Likewise, an Oracle APEX administrator can give access to a schema(s) to a given workspace. Applications in that workspace can then run against a schema from the list of granted schema(s). For each application, this is called the Parsing Schema and it must be selected from the available schemas that the application’s workspace has access to. The image outlines the relationships between schemas, workspaces, and applications.
Because schema permissions are granted at a workspace level, most (not all) workspaces match up one-to-one with the schema name. For example, for the HR schema, there would be a workspace called HR as well. That workspace would only have access to the HR schema and therefore all applications in the workspace would run against the HR schema.
Workspaces have users assigned to them. These users can be developers and/or end users of the application. It’s recommended that workspace users should only be used for developer access to the Oracle APEX development environment.
Users can be defined in multiple ways. How they are defined may affect how you setup workspaces. Note: this list is based off of Oracle APEX 5.0.
- Application Express Accounts: Prior to Oracle APEX 5.0, this was the only way to manage users. It is the most popular way most organizations assign developers permissions to workspaces. Instance administrators need to manually (or via a script) create these accounts.A user is created and can be assigned to only one workspace. If a developer/user needs access to multiple workspaces, they must create multiple accounts. The username can be the same across workspaces.This type of developer account may affect how many workspaces you create. Some teams initially consider creating one workspace per application. If you do this, then you’ll need to create separate accounts for each user in each workspace and may not be a sustainable solution.
- Database (DB) Accounts: DB accounts are used to log into Oracle APEX.
- HTTP Header Variable: This is useful for 3rd party authentication tools which apply a special HTTP header that identifies the user. If this header is missing, it usually means the user is not authenticated and you can configure a redirection page.
- LDAP Directory: Tie into an organization’s LDAP directory. This can be very useful for some organizations as can create a group for developers.
- Oracle Application Server Single Sign-On: Used with Oracle’s Single Sign-On (SSO). apex.oracle.com uses this authentication model to manage developer access.
Workspace IDs and Interactive Reports
When creating a workspace, you can define the workspace ID. If left blank the workspace ID will be automatically generated. The workspace ID is significant as it used to define how saved Interactive Reports (IR) are maintained with each update of an APEX application. David Peake, from the Oracle APEX Team, wrote a great explanation of how users saved IRs are dependent on the workspace IDs.
In the past, the only way to ensure that users saved IRs were not deleted with each application update was to ensure that the workspace ID was the same across all environments (dev, test, UAT, prod, etc). Now, you can easily work around this by setting an offset as part of an application’s import process which is documented in the APEX_APPLICATION_INSTALL API. The documentation covers different situations and the steps involved for each one.
Since there are ways to handle different workspace IDs, preserving workspace IDs across environments is not something most people should be concerned about.
Workspaces and RESTful Services
When defining a RESTful service there is currently no way to specify the parsing schema. If you only have one schema assigned to the workspace this is not an issue. If you a workspace that has access to multiple schemas, this may be a concern. The default (initially associated parsing schema to the workspace) will be used to parse the request.
They’re several ways to get around this issue:
- Still create a one-to-one mapping of workspace to schema assignment with the only purpose of managing that schema’s RESTful services
- Grant permissions from the schema objects in question to the workspace’s default parsing schema. Then, use fully qualified references to the specific object (ex. schema.object).
For organizations new to APEX, developers usually want to “kick the tires” a bit by creating their own test applications and installing sample applications. To help facilitate this, I recommend giving each developer their own workspace and schema. This encourages them to learn more about the tool without any chance of negatively impacting your business applications.
Once you’ve determined the user/developer management scheme, planning out your workspaces shouldn’t be complicated. If you do find that you made a design decision that needs to be changed, you can change things around with minimal amount of effort.