X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

  • October 29, 2019

How to Script Workspace Provisioning on Oracle Autonomous Database

Jason Straub
Consulting Member of Technical Staff

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

  1. Allow ADMIN to grant execute on APEX_INSTANCE_ADMIN 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 EXECUTE on APEX_INSTANCE_ADMIN 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.

grant execute on apex_instance_admin 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');
     
    --Add the parsing db user of this application to allow apex_util.set_workspace to succeed
    apex_instance_admin.add_schema(
        p_workspace      => 'TEST1',
        p_schema         => 'PROVISION');
         
    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_instance_admin.remove_schema(
        p_workspace        => 'TEST1',
        p_schema           => 'PROVISION');
         
    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);

        --Add the parsing db user of this application to allow apex_util.set_workspace to succeed
        apex_instance_admin.add_schema(
            p_workspace      => l_workspace_base||i,
            p_schema         => 'PROVISION');

        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_instance_admin.remove_schema(
            p_workspace        => l_workspace_base||i,
            p_schema           => 'PROVISION');

        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.