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-4302-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 (201) 348-4505, 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 N Chapter 1: Threat Analysis ................................................................................................1 N Chapter 2: Implementing a Security Plan ..........................................................................7 N Chapter 3: APEX Architecture ..........................................................................................15 N Chapter 4: Instance Settings ...........................................................................................41 N Chapter 5: Workspace Settings .......................................................................................89 N Chapter 6: Application Settings .....................................................................................101 N Chapter 7: Application Threats ......................................................................................129 N Chapter 8: User Authentication .....................................................................................159 N Chapter 9: User Authorization .......................................................................................177 N Chapter 10: Secure Export to CSV .................................................................................185 N Chapter 11: Secure Views .............................................................................................201 N Chapter 12: Virtual Private Database ............................................................................211 N Chapter 13: Shadow Schema ........................................................................................225 N Chapter 14: Encryption ..................................................................................................247 Index .................................................................................................................................265 v
Contents Foreword ............................................................................................................................ xv About the Author .............................................................................................................. xvii About the Technical Reviewer ........................................................................................... xix Acknowledgments ............................................................................................................. xxi Introduction ..................................................................................................................... xxiii N 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 N 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
N CONTENTS
Development ...............................................................................................................................10 Contingency.................................................................................................................................10 Review and Revision ...................................................................................................................11 Security Reviews .........................................................................................................................11 Automated Reviews ............................................................................................................................................. 11 Manual Reviews .................................................................................................................................................. 12
Simulating a Breach ....................................................................................................................12 Summary .....................................................................................................................................13 N 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
N CONTENTS
N 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
N CONTENTS
N 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 N 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 N 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
N 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 N 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
N CONTENTS
Post Logout URL ................................................................................................................................................ 168 Session Cookie Attributes .................................................................................................................................. 168
Mechanics of Authentication .....................................................................................................169 The Login Page .................................................................................................................................................. 169 Login Page Processes........................................................................................................................................ 170 Logging Out ....................................................................................................................................................... 174
Summary ...................................................................................................................................175 N Chapter 9: User Authorization .......................................................................................177 Authorization Schemes .............................................................................................................177 Implementing Authorization Schemes.......................................................................................179 Role Location ..................................................................................................................................................... 179 Table-Based Roles ............................................................................................................................................. 179 Gatekeeper Authorization Scheme .................................................................................................................... 180 Page-Level Authorization Schemes ................................................................................................................... 180 Authorization Inconsistencies............................................................................................................................ 182
APEX Access Control .................................................................................................................183 Summary ...................................................................................................................................184 N Chapter 10: Secure Export to CSV .................................................................................185 APEX Export Options..................................................................................................................185 Maximum Row Count ........................................................................................................................................ 185 Column Restrictions: Standard Reports ............................................................................................................. 187 Column Restrictions: Interactive Reports .......................................................................................................... 187
Custom Export to CSV................................................................................................................188 Restricting Records with ROWNUM ................................................................................................................... 188 Restricting Records with PL/SQL ....................................................................................................................... 190
Summary ...................................................................................................................................200
xii
N CONTENTS
N Chapter 11: Secure Views .............................................................................................201 The View ....................................................................................................................................201 Secure View Components..........................................................................................................202 Application Contexts .......................................................................................................................................... 203 PL/SQL Procedure .............................................................................................................................................. 203 Secure View SQL ............................................................................................................................................... 204 Security Attributes ............................................................................................................................................. 206
Benefits and Drawbacks ...........................................................................................................208 Summary ...................................................................................................................................209 N Chapter 12: Virtual Private Database ............................................................................211 The Evolution of Data ................................................................................................................211 VPD Basics ................................................................................................................................212 Integration with APEX ................................................................................................................212 VPD Policy Function ........................................................................................................................................... 213 Column Masking and Obfuscation ..................................................................................................................... 215
Managing VPD in Oracle Enterprise Manager............................................................................222 Summary ...................................................................................................................................223 N Chapter 13: Shadow Schema ........................................................................................225 Overview ...................................................................................................................................225 Components ..............................................................................................................................226 Database: Schema and Object Creation ............................................................................................................ 226 Data Schema: Views .......................................................................................................................................... 228 Revoke Privileges .............................................................................................................................................. 229 System and User Event Trigger.......................................................................................................................... 230 APEX: Simple Form and Report.......................................................................................................................... 231 DML APIs and Processes ................................................................................................................................... 232 Grants and Synonyms ........................................................................................................................................ 238 Table API Processes ........................................................................................................................................... 238
xiii
N CONTENTS
Securing Data ............................................................................................................................242 Application Context............................................................................................................................................ 242 Views ................................................................................................................................................................. 244 Synonym ............................................................................................................................................................ 244 PL/SQL Initialization Code.................................................................................................................................. 245
Summary ...................................................................................................................................246 N Chapter 14: Encryption ..................................................................................................247 Encryption .................................................................................................................................247 HTTPS ........................................................................................................................................248 APEX HTTPS Settings ................................................................................................................251 Instance Admin Console and Application Development Environment................................................................ 251 Applications ....................................................................................................................................................... 251
APEX Item Encryption ................................................................................................................252 Data Encryption ................................................................................................................................................. 255 DBMS_CRYPTO .................................................................................................................................................. 255 Encrypted Collections ........................................................................................................................................ 256 Example ............................................................................................................................................................. 257
Advanced Security Option .........................................................................................................262 Transparent Data Encryption ............................................................................................................................. 263 Network Encryption ........................................................................................................................................... 263
Summary ...................................................................................................................................263 Index .................................................................................................................................265
xiv
Foreword In May of 1999, Oracle Application Express was begun. I was the only direct report to a great visionary at Oracle Corporation, Michael Hichwa. His passion and creativity led to Oracle Application Express, and I’ve been proud to be directly involved in the development of this rich framework since day one. I’ve also had the pleasure to work directly with Scott Spendolini when he was a product manager on the Oracle Application Express team (in the “early years”). I credit Scott and the other product managers for making Oracle Application Express so successful. They were tireless in pitching and demonstrating Oracle Application Express to anyone who would listen. They helped to cultivate the APEX community, engaged them in social media, and ensured that the customer’s requirements and concerns were addressed in subsequent releases of Oracle Application Express. They authored countless tutorials, white papers, and presentations to help convince customers of the power and benefits of Oracle APEX. Scott was so enthused with Oracle Application Express that he formed a company focused solely on Oracle Application Express solutions. He and his colleagues are directly responsible for the successful delivery of solutions for numerous, high profile, large-scale, and security-conscious customers. It is these repeated solutions and repeated security requirements that inspired Scott and his colleagues to author a tool to evaluate and identify possible security issues in Oracle Application Express applications. Scott has years of experience in understanding and assessing the myriad of security vulnerabilities that are possible in Web applications, and in Oracle Application Express environments, in particular. For many years, it seemed as if security in software development was an afterthought. It was always “build it and assess later,” or as my father would always say, “there’s always enough time and money to do it right the second time.” But this mentality needs to change—security must be a part of the development process and everyone must be conscious of this during design, development, testing and maintenance. There should be a secure coding guidelines document. There must be rules, and equally important, there must be the ability to continually assess whether an application or code violates those rules. The knowledge and experience conveyed in this book will empower the reader to establish this understanding and mindset. System and database administrators are tasked with setting up and managing Oracle Application Express environments, very often with little to no knowledge of APEX, how it works, how to monitor it, how to diagnose it, or how to secure it. To understand APEX is to understand the architecture, and Scott provides a very lucid and complete overview of the architecture of Oracle Application Express, how it’s organized, and why it is so efficient. Additionally, administrators of an APEX environment are provided with a wealth of options and controls to tweak the Oracle Application Express infrastructure. While the typical Oracle documentation will explain what something is, the chapter on instance settings also answers the anticipated questions of why you would want to change something. How many security vulnerabilities are considered “too many” in an application? One hundred? Ten? Five? I live by the rule that one is too many for the Oracle Application Express framework, because all it takes is a single vulnerability to provide an entrance to a malicious hacker. Once the entrance is established, it can be used to exploit other deficiencies in your application environment. But before you can understand how to assess the security of an application, you first must understand what types of exploits can be perpetrated against an application and environment and how to protect against them. Scott does an excellent job of explaining the type of threats that are possible and conveys very practical solutions to combat these threats.
xv
N FOREWORD
In 1999 when Oracle Application Express was begun, the