Application Express: Workspaces and Schemas

Time for another chapter, or at least verse, in the Apex security model.

With Apex, the relationship between Workspace and Schema is flexible. A single schema can have multiple workspaces against it, and a single workspace may be assigned multiple schemas. I’ve only worked on a couple of small Apex jobs, and have kept a one-to-one mapping between workspace and schema. That seems simpler to me.

In this example, the GARY workspace has two schemas, GARY and GROWL.

Going down a level, when you create an application under a workspace with multiple schemas, you pick one of them as the base schema for the application. That schema will be the ‘effective database user’ in terms of privileges.

Things are a bit different in the Workshop. The workshop belongs to the workspace and doesn’t have a single schema. Instead you get the drop-down list near the top-right corner. You choose the schema (out of the allocated schemas) and it runs as that user. Except, perhaps, in Nuno’s installation where he is seeing some odd results. 

Note that in both above cases the CURRENT_SCHEMA and CURRENT_USER are the same. When I tried an ALTER SESSION SET CURRENT_SCHEMA=…, it wouldn’t ‘stick’ for the next query. I could only get it to work if I include it in the same call. I assume that the mechanism which sets the current schema when it sets the parsing schema

As an amusement, I tried fiddling with the HTML to see if I could run SQL as a schema not linked to the workspace. It didn’t work and just raised an application warning (ORA-20000, rather than a built-in warning) that stated I didn’t have the authority on that schema.

Finally, I did some playing with DBMS_TRACE.TRACE_ALL_CALLS and identified WWV_DBMS_SQL as the ‘missing link’ between Apex and DBMS_SYS_SQL. The system catalog shows that APEX_040000 has execute permission on WWV_DBMS_SQL and that package has a dependency on DBMS_SYS_SQL