How to build a custom reset password feature in Oracle APEX

So, there's often a requirement to provide the "Reset Password" option to end-users of an Oracle APEX application. Of course, this is based on the fact that the authentication is done using APEX credentials and not based on any other external methods, such as EBS or Active Directory etc.

In such scenarios, Oracle APEX doesn't provide any out-of-box Reset Password option to users. So what's the workaround ? Well, we need to build a custom solution to have this feature.

Let's see how this can be achieved.

Pre-check:

Please navigate to Shared Components -> Authentication Schemes

Make sure the Current scheme is set to Oracle APEX Accounts

Please note that below solution works only for this scheme and won't be applicable to any other authentication options.

1. Page for entering Email ID where user can receive Reset Password URL:

- Create a new blank page. Let's number it 8001

- Create a Text Field and name it P8001_EMAIL

- Create a button and name it Submit

- Set Page Authentication to Public because user must be able to access it without limitation of any credentials

- Set Session State Protection for P8001_EMAIL to "Restricted - May not be set from browser"
This is to prevent possibility of URL Tampering. This way if the user tries to modify the URL in any way then it will give the error.

- If we run this page at this stage, it should look like this

2. Create a Link on seeded Login Page pointing to above Page :

- Now, let's create a link to this page on the actual login page

- Navigate to Page#9999 which is the standard login page in APEX

- Create a new button under Language Selector region and name it Reset Password

- Under Behavior section, set Action to Redirect to Page in this Application and Target to Page 8001

- If we run this page at this stage, it should look like this

- If we click on Reset Password link, it will take us to the newly created Page#8001

3. Add logic on the newly create Email Page from Step 1:

- Navigate to Page#8001

- Now, let's fetch the User Name associated with this EMAIL ID as we need to use it in further steps

- Create a new item named P8001_USER_NAME and set the Type to Hidden

- Set Session State Protection for P8001_USER_NAME to "Restricted - May not be set from browser"

This is to prevent possibility of URL Tampering. This way if the user tries to modify the URL in any way then it will give the error.

- Now, let's create a Computation for this item

- Right click on P8001_USER_NAME and select Create Computation

- Create computation with below details

  Point: After Submit

  SQL Query:
  SELECT USER_NAME

  FROM   APEX_WORKSPACE_APEX_USERS

  WHERE  UPPER(EMAIL) = UPPER(:P8001_EMAIL);

   Server-side Condition:

   Type: Item is NOT NULL
   Item: P8001_EMAIL 

3. Create a new Page to let user enter New Password

- Create a new blank page. Let's number it 8002

- Set Page Authentication to Public because user must be able to access it without limitation of any credentials

- Create a new Text Item named P8002_PASSWORD and set Type to Password

- Set Session State Protection for P8002_PASSWORD to "Restricted - May not be set from browser"

This is to prevent possibility of URL Tampering. This way if the user tries to modify the URL in any way then it will give the error.

- Create a new button named Submit with Action as Submit Page

- Now, create a new item named P8002_USER_NAME and set type to Hidden and Disable Value Protected option

- Set Session State Protection to "Checksum Required - Application Level" under Security section.
This way if the user tries to modify the User Name value in URL in any way then it will give the error.
We are setting the checksum at application level as the same user may be having access to other applications and we want to limit this restriction for our application only.

4. Generate Reset Password Link and Email to User

- Navigate to Page#8001

- Create a new item named P8001_LINK and set type to Hidden

- Go to Processes and Create a new Process named Generate Link.

Here, we are essentially using GET_URL API to generate the link pointing to our App (Identified by :APP_ID), Page 8002 and we are passing user name as the parameter for the item :P8001_USER_NAME

  PL/SQL Code:

  DECLARE

    LV_LINK VARCHAR2(4000);

  BEGIN

    LV_LINK := APEX_PAGE.GET_URL(

                                P_APPLICATION => :APP_ID,

                                P_PAGE        => 8002,

                                P_SESSION     => NULL,

                                P_ITEMS       => 'P8002_USER_NAME',

                                P_VALUES      => :P8001_USER_NAME

                                );

       :P8001_LINK := REPLACE(apex_mail.get_instance_url,'/ords/','') || LV_LINK;                            

  EXCEPTION

    WHEN OTHERS THEN NULL;

  END;

  Server-side Condition:
  Type: Item is NOT NULL
  Item: P8001_USER_NAME

