APEX ist bereits seit dem ersten Tag für Hosting-Umgebungen ausgelegt. Wie jedermann auf dem öffentlichen Demoserver apex.oracle.com selbst ausprobieren kann, kann ein Workspace in Selbstbedienung beantragt werden. Nach der Genehmigung durch den Administrator wird der Workspace einrichtet und der Entwickler kann mit der Arbeit beginnen.

Abbildung 1: "Beantragen" eines neuen APEX-Workspace

Dieser von APEX mit ausgelieferte Bereitstellungsprozeß ist zwar sehr bequem - in vielen Fällen passt er jedoch nicht auf die Bedürfnisse im Unternehmen ...

• Der Vorgesetzte muss den Workspace genehmigen und nicht der APEX-Administrator.
• Die von APEX standardmäßig erfassten Angaben reichen nicht aus - man möchte ggfs. eine Kostenstelle oder einen Abteilungsnamen erfragen.
• Die Seiten zur Beantragung eines Workspace soll durch Login geschützt werden
• Die Email-Adresse soll nicht frei eingebbar sein, sondern aus dem Login abgeleitet werden
• Der Name des Datenbankschemas soll generiert werden
• Und und und ...

Fast alle Anforderungen dieser Art ließen sich mit einer eigenen APEX-Anwendung problemlos abdecken - die Anforderungen könnten in einer eigenen Tabelle abgelegt werden. Zur konkreten Einrichtung des Workspace wird dann allerdings ein automatisierter Prozeß benötigt.

Bereits vor einigen APEX-Versionen wurde das PL/SQL-Paket APEX_INSTANCE_ADMIN eingeführt; damit können APEX-Workspaces auch aus PL/SQL heraus verwaltet werden. Die Notwendigkeit für dieses Paket kam mit der Runtime-Only-Installation auf - wenn keine APEX-Entwicklungsumgebung mehr da ist, ist auch keine APEX-Administrationsumgebung (Workspace INTERNAL) mehr da; die Aufgaben müssen also mit APEX_INSTANCE_ADMIN erledigt werden.

Im folgenden wird exemplarisch vorgestellt, wie ein sehr einfacher Prozess zum Einrichten eines APEX-Workspaces implementiert werden kann. Dieser kann dann natürlich so lange ausgebaut werden, bis konkrete Anforderungem im Unternehmen erfüllt sind.

• Die Anwendung zum Beantragen eines Workspace wird durch einen Login geschützt
• Die Mailadresse wird aus dem APEX-Usernamen Login abgeleitet (&APP_USER.@meinefirma.de)
• Der Workspace-Name wird generiert
• Es wird immer ein neues Datenbankschema erstellt - der Name wird generiert
• Alle Datenbankschemas bekommen TS_APEX_WORKSPACES als Tablespace zugewiesen
• Die Tablespace-Quota wird einheitlich auf 25MB gesetzt

Zur Umsetzung benötigen wir zunächst ein Datenbankschema (WSPROV) und einen APEX-Workspace.

create user wsprov identified by oracle;

-- Zuerst "normale" Privilegien ...
grant CREATE SESSION to wsprov;
grant ALTER SESSION to wsprov;
grant UNLIMITED TABLESPACE to wsprov;
grant CREATE TABLE to wsprov;
grant CREATE CLUSTER to wsprov;
grant CREATE VIEW to wsprov;
grant CREATE SEQUENCE to wsprov;
grant CREATE PROCEDURE to wsprov;
grant CREATE TRIGGER to wsprov;
grant CREATE TYPE to wsprov;

-- Zusätzlich sind sind diese beiden wichtig!
grant APEX_ADMINISTRATOR_ROLE to wsprov;
grant EXECUTE on APEX_INSTANCE_ADMIN to wsprov;

