1 / 40

Corralling APEX Applications in a Corporate Environment

Corralling APEX Applications in a Corporate Environment. Scott Chaplow HCL Technologies. Introduction. Scott Chaplow Systems Architect, HCL Technologies Level 4, ACC Building 18 London St Hamilton  3204 New Zealand +64 7 858 7129 +64 27 233 0615 scott.chaplow@hcl.com

symona
Download Presentation

Corralling APEX Applications in a Corporate Environment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Corralling APEX Applicationsin a Corporate Environment Scott Chaplow HCL Technologies

  2. Introduction Scott Chaplow Systems Architect, HCL Technologies Level 4, ACC Building 18 London St Hamilton  3204 New Zealand +64 7 858 7129 +64 27 233 0615 scott.chaplow@hcl.com scott.chaplow@fonterra.com

  3. HCL Overview Highlights Total Revenues $6.3 B Clients 500+ Employees 93,000 Countries 31 HCL Infosystems HCL Technologies MPE Asia Pacific BPO 7.6% 13.8% 5.0% Life Sciences 6.3% Engineering Services 19.0% 26.7% Others 6.9% Retail & CPG Europe 6.9% 21.4% Enterprise Apps E&U 8.9% Telecom 12.1% Diversifiedand De-Risked Portfolio 59.5% 25.5% 22.2% Financial Services Infrastructure Services US 29.9% 26.7% Custom Apps Manufacturing Service Line Mix Geo Mix Vertical Mix

  4. HCL in New Zealand NZ 300+ Consultants Auckland Hamilton Wellington • Locally registered since 1999 • 100 seat Development Centre in Auckland, offices in Hamilton and Wellington • 300+ onsite consultants • 200+ off-shore

  5. Fonterra APEX 2006 2007 2008 2009 2010 2011 2012 2013 Payroll Reporting DARSy Conv Cost Compliance System Ozone eProject INJMAN ASMR FTS Config Portal Requests RUCS ProFin WMLOG Operational Excellence Activity Mapping Rework APEX Portal Manu Capacity FSRPM Cost of Quality MFU Starter Culture ES WEBREM RX7 eBudget PCA A3 Training Portal FAM Data SNO WEBDOCS PMR Perform Reporting RFM / GSR Business Proc Upload Sheet BIPP RP Customer Visit Tool Request Tracker FSKAT MOMPA IS Report Data Load Value Portal PWMR OPT1 Bioscience Starter Culture

  6. Fonterra APEX Environments e-HR Payroll RX7 RX7 BPR-MDS Inform Rework RFM/ GSR MAX WEBREM e-HR Admin Payroll report ES eProject Value Portal WEBLEAVE Manu Cap BIPP APEX Portal ASMR Perform FS KAT WEBFORMS WEBREM FTS FSRPM Portal Request DARSy A3 Active Map Edit My Details PMR PCA WEB- DOCS RUCS Conv Cost Ozone INJMAN Comply WMLOG Train Portal SNO Oper Excel IS Data Load Visit Tool ProFin eBudget FAM Biosci Culture PWMR Cost Qual A3 OPT1 MFU Starter Request Tracker RP MOMPA Upload

  7. Application Examples • Developed over eight years by more than 30 developers • At least twelve APEX themes in use • Examples…

  8. The Problem • Variation • Twelve different themes • Duplication of effort • User access maintenance • Other functions • Lack of internal application security • No Authorization Schemes (security through obscurity) • Page Access Protection not enabled (URL tampering) • Report columns not escaping special characters (XSS) • Inappropriate use of &ITEM. syntax (SQL injection)

  9. The Journey Authentication Configuration Export / Import Shared Pages Shared security schema Import Template (base) Dropdown Menu Lookup Lists Auditing 2010 2011 2012 2013 Parameters User Security Tables & Functions Standard Admin Pages Import Template (pages) Single sign-on Jobs Security Assurance HR Data Authentication Access Administration

  10. The Vision Oracle APEX Database Shared Area security Shared Pages Security Application code data Manager ID Hire Date Preferred Name Last Name HR Data Person ID Position Organisation User Name Email Address Contact Details Termination Date Cost Centre Location

  11. The Result – A3 • Three areas of focus • Authentication • Access • Administration • Three Applications APEX Portal Shared Application A3 (Security Data) Application

  12. A3 Structure Shared Area (A3) A3 Application (A3A) Shared Pages (A30) User-selected Application’s Data

  13. A3 Features

  14. Authentication • Checks if there’s an outage • Refreshes user’s automatically assigned roles • Checks the user has access to the application • Randomly selects authentication host from list • Authenticates username and password

  15. Access – Security Structure Users Security Codes Roles Actions Pages

  16. Security Structure Range of Functionality Range of Data

  17. Access – Security Structure Users Security Codes Roles Actions Pages

  18. Application Security Functions

  19. Page Security Functions

  20. Administration – Security Structure Users Security Codes Roles Actions Audit Import Template Pages Parameter List Jobs

  21. Other Features • Standard Theme • Messages • Logging • Configuration Export and Import • Dropdown Menu • Single Sign-on • Shared Pages • APEX Portal • Security Assurance

  22. Standard Theme • Comply to Fonterra branding guidelines • Test all templates • Create guide on how each template should be used • Remove any extra templates

  23. Messages • Information and Outage messages • Use standard APEX notification variablesapex_application.g_notification (outage)apex_application.g_print_success_message (information)

  24. Logging • Standard functions for writing to log table • Debug message only generated if debugging switched on in APEX or a3_log_pkg.gv_debug is TRUE

  25. Configuration Export & Import • Configuration Export, by • Object type or specific object • Grouping of objects by change date • Entire application • Configuration Import

  26. Dropdown Menu • Started as a bit of “bling” for the applications • Integrated nicely with shared security • Integral for seamlessly adding shared pages

  27. Dropdown Menu Technical • Started with a Plugin from http://www.apex-plugin.com/ • Moved PL/SQL to shared schema • Moved images, CSS and JavaScript files to shared directory • Included menu HTML as JavaScript file with document.write(‘’); • Added page footer to shift last menu items left

  28. Single Sign-on Overview • Uses Session Initialization and Authentication Function • Triggered via the APEX request item f?p=App:Page:Session:Request:Debug:ClearCache:Items:Values:PrinterFriendly f?p=App:Page:Session:Request:Debug:ClearCache:Items:Values:PrinterFriendly A3-REDIRECT~Database~App~Page~Request~ClearCache~Items~Values

  29. APEX Login wwv_flow.accept ?p_flow_id=2001 &p_flow_step_id=101 &p_arg_names=Username-Item-ID &p_t01=username &p_arg_names=Password-Item-ID &p_t02=password Authentication Post Authentication Redirect to Home Page f?p=2001:1:95563177109636::NO:::: Authenticate to Active Directory

  30. Single Sign-on (new session) f?p=2001:1:95563177109636::NO:::: wwv_flow.accept ?p_flow_id=120 &p_flow_step_id=101 &p_request=A3-REDIRECT-LOGIN &p_arg_names=Username-Item-ID &p_t01=username &p_arg_names=Password-Item-ID &p_t02=A3-Redirect-key Authentication Redirect to Target URL f?p=120:4000:863177109636::NO:::: A3 Redirect Key Authenticate to Active Directory f?p=2001:1:955631877109636:A3-REDIRECT~MAX~120~4000~~~~:NO::::&cs=384D A3-REDIRECT~MAX~120~4000~~~~ Initialise Session (VPD) Post Authentication Redirect to login process on target application Generate A3 Redirect Key

  31. Single Sign-on (existing session) f?p=2001:1:95563177109636::NO:::: f?p=120:4000:863177109636::NO:::: f?p=120:4000:863177109636:A3-REDIRECT~MAX~2001~1~~~~:NO::::&cs=591X A3-REDIRECT~MAX~2001~1~~~~ Initialise Session (VPD) Redirect to target page in application reusing session Found Session ID 95563177109636 for App 2001 in Session Group

  32. Shared Pages • Original plan was to include a set of administration pages in the standard application template • Foundations • Consistent theme • Consistent variable naming • Shared security framework • Drop-down menu • Captures session state prior to accessing shared page • Shared application adopts security and session state of calling application

  33. APEX Portal • Home page for users listing the applications they have access to • Centralized reporting • Place for users to request further access

  34. Security Assurance • Report checks application is set up correctly • Checks compliance to the security standards • Authorization Scheme for entire application • Page Access Protection on • Report fields restrict HTML characters • &ITEM. Syntax not used in SQL queries • Checks page relationships

  35. APEX Base Tables • Tables available in the APEX_040000 schema (version 4.0) • Don’t alter these tables, or you’ll void your support

  36. Final Words

  37. Caveats • Applications are no longer stand-alone • Not using all standard features • References to base APEX tables

  38. Benefits • Application administration and support is easier • Application development is streamlined • Application security is assured • Application quality is improved • User access is controlled and auditable • User experience is consistent • Custom applications become trusted

  39. Questions

  40. Thanks www.hcl.com

More Related