This blog was originally published on October 29, 2019!

A recent Oracle APEX patch was applied to Oracle Autonomous Database Serverless that enabled the following:

  1. Allow ADMIN to grant APEX_ADMINISTRATOR_ROLE role to other database users
  2. Allow calling the workspace management procedures of APEX_INSTANCE_ADMIN: add_workspace, add_schema, remove_workspace, and remove_schema

This enables the ADMIN user to call the APEX_INSTANCE_ADMIN methods to script the creation of workspaces, or delegate that task to other database users. By scripting workspace creation, you can quickly provision many workspaces in a short time. Another use case is a workspace provisioning APEX application that allows self service workspace creation without requiring access to APEX Administration Services.

Creating a Workspace Provisioning App

To create a workspace provisioning app, you need to:

  1. Create a new APEX workspace

  2. Grant APEX_ADMINISTRATOR_ROLE role to the database user associated with the schema from step 1 above

  3. Create an application and modify the security attributes to allow the application to Modify the Workspace Repository

Create A Workspace for the Provisioning App

Follow the instructions in Joel’s previous blog post to create a workspace. In the examples that following the workspace and database user are named PROVISION.

Grant Necessary Privileges to Provisioning App Workspace DB User

The DB user associated with the provisioning app workspace will need some grants and privileges in order to create new workspaces that are functional. The following example does the necessary grants for a DB user named PROVISION. Execute the following SQL using Database Actions, SQL, logged in as the ADMIN database user.

grant APEX_ADMINISTRATOR_ROLE to PROVISION;

grant create user, drop user, alter user to PROVISION;

grant select on sys.dba_sys_privs to PROVISION;

begin
    for c1 in (select privilege
             from sys.dba_sys_privs
            where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE' ) loop
        execute immediate 'grant '||c1.privilege||' to PROVISION with admin option';
    end loop;
end;
/

Create the Provisioning App

The provisioning app in the example below creates a workspace called TEST1 with a database user called TEST1 and is meant to be a simple example. It also allows removing the TEST1 workspace and database user. Adding items for workspace name, database user, and password, and using those items in the provisioning process is a logical extension to this app.

  1. Create a new blank app

  2. Add a Static region to the home page

  3. Add a button Create_Workspace in region position Next

  4. Add a button Remove_Workspace in region position Delete

The application should now look similar to the one in the image below.

Workspace Provisioning App

Modify Security Properties of the App

In order to provision new workspace we must enable a security property for the app to modify the workspace repository.

  1. Navigate to Shared Components
  2. Click Security Attributes under the Security heading
  3. Under Database Session, check the Modify Workspace Repository option in the Runtime API Usage list

Modify Workspace Repository Security Attribute

Create Process for Create Workspace Button

  1. Navigate to Page Designer page 1

  2. Create an After Submit process on the page with the following properties

    • Name: Create Workspace

    • Success Message: Workspace created!

    • Error Message: Error creating workspace. #SQLERRM#

    • When Button Pressed: Create_Workspace

    • PL/SQL Code (replace the * for the passwords with an actual password that conforms to the database password policy):

begin
    execute immediate 'create user TEST1 identified by "************" default tablespace DATA quota unlimited on DATA';
     
    --grant all the privileges that a db user would get if provisioned by APEX
    for c1 in (select privilege
                 from sys.dba_sys_privs
                where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE' ) loop
        execute immediate 'grant '||c1.privilege||' to TEST1';
    end loop;
     
    apex_instance_admin.add_workspace(
        p_workspace      => 'TEST1',
        p_primary_schema => 'TEST1');
         
    apex_util.set_workspace(
        p_workspace      => 'TEST1');
         
    apex_util.create_user(
        p_user_name                    => 'TEST1',
        p_web_password                 => '************',
        p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_email_address                => 'test1@example.com',
        p_default_schema               => 'TEST1',
        p_change_password_on_first_use => 'N' );
         
    apex_util.set_workspace(
        p_workspace      => 'PROVISION');
end;

Create Process for Remove Workspace Button

  1. Navigate to Page Designer page 1

  2. Create an After Submit process on the page with the following properties

    • Name: Remove Workspace

    • Success Message: Workspace removed!

    • Error Message: Error removing workspace. #SQLERRM#

    • When Button Pressed: Remove_Workspace

    • PL/SQL Code:

begin
    apex_instance_admin.remove_workspace('TEST1');
 
    execute immediate 'drop user TEST1 cascade';
end;

Scripting Workspace Creation

Scripting workspace creation allows you to quickly create any number of workspaces in your ADB APEX instance. For example, if you need to create many workspaces for a class or workshop.

Consider the following code example which uses the PROVISION database user created in the previous exercise to provision multiple workspaces:

declare
    l_workspace_base constant varchar2(30) := 'WORKSHOP';
    l_db_user_base   constant varchar2(30) := 'WORKSHOP';
    l_password_base  constant varchar2(30) := 'AaBbCcDdEe!';
    i                         integer;
begin
    for i in 1.. 10 loop
        execute immediate 'create user '||l_db_user_base||i||' identified by "'||l_password_base||i||
            '" default tablespace DATA quota unlimited on DATA';

        --grant all the privileges that a db user would get if provisioned by APEX
        for c1 in (select privilege
                     from sys.dba_sys_privs
                    where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE' ) loop
            execute immediate 'grant '||c1.privilege||' to '||l_db_user_base||i;
        end loop;

        apex_instance_admin.add_workspace(
            p_workspace      => l_workspace_base||i,
            p_primary_schema => l_db_user_base||i);

        apex_util.set_workspace(
            p_workspace      => l_workspace_base||i);

        apex_util.create_user(
            p_user_name                    => l_db_user_base||i,
            p_web_password                 => l_password_base||i,
            p_developer_privs              => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
            p_email_address                => l_db_user_base||i||'@example.com',
            p_default_schema               => l_db_user_base||i,
            p_change_password_on_first_use => 'N' );

        apex_util.set_workspace(
            p_workspace      => 'PROVISION');
    end loop;
end;

In the above example we created 10 workspaces with a base name of WORKSHOP appended a number 1 through 10. We also created database users with the same base name and appended number, and created unique passwords for each. Run this example in the SQL Workshop’s SQL Commands.

Provision 10 Workspaces with a Script

Conclusion

The recent Oracle APEX patch applied to ADB serverless enables powerful provisioning enhancements for your APEX instance. You can script the creation of workspaces to quickly provision multiple workspaces. You can also build your own APEX app to handle provisioning of workspaces in your APEX instance.