Corralling APEX Applications in a Corporate Environment
Download
Report
Transcript 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
[email protected]
[email protected]
2
HCL Overview
Highlights
Total Revenues $6.3 B
Clients
500+
Employees
93,000
Countries
31
HCL Technologies
13.8%
26.7%
Asia
Pacific
Europe
Diversified and
De-Risked Portfolio
59.5%
Geo Mix
5.0%
6.3%
6.9%
6.9%
8.9%
12.1%
MPE
Life Sciences
Others
Retail & CPG
E&U
Telecom
25.5%
26.7%
HCL Infosystems
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
eProject
INJMAN
ASMR
Portal
Requests
RUCS
ProFin
WMLOG
Activity
Mapping
Rework
APEX Portal
FSRPM
Cost of
Quality
ES
WEBREM
eBudget
PCA
A3
FAM Data
SNO
WEBDOCS
FTS
Config
Manu
Capacity
Operational
Excellence
MFU Starter
Culture
RX7
RFM / GSR
Business Proc
Training
Portal
PMR Perform
Reporting
Upload Sheet
BIPP
RP
Customer
Visit Tool
Request
Tracker
FSKAT
MOMPA
IS Report
Data Load
Value Portal
PWMR
OPT1
Bioscience
Starter Culture
5
Fonterra APEX Environments
Payroll
Inform
BPRMDS
RX7
RX7
RFM/
GSR
WEBREM
e-HR
Payroll
Admin
WEBLEAVE
report
e-HR
WEBFORMS Perform
WEBREM
WEBDOCS
A3
Edit My
Details
BIPP
FS
KAT
FSRPM
DARSy
ASMR
Active
Map
IS
Data
Load
MAX
Train
SNO
Biosci
Culture
Portal
eBudget
A3
OPT1
RP
6
MFU
Starter
MOMPA
PCA
WMLOG
Visit
Tool
FAM
FTS
Conv
Cost
Comply
INJMAN
Manu
Cap
Portal
Request
PMR
RUCS
Ozone
ES
Value
Portal
eProject
APEX
Portal
Rework
Oper
Excel
ProFin
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 – A3
Three areas of focus
Authentication
Access
Administration
Three Applications
A3 (Security Data)
Application
Shared
Application
11
APEX Portal
A3 Structure
A3
Application
(A3A)
Shared
Area
(A3)
User-selected
Application’s
Data
12
Shared
Pages
(A30)
A3 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 Data
Range of Functionality
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 := a3_log_group( ‘Group’ );
a3_log_info( ‘Information’, v_group_id );
a3_log_debug( ‘Debug’, v_group_id );
a3_log_error( ‘Error’, v_group_id );
a3_log_warning( ‘Warning’, v_group_id );
Debug message only generated if debugging switched on in APEX
or a3_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 JavaScript files to shared directory
Included menu HTML as JavaScript 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:ClearCache:Items:Values:PrinterFriendly
A3-REDIRECT~Database~App~Page~Request~ClearCache~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_t01=username
Redirect
to Home Page
&p_arg_names=Password-Item-ID
Authenticate
to
&p_t02=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=A3-REDIRECT-LOGIN
Post Authentication
&p_arg_names=Username-Item-ID
Redirect
to Target URL
&p_t01=username
A3 Redirect
Authenticate
to
&p_arg_names=Password-Item-ID
Key
Active
Directory
f?p=120:4000:863177109636::NO::::
&p_t02=A3-Redirect-key
f?p=2001:1:955631877109636:A3-REDIRECT~MAX~120~4000~~~~:NO::::&cs=384D
A3-REDIRECT~MAX~120~4000~~~~
Initialise Session (VPD)
Generate A3
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::NO::::
f?p=120:4000:863177109636:A3-REDIRECT~MAX~2001~1~~~~:NO::::&cs=591X
A3-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