Expert Oracle Application Express Security

285 Pages·2013·13.02 MB·English

by  Scott Spendolini (auth.)

Exper t Oracle Application
Express Security
Scott Spendolini
Apress
Expert Oracle Application Express Security
Copyright © 2013 by Scott Spendolini
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material
is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting,
reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval,
electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed.
Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material
supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the
purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the
Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from
Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are
liable to prosecution under the respective Copyright Law.
ISBN 978-1-43 02-4731-9
ISBN 978-1-4302-4732-6(eBook)
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion
and to the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified
as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication, neither
the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may
be made. The publisher makes no warranty, express or implied, with respect to the material contained herein.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewer: Alex Fatkulin
Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel, Jonathan Gennick,
Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson,
Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan Spearing, Matt Wade,
Tom Welsh
Coordinating Editor: Kevin Shea
Copy Editor: Kim Wimpsett
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [email protected], or visit
www.springeronline.com.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook
versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook
Licensing web page at www.apress.com/bulk-sales.
Any source code or other supplementary materials referenced by the author in this text is available to readers
at www.apress.com. For detailed information about how to locate your book’s source code, go to
www.apress.com/source-code.
To my wife Shannon, who has always stood by and supported me in my career and in life.
—Scott Spendolini
Contents at a Glance
Foreword ............................................................................................................................xv
About the Author ..............................................................................................................xvii
About the Technical Reviewer ...........................................................................................xix
Acknowledgments .............................................................................................................xxi
Introduction .....................................................................................................................xxiii
(cid:78) Chapter 1: Threat Analysis ................................................................................................1
(cid:78) Chapter 2: Implementing a Security Plan ..........................................................................7
(cid:78) Chapter 3: APEX Architecture ..........................................................................................15
(cid:78) Chapter 4: Instance Settings ...........................................................................................41
(cid:78) Chapter 5: Workspace Settings .......................................................................................89
(cid:78) Chapter 6: Application Settings .....................................................................................101
(cid:78) Chapter 7: Application Threats ......................................................................................129
(cid:78) Chapter 8: User Authentication .....................................................................................159
(cid:78) Chapter 9: User Authorization .......................................................................................177
(cid:78) Chapter 10: Secure Export to CSV .................................................................................185
(cid:78) Chapter 11: Secure Views .............................................................................................201
(cid:78) Chapter 12: Virtual Private Database ............................................................................211
(cid:78) Chapter 13: Shadow Schema ........................................................................................225
(cid:78) Chapter 14: Encryption ..................................................................................................247
Index .................................................................................................................................265
v
Contents
Foreword ............................................................................................................................xv
About the Author ..............................................................................................................xvii
About the Technical Reviewer ...........................................................................................xix
Acknowledgments .............................................................................................................xxi
Introduction .....................................................................................................................xxiii
(cid:78) Chapter 1: Threat Analysis ................................................................................................1
Assessment ...................................................................................................................................1
Home Security Assessment ...................................................................................................................................1
Application Security Assessment ..........................................................................................................................2
Data and Privileges ................................................................................................................................................3
Types of Threats ............................................................................................................................4
Preventable ............................................................................................................................................................4
Unpreventable .......................................................................................................................................................6
Summary .......................................................................................................................................6
(cid:78) Chapter 2: Implementing a Security Plan ..........................................................................7
What Is a Security Plan? ...............................................................................................................7
Assessment ...................................................................................................................................8
Risk Analysis ..........................................................................................................................................................8
Access Control .......................................................................................................................................................8
Data Access ...........................................................................................................................................................9
Auditing and Monitoring ........................................................................................................................................9
Application Management .......................................................................................................................................9
Design ...........................................................................................................................................9
vii
(cid:78) CONTENTS
Development ...............................................................................................................................10
Contingency .................................................................................................................................10
Review and Revision ...................................................................................................................11
Security Reviews .........................................................................................................................11
Automated Reviews .............................................................................................................................................11
Manual Reviews ..................................................................................................................................................12
Simulating a Breach ....................................................................................................................12
Summary .....................................................................................................................................13
(cid:78) Chapter 3: APEX Architecture ..........................................................................................15
Overview of APEX ........................................................................................................................15
Administration Console ...............................................................................................................17
Managing Requests .............................................................................................................................................18
Managing Instances ............................................................................................................................................19
Managing Workspaces ........................................................................................................................................19
Monitoring Activity ...............................................................................................................................................19
Workspaces .................................................................................................................................20
Users and Roles ...................................................................................................................................................20
Schema Mappings ...............................................................................................................................................22
Components .........................................................................................................................................................22
Architecture .................................................................................................................................26
Metadata-Based Architecture ..............................................................................................................................26
Schemas ..............................................................................................................................................................27
Transactions ................................................................................................................................32
The f Procedure and WWV_FLOW.SHOW .............................................................................................................32
The WWV_FLOW.ACCEPT Procedure ....................................................................................................................33
Session State .......................................................................................................................................................36
Infrastructure ..............................................................................................................................38
Embedded PL/SQL Gateway ................................................................................................................................38
Oracle HTTP Server and mod_plsql .....................................................................................................................39
APEX Listener ......................................................................................................................................................39
Summary .....................................................................................................................................40
viii
(cid:78) CONTENTS
(cid:78) Chapter 4: Instance Settings ...........................................................................................41
Overview .....................................................................................................................................41
Runtime Mode .....................................................................................................................................................42
The Instance Administration API ..........................................................................................................................43
The Instance Administrator Database Role ..........................................................................................................43
Other Options .......................................................................................................................................................44
Configuration and Management ..........................................................................................................................44
Manage Instance Settings ...........................................................................................................45
Feature Configuration ..........................................................................................................................................47
Security ...............................................................................................................................................................48
Instance Configuration Settings ..........................................................................................................................56
Session State .......................................................................................................................................................60
Logs and Files ......................................................................................................................................................62
Messages ............................................................................................................................................................63
Self Service Sign Up ............................................................................................................................................64
Manage Workspaces ...................................................................................................................64
Create Workspace ................................................................................................................................................65
Create Multiple Workspaces ................................................................................................................................68
Remove Workspace .............................................................................................................................................70
Lock Workspace ..................................................................................................................................................71
Manage Workspace to Schema Assignments ......................................................................................................72
Manage Developers and Users ............................................................................................................................73
Manage Component Availability ..........................................................................................................................75
Export and Import ................................................................................................................................................76
View Workspace Reports .....................................................................................................................................76
Manage Applications ...........................................................................................................................................78
View Application Attributes ..................................................................................................................................78
Monitor Activity ...........................................................................................................................80
Realtime Monitor Reports ....................................................................................................................................80
Archived Activity Reports .....................................................................................................................................87
Dashboard Report ................................................................................................................................................87
Summary .....................................................................................................................................88
ix
(cid:78) CONTENTS
(cid:78) Chapter 5: Workspace Settings .......................................................................................89
Manage Service ..........................................................................................................................89
Service Requests .................................................................................................................................................90
Workspace Preferences .......................................................................................................................................91
Manage Meta Data ..............................................................................................................................................92
Manage Users and Groups ..........................................................................................................94
User Types ...........................................................................................................................................................95
Managing Users ...................................................................................................................................................96
Managing Groups ................................................................................................................................................98
Monitor Activity ...........................................................................................................................99
Workspace Management Best Practices ...................................................................................100
Summary ...................................................................................................................................100
(cid:78) Chapter 6: Application Settings .....................................................................................101
Application Settings ..................................................................................................................101
Definition ...........................................................................................................................................................101
Security Attributes .............................................................................................................................................108
User Interface ....................................................................................................................................................117
Page and Region Settings .........................................................................................................118
Page Settings ....................................................................................................................................................118
Region Settings .................................................................................................................................................124
Report Settings ..................................................................................................................................................126
Mobile Applications ...................................................................................................................127
Hesitancy Toward Corporate Adoption ...............................................................................................................127
Mobile Considerations for Security ....................................................................................................................127
Summary ...................................................................................................................................128
(cid:78) Chapter 7: Application Threats ......................................................................................129
SQL Injection .............................................................................................................................129
Anatomy of an Attack ........................................................................................................................................130
SQL Injection in APEX ........................................................................................................................................133
Bind Variable Notation and Dynamic SQL in APEX .............................................................................................136
x
(cid:78) CONTENTS
Cross-Site Scripting ..................................................................................................................139
Anatomy of an Attack ........................................................................................................................................140
Reflexive Attacks ...............................................................................................................................................140
Persistent Attacks ..............................................................................................................................................143
Sanitizing Data ..........................................................................................................................144
Restricted Characters ........................................................................................................................................145
APEX_ESCAPE ....................................................................................................................................................145
Column Formatting ............................................................................................................................................146
Escaping Regions and Items .............................................................................................................................151
Protecting Cookies .............................................................................................................................................152
Frames ...............................................................................................................................................................152
URL Tampering ..........................................................................................................................153
Authorization Inconsistencies ............................................................................................................................153
Page and Item Protection ..................................................................................................................................154
Virtual Private Database and Secure Views .......................................................................................................157
Summary ...................................................................................................................................158
(cid:78) Chapter 8: User Authentication .....................................................................................159
Types of Authentication Schemes .............................................................................................159
Application Express Users .................................................................................................................................160
Database Accounts ............................................................................................................................................160
HTTP Header Variable ........................................................................................................................................160
LDAP Directory ...................................................................................................................................................162
No Authentication (Using DAD) ..........................................................................................................................162
Open Door Credentials .......................................................................................................................................163
Oracle Application Server Single Sign-On .........................................................................................................163
Custom ..............................................................................................................................................................163
APIs for Custom Authentication .........................................................................................................................165
Common Authentication Scheme Components .........................................................................166
Source ...............................................................................................................................................................166
Session Not Valid ...............................................................................................................................................167
Login Processing ...............................................................................................................................................167
xi