Expert Oracle Application Express Security

286 Pages·2013·18.693 MB·English

by  Scott Spendolini

www.it-ebooks.info
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
www.it-ebooks.info
Contents at a Glance
Foreword ����������������������������������������������������������������������������������������������������������������������������xv
About the Author ��������������������������������������������������������������������������������������������������������������xvii
About the Technical Reviewer �������������������������������������������������������������������������������������������xix
Acknowledgments �������������������������������������������������������������������������������������������������������������xxi
Introduction ���������������������������������������������������������������������������������������������������������������������xxiii
■ Chapter 1: Threat Analysis ������������������������������������������������������������������������������������������������1
■ Chapter 2: Implementing a Security Plan ��������������������������������������������������������������������������7
■ Chapter 3: APEX Architecture ������������������������������������������������������������������������������������������15
■ Chapter 4: Instance Settings �������������������������������������������������������������������������������������������41
■ Chapter 5: Workspace Settings ���������������������������������������������������������������������������������������89
■ Chapter 6: Application Settings �������������������������������������������������������������������������������������101
■ Chapter 7: Application Threats ��������������������������������������������������������������������������������������129
■ Chapter 8: User Authentication �������������������������������������������������������������������������������������159
■ Chapter 9: User Authorization ���������������������������������������������������������������������������������������177
■ Chapter 10: Secure Export to CSV ���������������������������������������������������������������������������������185
■ Chapter 11: Secure Views ���������������������������������������������������������������������������������������������201
■ Chapter 12: Virtual Private Database ����������������������������������������������������������������������������211
■ Chapter 13: Shadow Schema ����������������������������������������������������������������������������������������225
■ Chapter 14: Encryption ��������������������������������������������������������������������������������������������������247
Index ���������������������������������������������������������������������������������������������������������������������������������265
v
www.it-ebooks.info
Introduction
Security is hard. If it’s easy, then it’s wrong.
Application security is on the forefront of everyone’s minds these days. It’s almost impossible to go more than a couple
of days without reading about another website organization that was hacked or had a data breach. Unfortunately, it
seems as if the problem is getting worse with time, not better. This can be attributed directly to the fact that there are
simply more people using computers, iPhones and the like today, thus increasing the number of attack vectors for the
bad guys.
There is a simple answer for this: severely limit access to information systems. This is, of course, not the best
answer, but it clearly would mitigate the problem down to a manageable chunk. Unfortunately, users will always need
access to data, and as developers, the responsibility of delivering this task in a secure fashion falls squarely on our
shoulders.
Therefore, developers need to build applications that are much more secure today than in the past. But given the
workload of the average developer (read: overworked), securing applications is often done hastily right before turning
over code to production, if ever at all. As a result of this, more insecure applications are put into production, which
leads to more breaches and data leaks.
To compound the problem, developers coming from older client server technologies often don’t have the
background in web development to even understand what secure is and what it is not. The concept of an end-user
being able to manipulate where they go via the URL or view the source code of a page is completely foreign to them.
Their lack of knowledge often leads them down the path of building web applications that are simply not secure, as
they simply don’t know what secure looks like.
As more business turn to the web and mobile technologies to enable their customers and employees to access
information, more applications that represent potential security vulnerabilities are created, thus giving hackers and
even malicious users more places to attack.
Oracle APEX is not unique, in that like any other web technology, applications can be developed with it in either
a secure or not-so-secure manner.
About This Book
The focus of this book is to cover the best practices required to develop secure APEX applications. It is important
to understand that the focus is at the APEX level itself, and does not go into great detail about how to secure your
database, web server, network and other parts of your infrastructure from other types of attacks. Each one of those
components will also need to be secured and monitored as well, but the specifics of doing so are simply out of scope
for this book.
This book is based on APEX 4.2, the latest version of APEX as of the publishing date. Many of the concepts
discussed also apply to prior versions of APEX in whole or in part, while some are unique to APEX 4.2.
The book is broken out into four sections, as follows:
xxiii
www.it-ebooks.info
■ IntroduCtIon
Security Planning & Assessment
Comprehensive security for any application in any environment will not just happen. It takes a great deal of time,
planning and forethought to ensure that the end result is as secure as it needs to be. Thus, a secure APEX application
starts with a comprehensive and thorough security plan.
Chapters 1 and 2 will illustrate the potential threats to your applications and how to mitigate each, and how
that can be translated into a security plan. It will also highlight what needs to be done to ensure that the security
plan is being implemented, and techniques for testing the plan. It provides guidance as to which questions to ask
to determine how much security needs to be applied to each application in your infrastructure.
APEX Security
Much of the steps in the other sections won’t matter if you don’t spend time securing APEX at the APEX level itself.
Chapters 3, 4, 5 and 6 will cover APEX security from three levels—the instance of APEX itself, the workspace, and
finally, the application. Many security threats can easily by eliminated by changing simple settings. The challenge,
of course, is to make sure that all of the settings are properly set, and understand the risk of setting them improperly.
User Access
All of the APEX security best practices in the world will do little good if users can roam anywhere they want once they
are authenticated. APEX does provide good controls to implement a solid user access policy, but these control need
to be enabled, configured and verified. Chapters 8 and 9 will discuss the different authentication and authentication
options that are available to APEX applications.
Data Access & Protection
Often times, data access is one of the last things that a developer considers when building an APEX application. While
APEX also provides some options to assist with users accessing records that they are not supposed to see, it’s not
always the best approach. Chapters 10, 11, 12, 13 and 14 cover some techniques that you can employ in your
application to ensure that data can only be seen by those who should see it.
Downloading the Code
The code for the examples shown in this book is available on the Apress web site, www.apress.com. A link can be found
on the book’s information page under the Source Code/Downloads tab. This tab is located underneath the Related
Titles section of the page.
Contacting the Author
Should you have any questions or comments—or even spot a mistake you think I should know about—you can
contact the author at [email protected].
xxiv
www.it-ebooks.info
Chapter 1
Threat Analysis
No two applications are exactly alike. Thus, the security required to protect one application is likely different—either
vastly or slightly—from that required for any other application. Determining to secure your application starts with
a proper assessment of the risk posed and corresponding threats. The upcoming section on “Assessment” goes into
detail on how to initiate your thinking about security.
As part of this assessment, it may help to classify threats into one of two categories: preventable and
unpreventable. The difference between and details of these threats are detailed in the section “Types of Threats.”
Assessment
How much security is enough? There is only one correct answer to this question: it depends. Unfortunately, that
answer doesn’t really answer the question.
Choosing how much security to apply to an application largely depends on a number of factors, including
• what you’re protecting,
• whom you are protecting it from,
• the likelihood that someone wants to steal your data or compromise your system, and
• the repercussions you would face in the case of a breach.
A helpful, easy-to-understand analogy to application security is home security. Most concepts in home security
can easily be translated to application security, especially during the analysis and mitigation phase.
Home Security Assessment
Consider the example of choosing how to provide adequate security for your home. The answer to the first question
is simple—you’re protecting your home, condominium, or apartment, a physical piece of property or real estate with
defined boundaries.
Next question: whom are you protecting your home from? That’s where “it depends” comes into play. Before
you can answer this question, you must ask yourself a number of others: How safe is the neighborhood? Is there a
history of people breaking into homes in the area? If you’re in an apartment, do you have a doorman or other security
personnel? If so, do you trust them? The answers to these additional questions will help guide you in answering the
initial one.
The third question—what is the likelihood that someone will break into your home?—also needs to be thought
through and relevant facts and opinions applied in order to arrive at an answer. If you live in a part of town that has
a history of break-ins, obviously the likelihood will be greater. If your property is in a rural gated community in a part
of town with less crime, then the likelihood will not be as great. When answering this question, it is important to also
consider “crimes of opportunity.” Even in the best neighborhoods, an iPod or GPS unit sitting in an unlocked car
presents an opportunity to otherwise honest people to make the wrong decision.
1
www.it-ebooks.info
Chapter 1 ■ threat analysis
Lastly—and in many ways, most importantly—the repercussions of an actual breach need to be considered. If
you don’t have anything expensive in your home, you might not be too bothered by the thought of someone breaking
in, as there’s little for them to take. If you do have nice things, as well as good homeowners insurance, most stolen
items can easily be replaced. However, the loss of family heirlooms and specific items that hold sentimental value
could result in great emotional stress. There is also the concern that burglaries these days sometimes take a turn for
the worse and end up with the burglar inflicting harm on the residents.
Once all of these questions are answered, it is a lot easier to answer the underlying “how much security is
enough?” question. In some cases, simply locking the door when you’re not at home will suffice. In others, perhaps
locking the door as well as purchasing and activating a home security system would be the best approach. In extreme
cases, the best course might be to move into another house in a better neighborhood.
Whatever decision is made, it was greatly influenced by both the answers to the original four questions and the
answers to the questions that arose from them. Different people who live in different parts of the world or on different
streets within the same community will come to different conclusions.
One of the most easily recognized homes in the United States is located at 1600 Pennsylvania Avenue NW in
Washington, DC. The White House is essentially a home with a larger-than-average home office attached to it. While
it is perhaps one of the larger homes in its neighborhood, it nonetheless has a physical boundary and surrounding
grounds, as evidenced by the tall perimeter fence.
Despite its location in a good neighborhood, the White House has been the scene of several break-in attempts. In
fact, only authorized personnel are allowed inside, making the answer to the second question simple: everyone else.
Given that there have been many attempts to break into the White House—either in a spectacular fashion via a small
airplane or by simply trying to scale the fence and make a run for it—the likelihood that someone will try to break in
again is extremely high. The repercussions of such a breach are extremely serious in all cases.
Given that the stakes are a lot higher at the White House, extreme precautions and countermeasures are
employed there. The entire property is under constant video surveillance, a highly trained armed security force is
present at all times, and many physical barriers are in place to prevent access to the grounds. And these are only the
precautions we know about.
Even though, at its core, the White House is not essentially different from anyone else’s home, the level of
perceived and actual threats to it is obviously much higher than for most homes. Thus, the additional layers of security
are more extreme and thorough.
Application Security Assessment
Application security should be assessed and applied in much the same way as in home security. And, also like home
security, one size does not necessarily fit all. Given unlimited resources, time, and money, all applications could have
all sorts of security layers built into them, making each one as fortified as the next. Unfortunately, unlimited resources
have never been nor ever will be available to any organization. Thus, we have to assess and determine what security
needs to be applied on an application-by-application basis.
To start, let’s consider the same four criteria that were used in assessing the need for home security. What are you
protecting? To elaborate on this, what does the application you’re protecting do? Is it a simple project management
system where tasks are entered and reported on, or does it contain sensitive information, such as Social Security
numbers or account numbers? Are there legislative regulations in place that dictate specific precautions to be
taken? Is the application based on data that is freely available to anyone in your organization? As in the case of home
security, the answers to these questions, as well as to the questions these questions lead to, will determine how much
or how little security you should put in place.
The next question—whom are you protecting the application from?—is almost always answered incorrectly. Most
organizations hold the view that if the application is within a firewall there is no way anyone from the outside can gain
access to the system. While we all know that this has proven to be false in the past, let’s ignore that for a moment.
Much less spectacular, yet much more likely, culprits are your authorized users. These users have already gotten
past the first hurdle—having a valid username and password—because one has already been given to them. Many
applications allow any user to see any record by design. Thus, an authorized user who wanted to steal data would
2
www.it-ebooks.info
Chapter 1 ■ threat analysis
have little difficulty in doing so. In fact, most APEX reports contain a link that allows the user to easily export all of the
data to a CSV file, which can easily be carried out of the office on a USB drive or sent as an e-mail attachment.
Consider, for example, how WikiLeaks works. It is not political to point out that WikiLeaks does not actively hack
into systems and try to steal data. Rather, they are merely a purveyor of data. Authorized users can anonymously upload
sensitive data to the WikiLeaks site, where it is verified and, if deemed legitimate, released to the public en masse. At
some point, an authorized user had to have access to all of this data, typically by way of some sort of export function.
Therefore, it is important to consider trusted, authorized users as part of the set of people you want to protect
your data from, as not only are they the ones most likely to steal it; they also are the ones who have the least difficulty
doing so.
Next, consider the likelihood that someone wants to break into your system and steal data. Depending on what the
system does, the likelihood will either increase or decrease accordingly. Obviously systems with more sensitive data
or more escalated privileges will be more likely targets. Certain organizations are a target for hackers simply based on
their name or business alone. To a hacker, government intelligence agencies and large corporations are much more
attractive targets than smaller organizations or government agencies that don’t have a focus on national security.
You don’t have to be the CIA or Microsoft to take this question seriously, as your data is critical to your business
and so requires adequate protection. When evaluating the likelihood that someone wants access to your systems, be
sure not to compare your concern directly to that of other organizations. Likelihood does not translate well from one
organization to another because it’s a relative concept that needs to be evaluated at the level at which it is applied.
Regardless of your organization’s size and fame, your data is as important to you as the CIA’s data is important to
them, and any precautions you take should be based on that.
Lastly, consider the repercussions of an actual breach or break-in to one of your systems. If a project management
system was compromised, the repercussions would likely be limited, as the data contained therein may be of little
interest or value. But if your application has sensitive financial or classified data, the repercussions could include
financial loss, physical harm, even death.
Your applications likely fall somewhere in the middle of these two scenarios. For example: If a salesperson
leaves to work for a competitor and takes all of his or her contract data, customers may soon start to do business with
that competitor. Or if a student is able to break into the grading system at a college, everyone’s grades may be made
available to the public, thus embarrassing the college and perhaps causing a reduction in enrollment or legal action.
Data and Privileges
In addition to the four factors mentioned earlier, two other key factors need to be considered when assessing the
security required for your applications: data and privileges.
The data on which your application is based is a good place to start, as its level of sensitivity tends to dictate
how much security is required. If your data is not very sensitive, then implementing data access controls may not be
required, as any user can already see any record. But if the data is more sensitive, data access should immediately be
brought to the forefront and a solid plan needs to be designed and implemented.
APEX itself does not provide much in the way of tools for securing data. Fortunately, the Oracle Database does.
Depending on your needs, you can use something as simple as a secure read-only view to secure your data so that
only authorized users can view it. Oracle also provides more robust tools—such as Virtual Private Database—that
can assist in providing secure access to your data. Data security is discussed in greater detail in Chapters 11 and 12.
Smaller applications that don’t do much—say read-only reporting system or a simple data entry application—
may require less attention than an application used to manage user roles or access to other systems, but this is not
an excuse to ignore role-based security. APEX applications have a tendency to start very small and then quickly grow
to something much larger—either in the sheer size of the application or the number of users. Initially, little access
control is needed for many of these applications, but as they grow, access control becomes more and more critical
and increasingly difficult to implement. Thus, no matter what the size or scope of an application, attention needs to be
paid to basic user management and access control.
APEX does provide a basic user-to-role management utility called Access Control. Developers can easily add this
capability to their applications via a wizard, instantly creating a view, edit, and administrator role. While this feature
3
www.it-ebooks.info
Chapter 1 ■ threat analysis
works for basic access control issues, it is somewhat limited in various ways. Chapter 9 addresses additional ways of
managing access control in an APEX application.
Types of Threats
As noted before, threats can be grouped into two categories: preventable and unpreventable. The first group, as the
name implies, can be prevented as long as secure best practices are adhered to, such as cross-site scripting, URL
tampering, and SQL injection. The second group is an unfortunate necessity of doing business. At some point, users
will need access to sensitive parts of the system. This requirement cannot be prevented and in fact is required for the
system to function. Therefore, the only alternative is to provide solid auditing tools so that in the case of a breach, the
perpetrator can easily and unequivocally be identified.
Preventable
Many threats in APEX applications can be prevented with just a little extra effort. Unfortunately, they often go
unresolved due to the lack of time and not understanding how to locate and remedy them.
Preventable threats can be broken out into three different types:
• URL tampering,
• SQL injection, and
• cross-site scripting.
When building APEX applications, APEX typically selects the most secure options for page and shared
components. However, for a number of reasons, those settings can be and often are changed to less secure settings.
Assuming that a page was generated by a wizard and therefore is secure is a bad assumption to make.
URL Tampering
The URL of an APEX application is made up of a colon-delimited string of values that are passed to a parameter “p” of
a procedure called “f”. This is often referred to as the “f and p” syntax. The string of characters passed in is fixed, and
any APEX developer can likely recall the purpose for many, if not all, of the positions. The APEX URL syntax is defined
in Listing 1-1 below.
Listing 1-1. The APEX URL Syntax
Application ID:Page ID:Session ID:Request:Debug:Clear Cache:Item Names:Item Values:Printer Friendly
Given that the definition of the APEX URL syntax is standard across all APEX applications, it doesn’t take a lot
of skill to learn how to manipulate it. A malicious or simply curious user could easily change the values in the URL
bar of a browser and resubmit the page. Listing 1-2 below illustrates a simple APEX URL that references page 1 of
application 100:
Listing 1-2. A Simple APEX URL That Refers to Page 1 of Application 100
http://localhost/apex/f?p=100:1:12432087235079
URL tampering poses one of the most dangerous threats, as it takes zero programming skills to launch an
attack. By changing portions of the URL, a malicious user might gain access to pages or to records that the user is
not supposed to see. Fortunately, both APEX and the Oracle Database employ a number of techniques that can
completely neutralize URL tampering attacks. These are addressed in later chapters of this book.
4
www.it-ebooks.info
Chapter 1 ■ threat analysis
SQL Injection
SQL injection is much more sophisticated than URL tampering, as these attacks require at least a working knowledge
of SQL. An SQL injection attack is designed to pass in actual SQL fragments that then get executed rather than inserted
into the database as data. SQL Injection attacks can range in severity from minor to major, depending on a number of
factors. Consider the SQL in Listing 1-3 below:
Listing 1-3. An SQL Statement with a Potential SQL Injection Risk
SELECT
customer_name,
cust_first_name,
cust_last_name
FROM
demo_customers
WHERE
customer_name = '&P1_SEARCH.'
By using the APEX &ITEM. substitution syntax, the developer has introduced an SQL injection risk, since APEX
will replace the string &P1_SEARCH. with its corresponding value before it parses, binds, and executes the query. If the
user entered something like ACME, no danger would be present and the query would execute as expected.
However, if the user was more malicious and entered ACME' OR 'x' ='x for the value of P1_SEARCH in an APEX
form, the SQL would actually be modified before it was parsed and executed by the database. The actual SQL that
would be passed to the database and run is illustrated in Listing 1-4 below.
Listing 1-4. The SQL That Will Be Executed if a Malicious Value Is Passed In
SELECT
customer_name,
cust_first_name,
cust_last_name
FROM
demo_customers
WHERE
customer_name = 'ACME' OR 'x' = 'x'
Notice that the WHERE clause now has an OR condition that will check for one or the other conditions to be true.
If the customer_name is, in fact, ACME, then that record will be returned. If it is not ACME, then the second part of the OR
will be evaluated, which will always be true. Thus, all records from the demo_customers table will be returned, which
clearly was not the intent of the original SQL.
Fortunately, APEX applications typically don’t face a lot of SQL injection risk, largely due to the fact that when
referencing APEX items in SQL or PL/SQL regions, most developers use bind variable syntax. When bind variable
syntax is used in APEX, the values of items are not passed in until after the query executes, making it impossible for
them to influence the SQL itself.
Simply using bind variable syntax everywhere does not make you one hundred percent immune from SQL
injection attacks. If bind variable syntax is used in conjunction with dynamic SQL, take care to ensure that the actual
SQL is evaluated before the APEX items are. Chapter 7 covers SQL injection in greater detail.
Cross-Site Scripting
Cross-site scripting occurs when a malicious user at a high level passes in a fragment of JavaScript that is later
executed by the same or other users in the application. Think of it as a type of SQL injection for JavaScript. Somewhat
5
www.it-ebooks.info