Die APEX-Anwendung, über welche künftig APEX-Workspaces beantragt werden sollen, wird in diesem Workspace; deren Tabellen und der PL/SQL-Code, der die neuen Datenbankuser und APEX-Workspaces konkret erzeugt, wird im Datenbankschema liegen. Aus diesem Grund benötigt es, neben den "normalen" Privilegien, die APEX_ADMINISTRATOR_ROLE und das EXECUTE-Privileg auf APEX_INSTANCE_ADMIN. Tatsächlich ausgeführt wird das Package jedoch, da zu einem neuen APEX-Workspace auch ein neues Datenbankschema gehört, als SYS.

Weiterhin wird das erwähnte Tablespace TS_APEX_WORKSPACES benötigt. Legen Sie es als DBA in etwa wie folgt an - natürlich müssen Sie den Pfad zu den Datendateien, die gewünschte Dateigröße und die Autoextend-Eigenschaften an Ihre Umgebung und Bedürfnisse anpassen. In diesem Beispiel werden alle neuen APEX-Workspaces diesen Tablespace verwenden.

create tablespace ts_apex_workspaces
datafile '/path/to/orcl/datafiles/tsapexworkspaces01.dbf' 
size 1G
autoextend on;

Das folgende SQL-Skript legt die Tabelle an, in welche die Anforderungen (Requests) gespeichert werden. Lassen Sie es im gerade erzeugten Datenbankschema WSPROV laufen.

create table tab_apex_workspace_requests(
  id              number(10),
  workspace_name  varchar2(255), 
  admin_userid    varchar2(255) not null,
  admin_email     varchar2(255) not null,
  admin_name      varchar2(255) not null,
  admin_vorname   varchar2(255) not null,
  admin_password  varchar2(6),   
  zeitstempel     date          not null,
  status          varchar2(20)  default 'REQUEST',
  message         varchar2(4000),
  constraint pk_apex_ws_req primary key (id),
  constraint ch_apex_ws_req_status check (status in ('REQUEST', 'WORKING', 'ERROR', 'APPROVED'))
)
/

create sequence seq_apex_workspace_request start with 10
/

create or replace trigger tr_apex_workspace_request
before insert on tab_apex_workspace_requests
for each row
begin
  :new.id := seq_apex_workspace_request.nextval;
  :new.zeitstempel := sysdate;
  :new.status := 'REQUEST';
end;
/
sho err

Erstellen Sie danach eine APEX-Anwendung mit einer Formularseite. Das Formular sollte Eingabefelder für Namen und Vornamen enthalten; die Felder für Email sowie für APEX-User-ID sollten auf Read Only gestellt werden. Das "Generieren" der Emailadresse könnte in einfachen Fällen mit mit einer Belegung des Default ("&APP_USER.@meinefirma.de") erreicht werden; komplexere Fälle erfordern ein wenig PL/SQL-Logik. Am besten verwenden Sie nicht den APEX-Formularassistenten - legen Sie die Formularfelder einzeln an und fügen Sie die Daten mit einem PL/SQL-Prozess in die Tabelle ein. Eine Anwendung zur Illustration stellen wir hier zum Download bereit.

Das fertige Formular könnte wie in Abbildung 2 aussehen. Erstellen Sie dann einen Bericht, der alle Anforderungen des angemeldeten Nutzers anzeigt; das SQL hierfür ist recht einfach:

select * 
from tab_apex_workspace_requests 
where admin_userid = :APP_USER

Abbildung 2: Eigenes Formular zum Beantragen eines APEX-Workspace

Als nächstes wird die PL/SQL-Prozedur erstellt, die anhand dieser Angaben den APEX-Workspace erstellt. Dazu müssen folgende Schritte ausgeführt werden.

• Ein Passwort für den APEX-Nutzer als auch für das Datenbankschema muss generiert werden
• Ein neues Datenbankschema muss erstellt werden (CREATE USER)
• Die nötigen Privilegen wie CREATE TABLE, CREATE PROCEDURE und andere müssen vergeben werden
• Der Workspace muss mit APEX_INSTANCE_ADMIN erstellt werden
• Das Benutzerkonto ADMIN muss im neuen Workspace eingerichtet werden

Diese Aktivitäten sind im PL/SQL-Paket PKG_WORKSPACE_PROVISIONING zusammengefasst. Spielen Sie es ebenfalls ins Parsing-Schema des APEX-Workspace ein.

