Your Unauthorized Guide to Relational Database Security
This book will help the DBA to assess their current level of risk as well as their existing security posture. It will then provide practical, applicable knowledge to appropriately secure the Oracle database.
Solutions in this Chapter:
- A Brief History of Security Features
- The Regulatory Environment Driving Database Security
- Major Data Theft Incidents
- A Step-by-step Approach to Securing Oracle
A senior database manager
at one of the world’s largest banks once told me that the best way to secure
Oracle is to unplug it from the wall…and he is probably right. In fact, this
holds true for nearly every networked application. Unfortunately, for many of
us turning off the database is not an option; we must find another way to
secure our systems. New technologies and services drive revenue for businesses,
particularly those that provide a tailored experience to customers. These
systems frequently require storing, processing, and offering access to personal
information. No different are the systems that store corporate secrets or
financial information. Much of the data they store is extremely sensitive, but
it all needs to be readily and easily accessible nonetheless. This creates a
significant data security challenge, one we will address in detail throughout
This book is designed to
help you establish a practical security program for Oracle databases. We will
create a means for measuring and assessing the security of your databases, and
give you tools to create a security scorecard for each of your Oracle
databases. This is not a Database Administrator (DBA) handbook—far from it.
Instead, we are writing to the entire database security community, which
includes DBAs, but also includes Information Technology (IT) security staff,
auditors, and even the Chief Information Security Officer (CISO).
Oracle is by far the most
widely deployed database in the world. It is a central component of critical
systems across nearly every major industry that thrives today. In the financial,
medical, telecom, infrastructure, government, and even the military, the
databases and the data within them are the business. Over the last several
years, databases have become a frequent target of cyber attacks. At first,
these attacks were primarily intended to cause disruptions in business and gain
notoriety among the hacker community. This has changed dramatically with
database attacks increasingly focused on extracting the sensitive and valuable
information from systems in an attempt at monetary gains by the attacker.
Stealing personal information for use in identity theft, stealing credit card
numbers to make purchases at will, stealing corporate secrets to take back the
competitors edge, these are today’s drivers behind attacks on databases.
Because of Oracle’s dominance in the marketplace, many of these attacks have
been focused on Oracle systems. Oracle has been an unwilling conspirator in
these attacks, having built a system riddled with vulnerabilities for the
attackers to go after. At the same time, Oracle has built the most complete
suite of security features in any commercial database. This book will show you
how to properly use these features to ensure your databases remain available
and secure in a diverse and rapidly changing environment.
A Brief History of Security Features in Oracle
The Oracle database was born out of a
called Project Oracle, run by the Central Intelligence Agency. Given the
initial customer, security was a serious concern from day one. If you go back
to the initial release of Oracle from 1979 (actually dubbed version 2) the
beginnings of today’s security system were already present. Those were the days
when databases were housed in physically protected secure rooms, with no
outside or network access. At the time, there was no means to access Oracle
without being on the server itself. The concern about an outside attacker
breaching the system was hardly even considered; however, the threat from an
insider was a present concern. It is quite likely that this threat brought
about some components of the often confusing and misleading error message
system that remains in use today. For example, try selecting from a table or
view that exists in Oracle that you do not have permissions to access. Oracle
will respond with the following:
SQL*Plus: Release 10.2.0.1.0 – Production on Sat Sep 15 13:18:52 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from sys.user$;
select * from sys.user$;
ERROR at line 1:
ORA-00942: table or view does not exist
Why tell the user that the object they requested does not
exist, rather then provide a message telling them they do not have the proper
permissions? It’s simple. If a user does not have rights on an object, that
user has no reason to know that the object exists. Handing out any more
information than is absolutely necessary has never been good for security,
regardless of what is being secured.
Passwords were present in version 2 as well, but the password
management system was extremely basic. Overall, there was a minimal need for
security when Oracle first got started, but underpinnings were put in place. As
the technology world began to change, and the protections offered by physically
securing the servers were no longer sufficient, Oracle changed as well and
began to implement a complex array of increasingly sophisticated security
At first, controls on user rights and privileges were nearly
non-existent. Within a few years, Oracle introduced the concept of roles. Three
roles were rigidly defined: CONNECT, RESOURCE, and DBA. Users could be assigned roles, but these roles could not be
modified, nor could custom roles be created. All privileges were assigned
directly to the roles, never to users. This continued until version 6 of the
database was released, when Oracle introduced the capability to grant
privileges to users. This was a significant improvement in the security system,
allowing administrators to have much more granular control in giving out access
to data on an as-needed basis. The CONNECT, RESOURCE, and DBA roles remained
statically defined until version 7 of Oracle was released and the concept of
user-defined roles was introduced. User-defined roles revolutionized the
privilege control system in Oracle, allowing for efficient and flexible
management of permissions by allowing them to be grouped together and assigned
or removed in bulk. The Oracle7 role system remains in use today and will
likely not be changed with the future release of Oracle11g. We’ll cover using
roles and privileges in detail in Chapter 4.
One more major development in the privilege control system came
in Oracle8i: invoker rights procedures.
Stored procedures have been around for quite some time, and were commonly used
as they are today, to group complex functionality into a single procedure and
then offer easy access through a simple interface. For a long time, all
procedures were run with definer rights.
This means that when a procedure is called, it runs at the privilege level of
the definer (the user who owns the procedure), often the DBA. Invoker rights procedures are different.
They run at the privilege level of the invoker (the user who ran the
procedure). This offers flexibility to database developers who can now create
procedures that access data and database functions without the worry of a user
getting access to data they should never see.
Until version 5 of the database, Oracle offered no networking
features. Database access was only permitted by directly connecting from the
host operating system (OS). This required users to be able to access the server
on which Oracle ran, making it impossible to implement any kind of distributed
computing system. In version 5, SQL*Net 1.0 was introduced and with it came a
dramatic change in how Oracle was used and how it was secured. It was no longer
necessary to access the host OS to log in to the database; all that was needed
was a network connection and some client software and the database could be
accessed remotely. The introduction of SQL*Net had an important side effect.
Users could now interact directly and exclusively with Oracle’s immature user
authentication system and could completely bypass the mature authentication
features offered by the database host OS.
Oracle Advanced Networking Option
The Oracle Advanced Networking Option (ANO) was released with
version 7.3 of the database in 1996. ANO was the first Oracle product to offer
strong security for a networked database; its two primary features were network
security and single sign-on.
In Oracle’s terms, network security means both confidentiality
and integrity protection. The confidentiality part ensures that nobody can read
the data as it crosses the network, while the integrity protection part ensures
that nobody can change the data as it moves. Confidentiality was implemented
using the symmetric key encryption algorithms Ron’s Code 4 (RC4) and Data
Encryption Standard (DES). The integrity protection was implemented with a
cryptographic hash or message digest function called Message Digest 5 (MD5).
Oracle chose strong algorithms and made attacks against the data as it
traverses the network very difficult to execute.
In an attempt to simplify password management for
organizations, Oracle began to integrate with third-party providers of single
sign-on (SSO) authentication systems. The intention was to integrate Oracle
databases into enterprise SSO systems, allowing users to set a single strong
password in one place, and then use the same account to access all systems.
This allowed users to remember only one password, and it allowed administrators
to force the use of strong passwords on their users. Furthermore, SSO makes
user provisioning and password management simple, as all credentials are
managed centrally. In the initial release, Oracle offered support for a number
of SSO systems, including Kerberos, CyberSAFE, SecurID, Biometric, and DCE
The i in Oracle8i
Oracle ushered in the Internet era with the release of
Oracle8i in early 1999. Targeted at the eCommerce marketplace in the heat of
the .com boom, Oracle had the right product at the right time to claim an
enormous share of the online retail market. Touted as a platform for developing
Internet applications, Oracle8i was built to face the Internet and store
sensitive data. This represented a shift in how and where databases were used.
Hackers started finding databases directly accessible from the Internet. Oracle
hacking went mainstream.
Network security continued to be a strong suit for Oracle. They
renamed SQL*Net as Net8 and renamed the Advanced Networking Option to Oracle
Advanced Security (OAS). Major enhancements were made to both the network
security and single sign-on components of OAS. Oracle added support for Secure
Sockets Layer (SSL) (network authentication, encryption, and integrity
protection) and Remote Authentication Dial-in User Service (RADIUS)
(centralized user authentication). Both are standards-based systems that had
been publicly reviewed, offering assurance to companies who didn’t want to rely
on Oracle alone to attest to the effectiveness of their security protocols. In
the releases since 8i, Oracle has continued to offer enhancements and upgrades
to the OAS product.
Oracle has offered auditing features in the database since
very early on. Capabilities were provided to audit log-ins, object access, and
database actions (defined as anything that makes a change to a database object,
such as CREATE TABLE or ALTER DATABASE). Each event would be captured and
labeled as successful or failed. Early versions of Oracle auditing were
somewhat limited. Database actions could only be audited by role (CONNECT,
RESOURCE, and DBA), not by individual user. SYSDBA activities could not be
audited at all. Audit data was stored inside the database in the SYS.AUD$
table, and needed to be periodically truncated by the DBA. However, Oracle has
supported auditing of access to SYS.AUD$ from the beginning.
With the Oracle7 release, database triggers were introduced.
This new functionality was useful in many areas, audit in particular. The
built-in Oracle auditing system records only those events that have occurred;
it does not record before and after values for data changes. Triggers could be
used to do just that, triggering on an update or delete to capture the old
value before replacing or removing it. While a trigger-based audit system had
to be implemented manually, it was a useful and powerful addition to the native
auditing capabilities of the database.
Oracle7 also brought about the capability to write audit data
directly to a flat file instead of into the database. This provided administrators
with needed flexibility and allowed for tighter access controls on the audit
data. The restrictions on database action auditing were lifted, allowing
auditing by an individual user instead of by role.
Oracle8 came with its own set of improvements to the audit
system. Things got much more granular with the capability to enable auditing at
the object, schema, and system level. Oracle also added several views to allow
for simpler review and analysis of the stored audit data.
Fine Grained Auditing
Fine Grained Auditing (FGA) was first introduced with
Oracle9i. A major improvement in Oracle’s auditing capability, the first
release of FGA focused on auditing of SELECT statements. Previously, Oracle
auditing could record that a user had read from a table or view with a SELECT
statement. While it was possible to know that a SELECT statement was issued and
who issued it, it was not possible to determine what data was selected. FGA was
designed to collect this information. Each event logged by FGA detailed the
user, date/time, schema, table, columns accessed, the exact SQL statement
issued including bind variables, and a system change number. This was a level
of detail never before seen in a database auditing system, allowing for
complete recreation of each audited event. By logging the exact SQL statement
executed and the system change number, it was possible to determine exactly
what data had been returned by the query. A DBA could use a flashback query to
effectively restore the database to the point in time when the query was first
executed and then run it again. The results would be the same, proving what
data had been read from the system.
Oracle took this a step further by allowing administrators to
audit access to individual rows by evaluating the row against a set of
conditions supplied when auditing was configured. This allowed for auditing
access to sensitive data only. The DVA could set up a column to indicate
sensitivity, and then configure the audit system to capture access only to the
rows labeled sensitive. This powerful feature had only one major shortcoming;
it worked for SELECT only. There was no granular auditing of the Data
Manipulation Language (DML) commands INSERT, UPDATE, or DELETE.
With the release of Oracle10g came another round of major
improvements to the built-in auditing systems. FGA was enhanced with the
capability to audit DML statements, providing the same level of detail for
INSERT, UPDATE, and DELETE statements as had previously been supplied only for
SELECT. The improvements in 10g were not limited to FGA, in fact, the entire
audit system was overhauled to make all audit capabilities more granular and
FGA-like. By setting the audit_trail
parameter to db_extended, standard
Oracle audit will capture both the exact SQL text executed, along with the
values for any bind variables used for any query run against the database.
Oracle DBAs now have a powerful mechanism to track exactly what their users are
doing in the database.