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:
- Allow ADMIN to grant APEX_ADMINISTRATOR_ROLE role to other database users
- 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:
-
Create a new APEX workspace
-
Grant APEX_ADMINISTRATOR_ROLE role to the database user associated with the schema from step 1 above
-
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.
-
Create a new blank app
-
Add a Static region to the home page
-
Add a button Create_Workspace in region position Next
-
Add a button Remove_Workspace in region position Delete
The application should now look similar to the one in the image below.
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.
- Navigate to Shared Components
- Click Security Attributes under the Security heading
- Under Database Session, check the Modify Workspace Repository option in the Runtime API Usage list
Create Process for Create Workspace Button
-
Navigate to Page Designer page 1
-
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
-
Navigate to Page Designer page 1
-
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.
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.