- Create another process named Send Email with below details

  Type: Send E-Mail

  From: <From Email ID>

  To: &P8001_EMAIL.

  Subject: Reset Password

  Body:
  Please follow the below link to reset your Password:

  &P8001_LINK.

  Send Immediately: Yes

  Server-side Condition:
  Type: Item is NOT NULL
  Item: P8001_USER_NAME

Ensure to check Send Immediately check-box. This is essentially like implicitly issuing APEX_MAIL.PUSH_QUEUE api which sends the email instantly.

More details on PUSH_QUEUE here.


Here, make sure to use the From Email Id based on the Email Delivery configuration done in OCI. Please refer to my previous blog post to understand how to do this configuration.

- Now, this page is ready so let's test it

- Run this page and enter a valid Email ID and click Submit

- We should receive the Reset Password email with the URL pointing to Page 8002 along with the User Name and Checksum passed via the URL

4. Update the Password for the user returning via above URL

- Navigate to Page#8002

- Create a new item P8002_DISP_USER on this page. This is to show the user name for whom the password it being reset.

- Set this item to Display Only

- In the Source, set Type to Item and Item to P8002_USER_NAME

- Set Session State for this item to Per Session

- If we run the page at this stage using the URL received in the email, then it should look like this. We should be able to see the User Name to whom the Email was set indicating that the password will be reset for this user.

- Now, let's create a new Process named Reset Password with below details

  Language: PL/SQL

  Code:

  BEGIN

    APEX_UTIL.RESET_PASSWORD(P_USER_NAME => UPPER(:P8002_USER_NAME),

                             P_OLD_PASSWORD => NULL,

                             P_NEW_PASSWORD => :P8002_PASSWORD,

                             P_CHANGE_PASSWORD_ON_FIRST_USE => FALSE

                            );

  EXCEPTION

    WHEN OTHERS THEN NULL;

  END;

  
  Success Message: Your password has been reset successfully.

  Server-side Condition:

  Type: Item is NOT NULL
  Item: P8002_USER_NAME

Important:

APEX_UTIL.RESET_PASSWORD API can be executed only by someone who has Admin Role.

Hence, the custom schema/user where this app resides must be granted 'APEX_ADMINISTRATOR_ROLE' role in order to be able to execute above code.

If this configuration is not done, then you will see errors on the page as Reset Password API will fail due to insufficient privileges.

- Now, let's create a Branch to page 9999 so that user is redirected to seeded Login Page upon resetting the password

- That's it ! The Reset Password page is ready. Let's test the same.

- Click on the URL from the Reset Password Email

- Enter the new Password and click Submit

- Voila ! You should see the Success message and the control should return to the seeded Login Page

- Now you will be able to login using the newly reset password.

Read More

How to Personalize / Customize Pages in Oracle Fusion Cloud using Sandboxes

Ever wondered how can we Personalize seeded pages in Oracle Fusion, like we used to do in the eBusiness Suite (EBS) ?

Well, it's possible to achieve this to some extent by leveraging Sandbox feature in ERP Cloud.

Sandbox ? What's that ?

Sandbox is an out-of-box tool provided by Oracle Fusion to perform customizations in isolation without impacting other configurations in the given environment. Sandbox is a logically isolated area in your environment where you can do your Personalizations/Customizations and test them without interfering with those pages in the real world. While you are configuring and testing these changes, other users won't see any of them until you Publish the Sandbox.

Sandbox essentials in a Nutshell:

• Only one sandbox can be active at a given point of time.
• Once the Sandbox is activated, it's name is displayed in the global area.
• Ensure to log out from Oracle Fusion and log back in after activating a Sandbox. This is to ensure that the cache is cleared and it helps avoid conflicts.
• A sandbox will remain active until
   - You exit the sandbox
   - Publish the sandbox
   - Delete the sandbox
   - Set another sandbox as Active
• Oracle Fusion patches usually retain the changes from Published Sandboxes but any unpublished Sandboxes may not work if the Oracle Patch brings new changes to the given page which effective make such Sandboxes obsolete/incompatible. It's better to create new Sandbox in such cases.

Steps to create and activate a Sandbox

1. Click on Navigator, go to Configuration Menu and select Sandboxes

Alternatively, you can enter Sandbox area by clicking User Menu on Top Right and then selecting Edit Pages under Administration menu

If you follow this path, you will see a pop-up message as shown below. Click on "Activate a sandbox" option and it will take you to the Sandbox area (same as above step).