create or replace package pkg_workspace_provisioning authid current_user
is
  procedure create_requested_workspaces;
end pkg_workspace_provisioning;
/
sho err

create or replace package body pkg_workspace_provisioning 
is
  C_DEFAULTTS constant varchar2(30) := 'TS_APEX_WORKSPACES';
  C_TS_QUOTA  constant varchar2(30) := '25M';

  function generate_password return varchar2 is
    v_consonants varchar2(21) := 'bcdfghjklmnpqrstvwxyz';
    v_vocals     varchar2(5)  := 'aeiou';
    v_password   varchar2(6)  := '';
  begin
    v_password := v_password || substr(v_consonants, round(dbms_random.value(1,21)), 1);
    v_password := v_password || substr(v_vocals, round(dbms_random.value(1,5)), 1);
    v_password := v_password || substr(v_consonants, round(dbms_random.value(1,21)), 1);
    v_password := v_password || substr(v_vocals, round(dbms_random.value(1,5)), 1);
    v_password := v_password || substr(v_consonants, round(dbms_random.value(1,21)), 1);
    v_password := v_password || substr(v_vocals, round(dbms_random.value(1,5)), 1);
    return v_password;
  end generate_password;

  procedure create_schema(
    p_schemaname varchar2,
    p_password   varchar2
  ) is 
  begin
    execute immediate 'create user '||p_schemaname|| ' identified by '||p_password||' default tablespace '||C_DEFAULTTS;
    execute immediate 'alter user '||p_schemaname|| ' quota '||C_TS_QUOTA||' on '||C_DEFAULTTS;
    execute immediate 'grant create operator to '||p_schemaname;
    execute immediate 'grant create cluster to '||p_schemaname;
    execute immediate 'grant create dimension to '||p_schemaname;
    execute immediate 'grant create indextype to '||p_schemaname;
    execute immediate 'grant create any context to '||p_schemaname;
    execute immediate 'grant create table to '||p_schemaname;
    execute immediate 'grant create sequence to '||p_schemaname;
    execute immediate 'grant create view to '||p_schemaname;
    execute immediate 'grant create session to '||p_schemaname;
    execute immediate 'grant create synonym to '||p_schemaname;
    execute immediate 'grant create type to '||p_schemaname;
    execute immediate 'grant create trigger to '||p_schemaname;
    execute immediate 'grant create procedure to '||p_schemaname;
    execute immediate 'grant create materialized view to '||p_schemaname;
    execute immediate 'grant create job to '||p_schemaname;
    execute immediate 'grant alter session to '||p_schemaname;
  end create_schema;

  procedure create_workspace(
    p_workspace    in varchar2,
    p_password     in varchar2,
    p_schemaname   in varchar2,
    p_admin_email  in varchar2,
    p_admin_vname  in varchar2,
    p_admin_nname  in varchar2
  ) is 
    v_wsid      number;
    pragma autonomous_transaction;
  begin
   wwv_flow_api.set_security_group_id(p_security_group_id=> 10);
   apex_instance_admin.add_workspace(
     P_WORKSPACE            => p_workspace,
     P_PRIMARY_SCHEMA       => p_schemaname,
     P_ADDITIONAL_SCHEMAS   => p_schemaname
   );
   commit;

   select workspace_id into v_wsid
   from apex_workspaces
   where workspace = p_workspace;

   wwv_flow_api.set_security_group_id(p_security_group_id => v_wsid);

   apex_util.create_user(
     p_user_name       => 'ADMIN',
     p_web_password    => p_password,
     P_DEVELOPER_PRIVS => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',  
     p_first_name      => p_admin_vname,
     p_last_name       => p_admin_nname,
     p_email_address   => p_admin_email,
     p_default_schema  => p_schemaname
   );
   commit;

   apex_instance_admin.enable_workspace(
     P_WORKSPACE       => p_workspace
   );
   commit;
  end create_workspace;
  
  procedure create_requested_workspaces is 
    pragma autonomous_transaction;

    v_schemaname varchar2(30);
    v_workspace  varchar2(30);
    v_password   varchar2(10);

    v_message    varchar2(4000);
  begin
    for i in (
      select * from tab_apex_workspace_requests where status = 'REQUEST'
    ) loop
      v_password   := generate_password;
      v_workspace  := 'APEXWS_'||i.id;
      v_schemaname := 'APEXWS_'||i.id;

      update tab_apex_workspace_requests set 
        status         = 'WORKING' ,
        workspace_name = v_workspace,
        admin_password = v_password
      where id = i.id;
      commit;
      begin
        create_schema(v_schemaname, v_password);
        create_workspace(
          p_workspace    => v_workspace,
          p_password     => v_password,
          p_schemaname   => v_schemaname,
          p_admin_email  => i.admin_email,
          p_admin_vname  => i.admin_vorname,
          p_admin_nname  => i.admin_name
        );
        update tab_apex_workspace_requests set status='APPROVED' where id = i.id;
        commit;
      exception 
        when others then
          v_message := sqlerrm;
          update tab_apex_workspace_requests set status='ERROR', message = v_message
          where id = i.id;
          commit;
      end;
      /* Hier noch eine Email mit APEX_MAIL senden */
    end loop;
  end create_requested_workspaces;
