****************************************************************************************** TRAUST BLOG | SELF-REGISTRATION EXAMPLE | ANDREW SCHULTZ ****************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.1 - CREATE SEQUENCE EXAMPLE_USER_SEQ TRAUST.APEX.SELF-REGISTRATION.1.2 - CREATE TABLE PENDING EXAMPLE USER QUEUE TRAUST.APEX.SELF-REGISTRATION.1.3 - INSERT PENDING EXAMPLE USER INFO RECORD TRAUST.APEX.SELF-REGISTRATION.1.4 - CREATE USER GROUP FOR EXAMPLE USERS TRAUST.APEX.SELF-REGISTRATION.1.5 - GENERATE TEMPORARY PASSWORD TRAUST.APEX.SELF-REGISTRATION.1.6 - CREATE A NEW EXAMPLE USER TRAUST.APEX.SELF-REGISTRATION.1.7 - DBMS_SCHEDULER JOB FOR CREATE A NEW EXAMPLE USER TRAUST.APEX.SELF-REGISTRATION.1.8 - PL/SQL BLOCK TO CALL DBMS_SCHEDULER JOB ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.1 - CREATE SEQUENCE EXAMPLE_USER_SEQ ********************************************************************************************** CREATE SEQUENCE example_user_seq /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- Used for the id in the pending_example_user_queue table -------------------------------------------------------------------------------------*/ MINVALUE 1000 START WITH 1000 INCREMENT BY 1 -- example_user_seq.nextval ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.2 - CREATE TABLE PENDING EXAMPLE USER QUEUE ********************************************************************************************** CREATE TABLE traustblog.pending_example_user_queue( /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- This table will temporarily store information on a new user replace traustblog. with your schema name -------------------------------------------------------------------------------------*/ pend_user_id number, user_name varchar2(25), first_name varchar2(75), last_name varchar2(75), email_address varchar2(100) ); ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.3 - INSERT PENDING EXAMPLE USER INFO RECORD ********************************************************************************************** CREATE OR REPLACE PROCEDURE traustblog.insert_pend_example_user (peu_pend_user_id IN number, peu_user_name IN varchar2, peu_first_name IN varchar2, peu_last_name IN varchar2, peu_email_address IN varchar2) /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- After registering, the example user's registration information is temporarily added to the pending_blog_user_queue table Users must be created in this workaround fashion because of the default security measures within Oracle Apex that prevents certain commands from running on pages with non-logged in users (i.e. registration page) replace traustblog. with your schema name -------------------------------------------------------------------------------------*/ IS BEGIN insert into traustblog.pending_example_user_queue (pend_user_id, user_name, first_name, last_name, email_address) values (peu_pend_user_id, peu_user_name, peu_first_name, peu_last_name, peu_email_address); END; ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.4 - CREATE USER GROUP FOR EXAMPLE USERS ********************************************************************************************** BEGIN /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- Creates the user group for example users -------------------------------------------------------------------------------------*/ APEX_UTIL.CREATE_USER_GROUP ( p_id => null, -- trigger assigns PK p_group_name => 'Example Users', p_security_group_id => null, -- defaults to current workspace ID p_group_desc => 'Example Users for an APEX self registration example' ); END; ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.5 - GENERATE TEMPORARY PASSWORD ********************************************************************************************** CREATE OR REPLACE FUNCTION traustblog.genTempPassword return varchar2 /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- Called to generate a temporary password when a new user is created or when accounts need to have their passwords reset replace traustblog. with your schema name -------------------------------------------------------------------------------------*/ IS shuffle_password_table apex_t_varchar2; temp_pw_lower varchar2(15); temp_pw_mixed varchar2(15); temp_pw_upper varchar2(15); temp_pw_spec_1 varchar2(15); temp_pw_spec_2 varchar2(15); temp_pw_number varchar2(15); concat_password varchar2(25); final_password varchar2(25); /*-------------------------------------------------------------------- Get 2 random lowercase letters --------------------------------------------------------------------*/ cursor c1 is select dbms_random.string('L',2) from dual; /*-------------------------------------------------------------------- Get 5 random mixed case alpha characters --------------------------------------------------------------------*/ cursor c2 is select dbms_random.string('A',5) from dual; /*-------------------------------------------------------------------- Get 2 random uppercase letters --------------------------------------------------------------------*/ cursor c3 is select dbms_random.string('U',2) from dual; /*-------------------------------------------------------------------- Get 1 random special character from the list --------------------------------------------------------------------*/ cursor c4 is select case round(dbms_random.value(1,21)) when 1 then '^' when 2 then '=' when 3 then '/' when 4 then '%' when 5 then '`' when 6 then '*' when 7 then '~' when 8 then '+' when 9 then '?' when 10 then '!' when 11 then '\' when 12 then ':' when 13 then '@' when 14 then '_' when 15 then '#' when 16 then '>' when 17 then '&' when 18 then '$' when 19 then '<' when 20 then '-' end as special_char from dual; /*-------------------------------------------------------------------- Get 1 random special character from the list --------------------------------------------------------------------*/ cursor c5 is select case round(dbms_random.value(1,21)) when 1 then '+' when 2 then '*' when 3 then '&' when 4 then '^' when 5 then '?' when 6 then ':' when 7 then '!' when 8 then '@' when 9 then '>' when 10 then '~' when 11 then '%' when 12 then '/' when 13 then '#' when 14 then '<' when 15 then '-' when 16 then '=' when 17 then '_' when 18 then '`' when 19 then '\' when 20 then '$' end as special_char from dual; /*-------------------------------------------------------------------- Get a random number between 10000 and 99998 --------------------------------------------------------------------*/ cursor c6 is select round(dbms_random.value(10000,99999)) from dual; BEGIN open c1; fetch c1 into temp_pw_lower; open c2; fetch c2 into temp_pw_mixed; open c3; fetch c3 into temp_pw_upper; open c4; fetch c4 into temp_pw_spec_1; open c5; fetch c5 into temp_pw_spec_2; open c6; fetch c6 into temp_pw_number; /*-------------------------------------------------------------------- Combine all the cursor values into a apex_t_varchar2 datatype and randomize them for the final password --------------------------------------------------------------------*/ concat_password := concat(temp_pw_lower, concat(temp_pw_mixed, concat(temp_pw_upper, concat(temp_pw_spec_1, concat(temp_pw_spec_2,temp_pw_number))))); shuffle_password_table := apex_string.split(concat_password,null); final_password := apex_string.join(apex_string.shuffle(shuffle_password_table),''); return final_password; close c1; close c2; close c3; close c4; close c5; close c6; END; ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.6 - CREATE A NEW EXAMPLE USER ********************************************************************************************** CREATE OR REPLACE PROCEDURE traustblog.create_new_example_user /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- The example user is created from their registration information in the pending_example_user_queue table After the user is created, their record is deleted from the pending_example_user_queue table Users must be created in this workaround fashion because of the default security measures within Oracle Apex that prevents certain commands from running on pages with non-logged in users (i.e. registration page) replace traustblog with your schema name replace the email address "from" address with your email address email settings must be setup before in APEX -------------------------------------------------------------------------------------*/ AS l_workspace_id NUMBER; example_group_id NUMBER; temp_pass varchar2(25); l_body CLOB; BEGIN /*-------------------------------------------------------------------- Get the workspace id --------------------------------------------------------------------*/ select workspace_id into l_workspace_id from apex_workspaces where workspace = 'TRAUSTBLOG'; /*-------------------------------------------------------------------- Set the SGID based on the workspace --------------------------------------------------------------------*/ apex_util.set_security_group_id(p_security_group_id => l_workspace_id); /*-------------------------------------------------------------------- Get the group id for the example users group --------------------------------------------------------------------*/ example_group_id := APEX_UTIL.GET_GROUP_ID('Example Users'); /*-------------------------------------------------------------------- Loop through all users in the pending_example_user_queue table --------------------------------------------------------------------*/ for x in (select * from traustblog.pending_example_user_queue) loop /*-------------------------------------------------------------------- Generate a temporary password --------------------------------------------------------------------*/ temp_pass := traustblog.genTempPassword; /*-------------------------------------------------------------------- One final check to make sure the username is unique --------------------------------------------------------------------*/ if APEX_UTIL.IS_USERNAME_UNIQUE(x.user_name) = TRUE then apex_util.create_user ( p_user_name => x.user_name, p_first_name => x.first_name, p_last_name => x.last_name, p_email_address => x.email_address, p_web_password => temp_pass, p_group_ids => example_group_id, /*-------------------------------------------------------------------- For extra security, this resets the user's access to the TRAUSTBLOG schema only --------------------------------------------------------------------*/ p_allow_access_to_schemas => 'TRAUSTBLOG', /*-------------------------------------------------------------------- For extra security, this resets the user's default schema to TRAUSTBLOG --------------------------------------------------------------------*/ p_default_schema => 'TRAUSTBLOG', /*-------------------------------------------------------------------- For extra security, this resets the user's developer role to an end user Note: In FETCH USER and EDIT USER p_developer_privs is named p_developer_role --------------------------------------------------------------------*/ p_developer_privs => null, p_account_locked => 'N', p_failed_access_attempts => 0, p_change_password_on_first_use => 'Y', p_first_password_use_occurred => 'Y'); /*-------------------------------------------------------------------- Email text defined --------------------------------------------------------------------*/ l_body :='Hi '||x.first_name||' '||x.last_name||', You have successfully registered for the Self-Registration Example Application with an example account. Login to your account with your temporary password Username: '||LOWER(x.user_name)||' Temporary password: '||temp_pass||' If you have any questions, please send us an email The Traust Team info@traustconsulting.com www.traust.com '; apex_mail.send( p_to => x.email_address, p_from => 'your_email_address@something.com', -- put your email address here p_body => l_body, p_subj => 'Welcome to the Self-Registration Example Application!'); APEX_MAIL.PUSH_QUEUE; /*-------------------------------------------------------------------- Remove the user from the queue --------------------------------------------------------------------*/ delete from traustblog.pending_example_user_queue where traustblog.pending_example_user_queue.pend_user_id = x.pend_user_id; /*-------------------------------------------------------------------- Set the temporary password back to null --------------------------------------------------------------------*/ temp_pass := null; end if; end loop; END; ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.7 - DBMS_SCHEDULER JOB FOR CREATE A NEW EXAMPLE USER ********************************************************************************************** /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- A scheduled job that calls the stored procedure create_new_example_user to create the example user after they registered **** Must be created as sys or sysdba **** The scheduler can be called in Oracle APEX from a pl/sql process block replace traustblog with your schema name -------------------------------------------------------------------------------------*/ BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'traustblog.CREATE_EXAMPLE_USER_FROM_QUEUE_JOB', job_type => 'STORED_PROCEDURE', job_action => 'traustblog.create_new_example_user', number_of_arguments => 0, repeat_interval => 'freq=secondly; bysecond=5', end_date => NULL, enabled => FALSE, auto_drop => TRUE, comments => '' ); END; ********************************************************************************************** TRAUST.APEX.SELF-REGISTRATION.1.8 - PL/SQL BLOCK TO CALL DBMS_SCHEDULER JOB ********************************************************************************************** BEGIN /*------------------------------------------------------------------------------------- DESCRIPTION --------------------------------------------------------------------------------------- To call the traustblog.CREATE_EXAMPLE_USER_FROM_QUEUE_JOB Create a page process PL/SQL block in APEX and then used this code -------------------------------------------------------------------------------------*/ dbms_scheduler.run_job(job_name => 'CREATE_EXAMPLE_USER_FROM_QUEUE_JOB', use_current_session => FALSE); END;