2. Once the Sandboxes window is opened, you can click on Create Sandbox button from Available Sandboxes tab

3. Let's name our Sandbox as "TestSandbox" and set Publishable to Yes. We can select and use many Tools under this Sandbox.
For our example, let's select Page Composer tool and click Create and Enter button.

4. Once you enter a Sandbox, it's name will appear on top indicating that you are now working in a Sandbox environment and whatever changes you do will be visible to you alone.

5. Let's click on Home button to go to our Homepage

6. In our example, we are going to Personalize Create Invoice page under Payables business area

7. Navigate to Payables -> Invoices from within the Sandbox environment (while Yellow Sandbox bar appears on the top of the window)

8. Let's open the Create Invoice page

9. Navigate to Tools menu under Sandbox area and select Page Composer

10. Once you are in Page Composer mode, you will see more tools and table in the top region

11. Click on Select tab

12. Now, let's say we want to Hide the Description field on this page.

      Click on the area around Description field and you will see a small pop-up showing options to Edit this area

13. Click on Edit Component option

14. You will see a pop-up window showing Component Properties for "Description:"

      Here, let's uncheck "Show Component" option and click Ok

15. Once you return to the Create Invoice page, you will see "Description" field has now disappeared

16. With this, we've achieved to Personalize/Customize "Create Invoice" page but remember, this change is only applicable to this Sandbox and is not visible to other users.


17. Now, let's see how we can publish this Sandbox so that this change gets applied across the actual environment and will be visible to all the users.


18. Navigate to right top area on this page and click Close

19. Click on the Sandbox name on top left cornet (TestSandbox in our case) and click Publish

20. You will see below pop-up indicating that you will be taken to Sandbox page to publish this Sandbox. Click Yes

21. Once you land on Sandbox page, you can verify the Sandbox Name and Current Status.

      You can also see/verify the actual page name that was modified as part of this change.

      Click on Publish button and you will see a final pop-up box asking for final confirmation.

      Click Yes to Publish this Sandbox

      Please note: Publishing a Sandbox is an irrevocable activity; so please take this action only after you are certain about all the changes.

22. Once the Sandbox is published, navigate to the same "Create Invoices" page in Payables

      Here, we will see that the "Description" field is now hidden on the actual page as well.

This way we can create and publish a Sandbox to achieve Personalizations/Customizations on Oracle Fusion pages.

Read More

How to configure Email Delivery in OCI and send emails from APEX deployed in Oracle Autonomous Database

In this Blog, we will learn how to set up Email Delivery configurations in Oracle Cloud Infrastructure (OCI) and use the same in APEX which is deployed in Autonomous Database on this OCI environment.

Part 1: Configurations in OCI

Step 1: Creating a Group

Navigate to Identity & Security -> Groups

Create a new Group. We'll name it MailGroup

Step 2: Creating an User

Navigate to Identity & Security -> Users

Now, let's create a new Identity and Access Management (IAM) User as shown below. We'll name it mailsender@dummydomain.com

Step 3: Adding User to the Group

- Navigate to Groups.
- Open MailGroup
- Click on Add User to Group
- Select mailsender@dummydomain.com to add it to this group

Step 4: Create Email Policy / User Permissions

Navigate to Identity & Security -> Policies

- Create a new Policy. We'll name it MailPolicy
- Set Policy use case to Email Management
- Select/assign MailGroup under Groups section

Step 5: Create SMTP Credentials

- Navigate to User Profile
- Click on User Settings

- Navigate to Resources section and click on SMTP Credentials

- Click on Generate SMTP Credentials

- Enter a Description of the SMTP Credentials in the dialog box. We'll name it MailSender

- Click Generate SMTP Credentials. A user name and password is displayed.


IMPORTANT:

Copy the user name and password for your records before closing the dialog box. This is very important because you can't retrieve the password again after closing the dialog box for security reasons.


Step 6: Create Email Domain

- Navigate to Developer Services -> Email Delivery

- Click on Create Email Domain
- We'll name it dummydomain.com
- Create

Step 7: Create an Approved Sender

- Navigate to Approved Senders section
- Create Approved Sender
- We'll name it mailsender@dummydomain.com
- Please note that this will be the email address which will be used to send emails from OCI and/or APEX. 

Step 8: Check SMTP Configuration

- Navigate to Configuration under Email Delivery section
- Copy the Public Endpoint from SMTP Configuration

Part 2: Configurations in APEX

Step 1: Set APEX Instance Parameters

