Note:

Enable Excel-to-Component Interface Utility SSO with OCI IAM using Device Code Flow

Introduction

The Excel-to-Component Interface (Excel to CI) utility in PeopleSoft serves as an invaluable tool, enabling users to seamlessly import data from Microsoft Excel spreadsheets into the PeopleSoft database through standard username and password authentication. Each source workbook comprises both worksheets and Excel Visual Basic code modules that execute essential business logic for each transaction. For more information, see Understanding the Excel-to-Component Interface Utility.

With the rapid advancements in Oracle Cloud Infrastructure Identity and Access Management (OCI IAM), the demand for applications like PeopleSoft to support Single Sign-On (SSO) became increasingly vital. This evolution reflects the necessity for streamlined user experiences, enhanced security, and simplified access across multiple platforms, allowing users to navigate seamlessly while maintaining robust authentication protocols.

OCI IAM serves as a robust framework for managing users and roles, facilitating user federation and provisioning, and enabling secure application integration through Oracle SSO configuration and Security Assertion Markup Language (SAML)/OAuth-based Identity Provider (IdP) administration. Notably, it features Oracle Identity Domains App Gateway, which supports Single Sign-On (SSO) for applications utilizing Header-Based Authentication, such as PeopleSoft. For more information, see Configure seamless authentication for PeopleSoft applications using OCI IAM Identity Domains.

In this tutorial, we will explore the transformation of the traditional Excel to CI sheet, modernizing it to support standard authentication protocols. We will delve into how OCI IAM SSO can be utilized to authenticate users, enabling seamless data uploads through these enhanced spreadsheets.

PeopleSoft SSO integration with OCI IAM

PeopleSoft integrates with OCI IAM for SSO through the App Gateway, which functions as a reverse proxy. It intercepts HTTP requests to the PeopleSoft web interface, ensuring users are logged in and authorized. The OCI IAM Identity Domain manages authentication for the PeopleSoft application.

Introduction to OCI IAM Support for Device Code Flow

Device Code Flow enables user authentication on devices or operating systems lacking a web browser. This method allows users to sign in interactively using another device, such as a computer or mobile phone. The following image illustrates how the Device Code Flow operates.

Device Code Flow

For more information about Device Code Flow and its configuration within OCI IAM, see Using the IDCS OAuth Device Flow for Fun and Profit and Device Code Grant Type.

Additionally, we understand that the Device Code Flow is compatible with Microsoft Excel spreadsheets. For more information, see Excel, OAuth 2.0 and Device Code Flow. We will enhance the traditional Excel to CI spreadsheet to support Device Code Flow. Let us begin by creating a confidential application in OCI IAM to facilitate this integration.

Objectives

Prerequisites

Task 1: Create a Confidential Application for Device Code Flow

We will register a confidential application under the OCI IAM Identity Domain which is integrated with your PeopleSoft for SSO with the allowed grants type Device Code.

  1. Log in to the OCI Console, under Identity Domain, click Domain Information and copy Identity Domain URL.

    domain-url

    Note: Exclude :443 from the end of the domain URL, we will need this information in Task 6.

  2. Go to the navigation menu and click Applications.

    add-application

  3. Select Confidential Application and click Launch workflow.

    create-confidential-application

  4. Enter a Name for your application and click Next.

    app-name

  5. Select Skip for later, Device Code as the Allowed grant types and click Next.

    grant-type

  6. Click Finish.

    finish

  7. Click Activate to activate the application.

    activate

  8. Note down the Client ID and Client Secret for using it in further configurations.

    client-id-secret

Task 2: Excel to CI and OCI IAM Architecture using Device Code Flow

Steps to submit data to the PeopleSoft database using Device Code Flow.

  1. Initiation of Device Code Flow: When the user attempts to submit data to the PeopleSoft database, the Excel sheet initiates the Device Code Flow.

  2. User Code Display: A 6-letter user code is presented to the user, which must be copied for future use in obtaining the access token.

  3. Browser Authentication: Once the user code is noted, the Excel sheet automatically opens the default web browser and prompts the user to log in. If a session is already active, the user will be logged in automatically.

  4. Entering the User Code: After successfully logging in and completing any multi-factor authentication (MFA) requirements, the browser will prompt the user to enter the previously copied user code.

  5. Access Token Issuance: Upon successfully submitting the user code, an access token is issued to the Excel sheet, enabling it to submit data to the PeopleSoft database.

The following image illustrates the Device Code flow in more detials.

Flow Diagram

To facilitate this process, we will proceed to modify the VB code and enhance the authentication and data submission modules.

Task 3: Update the VB Code for Log in using Device Code Flow

Download a copy of the Excel to CI spreadsheet from here: ExcelToCI Sheets_ExcelToCI2007-AppG_SampleFile.zip and save to your local system where you have your original Excel to CI spreadsheet. The folder should also contain the dependent file RelLangMacro.xla for proper functioning of Excel to CI.

  1. Open your Excel to CI spreadsheet and update the Connection Information sheet with the PeopleSoft App Gateway details. Enter the following information.

    • Web Server Machine Name.
    • Protocol.
    • HTTP Port.

    Connection Information

  2. Click Developer and Visual Basic to open MicroSoft Visual Basic for Applications. Click Modules.

    Modules

  3. Under Modules, expand the Login module and update the Client ID and Client secret of the confidential application created in Task 1 and OCI IAM Domain URL in the following mentioned functions and save the file.

    Note: The spreadsheet has comments to help you with where the details should be added.

    1. Public Function getLoginInformation.
    2. Public Function makeInstrospectRequest.
    3. Public Function makeIDCSRequest.

    Note:

    • Proof of Concept: This sheet serves as a proof of concept demonstrating that Excel to CI can support Device Code Flow for submitting data to the PeopleSoft database.

    • Code Quality: Note that the code may not adhere to best practices in VB coding. We strongly recommend thorough testing on your end before deploying it to a Production environment.

    • Data Submission Testing: The spreadsheet has been tested for data submission use case. If it defaults to username and password authentication for any another functionality, you can modify the VB module to incorporate the device code logic for improved functionality.

    • Reference Check: Additionally, review the Excel to CI reference under the Tools in the Microsoft Visual Basic for Applications page to ensure alignment with the following screenshot.

      Reference

Task 4: Test the Data Upload using Device Code Flow

  1. Add sample data in the Data Input and click Add-ins, Stage Data for Submission.

    Sample Data

  2. In the Staging and Submission sheet, you should be able to see the new data entries that you want to submit to the PeopleSoft database. Click Add-ins and Submit Data.

    Submit Data

  3. The Excel sheet will now initiate the Device Code Flow and will display the User Code, make a note of it. Click OK and you will be redirected to the OCI IAM log in page.

    Initiate Device Code

    User Code

  4. Log in to OCI IAM with your SSO credentials.

    Federated Login

  5. Enter the user code in Code and click Submit.

    Enter User Code

  6. If the user code is correct, you will see a Congratulations screen confirming your access to the application.

    Access Granted

  7. The access token is sent back to the Excel sheet, which is used to submit the data to PeopleSoft database. You should see OK against your data entries confirming the data submission.

    Data Submitted

    The sheet will store the access token details for its next execution and unless the access token is Active, you will not be challenged to go through the Device Code Flow.

Opportunities for Enhancement in the Excel to CI Sheet

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.