end pkg_workspace_provisioning;
/
sho err

Die Prozedur PKG_WORKSPACE_PROVISIONING.CREATE_REQUESTED_WORKSPACES arbeitet nun alle offenen Requests aus der Tabelle TAB_APEX_WORKSPACE_REQUESTS ab. Am besten lassen Sie diese per Datenbank-Job in regelmäßigen Intervallen ausführen. Dieser Job muss, da neben dem APEX Workspace auch neue Datenbankuser erzeugt werden, als SYS ausgeführt werden. Natürlich können Sie auch einen anderen DBA-User mit entsprechenden Privilegien nehmen.

-- Dieser JOB muss als SYS erzeugt werden!
begin
  dbms_scheduler.create_job(
    job_name            => 'APEX_AUTOAPPROVE_JOB',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 
  'begin 
     execute immediate ''alter session set current_schema=WSPROV'';
     wsprov.pkg_workspace_provisioning.create_requested_workspaces; 
   end;
  ',
    number_of_arguments => 0,
    start_date          => to_timestamp('2011-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    repeat_interval     => 'FREQ=MINUTELY; INTERVAL=5',
    end_date            => null,
    job_class           => 'DEFAULT_JOB_CLASS',
    enabled             => true,
    auto_drop           => true,
    comments            => null
  );
end;
/
sho err

Mit DBMS_SCHEDULER.DROP_JOB können Sie den Job wieder löschen.

Tragen Sie nun einen "Workspace-Request" in das Formular der APEX-Anwendung ein und verfolgen Sie im Bericht den Status. Nach einigen Minuten wird der Status zuerst auf WORKING und dann auf APPROVED gestellt. Anschließend ist der Workspace eingerichtet (Abbildung 3).

Abbildung 3: Verfolgung des Bereitstellungs-Status im APEX-Bericht

Das erstmalige Login läuft wie immer ab. Zuerst muss das Standardpasswort geändert werden und danach kann man im Workspace arbeiten.

Abbildung 4: Erstmaliger Login in den neuen APEX-Workspace

Dieses einfache Beispiel lässt sich natürlich weiter denken; so könnte man durchaus verschiedene Workspace-Größen denkbar; das Formular könnte (analog zum Prozess auf apex.oracle.com) verschiedene Größen zur Auswahl anbieten. Technisch würde die Tablespace-Quota von 25M, die im Beispiel mit einer PL/SQL-Konstante "hart" kodiert ist, dynamisch gestaltet. Auch Genehmigungsprozesse oder die Kombination mit einem Abrechnungsmodul ist denkbar. APEX macht das Erstellen der "Provisioning-Anwendung", wie immer, sehr einfach.

Mehr Informationen

Zurück zur Community-Seite