Corralling APEX Applications in a Corporate Environment Scott

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 scott. chaplow@fonterra. com 2

HCL Overview Highlights HCL Technologies Total Revenues Clients $6. 3 B 500+ Employees 93, 000 Countries 13. 8% 26. 7% Asia Pacific Europe Diversified and De-Risked Portfolio 59. 5% Geo Mix HCL Infosystems 31 5. 0% 6. 3% 6. 9% 8. 9% 12. 1% MPE Life Sciences Others Retail & CPG E&U Telecom 25. 5% 26. 7% 7. 6% BPO 19. 0% Engineering Services 21. 4% Enterprise Apps Financial Services 22. 2% Infrastructure Services Manufacturing 29. 9% Custom Apps US Vertical Mix 3 Service Line Mix

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 4

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

Fonterra APEX Environments Payroll Inform BPRMDS RX 7 WEBREM e-HR Payroll Admin WEBLEAVE report e-HR WEBFORMS Perform WEBREM WEBDOCS A 3 Edit My Details INJMAN e. Project APEX Portal BIPP ES Value Portal FS KAT ASMR Active Map SNO Biosci Culture Portal e. Budget A 3 OPT 1 RP 6 Visit Tool FAM MFU Starter MOMPA FTS PCA Conv Cost Comply MAX Train Manu Cap Portal Request PMR RUCS Ozone Rework FSRPM DARSy IS Data Load RFM/ GSR WMLOG Oper Excel Pro. Fin PWMR Cost Qual Request Tracker Upload

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

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) 8

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

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

The Result – A 3 § Three areas of focus § Authentication § Access § Administration § Three Applications A 3 (Security Data) Application Shared Application 11 APEX Portal

A 3 Structure A 3 Application (A 3 A) Shared Area (A 3) User-selected Application’s Data 12 Shared Pages (A 30)

A 3 Features 13

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 14

Access – Security Structure Users Security Codes Roles Actions Pages 15

Security Structure Range of Functionality Range of Data 16

Access – Security Structure Users Security Codes Roles Actions Pages 17

Application Security Functions 18

Page Security Functions 19

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

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

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

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

Logging § Standard functions for writing to log table Procedure / Function v_group_id : = a 3_log_group( ‘Group’ ); a 3_log_info( ‘Information’, v_group_id ); a 3_log_debug( ‘Debug’, v_group_id ); a 3_log_error( ‘Error’, v_group_id ); a 3_log_warning( ‘Warning’, v_group_id ); § Debug message only generated if debugging switched on in APEX or a 3_log_pkg. gv_debug is TRUE 24

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

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

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

Single Sign-on Overview § Uses Session Initialization and Authentication Function § Triggered via the APEX request item f? p=App: Page: Session: Request: Debug: Clear. Cache: Items: Values: Printer. Friendly A 3 -REDIRECT~Database~App~Page~Request~Clear. Cache~Items~Values 28

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

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

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

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 32

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

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 34

APEX Base Tables § Tables available in the APEX_040000 schema (version 4. 0) § Don’t alter these tables, or you’ll void your support Workspaces WWV_FLOW_COMPANIES Pages WWV_FLOW_STEPS Workspace Schemas WWV_FLOW_COMPANY_SCHEMAS Page Regions WWV_FLOW_PAGE_PLUGS Workspace Users WWV_FLOW_FND_USER Page Region Columns WWV_FLOW_REGION_REPORT_COLUMN Applications WWV_FLOWS Interactive Reports WWV_FLOW_WORKSHEETS Application Processes WWV_FLOW_PROCESSING Interactive Report Columns WWV_FLOW_WORKSHEET_COLUMNS Application Items WWV_FLOW_ITEMS Page Buttons WWV_FLOW_STEP_BUTTONS Authentication Schemes WWV_FLOW_CUSTOM_AUTH_SETUPS Page Items WWV_FLOW_STEP_ITEMS Authorization Schemes WWV_FLOW_SECURITY_SCHEMES Page Processes WWV_FLOW_STEP_PROCESSING Parent Tabs WWV_FLOW_TOPLEVEL_TABS Page Branches WWV_FLOW_STEP_BRANCHES Standard Tabs WWV_FLOW_TABS APEX Activity Log WWV_FLOW_ACTIVITY_LOG 35

Final Words 36

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

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 38

Questions 39

Thanks www. hcl. com