- Connect to the Autonomous Database as ADMIN user (SQL Developer in OCI)

- Run the following command using the Public Endpoint, Username and Password generated in above steps:

BEGIN

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.us-ashburn-1.oci.oraclecloud.com');

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', '<username from above steps>');

APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', '<password from above steps>');

COMMIT;

END;

/

We've now completed all the configurations pertaining to OCI and APEX for Email Delivery.

Now, let's see how to send emails from APEX application.


Step 2: Send Email

- To send emails in APEX, we've to use APEX_MAIL.SEND API

Here's the API definition:

APEX_MAIL.SEND(
    p_to                        IN    VARCHAR2,
    p_from                      IN    VARCHAR2,
    p_body                      IN  [ VARCHAR2 | CLOB ],
    p_body_html                 IN  [ VARCHAR2 | CLOB ] DEFAULT NULL,
    p_subj                      IN    VARCHAR2 DEFAULT NULL,
    p_cc                        IN    VARCHAR2 DEFAULT NULL,
    p_bcc                       IN    VARCHAR2 DEFAULT NULL,
    p_replyto                   IN    VARCHAR2);

- Navigate to APEX on OCI

- Navigate to SQL Commands window under SQL Workshop

- Enter below code and run

BEGIN

APEX_MAIL.SEND

(

    p_to   => 'xyz@gmail.com'

   ,p_from => 'mailsender@dummydomain.com'

   ,p_subj => 'Test Notification Subject'

   ,p_body => 'Test Notification Body'

);

COMMIT;

APEX_MAIL.PUSH_QUEUE;

END;

/

Please note that we've used mailsender@dummydomain.com as the From Email as it was configured as an Approved Sender in OCI. Email delivery will fail if any other Email Id is used for From Email parameter.

- Once above steps are completed, the Email Notification should be successfully delivered to the designated mailbox:

Read More

How to utilize session variables inside BI publisher reports in Oracle Fusion Cloud

So, you are trying to create a custom report in Oracle Fusion based on it's older EBS version but the EBS query contains references to session variables like USER_ID, RESP_ID.

But in Fusion, we cannot derive such session/user specific information using profile options like we used to do in EBS.

Let's see how to derive this information in Fusion BI reports.

The BI Publisher stores information about the current user that can be accessed by the report data model.

The user information is stored in system variables as described below.

Note:
BI Publisher populates all the system variables in an online report.

In case of a scheduled job, publisher doesn’t populate below system variables:

XDO_USER_REPORT_LOCALE
XDO_USER_UI_LOCALE
XDO_USER_UI_ORACLE_LANG

If we want to add the user information to a custom data model, we can define the variables as parameters and then define the parameter value as an element in the data model.

We can also add the variables as parameters then reference the parameter values in the report.

For example, if we use below query in Data Model:

select
:xdo_user_name as USER_NAME,
:xdo_user_report_oracle_lang as REPORT_LANGUAGE,
:xdo_user_report_locale as REPORT_LOCALE,
:xdo_user_ui_oracle_lang as UI_LANGUAGE,
:xdo_user_ui_locale as UI_LOCALE
from dual

It will return below output:

XML Output:

<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Publisher - >
<DATA_DS>
<G_1>
<REPORT_LANGUAGE>US</REPORT_LANGUAGE>
<REPORT_LOCALE>en_US</REPORT_LOCALE>
<UI_LANGUAGE>US</UI_LANGUAGE>
<UI_LOCALE>en_US</UI_LOCALE>
<USER_NAME>john.doe</USER_ID>
</G_1>
</DATA_DS>

Now, let's see a practical example where we can use such derivations.

Suppose below is the business requirement:

- Identify the current user name

- Find out all roles associated with this user

- Find out all the business units associated with above role(s)

Then we would write below query to achieve this:

SELECT   DISTINCT bu.bu_name

 FROM     fusion.FUN_ALL_BUSINESS_UNITS_V     bu,

                 fusion.FUN_USER_ROLE_DATA_ASGNMNTS  role,

                 fusion.per_users pu

 WHERE role.org_id = bu.bu_id

AND        pu.USER_GUID = role.USER_GUID

AND        pu.username = :xdo_user_name

AND        role.active_flag = 'Y'

The above query can be extrapolated to further utilize this business unit information to link in a bigger derivation/query based on the requirements.

This is how we can make use of session/system variables in BI publisher report in Oracle Fusion.

Read More